Overview: A few tips for getting the column headings to display at the top of each page of a SQL Server Reporting Services (SSRS) report. SQL Server 2008 R2 was used for all examples.
Using the Advanced Properties
This entry is to expand on my previous blog about Repeating Column Headings which discusses the use of Advanced Tablix Member Properties to repeat column headings on each page. If you haven’t used the “RepeatOnNewPage” property too much, it can seem like sometimes it works & sometimes it doesn’t. It can definitely be a bit frustrating.
Before we go into a few examples, I want to mention the Tablix Properties. These are great when working with a Matrix; however, they have no affect with a regular Table data region.
Rather than using the above Tablix Properties, since we have a Table we’re going to be using the Advanced Tablix Member Properties. The Advanced Tablix Member Properties become available when you click the little arrow on the right side of the grouping pane.
Case 1: No Row Groups – Column Headings in a Header Row – Works!
This report has one Details group, but no parent groups. Notice the column headings are located in a header row of the table. The RepeatOnNewPage property works perfectly in this type of layout (i.e., no row groups).
Case 2: One Row Group – Column Headings in a Header Row – Repeating Does NOT Work
In this layout I moved the Product to be a parent group of the Details. I left the column headings like they were in the previous example: in a header row of the table. Although I set the same advanced tablix member properties, the column headings do NOT repeat on the following page.
Why, you ask? Read on to Case 3.
Case 3: One Row Group – Column Headings Within the Group – Works…Maybe
This layout moves the column headings to be within the row grouping (see how they are within the bracketed rows, rather than one row on top (like they were in Case 2).
This technique works, but since the headings repeat within every single group, it might or might not work for your real life situation.
Case 4: Using a Fake Group – Row Headings Within the Group – Works Great!
This last layout improves on Case 3 by creating a fake group. This fake group is so we can keep the Column Headings within a row group, yet not have them repeat for every product that changes.
The group is set up like this; it really doesn’t group on anything:
The column headings now only appear once, at the top of every page. Perfect!
Finding More Information
There’s a key concept discussed at this MSDN article: How to Display Row and Column Headers on Multiple Pages (Reporting Services). It's this comment that inspired me to use the "fake group" approach discussed in Case 4.
"For a table, the row that contains column names is controlled by the tablix member in a row group. Set tablix member properties in the Properties pane."