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.
Right-click on the Datasets folder to create a new DataSet. Writing the below SSMS code shows the data set we will use for this 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
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. Let me choose the category as the Group by option and click OK. It means the whole table will be grouped by category.
How to add Row Numbers to the SSRS Group Report?
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.
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. Rename the Column header as the S.No. Next, right-click on the empty cell and choose Expression.
Write the following Column Expression to add Row Numbers to the SSRS Group Table Report.
=RowNumber(“CatDSet”)
The below report preview shows that the Row Numbers are displaying values irrespective of the Groups.
It is because we used the DataSet name as the argument value of the RowNumber() function. However, to add row numbers to the grouped report, replace the DataSet name with the Group name(Category). First, edit the expression and write the below code to the text box under the S.No header.
=RowNumber(“Category”)
Within the SSRS table report preview, you can see that the row numbers restart at each group member (for instance, bikes).
