Share via


Displaying fixed number of rows in Report using expression based page break

Have you ever ran in to a requirement where you want to accommodate only X number of rows in a page? Have you ever wondered how this can be achieved through Reporting services? In the first place, Is it even possible to achieve this?

I have one answer for all the questions and that is “IT IS POSSIBLE and IT IS THAT SIMPLE Smile”.

This is how you can achieve it.

On your report, In the grouping section, add a TOP most group (Parent group) with the following expression:

=Ceiling(RowNumber(Nothing)/X)

where X => Number of rows to be displayer in the page.

image

image

Leave rest of the settings as default.

Right click on the newly added group and In the group properties, under Page Breaks, ensure you select “Between each instance of a group” as shown below:

image

Select the Sorting tab and delete the SORT expression which is based on the newly added grouping expression.

Click on OK.

You’ll notice an additional column being added to the table. Please go-ahead and safely delete the column by selecting the option as shown below:

image

Adjust the width of the report body to ensure it is back to the initial position overcoming the extra space that was added.

Preview / Render the report to see the expected output.

Attached is the sample report that is based on Northwind database. You can use this as a reference.

Reference: Reporting services expression samples

Happy Reporting!

Selva.

[All the posts are AS-IS with no warranty]

SimpleSelect.rdl