SQL Server 2008 R2 Reporting Services – I’ve Got Your Page Number (T-SQL Tuesday #005)

April 13, 2010

As you might have guessed if you’ve been reading my blog lately, I’ve been spending a lot of time working with the business intelligence features in SQL Server 2008 R2. Not just Reporting Services, but the whole BI stack. However, it just so happens that today is T-SQL Tuesday and the topic this month is Reporting (hosted by Aaron Nelson – blog twitter) , so I thought I’d use the opportunity to contribute another observation about new properties and globals that you can find in the upcoming release of Reporting Services. Today, I’ll explain how you can use the PageName and ResetPageNumber properties to dynamically change the values of Globals!PageName (a new global) and Globals!PageNumber (a long-time global) respectively during report execution. And I’ll introduce Globals!OverallPageNumber and Globals!OverallTotalPages.

One of the problems that I currently have with earlier versions of Reporting Services is the inability to control page numbering when I use subreports. I have a situation where I have many individual reports that I want to combine into a single PDF document for distribution. Page numbering is available only in the page header or page footer of a report, so I can’t define the page numbering strategy in the report that I define as a subreport. And even if I could, prior to SQL Server 2008 R2, there was no simple way to renumber pages dynamically within the report. That is, the page numbering applies to the entire report. I cannot number the pages in a distinct section of the report separately from other sections.

With the new version of Reporting Services, I can now solve this problem. Not only can I have separate page numbering by “section”, but I can also assign names to each “section”. For this example, I’ve taken a very simple approach to prove out what’s possible. That means it’s  not pretty or elegant, or meant to illustrate best practices, but it’s functional and to the point. Let’s get started.

The master report contains a Tablix data region which has a PageName property that I can use for naming the first section of my report, as shown in Figure 1. You can use static text, as I have done, or you can use an expression. The PageName property is a property of a data region or a group. I haven’t tested it extensively, but it appears to remain constant from page to page until the report execution renders another data region or group where the PageName property is set to a new value.

Figure 1

In the report that I’ll use as a subreport, I set the PageName property to Sales Summary, on the outermost tablix. You’ll see later that this property “pulls through” to the rendered report. I can also set page breaks (see Figure 2) on the groups in that report which appear in the rendered report, which I have on the group (which breaks on calendar year) of the outermost tablix. The only thing that I cannot define in this report is page numbering because pagination properties are ignored when a report is used as a subreport. Note that the PageName is left blank here. It will continue to use Sales Summary which is defined on the Tablix2 object.

Figure 2

Now the problem that I have with subreports is that there is no way for me to tell it to 1) insert a page break before rendering the subreport, and 2) reset the page numbering. However, I can use a rectangle to handle the pagination for me. So I add a rectangle to MasterReport, insert a subreport inside the rectangle, and configure the subreport to point to the ResellerSales report. Next, I configure the pagination properties on the rectangle, as shown in Figure 3. I force a page break with BreakLocation = Start, and set ResetPageNumber to True.

Figure 3

The page number reset applies only to the current page counter. You display the value of the current page counter in the page header or footer by using the Globals!PageNumber, which has always been a part of Reporting Services. However, in SSRS 2008 R2, you can now reset the current page counter to zero anytime you want to begin a new page numbering sequence in your report. Meanwhile, you can display the running count of ALL pages in your report by using the new globals, Globals!OverallPageNumber and Globals!OverallTotalPages. To illustrate the two page counters, I have two textboxes in the page footer of my report (which is the only place you can use the globals related to pagination).

The first expression applies the current section of my report which is affected by the page number reset and displays the value of the PageName property:

=Globals!PageName + " "  + CStr(Globals!PageNumber) + " of " + CStr(Globals!TotalPages)

The second expression displays the overall page count, which is unaffected by the page number reset:

=CStr(Globals!OverallPageNumber) + " of " + CStr(Globals!OverallTotalPages) + " Overall"

Now when I preview the report, I see the footer shown in Figure 4 on the first page of the report. Here you can see the first “section” of the report is a total of 5 pages while the overall report is 9 pages.

Figure 4

And then paging forward to the first page of the subreport, I find the footer shown in Figure 5. The PageName property of the tablix in the subreport now replaces the prior page name. (Remember that you can use an expression instead of static text.)

Figure 5

If you want to take a closer look, you can download my project and try it out yourself. To execute the report, you must have the AdventureWorksDW2008R2 database installed, and you must download and install SQL Server 2008 R2.

3 Responses to “SQL Server 2008 R2 Reporting Services – I’ve Got Your Page Number (T-SQL Tuesday #005)”

  1. [...] This post was mentioned on Twitter by Stacia Misner. Stacia Misner said: Take 2 w/link this time- Blog: What's in a Page Name? “T-SQL Tuesday #005” #TSQL2sDay #ssrs Reporting Services 2008 R2 http://bit.ly/aqP5e3 [...]

  2. [...] Stacia Misner shares with us some observation about new properties and globals that you can find in the upcoming release of Reporting Services including PageName, ResetPageNumber, Globals!PageName & Globals!PageNumber, Globals!OverallPageNumber and Globals!OverallTotalPages. [...]

  3. Gawd I hate configuring this mindless crap. Anyone remember the 80s where making subreports and batching reports was a simple task? Somehow M$ convinced people that their toy OS is anything but a hobbyist’s conceit, and instead of spending my time making reports, I spend my time configuring M$ to do the most trivial things imaginable.

Leave a Reply