Add Row Numbers to SSRS Report

When you are developing a Table or a Matrix report, there will be a requirement to display the Serial numbers or row numbers before the SSRS table report. In this article, we will show you, How to add Row Numbers to the 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 Report DataSet to explain the adding SSRS row numbers. Please refer to the Shared Data Source and Dataset articles to understand the steps involved in creating the Shared Data Source and Dataset that we used for this rep

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 the Add Headers and Footer article for adding Page Headers and Footers to check the Report Preview.

Please refer to Add Row Numbers to the Group Report article for grouping examples.

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, extracting the continuous column from the Database is impossible. So, let me show you the ideal approach to adding continuous columns or add row numbers to the 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 Expression, a new window called Expression will open. Use this expression window to write the custom expression. In the reporting service, we had one function called RowNumber, and in this example, we will use this SSRS row number.

Once you are done assigning the SSRS row number, Click OK to close the window. As you can see our report preview displays the continuous number (Row Numbers) inside the table report.

Let me show you the last page to see the SSRS row number.