SQL Server Reporting Services Controlling Report Page Breaks

By:   |   Comments (22)   |   Related: > Reporting Services Formatting


Problem

The final report display for SQL Server Reporting Services reports various depending on the method of output and I want to know how to control and appropriately set pages and page breaks in SSRS?

Solution

Any report developer who has worked with SQL Server Reporting Services for any time will definitely say that coordinating and navigating page breaks and page numbering can be quite a challenge. It seems that Newton's Third Law, "For every action, there is an equal and opposite reaction", applies to setting page sizes and page breaks in SSRS. Often one change or adjustment, which seems to correct one issue with paging, very well could cause another unforeseen change to another page setting.

In this tip, we will review the handling the various page and page break settings in order to achieve the best visual result for your report consumers. We will start our examples at the render level and then move to the report level and finally to the object level.  

We will use the Adventure Works databases as the basis for our SSRS report examples. The 2014 versions of the regular and data warehouse databases are available on Codeplex at https://msftdbprodsamples.codeplex.com/releases/view/125550. Once you download and install the SQL Server databases, we will subsequently use SQL Server Data Tools for Business Intelligence (SSDT-BI) for Visual Studio 2013 to develop reports.

SQL Server Reporting Services Page Settings and Page Breaks

At the highest level, page breaks are dictated by the render format used to display the report. The render formats are actually broken into two categories: 1) a soft page-break renderers and 2) hard page-break renderers.  

SSRS Soft Page Breaks

The soft page-break renderers include outputs that are generally displayed on a screen and includes the SSRS preview, HTML and MHTML formats. This format utilizes the scroll bar instead of inserting a vertical page break which of course is appropriate for display or screen based. Both the horizontal and vertical page breaks are approximated and are not exact locations. Somewhat surprisingly, the Excel and Word render formats also fall within the soft render category. Some additional traits of the soft page-break render format include:

  • Inserted, explicit, or forced page breaks are honored
  • Margins are not applied
  • Report sizes can adjust to include orphaned and oversize objects in a report        

SSRS Hard Page Breaks

To the contrary, the hard page-break render formats includes those formats that are more centered around physical page outputs which are generally printed and include pdf, image, and print formats. This format puts hard breaks at very specific vertical and horizontal points of the report. Some additional traits of the hard page-break render include:

  • Pages moves left to right and then top to bottom
  • Inserted, explicit, or forced page breaks are honored
  • Items that are set to keep together may still be pushed to a next page if not enough spaces exists

These two render formats are driven by two report properties found in the Page Category section of report properties.  The InteractiveSize Width and Height control the size of the soft page-break render formats. When an object is displayed using one of the hard break renderers, then the PageSize and Margins properties are used.

Report Properties

Using a Sales by Region report as an example, you can quickly see that the two render formats generate two very different page results. The soft page break renders just 8 pages as shown next.

soft page break

To the contrary, the hard page-break render generates 13 pages.

hard page break

Of course, if we force a page break between groups, using the state field for the below example, we actually have an equal number of pages for both the hard and soft break render format. We can similarly add a page break before or after objects such as rectangles, tablixes, and lists. 

page break group
PDF
group hard break
HTML Preview
group soft break

The reason the page counts are the same is because the report object resulting size is less than the Interactive and Page size properties. To monitor your object sizes, it is normally a good idea to not only look at the page size settings, but also to display the ruler on the design grid.

The ruler is not shown by default, but can be enabled by clicking Report on the Menu Bar, and then selecting View > Ruler. However, with objects such as tablix, just because the tablix fits fully on the page in the Design view, does not mean the tablix will fit on a single page when the report is executed. The reason for this situation is that a report designer could easily have multiple column and row groups which expand the number of columns based on these groupings. For instance, if one of the groupings is year, the report will actually generate a column for each year in the dataset

Turn on Ruler
Ruler

Actually, we can produce the opposite effect on a soft page-break render format, at least at the tablix level by setting the "Keep together on one page if possible" setting as show below.

Keep Together

As displayed below in the report preview, this setting is for the soft page break rendering and keeps the entire tablix on one page display.  I need to express a very big "caution" in using this property. If you use this property on a report which has a large number of rows and / or columns being returned to the report object, the report server can easily use up all available memory attempting to display the entire report one page. Furthermore, trying to display all the data on one page also impacts performance. For instance, one report that was actually 810 pages, actually took 10 minutes to render when this option was set.

