How to display fixed number of rows in a table using SRS Report

There are times when you would like to restrict the number of rows that are being displayed on one page in a table. By default if you add a table to a report and set the datasource to a dataset, it will list out all the rows of the dataset in there and if the table data exceeds the report page then it moves to the next page.

Suppose now you want to make sure that there should be note more than 3 rows on each page in the table, you can achieve this by making the following changes to your report.
Say the default query being used to retrieve data is the following

SELECT productdescription, quantity, priceperunit, extendedamount FROM FilteredQuoteDetail

And it will display the following result set.

To fix the rows of the page you need to change the Query to create a custom column in the query that you can use for grouping the rows on a page. Lets call that the page number for each of the record.
We have used the Row_Number() function to group the rows so that there are only 3 rows on each page.

SELECT productdescription, quantity, priceperunit, extendedamount, page = (CASE WHEN (Row_Number()
OVER (ORDER BY productdescription) % 3 = 0) THEN (Row_Number() OVER (ORDER BY productdescription) / 3) - 1 ELSE (Row_Number() OVER (ORDER BY productdescription)
/ 3) END)
FROM FilteredQuoteDetail

It displays the following result set.

Now you need to add the list the on the report page and Group this list by the page column as shown in the below screenshot.


Now add the table under this list. As shown in the below screenshot.


Now you run this report then data display in 2 pages.

There you are!!!