Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (5)   |   Related Tips: > Reporting Services Formatting

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.

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


Last Update:






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.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, June 06, 2016 - 12:59:58 AM - Chen Back To Top

 

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 04, 2015 - 11:46:24 AM - Ghanesh Back To Top

Great! :) Etienne

 


Thursday, June 04, 2015 - 9:34:26 AM - Etienne Back To Top

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

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

 


Thursday, June 04, 2015 - 9:08:38 AM - Ghanesh Back To Top

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.

Ghanesh

 

Thursday, June 04, 2015 - 8:27:41 AM - Etienne Back To Top

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?

Etienne


Learn more about SQL Server tools