Limit Rows on each Page of the SSRS Grouped Table Report

This SSRS article shows how to limit the total number of rows displayed on each Page of a grouped table report 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 for this Limit Rows on each Page of the SSRS Grouped Table 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

Limit Rows on each Page of the SSRS Grouped Table Report

We have designed a simple table report of product sub-category sales and formatted the font and colors.

Under the Row group section, click the down arrow beside the details and select add group and parent group.

Choose the Category column as the group by option and click the OK button.

Next, we added a new column in the middle of the Category and Sub-Category named S.No. To do this, right-click the Sub Category column, choose the insert column, and then the Left option. Next, right-click on the empty cell and select the Expression option.

Within the Expression window, we used the RowNumber() function with the DataSet name as the argument value to display the row numbers. It is optional.

RowNumber("CatDSet")

Let me show you the report preview. It has four pages, products are grouped by category, and S.No column returns the serial or row numbers.

Next, under the Row group section, click the down arrow beside the Category and select add group and Child group.

Click the ƒx button to write the grouping expression. In this example, we want to limit 10 rows on each Page, so we divide the RowNumber by 10. Here, the Ceiling function is to get the smallest integer value greater than or equal to the result. Remember, don’t forget to place the Group name, i.e., Category, as the RowNumber argument.

The expression to Limit Rows on each Page of the SSRS Grouped Table Report

=Ceiling(RowNumber("Category")/10)

By default, the grouping will apply to sort the entire table. Here, we are not grouping by any column; instead, it is an expression that will throw an error. So, right-click Group1 and choose Group Properties. Next, 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.

Page Breaks to Limit Rows on each Page of the SSRS Grouped Table Report

Right-click on the extra group column and select the Delete Columns option to remove it from the final report. If you want, leave it.

Choose the Delete Columns only option from the pop-up window. Please don’t choose the first option; it will delete the earlier created group.

The final report is.

Final Design to Limit Rows on each Page of the SSRS Grouped Table Report

Now, if you look at the Limit Rows on each Page of the SSRS Grouped Table Report preview, it shows 10 rows from the group.

Let me check another page.