You have a requirement where you need to display a fixed number of rows on each page of an SQL Server Reporting Services (SSRS) report, but there is no out of the box option to limit the number of records displayed per page. How can you limit the numbers of records per page for an SSRS report?
This tip assumes that you have previous experience building a simple SSRS Report.
In this article we will demonstrate how to display a fixed number of rows on each page of an SSRS report using a Tablix data region. I will use the AdventureWorksDW2008R2 sample database and SQL Server 2012 Reporting Services to demonstrate the solution.
I have already created a data source connection to the AdventureWorksDW2008R2 database, now let's create a dataset for the demo report. The below dataset returns Productkey and EnglishProductName.
Problem
You have a requirement where you need to display a fixed number of rows on each page of an SQL Server Reporting Services (SSRS) report, but there is no out of the box option to limit the number of records displayed per page. How can you limit the numbers of records per page for an SSRS report?
Solution
This tip assumes that you have previous experience building a simple SSRS Report.
In this article we will demonstrate how to display a fixed number of rows on each page of an SSRS report using a Tablix data region. I will use the AdventureWorksDW2008R2 sample database and SQL Server 2012 Reporting Services to demonstrate the solution.
I have already created a data source connection to the AdventureWorksDW2008R2 database, now let's create a dataset for the demo report. The below dataset returns Productkey and EnglishProductName.
DataSet Query
SELECT ProductKey, EnglishProductName FROM DimProduct WHERE ProductKey < 76
Let's add a Tablix to display the ProductKey and EnglishProductName. As you can see from the below image I have added a Tablix, but the Tablix will not display a fixed number of rows per page.
Follow these steps to display a fixed number of records per page
Add a Parent Group to the existing top level row group. The recently created Tablix doesn't have a row group, so right click on Details (under Row Groups section), click on "Add Group" and then Parent Group. You can refer to the below image.
Once you click on Parent Group, it will open a Tablix Group. In the Group By expression, enter=CEILING(RowNumber(Nothing)/25) where 25 is the number of records to be displayed per page. If you want to display 50 records then choose 50.
In the expression I have used the Ceiling function (which returns the smallest integer greater than, or equal to, the specified numeric expression) and the RowNumber function (which performs a running count of rows within a specified scope). The "Nothing" keyword is used when you want to perform a running count of rows for the topmost group or data region. You may have to replace the "Nothing" keyword with the name of a specific data region or group. There is no need to check the Add group header and Add group footer checkboxes. You can refer to the below image.
As you can see from the below image, a new row group named "Group1" has been created and SSRS has automatically added a new column in the Tablix.
Right click on the recently created group "Group1" and click on Group Properties. You can refer to the below image.
Once you click on Group Properties, it will open a Group Property window. Please give a proper name to this group, because SSRS by default gives a group name which is not descriptive. It is important to give a useful name, so that it will make sense to the next person who is trying to understand the report definition. You can refer to the below image.
Next, click on Page Breaks and then check "Between each instance of a group". Don’t choose to break at the start or end of a group. You can refer to the below image.
SSRS automatically creates sorting when a group is created and sorting will be the same as the Group By expression. When the "Page_Break_Group" group was created, sorting was also created. In our case this sorting expression will cause an error, so we have to remove it. To remove sorting go to the Sorting section and delete the sorting as shown below.
We don’t need the first column from the Tablix, this column was created automatically when we created the"Page_Break_Group" group. Lastly, delete the first column.
To delete the first column, right click on the first column then click on "Delete Columns". We have to keep the group because it contains the Ceiling function, so choose the second radio button "Delete columns only" and then click"OK". You can refer to the below image.
We have made all required changes, now the Tablix will display 25 records per page for the SSRS report. You can preview the report and verify for yourself.
