March 27, 2008 at 6:50 am
Hello all,
I have been writing reports for SQL Server Reporting Services that are formatted for printed output (i.e. with pages, and not on the Web).
Often, these reports involve tables with group headers, etc. In the details section, if the fields being passed to the report are coming from a memo/long text field, SSRS goes to extreme lengths to keep the items together.
Pages are immediately broken, leaving a lot of blank space on the first page, and the text within the memo field could be carried on to one or more pages afterward. When this happens, any group headers in the table do not print on the following pages, even if the property of the headers are set to Print On New Page.
In short, any time there is an implicit page break (a page break forced by SSRS due to items not being able to fit on the page), the results regarding table headers and memo fields are highly unpredictable, save the fact that there are almost always blank pages in the middle.
I have changed the margins, and the sizes of items in the report layout to be as small one can make it to make sure items fit on the page.
This implicit page break behavior is even worse with Matrix-based reports.
Is this page break problem rectified in RS 2005 at least?
March 27, 2008 at 7:00 am
What happens if you export to PDF and then print?
Do the memo\long text fields containg special characters like carraige returns and line feeds? If they do you should try removing them and inserting spaces to see if that helps.
It's been awhile since I have worked in RS, but I do remember that, at least in 2000, printing and page breaks were more consistent in the export to PDF option than in printing from the web.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 27, 2008 at 8:16 am
The application for which these reports are being written does not use the HTML implementation of SSRS. Most reports are being printed to paper or exported to PDF.
As a result, the "Print Preview" view that is seen in Visual Studio (2003) is where the problems are occurring.
March 27, 2008 at 8:45 am
In my experience you really can't trust the preview tab in visual studio. A better way to test reports is to set your configuration to DebugLocal which will open a new process to view the report. You get a truer representation of the report and if there is a problem you can kill the report without having to kill VS.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 28, 2008 at 11:42 am
I had a similar problem with blank pages or page breaks cutting up detail lines. I worked with Microsoft on this and another problem. They had no answer for the breaking problem. They ended up creating a patch for an intermittent PDF/TIFF hanging problem during report rendering.
To solve the page breaking issues, I ended up (in my stored proc) having it manually count the detail lines and then force a page break at defined intervals to keep multiple row detail together on the same page.
To do this, I put an additional detail line in with a rectangle set up with a small table in it and set the "page break after table" option on the table. The visibility of the rectangle is dictated by a value from the stored proc. I pass it a Y or N to indicate whether to break or not.
This is a shabby way to have to do something but it works.
June 30, 2008 at 10:06 am
Scott-
Could you please tell me how you (in your stored proc) had it manually count the detail lines? Also what do you mean by this: "The visibility of the rectangle is dictated by a value from the stored proc. I pass it a Y or N to indicate whether to break or not."? How did you do that in the sp?
June 30, 2008 at 10:44 am
Yes, the stored proc passes a Y or N to set the visibility of the rectangle.
As far as counting detail lines, I create a temp table using my final recordset and an identity column.
Select IDENTITY(int, 1,1) AS ID_Num, *, 'N' as DetailPageBreak
into #tempLoadPageBreaks
From FinalRecordSet
Order By Whatever
Now ID_Num gives you a reference point to base your pagebreaks on.
Use an update statement to update the DetailPageBreak to 'Y' based on your business needs and use ID_Num as the reference point. If you want to break on every 10th detail line, the ID_Num will divide evenly with 10. Set that DetailPageBreak to 'Y'
My update statements are pretty wordy, I had requirements that dictated that some pages break on 4 or 5 detail lines, then others were 7 or 8 depending on the amount of data in the detail and what type of page was being displayed (first page has a header, the others don't). I also had complex groupings so I tracked the min and max id for that grouping.
All this seems like something reporting services should handle internally and maybe with 2008 it might. I haven't researched the issue in months.
Good luck with your endeavor.
July 1, 2008 at 9:21 am
Thanks, Scott. I can't even get one record to fit on one page much less 10. Maybe I'll just have them export to Excel before printing. (Oh and I should've said I'm on 2005.) Thanks again!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply