Friday, January 20, 2012

SSRS: Removing Blank Pages In Your Reports

If you find yourself writing reports for a truly international audience (i.e. US and EU) then you'll know about the nightmare of paper sizes when your users want to hit "Print" but a larger, and much more significant, issue is the blank pages that sometimes get printed either between or at the end of your reports.

This blog post lists a few things you might want to check to make sure your report is printing on as few pages as possible.

NOTE: If you are having problems printing matrix reports then there is a specific solution for you at the bottom (and an explanation of why you're seeing the blank pages).

Formatting For Printing
If you follow the steps to create a sample report here and then right-click the background of your created report and select "Report Properties";

Report Builder 3: Right-click in the Red Hatched Area for "Report Properties"
This will show you the properties for your report;

Report Builder 3: Report Properties > Page Setup
This is the "default" size created by the wizard. You'll notice that it has defaulted Centimetres, the UK-standard of A4 (as I'm in the UK) and that we have 2cm margins all round. These defaults (at least the Centimetres and A4) seem to be there as I've set them previously.

The major change I make on this screen is changing all the margins to 1.27cm which is just large enough for the printer to handle and gives over a centimetre of extra real-estate both vertically and horizontally. Of course another significant change is to switch the paper layout between Portrait and Landscape if that makes sense for your report!

Now assuming you are using A4, Portrait, and you have changed your margins to 1.27cm then the maximum size for a label you will be able to fit onto a single page is 184.6mm (210mm total width - 2 * 12.7 margins). To show this create a label on the main report, set it's location to 0,0 and make it 184.6mm wide. Put some right-aligned text (I'm using &ReportName) into the label;

Report Builder 3: 184.6mm Wide Label (A4, Portrait)
Now run the report;

Report Builder 3: &ReportName as GUID
It's displaying the &ReportName value as a GUID (I haven't yet saved the report) but the point is that it's some way over to the right. To find out what this looks like on paper click the "Print Layout" button in the ribbon above the GUID;

Report Builder 3: Print Layout
As you can see the GUID is now at the very right of the page and the page count is 1. Just as a quick test if you enlarge the label to 184.7mm (adding just .1mm) and then re-run the report;

Report Builder 3: Page Count Increased To TWO
You can see that the total page count for the whole report has increased to two (with the second page actually looking blank!).

Additional Formatting Problems With Matrix Reports
As soon as you start dynamically adding columns based on new data in the query the risk of getting blank pages, if you are using fixed width headers, dramatically increases.

Once again follow the steps given here to create a report only this time use the SQL;

SELECT 'Red' AS COLOUR, 'Pencil' AS ITEM, 3 AS QUANTITY FROM DUAL UNION
SELECT 'Red', 'Ruler', 11 FROM DUAL UNION
SELECT 'Red', 'Rubber', 4 FROM DUAL UNION
SELECT 'Red', 'A4 Folder', 9 FROM DUAL UNION
SELECT 'Green', 'Pen', 14 FROM DUAL UNION
SELECT 'Orange', 'Pencil', 23 FROM DUAL UNION
SELECT 'Cyan', 'Ruler', 21 FROM DUAL UNION
SELECT 'Orange', 'Rubber', 14 FROM DUAL UNION
SELECT 'Cyan', 'A4 Folder', 17 FROM DUAL UNION
SELECT 'Purple', 'Rubber', 14 FROM DUAL

And on the "Arrange fields" dialog instead of adding all three columns into the "Values" box add Colour to "Row Groups" and Item to "Column groups" as below;

Report Builder 3: Arrange Fields using Column and Row Groups
At the end of this process you will have something like this;
Report Builder 3: A Table With Column and Row Groups
When you run this report and look at the Print Layout (assuming you have selected A4, and 1.27mm margins in the Report Properties dialog - see above) you will get something like this;

Report Builder 3:

The point that might surprise you is if you look at the page count in the ribbon you will notice that it is TWO pages long and if you forward on to the second page it's blank.

If you look at the image below you can see the huge amount of white space to the right and below the table;

Report Builder 3: White Space
Now when the table expands the white space is ADDED so in order to not have blanks to the right and below the table we need to re-size the background and remove as much of it as possible. After re-sizing this becomes;

Report Builder 3: Resized Report Minimising White Space
Now when you run the report and look at the Print Layout you will see that we are back down to a single page.

Now a little "quirk" of how this tidying up takes place is that if you expand the width of the title to 184.6mm and then re-run the report you'll find you're back to two pages. It seems that because the amount of white space is now significant it's actually being added back in again. The easiest way round this is to add a new column to the right of "Total";

Report Builder 3: New Column
Now mark the column visibility as "Hide" and re-run the report;

Report Builder 3: All On One Page
And now you're back to having your report on a single page.


Post a Comment