Recently we had a requirement to reduce the height of an SSRS report which was being rendered in SharePoint’s Report Viewer. When the interactive size property did not have any effect at all, which is still a mystery to me, we decided on a different approach. The solution we opted to use was to limit the report records displayed to 50 rows per page, which involves using the Ceiling function in SQL Server Reporting Services.
The following discussion presumes that the report has been developed & all that's left is handling the pagination.
First, add a Row Group which is a Parent of the existing top level group. In the Group By expression, enter =CEILING(RowNumber(Nothing)/50) where 50 is the number of records to be displayed per page. Be sure to leave the group header & footer boxes unchecked. Since this group’s only purpose will be to force page breaks, we won’t be taking up any real estate on the report.
Ceiling function: Returns the first whole number equal to or higher than its argument.
RowNumber function: Performs a running count of rows within a specified scope. The keyword controls the scope of where the counting begins. By using Nothing, it will utilize the outermost data region (in this example, I only have one tablix data region). You may also replace the Nothing keyword with the name of a specific data region or group (within quotes). A discussion of RowNumber can be found here.
Next, right-click the group just created and go to its Group Properties. Give the group a useful name (because Group1 is certainly not a descriptive name in my humble opinion). Use whatever makes sense to you or, perhaps more importantly, what would make sense to the next person who is trying to understand the report definition. I like to use naming conventions to be self-documenting as possible.
On the Page Breaks section of Group Properties, check “Between each instance of a group.” Don’t choose to break at the start or end of a group or you could end up with an extra blank page.
Next, go to the Sorting section of Group Properties. You’ll most likely just want to remove the sorting because it's handled elsewhere. When you created the group, SSRS automatically created sorting to be the same as your Group By expression. While this is usually a nice convenience, in this case it’ll cause an error the first time you run the report if you don't remove it.
The Row Groups pane will now look similar to the following:
Lastly, delete the first column in the tablix that SSRS created for us automatically when the group was created. In order to enforce page breaks using this group, we don’t need to actually display anything on the report. Choose the 2nd radio button to “Delete columns only” which preserves the group itself which contains the Ceiling function.
That’s it for handling pagination based on number of records per page. All that should be left is fine-tuning, such as repeating column headings on each page as discussed in this blog entry.