Using SQL Server Reporting Services, we have always had the ability to use the built in fields (the Globals collection) to display page number and total pages for a report. One thing that was often desired (and extremely difficult and not reliable) was the capability to show page numbers within a particular group. For example, let’s say we have a listing of products and their categories. You may want to see how many pages are in each particular category as well as the total pages of the report. In the 2008 R2 release, we are now able to do this by using a new member of the Globals collection.
In this blog post, I am going to show you how to have page and group level page numbering. To start off with, I am going to use the report that I posted a while ago for Alternating Group Colors and Alternating Rows. If you want to download the zip file containing the report itself, you can download it here.
After downloading the sample, extract the rdl file and add it to a test reporting services project or create a new project and add the report. If it has the crazy unique identifier name on it, you may want to rename it before adding it to the project. For your convenience though, I have added a link to a project containing the original and completed reports. J
Now that we have a report to play with that contains grouping, I want you to free up some space in the header. Either remove the “Pragmatic Works” image or just make the header bigger. Then drag two text boxes into the header section of the report with one being right above the other. It may look something a little like that shown below at this point.
Right click on the top textbox and select Expression. Then enter the expression as shown below. What this I did was typed in the literal “Page “ and the & sign, then selected the Built-in Fields to get the Global collection items. Which, I then selected PageNumber, typed in the & for concatenation and then the “ of “. Then I added another & and then double clicked TotalPages. J In other words, I typed this:
="Page " & Globals!PageNumber & " of " & Globals!TotalPages
So far, I have done nothing different than what has been around for quite some time. We are just getting the page number and the total pages for the report. However, these values will be what will be used for every group. Now that we have this created, we are going to do a similar expression in the next textbox. Enter the below expression into the second textbox that was added.
="Overall " & Globals!OverallPageNumber & " of " & Globals!OverallTotalPages
If you run the report right now, you will have the same exact values in each.
This is not what we want though. We are finally at the part where we can make magic happen! J Let’s configure the group properties for the “Category” group. Select the Category in the Row Groups grouping pane and hit F4 to show the Properties window for the Tablix. Expand the Group and the PageBreak properties. Change the value for BreakLocation to break Between each iteration of the Category. Then set the ResetPageNumber setting to True. This will serve as the counter reset for each category. Then change the PageName property to use the Fields!Category.Value. If you are following along, your settings may look like that shown below.
If you save these values and execute the report, your results may look something like that shown on the left in the image below. I have included two more screenshots to the right that show the progression as you hit the arrow to go to the next page. As you can see, we now have group and page level number capabilities.
For your convenience, just in case you don’t want to follow along, I have included this report in a Report Server Project created in BIDS (VS 2008) in zip format. You can download it here.
I hope that you have enjoyed this post. If you did, please take just a moment to rate it below! Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs or find me at any of the below methods.
Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works
Email: bmcdonald@pragmaticworks.com
Blogs: SQLBIGeek | SQLServerCentral | BIDN Articles | BIDN Blogs | SQLServerPedia
Twitter: @briankmcdonald
LinkedIn: http://tinyurl.com/BrianKMcDonald
