How to Add Auto Number Records, Rows or Lines In SQL Server Reporting Services by Using RowNumber Function

MS SQL Server Reporting Services is a great reporting tool with its capabilities. You can even further extend these capabilities and functionalities by using custom functions. Therefore the use of Reporting Services for enterprises as a reporting solution is growing.

As a developer while you are preparing a code for a report, adding auto numbers to your reports records or rows, lines etc. is a common process. There is mainly two approaches for maintaining your reports row numbers. One method for supplying a row number to a record is adding row numbers during the selection of these records by adding them within the sql statements. The second method which I will try to explain here in this article is customizing the report tool, in our case MS SQL Server 2000 Reporting Services, in order to add a column which is automatically containing row numbers for each record.

In Reporting Services during coding expressions for textboxes which contain information, you can use aggregate functions such as RowNumber, RunningValue, Avg, Sum, etc.

For more information about the aggregate functions which can be used within Reporting Services, please look for "Aggregate Functions" topic at Reporting Services Books Online which is installed with the Reporting Services.

For auto numbering rows we will use RowNumber function. RowNumber function returns a running count of all the rows within the specified scope. This means you may want to number all the rows in the overall report as well as add a second row number for them in the scopes they have been grouped by.

The syntax for RowNumber aggregate function is as: RowNumber(Scope)

The RowNumber functions returns an integer which represents the row number.

The Scope parameter of the RowNumber function is a string which defines the name of the dataset or grouping name, or the region where the auto numbering will be reset. That means if you give the name of a dataset as a scope parameter to your RowNumber function in your report, the number field will begin from 1 and go on for all records through the dataset. If you have grouping in your report on a dataset, and if you want the RowNumber function to begin from 1 on every group, then you can pass the name of the grouping as the scope parameter in your reports.

As an example for the use of RowNumber function in your expressions in textboxes that are placed in your Reporting Services reports, I created a report by using the Northwind sample database. So I also created a Shared Data Source to connect to Northwind database and named it as Northwind.

For more information and a guide for creating shared data sources for Reporting Services (.rdl) reports you can reference to Shared Data Source

The report has a dataset named "Employees" as the datasource of the report. And the select query for this dataset is "SELECT * FROM Employees"

After preparing the datasource of the report, I switched to Layout tab. And I dragged and dropped a table report item on the layout of the RowNumber report.

For the purpose of showing the usage of the scope parameter, I also inserted a group on the table. I gave the name EmployeesByCity to the grouping I have just created. And I grouped the records on the City column value. So I selected "=Fields!City.Value" from the dropdown list in the "Group on:" region.

By this grouping we will be able to report the employees grouped on their cities.

Second thing we will do on the layout tab will be adding the row number fields. So just insert two new columns. One will have the expression =RowNumber("Employees") and the other will have the expression =RowNumber("EmployeesByCity")

The last design in the layout tab of the report is as follows for our sample RowNumber.rdl report.

After all the steps done, by running our sample Reporting Services report which we have prepared for RowNumber functionality in the .rdl files, you will see the below report output.

You will realize that the numbering is always reset in the "No within City" column with the change in city that is the base column which the grouping is done. And the "No" column is increasing within the dataset without a reset.

In short, RowNumber aggregate function can be used in MS Reporting Services Reports in order to show the row, line or record number. This method is easier because you do not bother with preparing a sql statement which returns row numbers for each record. And the presentation layer can be easily altered, because it is not dependent on the data layer.

You can also use RunningValue function in your reports for your needs to auto number rows or having a running sum on certain columns. You can refer to RunningValue & SQL Server Reporting Services for the sample use of RunningValue aggregate function in Reporting Services.