November 14, 2007 at 1:48 pm
We have a report that needs to list all person(s) appointments for a given period of time. The stored procedure the report is using is very straight forward and it returns a dataset of all appointments ordered by person and start date. The tricky part is to list 1 appointment with details on the top of each page and all other appointments but the one that's on top displayed on the bottom. So that, if there are ten appointments total in a given period of time for a person, page 1 will have appointment 1 on the top and appointments 2 to 10 on the bottom. Page 2 will have appointment 2 on the top and appointments 1, 3 to 10 on the bottom, and so on, for all people that have appointments in a given time period. The developers working on this report say there is nothing that can be done in reporting services and tell me it all should be constructed in a stored procedure by looping through appoinments and writing all combinations to a temporary table and then doing a select from it. I can hardly believe that there is no mechanism of supressing rows in reporting services. Is there any way to do it besides changing the stored procedure? Thank you
November 14, 2007 at 4:17 pm
It can be done. In RS, you have two options that I can think of right now.
The easiest would be to use a report and a sub-report. Your main report would be the entire list and the sub-report would be the list without the current record. From a performance perspective, this would run the sub-report query for each row in the main query (pretty close to what you would have in the stored procedure with a cursor). The big down-side to this would be if you need to export this thing to Excel - you would be out of luck since your sub-report would not appear.
Secondly, you could create a report with a datagrid, using the data set as the source. Within this data grid, you place another data grid and use the same data set. On this grid, you can set a filter that filters out the row you do not want to repeat. Getting this to format correctly may be a bit of a trick, and you also make have a bunch of Excel export problems. This is pretty doable and the performance is probably going to be reasonable.
Also, your data set that you have to generate would not need to use a cursor. If you returned the current data set into a temp table, you can simply cross join from the temp table to itself where the task is not the same and you would end up with the cartesian product without the same record (if you followed that, great). If you wanted to avoid the temp table, you could actually do this with a CTE or even a sub query as well. Honestly, doing this in your stored procedure and using the temp table will probably yield the best performance and be easiest to build. If your developers have trouble making reporting services work with a stored procedure that returns data using temporary tables, I can give you some tips on that as well.
November 15, 2007 at 1:07 pm
Michael, thank you very much for your reply. I think we have a requirement that all our reports should have an ability to be exported into Excell. We can't use CTEs as well because the requirement is: it should work both in 2000 and 2005. But your self join suggestion seems to be a great idea - I haven't thought about generating a list of appointments this way! Thank you again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply