Display a fixed number of rows per page for an SSRS report

By:   |   Comments (12)   |   Related: > Reporting Services Formatting


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.

Adding DataSet

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.

Adding Tablix

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.

Adding Parent Row Group

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.

Tablix Group Expression Window

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.

Report After adding Row Group

Right click on the recently created group "Group1" and click on Group Properties. You can refer to the below image.

Group Properties

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.

Group Properties - Group Name

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.

Group Properties - Page Break

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.

Group Properties - Sorting

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.

Delete Group Column

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.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ghanesh Prasad Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, February 14, 2024 - 2:04:23 AM - Dhruv Back To Top (91945)
this is not working, I tried it and showing only one row per page.

Monday, October 30, 2023 - 11:20:32 AM - Billy C Back To Top (91718)
Everyone always shows this as an example but it does not work where you already have a parent/child grouping. If you add this solution to the mix it does not work.

Wednesday, February 23, 2022 - 4:31:39 AM - flx Back To Top (89824)
Wow, thanks.Thats a real contribution for all devs (who sadly still have to use Report RDLC) :)

Tuesday, April 27, 2021 - 2:03:41 AM - hamid Back To Top (88608)

Wednesday, September 5, 2018 - 9:22:08 AM - Symas Back To Top (77368)


i am printig label 1X8 using math.ceiling it is working perfectly. But client demand is label must print horizontaly not verticaly.

e.g. if i am printing 10 labels the in output i will get 2 page with 8 label on first page and 2 label on second page.

the problem is on 2nd page. label is printing in bottom instead of side.

to resolve this, i took one matrix and deleted its row group. i moved my label design in column group and group it

like this.

=(rownumber(nothing-1)) Mode 4

it is printig correctly until unless i give 9 labels

then i am getting this error

Microsoft Dynamics NAV

Rendering output for the report failed and the following error occurred: (processing): ScalableList: Index 1 outside the allowed range [0::0]

if any oe have any idea then please help me.


Monday, April 30, 2018 - 3:59:17 PM - stee Back To Top (75824)

†That does not work, I tried it an ony one row†is shown per page.

Friday, August 4, 2017 - 6:53:27 AM - Rajesh Solanki Back To Top (63954)

 Hi Ghanesh,


I am trying to implenet above solution in my report but i am getting error.


An error occurred during local report processing. 
The definition of the report '/PagingDemo' is invalid.
The GroupExpression expression for the tablix •Tablixl' has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to astring constant that is equal to either
the name of a containing group, the name of a containing data region, or the name of a dataset.


Monday, June 6, 2016 - 12:59:58 AM - Chen Back To Top (41612)


My row limit is not working because I have multiple groupings, I guess. How do I correct it???? Is there any work-around? At the same time, I still want to keep my grouping. I have two groups above Details.

Thursday, June 4, 2015 - 11:46:24 AM - Ghanesh Back To Top (37511)

Great! :) Etienne


Thursday, June 4, 2015 - 9:34:26 AM - Etienne Back To Top (37460)

Very cool, it works, I used this in the HIDDEN property

=IIF(Globals!RenderFormat.Name = "EXCELOPENXML",FALSE,TRUE)


Thursday, June 4, 2015 - 9:08:38 AM - Ghanesh Back To Top (37453)

Hello Etienne,

You can have two Tablix and you have to play around with Tablix visiblity.

On Tablix 1 you have to follow all the steps mentioned in this tip and this Tablix will be visible when user will be viewing the report in report manager.

On Tablix2 you dont need to follow any step from this tip and this tip will be visible when user exports the data into Excel.

Hope it will help you to implement the logic.



Thursday, June 4, 2015 - 8:27:41 AM - Etienne Back To Top (37445)

Thanks it works well, but when I export the data it export it into different sheets in Excel, how can I export it only in 1 Excel sheet but keep the rest of the functionality the same as in this article?


get free sql tips
agree to terms