When you are developing a Table or a Matrix report, there will be a requirement to display the Serial numbers or row number before the SSRS table report. In this article, we will show you, How to add Row Numbers to SSRS Report OR How to use the RowNumber function. It is one of the standard requirements for every report developer.
We are going to use the below-shown DataSet to explain the SSRS row numbers. Please refer to Shared Data Source and Dataset articles to understand the steps involved in creating Shared Data Source and Dataset that we used for this rep
and the Custom SQL query that we used in the above Dataset is:
-- SSRS RowNumber Function USE [AdventureWorksDW2014] GO SELECT Terr.[SalesTerritoryRegion] ,Terr.[SalesTerritoryGroup] ,GEO.[EnglishCountryRegionName] ,GEO.[StateProvinceName] ,GEO.[City] ,GEO.[PostalCode] ,SUM(FACT.[OrderQuantity]) AS OrderQty ,SUM(FACT.[TotalProductCost]) AS ProductCost ,SUM(FACT.[SalesAmount]) AS Sales ,SUM(FACT.[TaxAmt]) AS TaxAmt FROM [DimSalesTerritory] AS Terr INNER JOIN [DimGeography] AS GEO ON Terr.SalesTerritoryKey = GEO.SalesTerritoryKey INNER JOIN [FactInternetSales] AS FACT ON Terr.SalesTerritoryKey = Fact.SalesTerritoryKey GROUP BY Terr.[SalesTerritoryRegion] ,Terr.[SalesTerritoryGroup] ,GEO.[EnglishCountryRegionName] ,GEO.[StateProvinceName] ,GEO.[City] ,GEO.[PostalCode]
Add Row Numbers to SSRS Report
For this SSRS row numbers demonstration, We are going to use the previously designed report. Please refer to Table Report, Format Table Report article, to understand the steps involved in creating Table Report & format. Next, visit Add headers and Footer article for adding Page headers and footers.
Let me show you the Report Preview.
As you can see from the above screenshot, it is challenging to point out a particular row from the above report. And it will become worse if we navigate to some 10 or 20th page.
If your Dataset holds the serial number (continuous number), then you can use that column. In real-time, it is not possible to extract the continuous column from the Database. So, let me show you the ideal approach to add the continuous columns or add row numbers to SSRS report.
First, add an empty column, or insert a column inside the table report. For this right-click on the Sales territory column, and select Insert Column, and then Left from the context menu.
That will add an empty column at the starting position.
Now right-click on the newly added column will open the context menu. Please select the Expression option from the context menu.
Once you click on the Expression, a new window called the Expression will open. Use this expression window to write the custom expression. In reporting service, we had one function called RowNumber, and in this example, we are going to use this SSRS row number.
Once you are done assigning SSRS row number, Click OK to close the window
As you can see that our report preview is displaying the continuous number (Row Numbers) inside the table report.
Let me show you the last page to see the ssrs row number.