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