Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Reporting Services (SSRS) Repeating Headers On Pages


By:   |   Read Comments (6)   |   Related Tips: > Reporting Services Development


Free MSSQLTips Webcast Today >> Optimize SQL Server Performance


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. The database is available on Codeplex at http://msftdbprodsamples.codeplex.com/releases/view/55330. 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.

Simple Tablix

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.

Tablix Properties

Of course this process is easy and results in our column header rows showing on each page as illustrated next.

SQL Server Reporting Services (SSRS) Group Headers

Using the Insert Row method

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.

Insert row outside group

New Row

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!

When the report is processed our new header row shows on page 1
new header page 2

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.

advanced settings

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.

static groups

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.

static settings

Now on page 2 and all subsequent pages, as illustrated next, the New Header row 3 is displayed.

new row show 1
new header page 2  show

Now say we add another new row, "New Header Row 4", shown in blue in the below screen print.

Header Row 4

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). 

Error for row 4

Thus, we can match up properties and the report will now run without error as illustrated in the following screen print.

row 4 success

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.  

Intermediate Row Group

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. 

intermediate row group page 1

intermediate row group page 2

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, March 08, 2017 - 7:59:23 AM - Scott Murray Back To Top

 

Matt.. I would check all the settings for each group and verify the header rows is set as header row. It definitely can be tricky.


Wednesday, March 08, 2017 - 5:58:14 AM - Matt Back To Top

Thanks for this but this is not working for me when the headers are inside a group. I have 3 different groups in my report and a header for each one; 2 of them repeat on each page but the 3rd one doesn't even though everything is set up the same way. The only workaround I could find is to insert the detail rows into a rectangle for that header to repeat! Surely, there must be a better way.

Thanks again! M.


Thursday, August 11, 2016 - 10:39:11 PM - Randall Smith Back To Top

THANK YOU!!! -    perfect tutorial with great pics!!! - this helped me big time!! 

 


Friday, March 04, 2016 - 9:38:48 AM - Aruna Back To Top

 I am unable to see Static row group when I click on advanced mode. I want to create form report with repeating headers but I am unable to do so.

 


Thursday, February 19, 2015 - 10:38:26 PM - Julius Back To Top

Hi Scott,

Can you please send us the sql used for the data set, so we can follow the steps?

Thanks


Thursday, January 15, 2015 - 4:05:40 PM - Ratana Back To Top

Hi Scott,

I am fairly new to SSRS and I never got a complete picture of SSRS from setting up the connection to producing reports.  I am familiar with Crystal report and MSAccess application but clueless for SSRS.  I want to learn report desinging  using SSRS and I don't know where to start for my own learning curve.

I have been way too frustrate as there is too many information on the web but none is what i was looking for.  If you could point me to a direction that is much appreciated. 

Thank you.

Ratana

 

 


Learn more about SQL Server tools