Repeating Header Rows with SQL Server Reporting Services

I spend a lot of time with a large variety of software. Some of it is really specialized stuff most people have never heard of, and the complexity required to use it borders on elaborate voodoo rituals. So it’s a rare breath of fresh air to discover an app that Just Works™,  such as SQL Server Reporting Services.

Except when it doesn’t. Sometimes, SSRS refuses to do what you want it to do. Thankfully, it does have “under the hood” access for the adventurous software spelunker. And, to be fair, I’ve only had one cause to descend into its cavernous depths: repeating a header on every page (instead of just the first page).

Here’s how you do it:

At first it seems obvious and simple: go to Tablix Properties and check the box that labeled “Repeat header rows on each page.” But when you do that, it has no effect on repeating header rows on each page. This is because header rows aren’t actually header rows in SSRS. (Don’t ask. Okay, if you must ask, take a look at Microsoft’s attempt at an answer. Basically, the row headers in the above screenshot are presumed by the dialog window to be group row headers when the tablix is in matrix mode.)

In any case, in order to repeat headers on every page we must first unlock the voodoo. The keys are well hidden, of course, behind a tiny, unlabeled button with a down arrow. I’ll give you ten seconds to find it:

Step Two in SSRS Repeating Header

Once you click the button, you can then select “Advanced Mode” which gives you access to automatically created static members which specify how header rows behave:

Step Three SSRS Repeating Header

From there, it’s smooth sailing — provided you know where to look and what setting to set, of course.

1)    Select the relevant “Static” groups under “Row Groups” (in my case it’s the top two above “(task_status)” since those light up the top two rows that are my actual header rows)

2)    In the properties pane (right column of report builder) for each static member:

  1. RepeatOnNewPage = True
  2. KeepWithGroup = After

Step Four SSRS Repeating Headers
Et voilà! The header rows will now show up on every page, like header rows are supposed to.

Share

Leave a Comment