Problem
As I design various SQL Server Reporting Services reports, the process to get column headers to repeat on each page seems to work and not work intermittently. How do I get certain rows to repeat or not repeat on each page within a SSRS report?
Solution
As you work with SQL Server Reporting Services designing reports, one requirement that often comes up is to show column headers on every page. This situation is usually straight forward if there is a single group or single header row. However, once you begin to add / insert additional header rows or you start to add and delete headers within and outside of groups, SSRS often will often end up with header rows NOT repeating on each page even if the Repeat Row Headers property is checked “on” in the tablix properties. In this tip, we will review how to get header rows to repeat on each page, discuss some common errors that you receive when adjusting header settings, and finally show some methods of limiting what rows repeat on each page.
Repeating Row Headers for SSRS Reports
In order to show the various scenarios for repeating row headers, we will use the AdventureWorks database. As of the writing of the tip, a SQL Server 2014 version of the AdventureWorks sample database was not available; thus, I just upgraded the SQL Server 2012 version. Once you download and install the SQL Server database, we will subsequently use SQL Server Data Tools (SSDT) to design a basic report based on data from the AdventureWorks database. As shown in the next screen print, we will create a simple tablix report using some data from the AdventureWorks database.
For this simple example, even with the three header rows, we can easily get the row and column headers to repeat on each page by going to the Tablix Properties window and checking the Repeat header rows on each page and Repeat header columns on each page properties. </p
Of course this process is easy and results in our column header rows showing on each page as illustrated next. </p
Say we decide to add a new row in our header area by inserting a row using the Insert Row method that is available by clicking on our Country Region textbox and then selecting Outside Group – Above. This process results in a new header row to which I added “New Header Row 3” text.
However, now when the report is processed our new header row shows on page 1, but on any subsequent page the New Header Row 3 header is missing!
To get our new header to show, we need to do a few things. First, we need to show the Advanced column / row group settings. To access this setting, you click on the small down arrow on the very right part of the Row Groups and Column Groups window, and then click Advanced Mode.
Now that the group settings are in advanced mode, we now see that a Static Row and Column are now showing. We can use these static rows and columns to adjust which rows repeat on each page.
First we need to click on the Static row listed under row groups and verify the appropriate header row is selected. Notice how in the below screen print that the New Header Row 3 is selected; this selection is important if you have several static rows. Now we need to verify the KeepWithGroup setting is set to “After”. Finally, the RepeatonNewPage must be set to True.
Now on page 2 and all subsequent pages, as illustrated next, the New Header row 3 is displayed.
Now say we add another new row, “New Header Row 4”, shown in blue in the below screen print. </p
Now when we run the report we suddenly get an error as shown below. SSRS is complaining because our new row’s RepeatonNewPage setting does NOT match the setting for our “New Header Row 3” ( the one we added in the previous steps). SSRS forces us to have all row groups including static rows to have the same RepeatonNewPage values IF they are at the same level (as Header Row 3 and 4 are). </p
Thus, we can match up properties and the report will now run without error as illustrated in the following screen print.
Setting To Not Display Header Row On All Pages for an SSRS Report
However, what if we actually want Header Row 3 to only appear on the first page, but we want Header Row 4 to appear on all pages. Out of the box, SSRS will not allow us to create this scenario. Even so, we can actually work around the issue using a new adjacent group; the group will group on “1” which basically creates just 1 row. We add this row group to just below the Header Row (Header Row 3 in our example) we want to only show on page 1. </p
Now, as seen in the below illustrations, we were able to hide Header Row 3 on all but page 1, but Header Row 4 shows on all pages. We could even hide the Intermediate Row Group row. </p
Conclusion
Column and row headers can be a frustrating formatting endeavor, especially if you want to have multiple headers and you begin to add and delete new header rows from within the existing groups. SSRS actually creates new static rows and columns which require that the KeepWithGroup and RepeatonNewpage properties to be set appropriately. Furthermore, these settings must be the same for all static rows or columns that reside on the same hierarchy level. To get around that requirement, you can use “adjacent” intermediate row groups to allow a header row to only show on the 1st page, while having subsequent headers appear on multiple pages.
Next Steps
- MSDN Group Pane- http://msdn.microsoft.com/en-us/library/cc281455.aspx
- MSDN SSDT Blog – http://blogs.msdn.com/b/ssdt/
- Look here for more Reporting Services Tips
