As a starting point, the SSRS I’m talking about here is SQL Reporting Services (had a mix up with SSMS yesterday and I’m not taking any chances). And the basic scenario is a common one. The report, which happens to be in Tablix format, has a number of rows of headers that are intended to appear at the top of each page.
This is not an abnormal request. Not in the slightest. However a recent problem strained my patience as I attempted to put the silly headers at the top of every page.
The problem was that, on an irregular but consistent manner (which is to say that it didn’t happen for every combination of parameters, but once you saw the problem, it didn’t just “go away”), the header would go missing. Instead of appearing at the top of the page, it just not be there. Or perhaps it only some of the rows of the header would be there. In this later case, if you changed the page size, you could get it to appear. But then another combination of parameters would cause the same behavior. But the upshot is that I couldn’t depend on the headers to appear every time I needed them too. Frustrating doesn’t begin to describe the feelings I had towards SSRS and headers.
First off, if you’re looking for a solution, some of the pages you’ll find do cover the fundamental process. For example here, here and here. And the information provided therein was accurate. Yes, you need to get the RowGroup into Advanced Mode. And you need to set the RepeatOnNewPage property to true. And KeepWithGroup to After. And FixedData to true. However, I’m afraid this might not be enough. At least, it wasn’t for me. Those steps had already been performed and I was still seeing the problem.
For me, the key to getting to the complete answer was something that I just happened to stumble across. It was a line in an MSDN document (that I can’t seem to find again) that said that row headers were only displayed on each page if there was enough room vertically. Column headers, on the other hand, would always be displayed. In other words, if you had placed your ‘column headers’ as just values in rows within the Tablix, then SSRS wouldn’t do anything special to ensure that they appeared at the top of each page. And this would be true even if you had taken the steps mentioned in the previous paragraph. If there was “too much” report data on the current page, the headers would be cut off as part of the prioritization that SSRS does.
With that piece of information in hand, I had another clue to search with. And that led me to a post that talked about the fact that repeating column headings only worked *if* there is a column headings area on the report. And, depending on how you created the Tablix, that might not be the case.
You call tell if you have a column heading area if you see a double dotted line someplace in the Tablix. If you don’t, then you find yourself in the situation I was in. Now, you *could* create a column group and move your row headers up to that level. For me, that was not a practical choice (because of the complexity of the data being shown in the headers). So instead, I faked it.
Start by adding a Parent Column Group. It doesn’t matter what you group by, so just select an arbitrary field or type in a constant. I choose ‘0’. Doing this adds the column heading areas to the report. Now we need to get rid of the added row without getting rid of the column heading area. In the Grouping Panel at the bottom of the report, right-click on the just added column group and select Delete. In the dialog that appears, you will be given the choice to delete either the group and related rows and columns or to delete just the group. The correct choice here is to delete just the group. If you also delete the related rows and columns, you will be back where you were before with no column heading area. Once you have deleted the group, you can go into the Tablix and delete the row which is related to the column group.
At this point, you will have a Tablix that has a column heading area with no rows in it. And your report will look as it did before. But, more importantly, because there is a column heading area, the row headers which you had previously created (and which had been cut off or just plain went missing) are now visible at the top of every page, regardless of whether a soft or hard page break rendering engine is used.
And now the world can get back to spinning on it’s regular axis. Whew!