Insert Page Breaks in SSRS Report

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

Insert Page Breaks in SSRS Report 1

and the Custom SQL query that we used in the above Dataset is:

-- SQL Query using for Insert Page Breaks in SSRS 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 SSRS Table ReportFormat 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.

Insert Page Breaks in SSRS Report 2

Let me show you the Report Preview.

Insert Page Breaks in SSRS Report 3

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.

Insert Page Breaks in SSRS Report 4

Once you select Group Properties, the below-shown window will open.

Insert Page Breaks in SSRS Report 5

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
Insert Page Breaks in SSRS Report 6

To show the page end position, we placed a custom TextBox inside the Page Footer.

Insert Page Breaks in SSRS Report 7

From the below screenshot, you can see that the SSRS Page Breaks report is displaying 69 pages instead of 16.

Insert Page Breaks in SSRS Report 18

As you see, the report is displaying each State province Group in individual pages.

Insert Page Breaks in SSRS Report 9

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 SSRS Report to understand the below-shown report.

Insert Page Breaks in SSRS Report 10

Let me show you the report preview.

Insert Page Breaks in SSRS Report 11

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.

Insert Page Breaks in SSRS Report 12

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.

Insert Page Breaks in SSRS Report 13

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 report article.

Insert Page Breaks in SSRS Report 14

Now you can see our newly created group. Before you start previewing the report. Please go to the Group properties

Insert Page Breaks in SSRS Report 15

and navigate yourself to Sorting tab

Insert Page Breaks in SSRS Report 16

Please delete the Sorting Column by clicking the Delete button

Insert Page Breaks in SSRS Report 17

Click Ok to close the properties window, and click the preview button.

Insert Page Breaks in SSRS Report 18

Next, let me apply the Page breaks using the approach that we explained in the first SSRS example.

Insert Page Breaks in SSRS Report 19

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.

Insert Page Breaks in SSRS Report 20

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.

Insert Page Breaks in SSRS Report 21

Now you can see that the report is displaying 10 records per page.

Insert Page Breaks in SSRS Report 22

Let me go to the second page.

Insert Page Breaks in SSRS Report 23

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 SSRS 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.

Insert Page Breaks in SSRS Report 24

Please add the default value as 15

Insert Page Breaks in SSRS Report 25

Next, please go to the Group properties and click the expression button beside the Group on the property.

Insert Page Breaks in SSRS Report 26

Replace 10 with a newly created parameter.

Insert Page Breaks in SSRS Report 27

Now you can see that the SSRS Page Breaks report is to display 15 records per page.

Insert Page Breaks in SSRS Report 28

Let me change it to 10

Insert Page Breaks in SSRS Report 29

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.

Insert Page Breaks in SSRS Report 30

Once you are done, Click OK to close the window

Insert Page Breaks in SSRS Report 31