Printing Forms - SSRS

Overview¶

SSRS (SQL Server Reporting Services) does not permit placing field values in the Page Header and Page Footer sections of a report, at least not with the Field!FieldName.Value syntax. This makes printing forms with SSRS a challenge when header data is desired at the top and/or bottom of each page, a common requirement for purchase and sales orders, bills of lading, and invoices, and others. This article outlines several design patterns to address this requirement in SSRS 2005.

As of SSRS 2008 R2, you can use the solution posted here. Of course, with that solution, the "page header" and "page footer" are actually created in a group header of a table.

Techniques¶

1 - Using Group Headers and Footers¶

This technique doesn’t work because we want footer data on every page. Data in a group footer is displayed only after all detail items have printed. The only way this would work is to consolidate the datasets into one and calculate the page numbers.

2 - Consolidated SQL¶

Summary¶

All data is consolidated into a single SQL statement. Based on the number of lines that can fit on a physical page, page numbers are calculated in the SQL statement. Go here for a walkthrough.

Advantages¶

It works in all situations, including rendering of multiple documents (orders, bills of lading, or invoices) at once.

Disadvantages¶

SQL is complex. Header and detail data are combined into one SQL statement, including order line items, comments, and instructions.
Page Numbers are calculated in the SQL based on the number of lines per page; therefore, the developer must determine or estimate the number of lines that will fit on a page, considering the room needed for header and footer regions and the fact that each detail line on the page may wrap. If the header or footer regions ever change, the determination of lines per page has to be revisited.
Because the text in line items may wrap, the report ends up typically having much wasted white space.
The footer region cannot be placed in a consistent location on the page, as the number of wrapping vs. non-wrapping lines varies from page to page.
Must add custom code to add additional blank lines between the detail region and the footer region.

3 - Caching Header Data in a Shared Dictionary Variable¶

Summary¶

Custom code is written to instantiate a shared Dictionary variable, which is then filled will data from the header dataset via function calls in cells of a hidden table in the Report Body. Go here for a walkthrough.

Advantages¶

SQL is divided logically into multiple datasets: header, line items, comments, shipping instructions, etc.
Header data is placed in the Page Header and Page Footer areas of the report.
SSRS handles pagination

Disadvantages¶

Suffers from intermittent issues in a multi-user environment.
Cannot render multiple documents (orders, bills of lading, or invoices) at once.

4 - Caching Header Data in Hidden Report Parameters¶

Summary¶

A hidden report parameter is created for every field value in the header dataset that is required in the Page Header or Page Footer. Go here for a walkthrough.

Advantages¶

Doesn’t suffer from intermittent issues in a multi-user environment.
SQL is divided logically into multiple datasets: header, line items, comments, shipping instructions, etc.
Header data is placed in the Page Header and Page Footer areas of the report.
SSRS handles pagination

Disadvantages¶

Cannot render multiple documents (orders, bills of lading, or invoices) at once.
For header datasets with many columns, it can be time-consuming to create all the report parameters needed.
Performance can be slow, presumably from having to load so many report parameters.

5 - Caching Header Data in ReportItems¶

Summary¶

(TODO)

Advantages¶

Can render multiple documents (orders, bills of lading, or invoices) at once.
Don’t have to add custom code to add additional blank lines between the detail region and the footer region.
The footer region is in a consistent location on the page

Disadvantages¶

SQL is complex. Header and detail data are combined into one SQL statement, including order line items, comments, and instructions.
Page Numbers are calculated in the SQL based on the number of lines per page; therefore, the developer must determine or estimate the number of lines that will fit on a page, considering the room needed for header and footer regions and the fact that each detail line on the page may wrap. If the header or footer regions ever change, the determination of lines per page has to be revisited.
Because the text in line items may wrap, the report ends up typically having much wasted white space. (This can be addressed by having each detail record consume two physical lines on the page, with the description on its own line the entire page width, and all the other fields on the second line.)