keep together result

To the contrary, this setting has minimal or no effect in the hard-break renders; the screen print below shows the pdf with this setting selected. The same number of pages, 13, are displayed for the pdf render format.

keep together pdf

SSRS Page Numbering Options

In addition to forcing page breaks at certain points, we can also set and reset the page numbers and page names that are rendered in the various formats. Within SSRS we have 4 global page number fields that can easily be added to a report page header or footer:

  • Page Number - Current page number which can be reset by group and object properties, affected by the reset page number property
  • Total Pages - Total pages within the group or object, affected by the reset page number property
  • Overall Page Number - Page number irrespective of the reset page number properties
  • Overall Total Pages - Total pages irrespective of the reset page number properties

Furthermore, we can actually set the PageName property (and InitialPageName property) and also display this field on our report. The PageName property additionally serves a really neat purpose for exporting to Excel; it actually handles naming each individual tab for each page break. Let us show an example of this situation using a group setup.  First, as shown next, we add  1) PageName field ( left most text box in footer ),  2) Overall Pages and Total Overall Pages in the middle two text boxes and 3) Page Numbers and Total Pages per each group (and reset in each group) in the right two text boxes.

Page Number fields

Next we adjusted the length part of the page sizes down to 3 inches, so we can see the full effect of this change.

Page Size Adjustment

Finally, as displayed below, for the StateProvince group, we tell SSRS to force a page break at the start of each group, we set the ResetPageNumberField to true to reset the pages within each group, and finally, we assign the PageName to be the StateProvince value.

Group Page Break Settings

The end result is illustrated next; I think the tab naming is a wonderful feature for report consumers!

Excel Page Break

The pdf render, shown below, is equally impressive. We can clearly see in the example report that the page name mimics the State / Province in the State Province group, while the overall page counts and the group page counts displays the overall page counts and the group page counts respectively. You should also note that these control properties can be equally set at the tablix level.

pdf page break

Conclusion

Even after designing an awesome report, inappropriately placed page breaks can sink the reports effectiveness. When designing a report, care and lots of testing need to be exercised to create the correct location for a page break; part of that care needs to be determining the ultimate render format needed for the report.

SSRS honors two major render formats. First, the soft page-break render includes the report preview, Word, Excel, and HTML and is more designed for reports which are displayed on a screen. To the contrary, the hard page-break format is utilized by pdf and image format among others, and is more designed for physical printing of reports. A soft break render uses InteractivePageSize as a guide for displaying the report and generally ignores the Margin settings; the hard break format adheres strongly to the PageSize settings and also to the Margins.

Furthermore, when displaying pages on a report, the PageName, Page Number, Total Pages, Overall Page Number, and Total Overall Pages properties can be used to display both total pages and page numbers within groups or tablixes. As an added feature, when exported to Excel, the PageName property is used to name each tab for an exported report.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, June 21, 2023 - 5:17:29 PM - Brad Larsen Back To Top (91325)
I've had some success using the "Disable" property for page breaks. It only affects the break property, not others such as reset page numbering.

My thought would be to add a thin dummy break that only does a page break, then disable it's page break whenever it's odd or even (whichever your testing proves as workable). I've never tried this, but it seems theoretically viable.

Monday, December 11, 2017 - 1:06:48 PM - Scott Murray Back To Top (73889)

One other ideas is to name each page (maybe a student name / number) and then use custom code code to check the page numer.  If even then just go to the next page; if code insert a page break.  Just theorizing as I have never done that.

 

Monday, December 11, 2017 - 12:51:41 PM - Scott Murray Back To Top (73888)

For this particular situation, I am almost wondering if a dynamic data subscription would be a better option as it is very difficult to control what the printer does.

Monday, December 11, 2017 - 12:08:11 PM - Jason Back To Top (73885)

Hi Scott,

I have found your info on this site very helpful, so thank you.  I am wondering if you have advice on the following scenario.  (I have searched the web but  have been unsuccessful).  I have a report card created for a middle school - due to the nature of report cards - i.e. some students take more classes than others, some teachers write more comments, etc. - the report card can be anywhere from one to three pages in length.  My goal is to be able to print these double sided.  However, I cannot find a way to create a page break that addresses this situation.  The issue now is,  if Student A has a 1 page report, the first page of Student B's report is printing on the back (page 2) of Student A's report.  I need to be able to somehow create an extra blank page for any reports that are 1 or 3 pages in length so that the next student's report always begins on page 1.  Is there a way to do this using SSRS?

 

