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 in SSRS. 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

Add Row Numbers to SSRS Report 1

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 SSRS Table ReportFormat Table Report article, to understand the steps involved in creating Table Report & format. Next, visit Add headers and Footer to SSRS Report article for adding Page headers and footers.

Add Row Numbers to SSRS Report 2

Let me show you the Report Preview.

Add Row Numbers to SSRS Report 3

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.

Add Row Numbers to SSRS Report 4

That will add an empty column at the starting position.

Add Row Numbers to SSRS Report 5

Now right-click on the newly added column will open the context menu. Please select the Expression option from the context menu.

Add Row Numbers to SSRS Report 6

Once you click on the Expression, a new window called the Expression will open. Use this expression window to write the custom expression. In SSRS, we had one function called RowNumber, and in this example, we are going to use this SSRS row number.

Add Row Numbers to SSRS Report 7

Once you are done assigning SSRS row number, Click OK to close the window

Add Row Numbers to SSRS Report 8

As you can see that our report preview is displaying the continuous number (Row Numbers) inside the table report.

Add Row Numbers to SSRS Report 9

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

Add Row Numbers to SSRS Report 10

Comments are closed.