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?

Sac

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

Thiamine

Yes, putting the table in the rectangle works. It also means there is no extra space being used by an empty rectangle, causing potential issues when exported to Excel – Nitroparaffin

Putting a rectangle below tablix and setting page breaks for rectangles instead of tablix also works grt. Thanks – Chambers

This really helped me out. – Facilitate

This seemed to do the trick. Thanks. – Varioloid

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.

Suspensoid

I did not experience the same success, 2008 R2 SQL Server/SSRS to 2010 Excel for those keeping score. Adding rectangles seems to be a workaround, but I will continue to look for an alternative. – Cephalometer

Answer 3

Instead of setting Page Break -> Break Location property to "End", set it to "Start" for all Tablixes, except the 1st one.

Elbe