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?

score 15 · Accepted Answer · answered Jan 29 '14 at 06:46

15

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

answered Jan 29 '14 at 06:46

cabbagetreecustard

647
2
13
22

2

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 – user3056839 Mar 20 '14 at 16:07
Putting a rectangle below tablix and setting page breaks for rectangles instead of tablix also works grt. Thanks – Jitendra Sawant Feb 08 '16 at 12:07
This really helped me out. – Will Aug 09 '17 at 14:46
This seemed to do the trick. Thanks. – Leonardo Mar 10 '23 at 17:14

score 1 · Answer 2 · answered Jul 11 '12 at 16:18

1

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.

answered Jul 11 '12 at 16:18

Steve

27
2

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. – SQLHound Oct 23 '15 at 20:41

score 0 · Answer 3 · answered Nov 17 '22 at 17:20

0

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

answered Nov 17 '22 at 17:20

HelenK

1

How do I enforce SSRS page breaks with empty tables?

3 Answers3