In this article we will show you, How to Insert Page Breaks in SSRS Report OR How to add Page Breaks to SSRS reports. This is one of the common requirement for every report developer.
For this example, We are going to use below shown Data Set. Please refer Shared Data Source and Dataset articles to understand the steps involved in creating Shared Data Source, and Dataset that we used for this report
and the Custom SQL query that we used in the above Data set is:
-- SQL Query using for Insert Page Breaks in SSRS Report Example
,SUM(FACT.[OrderQuantity]) AS OrderQty
,SUM(FACT.[TotalProductCost]) AS ProductCost
,SUM(FACT.[SalesAmount]) AS Sales
,SUM(FACT.[TaxAmt]) AS TaxAmt
FROM [DimSalesTerritory] AS Terr
INNER JOIN [DimGeography] AS GEO
ON Terr.SalesTerritoryKey = GEO.SalesTerritoryKey
INNER JOIN [FactInternetSales] AS FACT
ON Terr.SalesTerritoryKey = Fact.SalesTerritoryKey
GROUP BY Terr.[SalesTerritoryRegion]
Insert Page Breaks in SSRS Report
For this demonstration, We are going to use one of the previously designed report as we shown below. Please SSRS Table Report, Format Table Report in SSRS article to understand the steps involved in creating Table Report & format, and Add headers and Footer to SSRS Report article for adding Page headers.
Let me show you the Report Preview.
As you can see from the above screenshot, though we had 16 pages we always had to scroll up and down to view the last record and vice versa. In this situation we can use the Page Breaks to break the page using some condition. In this article we will show you multiple ways to break the pages, or to insert page breaks in ssrs report.
Insert Page Breaks in SSRS Report using Groups
As you can see from the below screenshot that our table report is Group By State Province name, and then By English Country Region Name. In this example we will break the page at each state province name. And to do this, Go to the Row groups pane, and click on the down arrow beside the State Province Name will open the menu. Please select Group Properties as we shown below.
Once you select Group Properties, below shown window will be opened.
Go to Page Breaks tab, and check mark the below shown option.
- Between each in instance of a group: If you select this option, Page will break when a new group starts. For example, Queensland in Australia is a new group instance so it will break at that point and display all records belong to Queensland in new page.
- Also at the start of a group: This will apply page break before Queensland
- Also at the end of a group: This will apply page break after Queensland
Just to show the page end position, we placed a custom Text Box inside the Page Footer.
From the below screenshot you can see that, the report is displaying 69 pages instead of 16.
As you see, report is displaying each State province Group in individual pages.
Insert Page Breaks in SSRS Report
Above approach is ideal if we had some Groupings. What happens if our report does not have any Groups?. For this we are suing the below shown table report. I suggest you to refer Add Row Numbers to SSRS Report to understand the below shown report.
Let me show you the report preview.
First, goto Row groups pane and right-click on the Details will open the context menu. From the context, Please select Add Group and then select the Parent Group option as shown in below screenshot.
Once you select the Parent Group option, a new Tablix group window will be opened to configure the grouping. In this example, We don’t have any Columns to group so we are clicking the Expression button to write some custom code.
Once you click on the Expression, a new window called the Expression will be opened. Use this expression window to write the custom expression. Following code will divide the records by 10. it means, we are grouping every 10 records as one group. I suggest you to refer Add Groups to Table report article.
Now you can see our newly created group. Before you start previewing the report. Please go to the Group properties
and navigate yourself to Sorting tab
Please delete the Sorting Column by clicking the Delete button
Click Ok to close the properties window, and click the preview button.
Next, let me apply the Page breaks using the approach that we explained in first example.
Next, we are deleting the Grouping column because we don’t want that extra column. To do so, right-click on the Group 1 column, and select Delete Columns from the context menu.
that will ask you, whether you want t delete the group, or only column. Please select Delete Column only option. Remember, if you seklect the other option then you have to start from creating a parent Group.
Now you can see that the report is displaying 10 records per page.
Let me go to second page.
Insert Page Breaks in SSRS Report using Parameters
We can also use Parameters to restrict the display records.In this example we will explain the same. Before you start, Please refer SSRS Parameters article to understand the steps involved in creating parameters.
In order to add Report Parameters, Right Click on the Parameters Folder present in the Report Data tab, and select Add parameters.. option from the Context Menu. Once you click on Add parameters.. option, it will open a new window called Report parameter Properties to configure the parameter properties.
Please add the default value as 15
Next, Please go to the Group properties and click the expression button beside the Group on property.
replace 10 with newly created parameter.
Now you can see that the report is display 15 records per page.
let me change it to 10
Above report is fulfilling our requirements but let me hide that parameter. To do so, right-click on the parameter will open the Report parameter Properties. Here, Please select the Parameter visibility option to Hidden.
Once you are done, Click OK to close the window
Thank You for Visiting Our Blog