In this article, we will show you how to Insert Page Breaks in SSRS Report. Or, How to add Page Breaks to SSRS reports. It’s one of the standard requirements for every report developer.
For this SSRS page breaks example, We are going to use the below-shown DataSet. Please refer to Shared Data Source and Dataset to understand the steps involved in creating Shared Data Source and Dataset that we used for this SSRS report
and the Custom SQL query that we used in the above Dataset is:
-- Query using for Insert Page Breaks in a Report Example
USE [AdventureWorksDW2014]
GO
SELECT Terr.[SalesTerritoryRegion]
,Terr.[SalesTerritoryGroup]
,GEO.[EnglishCountryRegionName]
,GEO.[StateProvinceName]
,GEO.[City]
,GEO.[PostalCode]
,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]
,Terr.[SalesTerritoryGroup]
,GEO.[EnglishCountryRegionName]
,GEO.[StateProvinceName]
,GEO.[City]
,GEO.[PostalCode]
Insert Page Breaks in SSRS Report
For this SSRS Page Breaks demonstration, We are going to use one of the previously designed reports, as we showed below.
Please refer Table Report, Format Table article to understand the steps involved in creating Table Report & format, and Add headers and Footer article for adding Page headers.
Let me show you the Report Preview.
As you 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 the 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 SSRS Page Breaks example, we will break the page at each state province name. For this, Go to the Row Groups pane, and click on the down arrow beside the State Province Name. Please select the Group Properties option.
Once you select Group Properties, the below-shown window will open.
Go to SSRS Page Breaks tab, and checkmark the below-shown option.
- Between each 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 on a 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 use page break after Queensland
To show the page end position, we placed a custom TextBox inside the Page Footer.
From the below screenshot, you can see that the SSRS Page Breaks report is displaying 69 pages instead of 16.
As you see, the report is displaying each State province Group in individual pages.
Insert Page Breaks in SSRS Report
The above approach is ideal if we had some Groupings. What happens if our report does not have any Groups?.
For this, we are using the below-shown table report. I suggest you refer to Add Row Numbers to understand the below-shown report.
Let me show you the report preview.
Goto Row groups, and right-click on the Details opens the context menu. From it, Please select Add Group and then select the Parent Group.
Once you select the Parent Group option, a new Tablix group window will be opened to configure the grouping. In this SSRS Page Breaks example, We don’t have any Columns to the group. So we are clicking the Expression button to write some custom code.
Choosing the Expression will open a new window called the Expression. Use this expression window to write the custom expression.
The following code will divide the records by 10. it means, we are grouping every 10 records as one group. Refer Add Groups to Table 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 the first SSRS 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 whether you want to delete the Group or only column. Please select Delete Column only option. Remember, if you select the other option, then you have to start by creating a parent Group.
Now you can see that the report is displaying 10 records per page.
Let me go to the second page.
Insert Page Breaks in SSRS Report using Parameters
We can also use Parameters to insert page breaks in SSRS Report or say, to restrict the display records. In this example, we will explain the same. Before you start, Please refer Parameters article to understand the steps involved in creating parameters.
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 the property.
Replace 10 with a newly created parameter.
Now you can see that the SSRS Page Breaks report is to display 15 records per page.
Let me change it to 10
The above SSRS Page Breaks 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
