Control Number of Rows per Page in SSRS

This is just a quick tour to show you how to control page breaks in SSRS report.

Sometimes you may want to control number of rows on each page of the report, for example, you want to put 10 rows on each page if the rendering extension is pdf.  To do that, you can work it out by using TSql, or simply you can just use SSRS to do it.

Now assume we have a query like below, and we want to control number of rows per page on the report.

 use staging  
 go  
 ;with cte as  
 (  
      select 1 as RowNbr  
      union all  
      select RowNbr + 1  
      from cte  
      where RowNbr < 50  
 )  
 select RowNbr from cte  

Firstly let’s get a blank report ready by adding data source and dataset.

 

Now add a parameter “RowNbr” to let user input number of rows needs to be displayed on each reporting page.

 

Next drop a table onto the report, and drag the dataset column into the table and remove empty columns. Your report now should be like demonstration below

 

Now in SSDT, go to the lower panel, under the “Row Groups” section, right click “Details” entry and click “Group Properties”. In the pop up window, navigate to the “Page Breaks” section, check the checkbox “Between each instance of a group”, and then close the pop up window.

After close the pop up window, leave the “Details” entry in selected status, go to the “Properties” window (which usually docked at the bottom-right corner, if you cannot see it on the IDE, go to the menu bar View – Properties Window). What we need to do now, is expand the “Page Break” under the “Group” section, and change the value of the “Disabled” property.

Expand the dropdown box of the “Disabled” property and select entry “<Expression…>”. On the expression editor window, input expression

 =IIF(Fields!RowNbr.Value mod Parameters!RowNbr.Value = 1, false, true)  

This basically tells the report after how many rows (controlled by report parameter) we would like page break to occur.

So that is all. Time to run the report and input number of rows you want to put on each page. Quite simple, isn’t it? :P