[solved]Delete A MatrixRow Element On The Design Of A Report
May 2, 2008
Hi,
I have a report in which a developper has put a matrix which has 4 MatrixRows.
I want to delete 2 of them.
When I righlt click a row and click Delete there is message about rows groups and columns groups and the row is still there.
When I right click the matrix and go to properties / group, i see Row group = [Static group] and I cannot modify it.
How can I delete a MatrixRow ?
a+, =)
-=Clement=-
Configuration :
SQL Server 2005
edit :
I found the solution : click on the cell in the 2nd column, not in the first, then right click and delete.
I would suggest Microsoft IT s to put less humor in their softs (you click delete in the wrong place, you get a weird message instead of a 'try another cell' )
Need some advice solving a little problem I have with my database!
Current setup:
I have a person table that is made up of 39 columns. I also allow for person records to be deleted but I do this by having another table I call LogicallyDeletedrecords. This table is made up of the PersonId, Reason for deletion/suppression and a date time stamp. To access Live records I created a view based on my Person table which contains a WHERE clause to exclude records that exist in the LogicallyDeletedrecords. Similarly, I have another view DeadPersonData which contains Person records that have been removed. Hope it all makes sense so far! Now on to my worries!
The problem:
My Person table contains 9+ million records. The LogicallyDeletedrecords table has 500k+ but I anticipate further growth over the coming weeks/months. My worry is that my LivePersonData view will be too slow to access as my LogicallyDeletedrecords table grows. What’s more, as part of my Load routine, I have to make sure that Person data loaded on to the system is excluded if that same person exists as a deleted member. Both of these actions could slow down my system as the deleted table grows.
My thoughts:
I’ve been thinking of physically deleting dead Person records from my person table (possibly creating an archive table to hold them). But then if I delete them how do I cross check the details when new Person details get loaded?! As I said, my current LogicallyDeletedrecords table holds the PersonId, ReasonDeleted and CreationStamp. The only way is to add further columns which I use to match Person Details?
this is sanjeev, i have SSIS package, using my c# program i want to add one execute package task to this package's sequence container.
it is creating the new package with out any probelm. but when i opened the package and try to move the newly created exeute package task it is giving the following error.
the element cannot be found in a collection. this error happens when you try to retrieve an element from a collection on a container during the execution of the package
I have a problem with one report on my server. A user has requested that I exclude him from receiving a timed email subscription to several reports. I was able to amend all the subscriptions except one. When I try to remove his email address from the subscription I receive this error:
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
For more information about this error navigate to the report server on the local server machine, or enable remote errors
Online no help couldn't offer any advice at all, so I thought I'd just delete the subscription and recreate it again, but I receive the same message. "Okay, no problem, I'll just delete the report and redeploy it and set up the subscription so all the other users aren't affected", says I. "Oh, no!", says the report server, and then it give me this message:
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() --- End of inner exception stack trace ---
What's even weirder is that I'm the owner and creator of the report and I'm a system admin and content manager on the report server and I set up the subscription when the report was initially deployed. Surely I should have sufficient rights to fart around with this subscription/report as I see fit?
I have rebooted the server, redeployed the report, checked credentials on the data source and tried amending and deleting from both the report manager and management studio but still I am prevented from doing so.
I have a very large table that I am trying to partition and use to reduce maintenance overhead as well as improve performance. The table contains about 12 years worth of data but only the most recent years is inserted/updated/deleted from thru the app. I created partitions on a computed(persisted) column which holds the "year" value derived from a date column. I have created the partitions with all the default set options, and the stored procedure which performs the delete against this table also was created with no special set options(basically database/session default). Yet, every time I try to run the proc to delete data thru the app, I get this error:
Msg 1934, Level 16, State 1, Procedure xxxx, Line 118 DELETE failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
I've tried setting ANSI_WARNINGS on and off when creating the proc, inside the proc etc.., its always the same error whatever I set the option to.
I have a couple of reports that I created within the SQL Report Designer, I would like to give the ability to the end user of C# Winform application to be able to make modification to this .rdl file using the Report Builder, however when I try and launch the report that I created within the Report Designer using the Report builder I get the following error..
Microsoft.ReportingServices.ReportBuilder.ReportModel.Report: The report 'http://localhost/ReportServer/MyReport)' cannot be opened because no data source is associated with it. Associate a model data source with this report and try again.
How can I resolve this situation.
I'm not sure if my approach is the best or if I'm I should be looking at the solution from another approach
there are two tables involve in replication let say table1 and replicated table is also rep.table1.
we are not deleting records physically in table1 so only a bit in table1 has true when u want to delete a record but the strange thing is that replication agaent report that this is hard delete operation on table1 so download and report hard delete operation and delete the record in replicated table which is very crucial.
plz let me know where am i wrong and how i put it into right way.
there is no triggers on published tables and noother trigger is created on published table.
I'm am on the report server as administrator. How can I cleanly delete a report? How can I see what folders and reports I have under the reportserver?
I'm looking under sql management studio and see the reportserver db, now what? I open up the IIS virtual for reportserver, now what? Nothing seems to point to a folder where I know I am able to deploy to and run reports from.
I'm trying to create a report like following format. Any idea how can I design it? 'ID', 'Title', 'Answer' and 'Comment' have to span multiple rows which will list 'Response'.
---------------------------------------------------------------------------------------------------------------------------- ID Title Answer Response Comment ------------------------------------------------------------------------------------------------------------------------------ 1 | Q1 | B | A | aaaaaaaaaaaaaaaaa | | | B | aaaaaaaaaaaaaaaaaaaaaaaaa | | | C | aaaaaaaaaaaaaaaaaaaaaaaaa | | | D | --------------------------------------------------------------------------------------------------------------------------------------- 2 | Q2 | C | A | bbbbbbbbbbbbbbbbbbbbbbbbbb | | | B | bbbbbbbbbbbbbbbbbbbbbbb | | | C | bbbbbbbbbbbbbbbbbbbbbbb | | | D | ---------------------------------------------------------------------------------------------------------------------------------------
I've got this data source that returns member information.. great.
I've got a function that when passed the member number and a month/ year returns days participating in the club.
I need to build a report that will output some of the columns for the member, but then report other information for 6 months for each member that all calulated based on days particpating for that month. Which 6 months will be dertimed by a single parameter passed to the report for starting month/year... basically the next 6 months.
Is a matrix report a good option for this? Or should I just have the function called 6 times in my data source? Ideally I layout the report for one month and then *somehow* join in my data source for 6 iterations where month is 1 through 6.
Right now I'm having trouble wrapping my mind around how and where to join the data? Especially if matrix and I have two data sources. I'm also not sure I can call my function from cells in the matrix or if I can even have mulitple data buckets in the matrix. I'm tempted to do this the only way I know how, but I suspect there is a better way.
I've never built a matrix report in ssrs, any information or help is appreciated.
I have developed an reporting solution for a customer with reporting services and using report builder. When he connect to http://localhost/reports , he see the page like
I have a report for a Quote print that has 1 table with 1 group in it by Quote #. The detail level is showing the line items on the quote and a second detail line for a sub report to show any notes. If the quote has 1 line on it, I am always getting a page break after the sub report even if it doesn't display anything.
The report shows the following:
Note each of these will contain a rectangle that the text boxes are displayed in unless it is a sub-report then no rectange.
Group header 1 - Address information Group header 1 - contains a sub report for some notes Group header 1 - Opening paragraph Group header 1 - Blank line Group header 1 - contains the line headings Detail - contains the line details Detail - contains the sub report for line notes Detail - blank line for spacing Group footer 1 - hidden based on expression Group footer 1 - hidden based on expression ** One of these 2 lines will print based on the expression Group footer 1 - sub report for more notes Group footer 1 - Closing paragraph Group footer 1 - Signature area Group footer 1 - sub report for last page of terms / scope notes. NOTE: This sub report also contains a table and the table has a page break before so that I can get these on another page.
So, after # 8 above there is a page break. The remaining footers before # 14 easily fit on the rest of the page. The group 1 also has a page break at the end so it breaks between quotes. I have tried to insert a page break on the rectangle for # 13 and remove the page break on the sub report in # 14 but although everything fits it doesn't page break before the sub report.
It looks like it always page breaks after the last page in the sub report.
To maximize the number of names and phone numbers we can fit on a page, we'd like to have three or four columns of LastName, FirstName, Number on each page. The first column starts with the A's and goes down until the bottom of the page then goes back up to the top of the page in the second column. When the maximum number of columns (probably 3 or 4) is reached for one page, it starts again in the first column of the second page.
Any design suggestions for the best way to architect this report using SQL Reporting Services 2005 are appreciated!
My website features a catalog of roughly 50,000 items, each of which may be appear in a list of search results or in a detailed view. There are counters on the pages that update totals for such appearances and track other item-specific information in several tables in a SQL database. The catalog of items changes frequently, so the list of item IDs is never exactly the same from month to month.
I've been asked to produce a monthly report of this data for each of the items in the catalog, with reports for the current and previous months (for many years) accessible at all times. Some -- but not all -- items are useful for one purpose or another and so can be considered as belonging to a group of items. Although I have not yet been asked to create a report that aggregates the values for all Group members into a single report for that Group, I can clearly see it would be valuable and will be requested soon.
To ensure the report captures the data for an entire month, it must be run at the very end of each month. That means I will need to run the report using a Schedule that kicks off the process at 12:01am every 1st of the month. The report must be processed and stored for later retrieval and rendering on demand.
Considering the number of items and the indefinite length of time the report data must be retained, my question is really what's the best way to set all this up?
Should I create a report for each item separately? That would mean the scheduled task would have to somehow discover the current list of item IDs (which is available via query from the database) and create and process (but not render) a report for each (passing the item ID as a report parameter?), adding it to the report history. Although each report would be small take only a short time to run, overall that seems like it would take a long time to run and create a huge number of reports to store each month.
Or should I create a single 'master' report that contains all the data for every item for the month, and then use the item ID as a filter on the data when it is rendered? While that means only one report is created each month and added to the history, it would be a much larger report and take much longer to run (with more potential for timeouts and errors to scuttle the whole report). It also means all the data for the entire report has to be loaded every time the report is rendered, even though only 1/50,000 of the data (the data for 1 of the 50,000 items) will actually be viewed with any given rendering. But that would seem overly cumbersome, slow, and wastefully band-width intensive.
Any alternatives, suggestions, considerations, etc. -- all welcome!
Does anyone else have problems with the speed of the design environment for SSRS reports? We are using visual studio to create and manage hundreds of production reports (Oil and Gas).
It literally takes 5 seconds for the design environment to react to each change in the report layout. For instance, a common change is to reposition a text box, change the length, text, font size, font weight etc...:
Grab the text box - 5 seconds until designer responds - then reposition
Grab the edge to change the length - ditto
change text - 5 seconds for designer to "save" the change and allow next action....
blah - blah - blah.... So, performing the simple change above takes at least 30 seconds in addition to whatever time it took to edit the text box.
I've talked to the other developers using SSRS here and they all report that's "just the way it is".
Is this normal or is there an environment issue/setting we are overlooking.
On our project we export SSRS reports to Excel. Report Definition Language (RDL) enables you to use several measurement units (for example, inches, pixels, centimeters, and points) to specify position and size values. However, Excel uses only points. Therefore, the SSRS Excel rendering extension converts the height and width of the table, the heights of the rows, and the widths of the columns to points.
This process may include rounding some values. In this situation, the table height or width and the sum of the row heights or column widths are different. To compensate for the difference, the SSRS Excel rendering extension inserts a small row or column to the workbook.
To prevent that ‘hidden’ row or column, we configure the table height and width to a value specified in "pt" (points) rather than "cm". 1 cm is about 28 points. Is it possible to configure SSRS to use points by default while designing a report?
I want to have two lines of column headers.( with 7 columns in parent columns header)
and 7 columns in child column header.
The first column header will show a parent record.
And after the parent record i want to show the next child related horizontal column headers and will show all child record related to the above parent record.
its a one to many: 1 parent record and below all child records for that parent record.
can i use subreports controls to show all child records of the parent.
I have a report that I need to develop in landscape format. Also I need it to be landscape when converting to PDF and taking printouts. let me know the exact height and width measurements in inches for the report and report body?
The problem is solved but still I would like to know what is going on behind the scenes. I was always thinking that the native client was involved and not oledb.
Using the Export/Import wizard you still end up going via SQL Server Agent that's where the Credential/Proxy items show up.
When I am working on an Integrations Services project and I open a Reporting Services file, it displays it in the Code view. Is there a way to display it in the Design view? Or do I have to close the project and open up a Reporting Services project?
I'm just beginning to use DTS. I have a test table which has only two column, char (10) and decimal (5,0). I want to do a simple transformation by adding the second column by 100 with the following statement, but I receive a type mismatch error!
Function Main() DTSDestination("a")=DTSSource("a") DTSDestination("b")=DTSSource("b").Value + 100 Main = DTSTransformStat_OK End Function
or
Function Main() dim x DTSDestination("a")=DTSSource("a") x = DTSSource("b").Value + 100 DTSDestination("b").Value = x Main = DTSTransformStat_OK End Function
Hello friends I need a very ergent help!!!!!!!!!!!!! Problem: my sql server on production had everything grayed out, when I want to administer this server as a SA. eg: I can't add users, I can't backup and so on. but the database is still running and users are accessing it. This SQL database is accessed by an application(it can also create users on the backend). I don't know how it happend ( i cant even get to errorlog). I beleive somehow sa user rights were taken away from him. Can anyone tell me how to restore sa with all rights.
Please help me on this matter, because I have to do lots of important tasks on this server.
Test1 that lists all the training courses that i can count to find out the total as below.
select count (distinct Training_Course) as total from Test1
Then Test2 lists all our customers and courses they have attended. I count the courses attended and then group by their ID.
select Cust_id, count (Attended) as TotalAttend from Test2 Group by Cust_id
What i am now trying to do, without any luck, is find out which Customers have attended all training sessions by comparing the two queries and only bringing back the cust_id where it matches the total count from the Test1 query.
Make any sense? Any help/suggestions gratefully recieved.
I'm very rusty with my SQL, and could use a little assistance on building this query. Thanks for taking the time to help me. It should be a relatively simple Count() query but I'm not getting the right results for some reason and I'm hoping somebody can point out my error to me.
I'm attempting to count the number of times each m_id is returned after running this query:
SELECT m_id FROM taglink WHERE m_id <> '25' AND t_id IN ( SELECT t_id FROM taglink WHERE m_id = '25' )
which returns: m_id 33 34 34 35 35 35 36
I want to count the number of times that m_id is returned so that the results of my query will be:
m_id | count 33 | 1 34 | 2 35 | 3 36 | 1
In my attempt to do this, I run the following query:
SELECT m_id, count( m_id ) AS "count" FROM taglink WHERE m_id IN ( SELECT m_id FROM taglink WHERE m_id <> '25' AND t_id IN ( SELECT t_id FROM taglink WHERE m_id = '25' ) ) GROUP BY m_id
The problem I'm having is that the query returns:
m_id | count 33 | 3 34 | 2 35 | 3 36 | 3
It seems to return the count of "3" for fields that should count "1," but count correctly if the fields count "2" or "3."
Perhaps somebody can spot my error... I can't seem to wrap my brain around this one. Thanks so much for your time!
I'm developing a Custom Report Item and would like to fill my properties dialog in design mode with real data. I have the name of the data source and the command text for the corresponding report dataset, read from the report design.
Is it possible to create an ado.net dataset so that I can display and use real data in my properties dialog box? First problem I have is to get the connection string from the data source string. I found no docs for that, especially the Microsoft.ReportingServices.DataExtensions.ReportDataSource class is not documented. Am I on the right way with that?
I have a very frustrating problem. But given that this is a development forum, I'll keep that for my 'Envelope Lickers Rehab' and instead talk about an issue I'm having.
I have a very large query (line-wise) that executes in less than a minute when run from the SQL Management Studio as well as via Excel Services.Â
It is a stored procedure with one parameter. When I try to prime the designer with this query it gives me the following message:
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated."
I've considered cracking open the xml source and manually creating the fields, but I'd rather not go there.
I have several maintenance plans setup and working in SQL/2005 (sp2 9.0.3042) on several servers; however, the .txt maint plan output files will not delete on any of the servers. The backups run fine, the old database and transaction log backup files delete fine (.bak and .trn); but, the .txt files won't go away unless I delete them by hand. I tried copy/paste the scripted command (xp_delete_file) and it runs with no errors but does not delete the old .txt files. ( This server was upgraded from SQL/2000 -
