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. 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.
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.
To the contrary, the hard page-break render generates 13 pages.
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.
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
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.
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.
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.
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.
Next we adjusted the length part of the page sizes down to 3 inches, so we can see the full effect of this change.
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.
The end result is illustrated next; I think the tab naming is a wonderful feature for report consumers!
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.
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
- Render Formats – http://msdn.microsoft.com/en-us/library/dd255288.aspx
- Read more SSRS formatting tips
