I am working a lot with matrices these days for a new SQL Server Reporting Services (SSRS) report in Enhansoft Reporting. The more I use a matrix the more things I discover that are different between matrices and tables, so here I will show you how to add repeating header rows within a SSRS matrix.
Normally I use tables, but in order to use the row group feature to display additional row details (or child groups) in the same column, I needed to use a matrix. When I took a look at the exported version of my new report, I noticed right away that the matrix headers were not appearing on any other page but the first one. That wasn’t right, so this post will show you, step-by-step, how I added repeating header rows.
By the way, earlier this month I did up a blog post about How to Enable Interactive Sorting in a SSRS Matrix. At the time I thought that was the only difference when it came to enabling features in a matrix and a table, but I soon discovered this one.
Problem
Above is a cropped version of Enhansoft Reporting’s new Application Dashboard SSRS report. The report was exported to PDF and the screenshot only shows you the header section on the first page. The information that I’m keeping my eye on is the matrix header row titles: Publisher, Application and Version.
Take a look at the second page of the same report as it is shown within Adobe Reader. The header row with Publisher, Application and Version is not displayed. Without the header, it’s not as easy to understand what each column is displaying. This is especially true for reports that are dozens of pages long.
Now that you see the problem, let’s fix it by adding repeating header rows.
Repeating Header Rows
Start by highlighting the matrix, right-clicking on it and then selecting Tablix Properties… as shown in the left screenshot.
Some of you at this point might be questioning, “Why Tablix properties? I thought you were showing the steps for a matrix.” Table and matrix templates are built on the Tablix data region, so the name of this data region is just one of those things that Microsoft sometimes does which confuses people.
Select both Repeat header columns on each page and Keep header visible while scrolling. At this point, if you are working on a table you are done. That is all you need to do in order to allow for column headers to be shown on all of your pages. A matrix, on the other hand, requires additional steps.
Next, you must enable Advanced Mode. See the small arrow that I’m pointing to?
Click on the arrow and then click on Advanced Mode.
Once in Advanced Mode, you see a number of changes. Row Groups and Column Groups appear differently and several (Static) objects are added.
Within the Row Group area, select the first (Static) row. Then in the Properties area, locate the RepeatOnNewPage option and change it to True. Make sure to save your report!
Problem Solved
After taking these extra steps and then exporting the same report to Adobe Reader, the column headers in my matrix are displayed! This report is now easier to read.
Are you looking for more of my tips about SCCM, Microsoft Intune, SSRS, or Power BI? Then, sign-up for the Enhansoft newsletter!
If you have any questions about how to add repeating header rows within a SSRS matrix. Please feel free to contact me at @GarthMJ.
We are using Microsoft Report Server 2012 & Report Builder v3. Unfortunately these instructions don’t work for us. Maybe it is a limitation of MSSQLRS2012 or Report Builder v3 or a combination of the both. Any ideas please? Thanks
The instruction are for Visual Studio version for creating SSRS reports. But the same basic steps will apply for Report Builder too.
I’m afraid I agree with Shell’s comment. I am using reporting builderv3 and these instructions do not result in the header being repeated.
Just to be clear you are using a matrix vs table and you did both steps? And just to confirm you did the advance mode step too?
along with RepeatOnNewPage = true, I also had to change KeepWithGroup=after, this was set to none by default in my case.
And I was able to repeat matrix’s column headers on every page. hope this helps
source: https://community.powerbi.com/t5/Desktop/Report-Builder-does-not-repeat-column-headers-on-subsequent/td-p/1863188
Thanks for the update Vickar
I have a tablix that has multiple row headers. The data set is a stored proc with no parameters, but 1/2 the columns are xmls based on CTE stuffs. There is a lot of concatenated data in the columns.
The report is set to 11×17 landscape, but all the columns can’t fit on 1 row, so they are split up onto 3 rows, plus a header row for each set of columns.
When 1 record splits over a page, I need the header row before the data to push to the next page, so it keeps with the data.
Nevermind. I split the sets of rows into separate tables, and then put the 2nd 2 tables into rows of the first table. Now it does what I want.