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 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 the Add headers and Footer article for adding Page headers and footers.

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

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, 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 reporting service, we had one function called RowNumber, and in this example, we will use this SSRS row number.