This SSRS article shows how to limit the total number of rows displayed per Page in a table report by dividing the row number by 10 with an example. To do this, right-click on the Datasets folder to create a new DataSet.
The below screenshot shows the data set that we use to this limit the number of rows per page in the SSRS report example.
The Sql query that we used above SSRS example is:
SELECT Cat.[EnglishProductCategoryName] AS Category,
SubCat.[EnglishProductSubcategoryName] AS SubCategory,
Prod.EnglishProductName AS ProductName,
Prod.Color,
SUM(Fact.OrderQuantity) AS Orders,
SUM(Fact.TotalProductCost) AS ProductCost,
SUM(Fact.SalesAmount) AS Sales,
SUM(Fact.TaxAmt) AS Tax
FROM FactInternetSales AS Fact
INNER JOIN DimProduct AS Prod ON Fact.ProductKey = Prod.ProductKey
INNER JOIN DimProductSubcategory AS SubCat ON Prod.ProductSubcategoryKey = SubCat.ProductSubcategoryKey
INNER JOIN DimProductCategory AS Cat ON SubCat.ProductCategoryKey = Cat.ProductCategoryKey
GROUP BY Cat.[EnglishProductCategoryName], SubCat.[EnglishProductSubcategoryName],
Prod.EnglishProductName, Prod.Color
We have designed a simple table report of product sales and formatted the font and colors.
How to Limit the Number of Rows Per Page in the SSRS Report?
Next, we added a new column to the left side of the Product Name and named as S.No. To do this, right-click the Product Name column, choose the insert column, and then the Left option to create a new column on the left side. Next, right-click on the empty cell and select the Expression option.
Within the Expression window, we used the RowNumber() function to display the row numbers.
RowNumber(Nothing)
The table report preview with row numbers.
Next, under the Row group section, click the down arrow beside the details and select add group and parent group.
Let me click the ƒx button to write an expression for grouping.
In the expression window, we divide the RowNumber by ten because we want to display or limit ten rows per Page. Here, we also used Ceiling to get the smallest integer value greater than or equal to the result.
The expression to limit the number of rows per page in the SSRS report is:
Ceiling(RowNumber(Nothing)/10)
The grouping will default sort the table records based on the given column. Here, it is an expression, and it will throw an error. So, right-click Group1 and choose the Group Properties window.
Go to the Sorting tab, and click the Delete button to delete the Sort by.
Select the Page breaks option between each instance of a group option under the Page Breaks tab.
I don’t want this extra group column to display in the final report. So, let me right-click on it and select the Delete Columns option. Then, if you wish, leave it.
It will open a pop-up window; choose Delete Columns only option. If you choose the first option, it will also delete the group we created earlier.
Now, if you look at the limit of the number of rows per page in the SSRS report preview, it shows 10 rows per Page.
Let me navigate to another page.
