SQL Server Reporting Services Table of Contents
Since there is no table of contents functionality in SQL Server 2000 Reporting Services, I had to develop a maintainable approach that worked well within the domain of Reporting Services as a technology. The Table of Contents requirement is as follows.
The client required a table of contents within their report to include a listing of Report Names with the respective page numbers that they fell on. So the output would be as follows:
Table of Contents
Report 1 Pages 1 – 23
Report 2 Pages 24 – 45
Report 3 Pages 46 – 55
The report consisted of the table of contents data region as well as various sub reports that were included in the main report. For each sub report, the total pages that comprised the report had to be obtained and incorporated into the table of contents. Unfortunately, the sub report item does not expose any properties, nor is there a direct way to get data in and out of the report when the data being sought is in the page header or footer. So the approach became to get the data for each report regarding total pages (from Globals!TotalPages) into the table of contents data region on the master report.
Let the Hacking Begin
What I did was use Reporting Services built in capability to consume a .NET assembly and authored one which had a single method. The method returns an integer and accepts as parameters the report name, and the number of pages. It then logs this information to a database (could be an XML file also) to a TableOfContents table. This table is then polled when the master report runs to create the TableOfContents data set. The .NET Assembly is called from the page footer from a text box and passes in the report name along with the Globals!TotalPages variable. Here is the code for the text box in the page footer region:
Code.ops.LogReportAttributes("Northwind Sales Sample", Globals!TotalPages)
The problem with this is that the sub reports need to be executed before the master report. This is easily remedied by using SQL Server Reporting Services to process the sub reports to allow them to write to the database’s TableOfContents table. Then the last step is to process the master report which will pull this information and then run the sub reports again. To mitigate the 2 time execution of the sub reports, I plan to have them cached on the Reporting Services Server.
The assembly must be GAC’d in order to be recognized by Reporting Services.
Hope this helps!