THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Stacia Misner

The Case of the Extra Page: Rendering Reporting Services as PDF

I had to troubleshoot a problem with a mysterious extra page appearing in a PDF this week. My first thought was that it was likely to caused by one of the most common problems that people encounter when developing reports that eventually get rendered as PDF is getting blank pages inserted into the PDF document. The cause of the blank pages is usually related to sizing. You can learn more at Understanding Pagination in Reporting Services in Books Online.

When designing a report, you have to be really careful with the layout of items in the body. As you move items around, the body will expand to accommodate the space you're using and you might eventually tighten everything back up again, but the body doesn't automatically collapse. One of my favorite things to do in Reporting Services 2005 - which I dubbed the "vacu-pack" method - was to just erase the size property of the Body and let it auto-calculate the new size, squeezing out all the extra space. Alas, that method no longer works beginning with Reporting Services 2008. Even when you make sure the body size is as small as possible (with no unnecessary extra space along the top, bottom, left, or right side of the body), it's important to calculate the body size plus header plus footer plus the margins and ensure that the calculated height and width do not exceed the report's height and width (shown as the page in the illustration above). This won't matter if users always render reports online, but they'll get extra pages in a PDF document if the report's height and width are smaller than the calculate space.

Beginning the Investigation

In the situation that I was troubleshooting, I checked the properties:

Item Property Value
Body Height 6.25in
  Width 10.5in
Page Header Height 1in
Page Footer Height 0.25in
Report Left Margin 0.1in
  Right Margin 0.1in
  Top Margin 0.05in
  Bottom Margin 0.05in
  Page Size - Height 8.5in
  Page Size - Width 11in

So I calculated the total width using Body Width + Left Margin + Right Margin and came up with a value of 10.7 inches. And then I calculated the total height using Body Height + Page Header Height + Page Footer Height + Top Margin + Bottom Margin and got 7.6 inches. Well, page sizing couldn't be the reason for the extra page in my report because 10.7 inches is smaller than the report's width of 11 inches and 7.6 inches is smaller than the report's height of 8.5 inches. I had to look elsewhere to find the culprit.

Conducting the Third Degree

My next thought was to focus on the rendering size of the items in the report. I've adapted my problem to use the Adventure Works database. At the top of the report are two charts, and then below each chart is a rectangle that contains a table.

In the real-life scenario, there were some graphics present as a background for the tables which fit within the rectangles that were about 3 inches high so the visual space of the rectangles matched the visual space of the charts - also about 3 inches high. But there was also a huge amount of white space at the bottom of the page, and as I mentioned at the beginning of this post, a second page which was blank except for the footer that appeared at the bottom. Placing a textbox beneath the rectangles to see if they would appear on the first page resulted the textbox's appearance on the second page. For some reason, the rectangles wanted a buffer zone beneath them. What's going on?

Taking the Suspect into Custody

My next step was to see what was really going on with the rectangle. The graphic appeared to be correctly sized, but the behavior in the report indicated the rectangle was growing. So I added a border to the rectangle to see what it was doing.

When I added borders, I could see that the size of each rectangle was growing to accommodate the table it contains. The rectangle on the right is slightly larger than the one on the left because the table on the right contains an extra row. The rectangle is trying to preserve the whitespace that appears in the layout, as shown below.

Closing the Case

Now that I knew what the problem was, what could I do about it? Because of the graphic in the rectangle (not shown), I couldn't eliminate the use of the rectangles and just show the tables. But fortunately, there is a report property that comes to the rescue: ConsumeContainerWhitespace (accessible only in the Properties window). I set the value of this property to True. Problem solved. Now the rectangles remain fixed at the configured size and don't grow vertically to preserve the whitespace. Case closed.

Published Friday, March 18, 2011 12:49 PM by smisner
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Kierslee said:

Thank you! Stacia you just saved me from going crazy. I just spent 3 hours on a very similar problem. Once I set "Consume Extra White Space" to true my problem was solved.

September 27, 2011 2:36 PM
 

smisner said:

Glad to help! :)

September 28, 2011 12:41 AM
 

Wayne said:

Thanks! Solved my PDF blank page prob too :-)

October 19, 2011 4:56 AM
 

SteveO said:

I still get a blank page, ah!

October 28, 2011 8:10 AM
 

