Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Printing Forms - SSRS

RSS
Modified on Fri, Jul 23, 2010, 9:48 AM by Administrator Categorized as SSRS (SQL Server Reporting Services)

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.)

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.