Table of Contents for PDF/Printed Report in SSRS

Question

Here's what I do know:

1. I know that document maps and bookmarks can be utilized on screen but not in a pdf.
2. SSRS does not have an out of the box ability to generate a printed Table of Contents with Page Numbers to a pdf.
3. I know that a TOC can be generated by exporting the document map to Word. This approach will not work for my situation.
4. Global variables can store page numbers, but they can't be utilized within the report body, only headers and footers.

The closest thing I can find is a reference to create an external assembly or dll to make it work. This link Eric Charran's blog says it can be done, but doesn't post any code. I might be able to limp my way into replicating a .net assembly if someone can give me the code for the method, but I've never created one and have very limited knowledge of VB and no C# knowledge. I have only ever referenced a dll from within SSRS.

Based on the number of people searching for this solution, anyone who would write this external assembly would be helping a ton of people! And I know I would be very grateful.

Thanks, in advance, for your attention.

Answer 1

This is what I was able to do with the help of colleagues and coworkers. It works well.

1. Create Main report with 1 sub report per section listed in the TOC

2. Create this table to hold the data the dll needs.

   CREATE TABLE [dbo].[tbl_TOC](
   [RID] [int] IDENTITY(1,1) NOT NULL,
   [TOCExecutionID] [varchar](50) NULL,
   [AssessmentID] [int] NULL,
   [ReportName] [varchar](50) NULL,
   [GlobalsTotalPages] [int] NULL,
   [LoadDate] [datetime] NULL DEFAULT (getdate())
   ) ON [PRIMARY]
   

3. Create this stored procedure to add the data necessary. We’ve got a unique ID being sent from the interface that runs the report. We are also using another parameter (AssessmentID) that is unique to our report.

   create proc rpt_dsTOC
   @TOCExecutionID varchar(50)
   as
   if OBJECT_ID('tempdb..#temp') is not null drop table #temp
   create table #temp (
       ord int NOT NULL,
       ReportName varchar(50) NULL,
       PageCnt int NULL,
       PageNo int null )
   
   ;with ctePageCount as (
   select distinct t.ReportName
       , t.GlobalsTotalPages PageCnt
       , t.TOCExecutionID
       , t.AssessmentID
   from        tbl_TOC t
   where        TOCExecutionID=@TOCExecutionID
   )
   insert into #temp
   select case when ReportName like 'Plan%' then 1  --List all of the          names of the subreports here
           when ReportName like 'Busin%' then 2
           when ReportName like 'Threa%' then 3
           when ReportName like '%Manag%' then 4
           when ReportName like '%Monit%' then 5
           when ReportName like 'Pande%' then 6
           when ReportName like 'Emerg%' then 7
           when ReportName like 'Key%' then 8
           when Reportname like 'Netw%' then 9
           else 10 end ord
       , ReportName
       , PageCnt
       , 0 PageNo
   from ctePageCount
   
   --This section calculates the pagenumber by using the total number of pages in each section
   
   declare @run int = 3
   declare @ord int, @ReportName varchar(50), @PageCnt int, @PageNo int
   declare     c cursor for
       select    ord
       , ReportName
       , PageCnt
       , PageNo
   from #temp
   order by ord;
   open c
       fetch next from c into @ord, @ReportName, @PageCnt, @PageNo
       update #temp set PageNo=@run
       set @run = @run + @PageCnt--5
   while @@FETCH_STATUS = 0 --EOF
   begin
       fetch next from c into @ord, @ReportName, @PageCnt, @PageNo
       if @PageNo=0
       set @PageNo = @run
       update #temp set PageNo=@PageNo where ord=@ord 
       set @run = @PageNo + @PageCnt
   end
   close c
   deallocate c
   select * from #temp order by ord
   

4. That stored procedure becomes a dataset for the TOC in the main report.

5. Put a Table for Table of Contents that references the dataset.

6. Create dll ReportTOC.ClassTOC:

   using System;
   using System.Data;
   using System.Data.SqlClient;
   
   namespace ReportTOC
   {
   public class ClassTOC
   
   {
       public static string logReportAttributes(string TOCExecutionID, int AssessmentID, string ReportName, int GlobalsTotalPages)
       {
           string retValue = String.Empty;
           try
           {
              // long TOCExecutionID = long.Parse(DateTime.Now.ToString("yyyyMMddHHmmss"));
               using (var conn = new SqlConnection("Server=10.2.36.11;Database=InternalApps;User Id=webclient;Password=webclient;MultipleActiveResultSets=true"))
               using (var command = new SqlCommand("usp_LogReportAttributes", conn) { CommandType = CommandType.StoredProcedure })
               {
                   conn.Open();
                   command.Parameters.AddWithValue("@TOCExecutionID", TOCExecutionID);
                   command.Parameters.AddWithValue("@AssessmentID", AssessmentID);
                   command.Parameters.AddWithValue("@ReportName", ReportName);
                   command.Parameters.AddWithValue("@GlobalsTotalPages", GlobalsTotalPages);
                   command.ExecuteNonQuery();
               }
               retValue = "Sent Successfully";
           }
           catch (Exception ex)
           {
               retValue = string.Format("{0} \n\r {1}", ex.Message != null ? ex.Message : "", ex.InnerException != null ? ex.InnerException.Message : "");
           }
           return retValue;
       }
   }
   }
   

7. We used .NET Framework 3.5

8. In each subreport: Set references to the assembly: ReportTOC, Version 1.0.0.0, Culture=Neutral, PublicKeyToken=null
9. Save the dll on your local (developer) computer here: C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies
10. Save the dll on your report server here: C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin\ReportTOC.dll