Report Design: Reset Page Number on Group
One of the frequent feature requests for Reporting Services was to add the ability to reset page numbers within the report, e.g. based on a change in the group value. Reporting Services 2008 R2 adds this capability through a more general feature to name pages, and define dynamic page breaks and optionally reset page numbers.
The screenshot below shows the result of a slightly modified Product Catalog sample report with overall page numbers, as well as page numbers per product category grouping.
In this posting I show how to build this kind of report step-by-step. All you need to try this yourself is the AdventureWorks2008 sample database (download), and the Product Catalog 2008 sample report (download). Alternatively, you can download the final report from the attachment of this posting (download), and/or read more details about it in the following book on pages 125-127.
Naming of pages in reports is particularly useful for Excel export as well (covered in more detail in an upcoming blog posting). The focus for this blog posting is on page break improvements in Reporting Services 2008 R2 that are very powerful yet could be overlooked in the property grid of groups and report items in Report Builder / Report Designer:
This report also utilizes new additions in the Globals collection in Reporting Services 2008 R2 from RDL expressions in the report. Note that these Global properties can only be referenced from expressions in the page header or page footer:
- You can set the PageName property on a group and refer to its current value from the page header by referencing =Globals!PageName
- Reference overall page number (OverallPageNumber) and total page count (OverallTotalPages), unaffected by page number resets, using
=Globals!OverallPageNumber, and
=Globals!OverallTotalPages. - Reset page numbers with each new page break (e.g. on a group) using the ResetPageNumber property, and reference related page counts (PageNumber, TotalPages) using
=Globals!PageNumber, and =Globals!TotalPages.
Steps for modifying the Product Catalog 2008 report to accomplish page numbers per product category.
- Open the Product Catalog 2008 sample report in Report Builder 3.0 of SQL Server Reporting Services 2008 R2.
- Tweak the existing report to prepare for resetting page numbers.
a) Remove the PageBreakAtEnd from the product catalog cover page rectangle (Rectangle2), by changing the page break location property value from End to None.
b) Open the tablix properties dialog for Tablix1, which shows the product catalog details, and ensure that the "Keep together on one page if possible" option is deselected. - While Tablix1 is selected, in the grouping pane select the Category row grouping. In the properties window for the Category tablix member, expand the Group properties, and set the following page break related properties as shown in picture above:
BreakLocation: StartAndEnd
ResetPageNumber: True
PageName: =Fields!Category.Value
Thereby, the PageName for a given page is set to the current product category shown on that page, and each product category starts on a new page and the page numbers reset. - Create a page header for the report, and add textboxes with expressions for the overall page counts and the product category specific page counts by referencing the new global collection properties:
Done – run the report and enjoy!