How do I enforce SSRS page breaks with empty tables?

Question

I'm working on an SSRS 2008 report. I have a very simple report with three tables, a different data set/tablix (with the same fields displayed) for each. I have PageBreak.BreakLocation set to End for the first two - I'd like the three to be displayed separately. Users will be exporting this directly to Excel.

When all three sets have data, it works fine, and in the Excel export it displays them in the appropriately named three sheets. When one doesn't have data, it does not enforce a page break. So if only my third data set has data...the previous two display above it, empty with just row headers, in the same sheet as the third data set, which is the only one that's exported.

I want the page breaks to be enforced even if there's no data for any of the three sets. I can use NoRowsMessage to explicitly indicate there's no data but I still want the separate tabs. Any ideas on how to accomplish this?

Answer 1

For me the accepted answer did not work but we use sharepoint integration to run our SSRS reports, which might be why.

Adding a rectangle underneath the tables I wanted a page break after and changing the rectangle properties to break at end worked for me.

This post suggests a method of putting the table inside the rectangle although I did not test this - http://www.sqlservercentral.com/Forums/Topic1315543-147-1.aspx

Answer 2

Apparently, there is a difference between how the export to Excel feature is handled when working locally versus using a deployed copy on the reporting server. When running locally, my Excel export worked just as John originally described it (combining multiple empty tables on the same worksheet). However, when I deployed the report and ran it from the server, the export worked as expected.