This article demonstrate how to display a fixed number of rows per page in SSRS.
Lets see step by step implementation of how to fix number of rows on per page in SSRS report.
Later, we will see how to change the number of rows per page in report dynamically based on user’s input value, using a parameter in SSRS.
Here we have a sample report as shown below.
Lets see the report preview, click on Preview tab.
You can see, there are 15 records in report. Assume that you want to see only 3 records per page in report, it can be done using a using a Tablix data region. Lets go through the step as shown below.
Right click on Tablix, then navigate to Add Group and select Parent Group..
After that, a Tablix group dialog box opens.
Click on expression (fx).ย
After that, write a following expression.
=Ceiling(Rownumber(Nothing)/3)
=Ceiling(Rownumber(Nothing)/3)ย , here 3 is the number of rows that would be displayed per page.
If you want to display any other number of rows per page then you can replace three with that number.
In the above expression, a RowNumber function, returns a running count of all rows in the specified scope, and Nothing specifies the outermost context, which performs a running count of rows for the topmost group or data region.
Now the row value returned by RowNumber function is provided to Ceiling function, which returns the smallest integer greater than, or equal to, the specified numeric expression.
After that, click OK button. You get back to Tablix group dialog box, just click on OK button.
Once you click on OK button, you will see a new row group named Group1 is created.
After that that go to Group1 property
After that, a Group properties window opens, Go to Sorting tab then select the first column and click on Delete.
After that, Go to Page Breaks tab, then select Between each instance of a group.
Next click on OK button.
After that, click on OK button.
Now Right click on Group1 column, and select the Delete Columns.
After that, select Delete columns only option.
Next, click on OK button.
Now you can see, Group1 column is deleted.
Lets see the report preview, and you can see first page displays first three rows only.
On next page 2, you can see it displays next 3 records from row number 4 to 6.
So far, we have seen how to limit the rows number per page, and to display three rows per page we have provided a hard code value 3 in expression.
In case, if you want to change the number of rows per page dynamically based on input value provided by user then you can use parameter.
Limit number of rows per page by parameter
Lets displays the number of rows per page dynamically using the parameter.
User will provide the number of rows per page value to the parameter as input and based on that value row per page in report will be displayed.
For this we will be using same report.
Now go to Parameters folder, right click and Add Parameter..
After that, a Report Parameters Properties window opens.
Here provides a Parameter Name, prompt, and Data type of parameter value as shown below.
Next click on OK button.
After that, right click on Group1 then select Group Properties..
After that, a Group Properties dialog box opens.
Click on expression (fx).
After that, you will see a Group1 expression in expression builder.
Here in place of 3 we will provide a number of rows per page value through parameter that we created recently.
In category section, select Parameters then you will see all the available parameters in Value section. Select ParamRowLimit parameter.
Just replace value hard code value 3 with parameter ParamRowLimit, just select 3 and double click on ParamRowLimit.
Lets finish the express, that will take value dynamically through parameter.
=Ceiling(Rownumber(Nothing)/Parameters!ParamRowLimit.Value)
After that, click on OK button.
Once you click on Ok button, you get back to Group Properties window. Just click on OK button.
Once you done with this, just go to preview page and see the final output.
You can see, it ask to provide the value for number of records per page that we want to see.
Just provide the value and click on View Report button.
Lets see 4 rows per page, just enter the value 4 and hit view report.
You can see it displays 4 rows per page.
Lets go to next page, and you can see on page 2 it displays next 4 rows.
Also Read..
Multi – value Parameters in SSRS
Add Row Number for Grouped Data in SSRS
Display checkbox for active and inactive values in SSRS report
Show message when no data found in SSRS
Display Running Total/Cumulative Total in SSRS
Display Running Total/Cumulative Total within groupings in SSRS
6,570 total views, 2 views today