Limits the Number of Records Per Page in SSRS 2012

While working on the requirement to fix the number of records per page in the report, I found that there is no such out of the box (OOB) property in SSRS 2012 to do so, However this can be achieved by some workarounds :). Below are the steps.

  • Navigate to the Rows Groups section and then Details. Select Add Group -> Parent Group -> Choose Expression in Group By (Leave Add group header/Add group footer as unchecked)
  • Paste the following code into the expression box.

=int((RowNumber(nothing)-1)/25)

  • In the group property navigate to Page Breaks option and select "Between each instance of a group" and leave the other options below unchecked.
  • If you run your report, you could get error saying "A sort expression for tablix ‘Tablix...’ uses the RowNumber function. RowNumber cannot be used in sort expressions." To fix the error remove the default sorting expression created in the group properties.
  • By default the added SSRS group also gets added in Tablix column, if you wish not to keep this, you can remove this (Be careful to delete columns only and not the columns and associated groups.

Since it's a workaround, it has some constraint too :) which is when you export the report to excel, it exports different pages into different sheets which may not acceptable in all the cases but if export to excel is not a requirement, you may live with it.