Limit Rows Per Page using SSRS Parameter

This SSRS article shows how to limit the total number of rows per page in a table report using a parameter value with an example. To do this, right-click on the Datasets folder to create a new DataSet.

Writing the below code inside the SSMS shows the data set that we use for this limit rows per page using the SSRS report parameter example.

The Sql query that we used in this 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 Per Page using the SSRS Parameter

Here, we have designed a simple table report of product sales and formatted the font and colors. We added a new column by right-clicking the Product Name, choosing the insert column, and then the Left option. Next, right-click on the empty cell to choose the Expression option and use the RowNumber() function to display the row numbers. It is optional and please refer to the Add Row Numbers article to understand the steps.

RowNumber(Nothing)

In this example, we need a Parameter to decide the row limit. So, right-click on the Parameters folder and choose the Add Parameter option to open the following window. Add Rows as the name and Choose the Number Of Rows as prompt text.

Under the Default Values tab, select the Specify Values option and click the Add button. Next, enter the parameter default value to limit the total rows per page. For example, we want to display or limit 10 rows per page, so enter 10.

Add Default Value to Limit Rows Per Page using SSRS Parameter

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 the @Rows parameter. Here, we also used Ceiling to get the smallest integer greater than or equal to the result.

The expression to Limit Rows Per Page using SSRS Parameter is.

=Ceiling(RowNumber(Nothing)/Parameters!Rows.Value)

Click OK to close the tablix group window. Next, right-click Group1 and choose the Group Properties window. By default, the row grouping expression sorts the table, which will throw an error. So, go to the Sorting tab, and click the Delete button to delete the Sort by.

Under the Page Breaks tab, select the Page Breaks option between each instance of a group option. It helps to limit rows per page using the SSRS report parameter.

Add Page Breaks to Limit Rows Per Page using SSRS Parameter

Right-click on the extra group column and select the Delete Columns option to remove it from the final report. Please choose the Delete Columns only option from the pop-up window. The first option will also delete the associated group we created earlier.

Now, if you look at the limit using the SSRS report parameter preview, it shows 10 rows per page.

Limit Rows Per Page using SSRS Report Parameter preview

Let me change the parameter value to 15 and see if it displays 15 rows per page.