Which Of The Following Is An Example Of A Control Break Report?

By: | Updated: 2015-03-04 | Comments (21) | Related: > Reporting Services Formatting

Problem

The terminal 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 programmer who has worked with SQL Server Reporting Services for whatsoever fourth dimension will definitely say that coordinating and navigating page breaks and folio numbering tin be quite a challenge. Information technology seems that Newton's Tertiary Law, "For every action, in that location is an equal and opposite reaction", applies to setting folio sizes and page breaks in SSRS. Often ane change or aligning, which seems to correct one upshot with paging, very well could crusade some other unforeseen change to another page setting.

In this tip, we will review the treatment the various page and page break settings in order to reach the all-time visual result for your study consumers. We volition showtime our examples at the return level and then movement to the report level and finally to the object level.

We volition use the Run a risk 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. In one case yous download and install the SQL Server databases, we will afterwards utilize SQL Server Data Tools for Business Intelligence (SSDT-BI) for Visual Studio 2013 to develop reports.

SQL Server Reporting Services Folio Settings and Page Breaks

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

SSRS Soft Folio Breaks

The soft page-interruption renderers include outputs that are generally displayed on a screen and includes the SSRS preview, HTML and MHTML formats. This format utilizes the curl bar instead of inserting a vertical page break which of grade is advisable 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 return 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 arrange to include orphaned and oversize objects in a report

SSRS Difficult Page Breaks

To the contrary, the hard folio-interruption render formats includes those formats that are more centered around physical page outputs which are mostly 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-suspension render include:

Pages moves left to right and and then top to lesser
Inserted, explicit, or forced folio breaks are honored
Items that are gear up to go along together may still be pushed to a next page if not enough spaces exists

These two render formats are driven by ii report properties constitute 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 i of the hard break renderers, and so the PageSize and Margins backdrop are used.

Report Properties

Using a Sales past Region written report as an case, you can quickly see that the ii render formats generate 2 very unlike page results. The soft folio interruption renders but viii pages as shown next.

soft page break

To the opposite, the hard folio-interruption render generates 13 pages.

hard page break

Of course, if we force a page break betwixt groups, using the state field for the below example, we actually have an equal number of pages for both the difficult and soft interruption render format. We tin similarly add a page break earlier or after objects such every bit 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 considering the report object resulting size is less than the Interactive and Page size properties. To monitor your object sizes, it is usually a skillful 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 tin 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 non hateful the tablix will fit on a unmarried page when the report is executed. The reason for this state of affairs is that a report designer could easily have multiple column and row groups which expand the number of columns based on these groupings. For case, if i of the groupings is year, the report volition actually generate a cavalcade for each year in the dataset

Turn on Ruler
Ruler

Actually, nosotros tin produce the contrary effect on a soft folio-break return format, at least at the tablix level by setting the "Keep together on one folio if possible" setting every bit evidence below.

Keep Together

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

keep together result

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

keep together pdf

SSRS Folio Numbering Options

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

Page Number - Current folio number which can be reset by grouping and object backdrop, affected by the reset page number property
Full Pages - Full pages inside the group or object, affected past the reset folio number holding
Overall Folio Number - Page number irrespective of the reset page number backdrop
Overall Total Pages - Total pages irrespective of the reset page number properties

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

Page Number fields

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

Page Size Adjustment

Finally, as displayed below, for the StateProvince grouping, we tell SSRS to strength a page interruption at the get-go 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 cease result is illustrated next; I recollect the tab naming is a wonderful characteristic for report consumers!

Excel Page Break

The pdf render, shown beneath, is equally impressive. Nosotros can conspicuously come across in the example written report that the page name mimics the Land / Province in the Country Province group, while the overall page counts and the group page counts displays the overall page counts and the grouping folio counts respectively. You should also notation that these command properties can be every bit set at the tablix level.

pdf page break

Conclusion

Even afterwards designing an awesome study, inappropriately placed folio breaks tin sink the reports effectiveness. When designing a report, intendance and lots of testing need to exist exercised to create the correct location for a folio intermission; part of that care needs to be determining the ultimate render format needed for the report.

SSRS honors 2 major render formats. First, the soft folio-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 folio-intermission 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 every bit a guide for displaying the report and generally ignores the Margin settings; the hard pause format adheres strongly to the PageSize settings and also to the Margins.

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