When you are developing a Table, or a Matrix report there will be a requirement to display the Serial numbers before the table. In this article we will show you, How to add Row Numbers to SSRS Report OR How to use RowNumber function in SSRS. This is one of the common requirement for every report developer.
We are going to use below shown Data Set to explain the same. Please refer 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 Data set 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 demonstration, We are going to use the previously designed report as we shown below. Please SSRS Table Report, Format Table Report in SSRS article to understand the steps involved in creating Table Report & format, and Add headers and Footer to SSRS Report article for adding Page headers, and footers.
Let me show you the Report Preview.
As you can see from the above screenshot, it is very difficult to point out particular row from the above report. And it will become worst 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.
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 be opened. 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 the same.
Once you are done, 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.
Thank You for Visiting Our Blog