Recently while working on a SSRS project where we had requirement to limit the number of records in report per page. This was something basically needed to improve the report visibility in the SharePoint page. If we fix the number of records per page in the report then we can control the height of the report and can avoid the vertical scroll bar in the report as well.
In this example I am going to show the 25 rows per page in the report…The steps are mentioned below to achieve this functionality.
In this example I am going to show the 25 rows per page in the report…The steps are mentioned below to achieve this functionality.
1. Add a Row Group (Parent)
2. In the group enter the expression, =CEILING(RowNumber(Nothing)/25)
3. In the above expression 25 is the number of records displayed per page.
4. The CEILING function returns the Returns the smallest integer greater than, or equal to, the given numeric expression.
http://msdn.microsoft.com/en-us/library/aa258240(SQL.80).aspx
5. The RowNumber Returns a running count of the number of rows for the specified scope.
http://msdn.microsoft.com/en-us/library/ms159225.aspx
6. Since we are using “Nothing”, the RowNumber will consider the outermost data region, this can be replaced with the name of the specific data region.
7. Once the group is created, go to the Group properties-->Page Breaks and select the check box “Between each instance of a group”
8. Make sure you have removed the sorting from the Group Properties since in this case this will cause an error.
9. Now at last we can delete the first column which was created when the Group was added since we do not need to display anything here. Please make sure to select the “Delete Columns Only” option while deleting the column.
10. Run the report and it should return 25 records per page.
Hope this article helps…please leave the comment if this is not clear or if you have any questions on this.
Thanks,
Gaurav
