Marquee Moon (Field References in Page Headers and Footers)
One of the questions that I get often is about putting database fields in their SSRS report page header and/or footer.
Note that putting fields in the page header and footer is not the same as putting a data region in these sections. Because of the impact on layout (what happens when it grows?), we do not want to have growing items in these sections. Therefore, any fields in these regions will be a single textbox at an report level aggregate scope (so there is only one instance).
You might know about one workaround which is to reference a textbox in the body of the report using =ReportItems!TextboxName.Value syntax. This is very useful for useful for dictionary or address book style reports as it allows you to reference the first or last instance of a textbox in the page. The challenge with this approach is that you need to make sure that the textbox appears on every page, which can be a tough in some reports.
Another approach is to use hidden parameters. To do this, create a new data set that contains the values that you want to put in the report. In general, the query should only return a single row. After defining the data set, create a hidden parameter for each value you want to display. Set both of the available values and the default values for these parameter to the data set and field that contains the information that you want to show. Then you can simply add them to the page header or footer using the =Parameters!ParameterName.Value syntax.
I've attached a modified version of a SQL 2005 sample report that demonstrates this approach. Instead of static text for the web site URL in the page footer, it retrieves it from a query.
Next up, I'll show how you can achieve report localization through a similar technique.
Comments
- Anonymous
November 01, 2008
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