One of the small new features in Reporting Services 2008 that has not been widely publicized, is the ability to directly reference dataset fields and dataset aggregations from expressions in the page header and page footer. You can accomplish this by simply dragging a dataset field directly into the page header/footer area of the report, or by manually writing expressions.
The attachment at the bottom of this posting contains a small report that demonstrates several techniques combined to render a customer directory of the fictional Northwind Traders company, based on the Northwind sample database (database download link). A partial screenshot of the first page of the report is shown below:
Please note the following areas of the customer directory report:
- Field reference / dataset aggregate calculation in page header
The page header contains information about the number of countries that Northwind Traders has customers. This information is calculated at runtime directly from the customer dataset by using a placeholder in the page header textbox with the following expression:
- Reportitem references in the page header to show the first and last country covered on the current page
For multi-page reports, such as the customer directory listing you may want to include text from the body of the report in the header. In our particular example, we want to show the first and the last country covered on a particular page of the customer directory. To do this, you would include an expression using the First function in a text box in the page header. To display the first occurrence of the Country value on the page, the expression would resemble the following:
Likewise, use an expression with the Last function to provide the last value of the Country text box on the page:
- Repeating table header and (country) group header on every page
This is accomplished by applying steps discussed in a previous posting.
- Fixed table header
The table header is always visible on top of every page when browsing the report interactively. This is accomplished by applying steps discussed in a previous posting.
In addition, the report shows a few of the abilities of the enhanced (Rich)Textbox in Reporting Services 2008, that enables using multiple fonts, styles, colors, paragraphs, etc. within the same textbox. It also uses a "stepped" layout for the table country/city groups, that can be easily accomplished through the new table wizard in Report Builder 2.0, or created with a few manual steps as well.
As a side-note, for using fields in page header or footer in Reporting Services 2005, please read this older posting on Brian Welcker's blog.