Displaying Variable Data in a Page Header or Footer
Page headers and footers can contain static content, but they are more commonly used to display varying content like page numbers or information about the contents of a page. To display variable data that is different on each page, you must use an expression.
If there is only one dataset defined in the report, you can add simple expressions such as [FieldName] to a page header or footer. Drag the field from the Report Data pane dataset field collection or the Built-in Fields collection to the page header or page footer. A text box with the appropriate expression is automatically added for you.
To calculate sums or other aggregates for values on the page, you can use aggregate expressions that specify ReportItems or the name of a dataset. The ReportItems collection is the collection of text boxes on each page after report rendering occurs. The dataset name must exist in the report definition. The following table displays which items are supported in each type of aggregate expression:
|
Supported in expression
|
ReportItems aggregates
|
Dataset aggregates (scope must be name of dataset)
|
|---|
|
Text boxes in body of report
|
Yes
|
No
|
|
&PageNumber
|
Yes
|
No
|
|
&TotalPages
|
Yes
|
No
|
|
Aggregate function
|
Yes. For example,
=First(ReportItems!TXT_LastName.Value)
|
Yes. For example,
=Max(Quantity.Value,"DataSet1")
|
|
Fields collection for items on the page
|
Indirectly. For example,
=Sum(ReportItems!Textbox1.Value)
|
Yes. For example,
=Sum(Fields!Quantity.Value,"DataSet1")
|
|
Data-bound image
|
Indirectly. For example, =ReportItems!TXT_Photo.Value
|
Yes. For example,
=First(Fields!Photo.Value,"DataSet1")
|
The following sections in this topic show ready-to-use expressions that get variable data commonly used in headers and footers. It also explains how the Excel rendering extension processes headers and footers. For more information about expressions, see Working with Report Expressions.
Adding a Page Number to a Header or Footer
Footers commonly display a page number. To display a page number in the header or footer of a report, create a text box in the footer and add the following expression:
=Globals.PageNumber & " of " & Globals.TotalPages
To format the page numbers and text independently, type the following directly into a text box in the page header or footer:
[&PageNumber] of [&TotalPages]
You can select each section of text and apply individual formats to the text. For more information, see How to: Format Text in a Text Box.
Adding the Report Title, Name, Path, and Report Execution Time to a Header or Footer
Use the following expressions to show the report title from a text box named ReportTitle or the report name as it is stored in the report server database with timestamp information about when the report was generated:
=ReportItems!ReportTitle.Value
=Globals.ReportName & ", dated " & Format(Globals.ExecutionTime, "d")
Adding Calculated Page Totals to a Header or Footer
For some reports, it is useful to include a calculated value in the header or footer of each report; for example, a per-page sum total if the page includes numeric values. Because you cannot reference the fields directly, the expression that you put in the header or footer must reference the name of the report item (for example, a text box) rather than the data field:
=Sum(ReportItems!Textbox1.Value)
If the text box is in a table or list that contains repeated rows of data, the value that appears in the header or footer at run time is a sum of all values of all TextBox1 instance data in the table or list for the current page.
When calculating page totals, you can expect to see differences in the totals when you use different rendering extensions to view the report. Paginated output is calculated differently for each rendering extension. The same page that you view in HTML might show different totals when viewed in PDF if the amount of data on the PDF page is different. For more information, see Understanding Rendering Behaviors.
For Reports with Multiple Datasets
For reports with more than one dataset, you cannot add fields or data-bound images directly to a header or footer. However, you can write an expression that indirectly references a field or data-bound image that you want to use in a header or footer.
To put variable data in a header or footer:
-
Add a text box to the header or footer.
-
In the text box, write an expression that produces the variable data that you want to appear.
-
In the expression, include references to report items on the page; for example, you can reference a text box that contains data from a particular field. Do not include a direct reference to fields in a dataset. For example, you cannot use the expression
[LastName]. You can use the following expression to display the contents of the first instance of a text box named TXT_LastName:
=First(ReportItems!TXT_LastName.Value)
You cannot use aggregate functions on fields in the page header or footer. You can only use an aggregate function on report items in the report body. For common expressions in page headers and footers, see Expression Examples (Reporting Services).
Adding a Data-Bound Image to a Header or Footer
You can use image data stored in a database in a header or footer. However, you cannot reference database fields from the Image report item directly. Instead, you must add a text box in the body of the report and then set the text box to the data field that contains the image (note that the value must be base64 encoded). You can hide the text box in the body of the report to avoid showing the base64-encoded image. Then, you can reference the value of the hidden text box from the Image report item in the page header or footer.
For example, suppose you have a report that consists of product information pages. In the header of each page, you want to display a photograph of the product. To print a stored image in the report header, define a hidden text box named TXT_Photo in the body of the report that retrieves the image from the database and use an expression to give it a value:
=Convert.ToBase64String(Fields!Photo.Value)
In the header, add an Image report item which uses the TXT_Photo text box, decoded to show the image:
=Convert.FromBase64String(ReportItems!TXT_Photo.Value)