Add Row Numbers to the SSRS Group Report

This SSRS article explains how to add row numbers to a group table report with an example. Our previous article described how to add the row numbers to the complete report. However, for the grouped reports, the process might be different. We must change the Expression to restrict the row numbers for a particular group and restart for a new group.

How to add Row Numbers to the SSRS Group Report?

Right-Click on the Datasets folder to create a new DataSet. The screenshot below shows the data set we will use for this example.


The Sql query that we used above SSRS example is:

SELECT Cat.[EnglishProductCategoryName] AS Category, 
       SubCat.[EnglishProductSubcategoryName] AS SubCategory, 
       Prod.EnglishProductName AS ProductName, 
       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.

Under Row Groups, click the down arrow beside the Details, choose Add Group, then the Parent Group option.

Table Report

Let me choose the category as the Group by option and click OK. It means the whole table will Group by category.

Choose the Group By Column

Click the Preview Tab to see the table report. Here our job is to add row numbers for each Group, such as accessories, bikes, and clothing. Remember, the row number has to restart for each category.

Repot Preview

Let me insert a New column to the left side of the Product Name. To do so, right-click on the Product Name, choose Insert Column, and then the Left option.

Insert a New Column inside a Table

Rename the Column header as the S.No. Next, right-click on the empty cell and choose Expression.

Edit the Column Expression

Write the following Column Expression to add Row Numbers to the SSRS Group Table Report.

Expression to add Row Numbers to the SSRS Group Table Report

The below report preview shows that the Row Numbers are displaying values irrespective of the Groups. 

add Row Numbers to the SSRS Group Table Report Preview

It is because we used the DataSet name as the argument value of the RowNumber() function. However, to add row numbers to grouped report, replace the DataSet name with the Group name(Category).

New Expression to add Row Numbers to the SSRS Group Table Report

Within the SSRS table report preview, you can see that the row numbers restart at each group member (for instance, bikes).

add Row Numbers to the SSRS Group Table Report Preview