Thanks

Sunday, November 26, 2017 - 9:53:15 AM - Dietrich Herrmann Back To Top (73210)

 Hello Scott,

your Site is a great Site. Very helpful!!
My problem:
My report has a Tablix with some Columns. The Tablix has in design time a width which requires a page of landscape format.
Now I set some Columns to hidden because of a value of a parameter. Thereby the Tablix has a width which requires a portrait format only.
The question is:
How can I set the report to portrait format because of the value of the parameter?
I show the report via ReportViewer Control in VisualBasic (VS2017) and want to see the report as a portrait format at runtime.

Regards-
Dietrich

Friday, December 16, 2016 - 3:08:29 PM - Rebeccah Back To Top (44991)

 

Thank you for the advice! The report was given to me with the main/sub report format, though the main report is only headers so I can try recreating the subreport in the main report body. It will make my datasets more complicated as the report can be run by individual invoice numbers, individual batch numbers or a range of either. The subreport is linked by invoice number, so that is the only thing the other datasets match on. The images for example do not have batch number and would need to be joined to another table to filter that way.

I will consider/experiment with this, but it looks like I am stuck with my two bad options :(

Rectangles cause the same page number issues and add empty white space when they are not visible

Friday, December 16, 2016 - 1:47:12 PM - Scott Murray Back To Top (44988)

 

A couple suggestions I can make. ....

 

SSRS does NOT handle page breaks in sub reports well!  You may be able to control the numbering by putting everything in one report. You could also explore using rectangles to control the page break locations.  

Friday, December 16, 2016 - 12:29:01 PM - Rebeccah Back To Top (44986)

I would love to find the page break/page number solution as that is the most straight forward way, but I am not sure how to explain it better.

The invoices are printed in batches (mostly, so we will stick with the simplest forms). If there are NO page breaks in the report at all, then each invoice shows page x of invoice total correctly. The invoices vary between 1 to more than 20 pages. There are three tablix in the subreport which may or may not be displayed depending on the type of invoice it is. Each tablix has a header which are all similar and which should appear at the top of every page (this is now working!) 

The problem is depending on the length of comments (In Tablix1), the number of rows in Tablix2, and the number and size of images (in Tablix3), Tablix3 will not always start on a new page. This means that I have the correctly repeating header from either Tablix1 or Tablix2, the last rows of that tablix and then the Tablix3 header all on the same page. Interestingly, I haven't seen any cases where there are details from Tablix3 on this page, they start on the following page after the (again, correctly repeating) header.

I would like to insert a page break before Tablix3 and call it a day, and this works if I only pull up a single invoice. However, when I pull up multiple invoices, the page numbers all revert to page 1 of 1 even though these invoices are all at least 3 pages long. 

I have tried explicitly adding the <ResetPageNumber>False</ResetPageNumber> to the page break, but that had no effect. 

Any help you can give is immensely appreciated! Thank you!!

Friday, December 16, 2016 - 11:08:29 AM - Scott Murray Back To Top (44984)

 

Rebeccah... not quite following completely your issue.  There re definite limitations with page breaks and number in SSRS, but many can be addressed in one fashion or another.

Friday, December 16, 2016 - 10:33:45 AM - Rebeccah Back To Top (44983)

This is a very informative tip, however, it does not address my issue with page breaks. Any page breaks in my invoice reset all page numbers to "1 of 1" when pulling multiple invoices. This is leaving me with two unacceptable choices, pages appearing with two "headers" between sections of the invoice or incorrect page numbers on every invoice.

Thursday, August 4, 2016 - 7:39:41 PM - Scott Murray Back To Top (43056)

 I am not sure you can do what you want to do.. You can control page breaks by the number of rows for instance, but cannot decide a page break after the page breaks have been defined.

Thursday, August 4, 2016 - 1:30:57 PM - Jen Back To Top (43054)

 Hi there, 

I created a report in SSRS 2012 that has one tablix that's duplicated based on how many entries the end user selects. Instead of having one tablix on each page, I'm trying to create a conditional page break that will check if rows in a tablix are split onto two pages, and push the entire tablix onto the next page if it's split. I've tried both soft breaks and hard breaks, but can't seem to figure it out. Can anyone help with this? Thank you!

-Jen

 

Tuesday, November 17, 2015 - 4:35:30 PM - Scott Murray Back To Top (39089)

Did you turn off keep together?

Tuesday, November 17, 2015 - 12:30:15 PM - Ekta Back To Top (39087)

Hi,

 

The information was really very useful. However, I am still having an issue with my report. I have a report that should be available in Internet explorer in HTML format. I am using soft page breaks an dthe report works fine until it fetched more than 5K records.

The report works absolutely fine in Visual Studio but when I deploy it on server it fails.

Can you help with this issue?

 

Thanks

Ekta

 

 

Wednesday, October 21, 2015 - 6:15:21 PM - CarlD Back To Top (38956)

Unfortunately, while this article does mention a number of good tidbits about SSRS page breaks, it skips over the most fundamental design error in the pagination of the "soft break" renderers (i.e. the "RPL" renderer):  This renderer completely ignores the interactive page size WITHIN A SINGLE TABLIX.  If you have a tablix that's 10,000 rows long, the RPL render will not insert a page break until after that tablix, regardless of how large the tablix is or what the interactive page size is set to.

This makes the simplest type of report - a single table - unmanageable in the browser if the report results are large.  Instead of paginating a 10,000 row table into e.g. 200, 50-row pages, it will emit a single 10,000 row table to the browser, which makes the browser basically lock up for a long time while it tries to layout that huge page, or possibly just outright crash.

Please vote on this item on Connect if you'd like to see this addressed after being wrong for over 10 years.

https://connect.microsoft.com/SQLServer/feedback/details/1246029/ssrs-report-interactivesize-is-ignored-by-the-rpl-renderer-which-is-used-for-all-interactive-viewing

Friday, October 2, 2015 - 3:03:24 PM - Scott Murray Back To Top (38815)

Ritesh,

 

Check your page size and interactive page sizes.

Friday, October 2, 2015 - 1:41:16 PM - ritesh sonea Back To Top (38813)

When I export the final report to PDF I have blank pages in betewen each page.  Not sure why its creating blank pages...everything seems to fit within the margins I've set.


Thank you

Wednesday, August 19, 2015 - 10:43:28 AM - ram Back To Top (38481)

thsi is right ans for sql server 2005.

Wednesday, August 5, 2015 - 12:32:57 PM - Julie Back To Top (38384)

Thanks for the suggestions. I do, indeed, have the keep together option in effect.   As far as exporting, I am exporting to pdf.  The page break option after x amount of rows won't work unfortunately, as the size of my rows change frequently.  I just wish to not break within a row.

Tuesday, August 4, 2015 - 11:29:35 PM - Scott Back To Top (38370)

Julie... Not sure that page breaks are the most widespread issue in SSRS, but it is very problem matic.  I will make a few suggestions that maybe you already have explored.  First is using the keep group together option. Furthermore, you did not mention whether you are printing directly or via pdf or Excel. You may get better results exporting to pdf.  Also you can explore setting page breaks after x number of rows.

Tuesday, August 4, 2015 - 4:11:55 PM - Julie Back To Top (38368)

Hi, I have scoured the internet for clues on how to defeat what seems to be one of the most widespread issues with SSRS.  No matter how many controls I set to limit page breaks, I can't seem to stop them. I cannot set a specific point, as in after each instance of a group as sometimes my groups are really little and wasting 3/4 of a page is unnacceptable. I just want to be able to say that if the group is running too long, push it to the next page, and vice versa. If the group ends halfway down and the next deliverable is 3/4 of a page long, then push that one to the next page. There does not sem to be a button that says, no matter what, NEVER split a deliverable between two pages.  It seems intuitive that this should be an option.  I understand the black box that exists btw the report and rendering, however, this is an ongoing, and HUGE issue for my users, who can't fathom having to look to the previous page for information on the deliverable they want to see.

Tuesday, May 5, 2015 - 8:25:49 AM - AlonM Back To Top (37095)

Hello,

Thanks!

 

Which version of Report viewer do you use?

My problem is with the ResetPageNumber property. I use the 10.0 version, can see that prop but not use it.

Anybody can help?

 

Thank all.














get free sql tips
agree to terms