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.
The Custom SQL query that we used in this Dataset is shown below. Please type this inside the SSMS to see the Data.
-- 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. As you know, though it produces 16 pages, we always had to scroll up and down to view the last record and vice versa. In this situation, we can use Page Breaks to break the page using some conditions.
In this article, we will show you multiple ways to break the pages or to insert page breaks in the SSRS report.
Please refer to the 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.
Insert Page Breaks in SSRS Report using Groups
As you can see from the below screenshot 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 with General settings. If you want to change the Grouping column, use this section. For now, Go to the SSRS Page Breaks tab, and checkmark the below-shown option.
- Between each instance of a group: If you select this option, the 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 belonging 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 a 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 displays 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.
Go to Row groups, and right-click on the Details to open 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 for 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 the Sorting tab. Please delete the Sorting Column by clicking the Delete button
Click Ok to close the properties window, and click the preview button to see the above group in action. It will create a group and set 10 records in each group. Using this group, we have to break the flow.
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 the 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 Reports 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 the Add parameters.. option from the Context Menu. Once you click on the 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 to see 15 records in each page.
