Tablix header not repeating on new page, when subreport causes page break

7
votes

I've been fighting with SSRS for too long now on what should be a simple matter. I'm hoping someone can help me see a solution.

I have a report which has a number of profiles, and each profile has a large number of data sections. The data sections (various tables and charts) are not related to each other in any way other than that they "key" back to the parent profile id.

The goal (which seems simple) is to have a header on each page with the profile information, and then have the data sections (each formatted completely differently) laid out.

The first problem we encountered was that for some reason SSRS2008 does not allow a tablix cell to contain another tablix with a different dataset (why it can't just require a filter based on the parent grouping is beyond me). The recommended workaround for this shortcoming is to either combine all data into a giant dataset (not possible here, data sections are too numerous and varied) or to embed subreports and pass in the parameters. While option number two increases the "work" needed to print a large number of profiles, we proceeded to implement this and it does work.

The problem comes in on the main tablix (the one containing the subreports) that has a static row header group set with "RepeatOnNewPage = true". This setting allows the header to show on every page as desired... except for subreports the span multiple pages, where the header is incorrectly ommitted.

For some reason, when the subreport spans multiple pages, the header of the parent tablix is not repeated! This is driving me insane as it would seem that a couple bad design decisions in SSRS has put me in a situation where I can't create the report I want... headers works without subreports, but I need subreports in order to "join" the data sections to the profile.

This would seem to be a simple and common requirement... after all, it's a report that is being migrated from a decades old reporting system. I've scoured StackOverflow and Google without success... I've seem a number of questions/answers about page breaks, but nothing that suggests a solution to this problem with subreports.

I'm posting this in case I'm missing something. If anyone has any suggestions at all, it would be much appreciated.

4
sql-server-2008reporting-services
We have more information on this problem. The missing headers appear not to be related to the use of subreports, but rather that when a Tablix row is split across multiple pages that the Tablix row header fails to be printed (on the additional pages). The subreport is in a tablix row (due to the "join" limitation we noted above), and the subreport can easily be greater than a page in length. ANY HELP or suggestions on this would be so much appreciated. – codinginthevoid

4 Answers

4
votes

A very clean and simple way to repeat the column header on each page

http://www.a2zmenu.com/Blogs/BI/Tablix-headers-not-repeating-in-SSRS-2008.aspx

2
votes

We've solved our problem... although not in the most ideal way.

The Page Header (of the "master" report, not the subreports) seems to be the only way to consistently have a header section.

We implemented a solution based in large part on this blog posting: Maintaining State in Reporting Services 2008

We modified slightly from the blog post, removing "id" from the methods and including a GUID to ensure the report values are not mixed up even if the user runs several copies of the report at the same time. (When using shared variables, remember that they are global to the report running across execution instances and across users on the report server, so a lot of care needs to be taken when they are used!)

In the report body we have a hidden text block that calls:

=Code.SetValue("xxx",Fields!Field1.Value) & Code.SetValue("yyy",Fields!Field2.Value)

and in the report page header we reference these as (for example):

=Code.GetValue("xxx")

This works because the body is calculated before the page header, even for Page 1.

Still, in the end it is a hack for something that should be in Microsoft's product, given that they have poured years of development into this product! Why we cannot have nested tablix's and easier to use headers is beyond me... again this is upgrading a report from a program from the early 90's which did all this without problem. At the very least, give us report variables that can be retrieved and set as the report is processed!

Regardless, things are as they are... we've tested the solution with shared variables and it seems to be working very well. We're going to run stress tests to make sure it's implemented correctly, I'll post something here if we find any further problems.

(And if anyone knows of some other way to get consistent header rows on pages, even when there are detail rows with large heights, please share...)

1
votes

We have faced exactly the same problem during implementation of customer demands on SSRS Reports, And did try a lot of things which all of them resulted with failure.

In our case, Main Report has 4 subreport. Each SubReport should be started in new page. And also when new subreport begins, page header should be changed dynamically based on Subreport in body in current page.

Also one of SubReports which was SubReport3 has another case. Basically Main Report was running based on Dealer level. But Customer wants SubReport3 to run for each SubDealer related with @Dealer parameter. Also Customer wants to see each SubDealer data started in New page inside SubReport3.

As a result, we found different workaround as follows.

We created Page Header to Main Report. (Report Menu -> Add Page Header)(As @codinginthevoid said it is most consistent way)

We putted 4 subreports inside separate tablix. We added new column to each tablix. That column in each tablix visiblity = false, width of that column can be as small as possible, then created placeholder inside of that column, wroted down expression as follows:

If expression is in Tablix1, expression was ="Tablix1", if in Tablix2 then "Tablix2" and etc.

Then started design page header for Tablix1, each report item in pageheader that was putted for Tablix1 has visibility expression as follows:

=ReportItems!Tablix1_HiddenTextbox1.Value IS NOTHING

then applied same thing for Tablix2 and etc.

In the end, there were lots of textbox in PageHeader of report and some of them position was completely same, overlapped, But when reports is being rendered, each of those textboxes is being shown it is related page with related subreport.

0
votes

I have encountered the same problem as well. What I found was that the way to solve the issue was to de-normalise the data, adding a 'Record Type' field to identify whether the row was a parent or a child.

There were two sub-tables in my tablix, both displaying detail data linked to the outer grouping. If I set the headers of the sub-tables to repeat on every page (using the Advanced arrow > Static Properties > KeepTogether = True, KeepWithGroup = After, RepeatOnNewPage = True) then one of the sub-tables would display correctly, however the parent table's repeating header would just disappear.

To keep a long story short, this was a massively annoying problem to solve. The solution is simple, it worked for me, but may not work for everyone.

Linked below is a SSRS2008 example RDL that connects to localhost and has an example of the problem I had and also the solution I implemented. Please feel free to use it if you find it useful!

(I would've posted it on here but Stack Overflow fails for code blocks it seems)

http://www.sqlservercentral.com/Forums/Topic1111567-1633-1.aspx?Update=1