smisner said:

HI Steve - That's too bad. Then the problem is likely to be something else. Add borders to the edges of each object to help you see the true edges. Double-check the page length and width against the report size. There IS a reason for the blank page!

October 30, 2011 3:40 PM
 

RobertP said:

OMG You just saved me from going completely insane, every one of reports was giving extra pages at the end.

THANK YOU!!!

December 2, 2011 5:00 PM
 

Joanne Millman said:

I do not see the option to Contain Consumer Whitespace in any of the property windows.  I'm working with Visual Studio 2005... any suggestions?

February 27, 2012 12:55 PM
 

smisner said:

Joanne, this feature is not available in Reporting Services 2005. You must have 2008 or higher.

March 4, 2012 1:18 PM
 

vac said:

I'm wondering how long I would be looking for this parameter without your nice article.

Thank You!

August 14, 2012 9:24 AM
 

nrps said:

Genius, thankyou - spent a very frustrating morning trying to get to the bottom of this.

August 30, 2012 8:32 AM
 

Jennifer said:

Thank you, thank you!  

November 2, 2012 12:26 PM
 

Anisha said:

Thank You..Thank You..Thank You...Only link that had solution to my report problem

May 15, 2013 12:31 PM
 

Barry Thankful said:

OMG THANK YOU! I have searched high and low for weeks for the solution to this STUPID problem.

........................,,-~*~,,

......................./:.:.:.:.:.|

......................|;.;.;.;.;./

......................|.;.;.;.;.|

............._,,,,,_.).;.;.;.;.|

.........,,-":.:.:.:."~-,;.;.;.|

........(_,,,,---,,_:.:.);.;.;..",,

......,-":.:.:.:.:.""-,,/;.;.;.;.;.",

.....(:.__,,,,,,,,,___);.;.;.;.;.;|

...../"":.:.:.:.:.:.:¯""\;.;.;.;.;.,"

....\",__,,,,,,,,,,,__/;;;;;;;;;/\

.....\.::.:.:.:.:.:.:.;.);;;;;;;;;/:\

.......\,,,,,---~~~~;;;;;;;;,"::::\

.........."""~~--,,,,,,,,,,-"::::::::::\

...................\::::::::::::::::::::::\

June 5, 2013 5:32 PM
 

Programmer said:

Thanks! I spent a good time trying to figure it out this.

August 29, 2013 10:06 AM
 

ian_scho said:

The 'Beginning the Investigation' section is gold for 95% of us with this problem.  Thanks for sharing.

September 12, 2013 7:41 AM
 

Steve said:

3 thoughts:

- Very helpful, I had forgotten about that property.

- Your second sentence is gibberish.  Please use grammar check if you're not going to proofread!

- Barry Thankful, I hope that's a thumb...

September 12, 2013 11:20 AM
 

Laxmi said:

Thanks for the post!

October 10, 2013 1:16 PM
 

Sanjay said:

3 comments for Steve

1) Forgotten - yes you are not god

2) gibberish - yes Staci is not god

3) I get the blank pages still - I am not god too

Cheers!

Sanjay

October 15, 2013 2:31 PM
 

Pavi said:

I am still getting blank pages.. :(

October 23, 2013 10:23 AM
 

Anil said:

I am using ssrs2005. where can i get that property. In 2010 i got that property but it hadn't worked by putting that ConsumeContainerWhitespace Property true. when i am converting my report to PDF it is giving blank pages between some of the data. For the 3 Pages it is giving 10 Pages. Please help me to solve this issue. thanks

November 13, 2013 1:19 AM
 

Sam said:

Funny!

December 3, 2013 4:26 PM
 

smisner said:

Anil, this property does not exist in SSRS 2005. You really need to analyze the size properties for Body,  and the margins and page size for Report. Also do you have a matrix or something that is expanding horizontally to push the body size out too much when factoring in the margins.

December 4, 2013 9:24 AM
 

Steve Obarowski said:

THANKYOUTHANKYOUTHANKYOUTHANKYOU!

This has been driving me crazy for hours

December 5, 2013 12:48 AM
 

Roshen Chackalayil said:

I Still had the extra blank page / alternate blank pages. Fortunate enough to find that the columns were not wrapping down. Reduced the column widths' and it worked.

March 26, 2014 3:49 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement