Row number within an SSRS report

Question

I have a group in my report where I have to use row numbers within that group, I was wondering if there is a way to display row numbers using expressions, or if I have to modify my Store procedure and include row numbers for that group.

Answer 1

Yes, row number is the answer. use it like this

SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, ... ORDER BY Col1) AS recID

Here is how to do Paging in SQL

DECLARE @startRow INT
SET @startrow = 50
;WITH cols
AS
(
    SELECT table_name, column_name,
        ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq,
        ROW_NUMBER() OVER(ORDER BY table_name DESC, column_name desc) AS totrows
    FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name, totrows + seq -1 as TotRows
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 49
ORDERBY seq

Answer 2

You can use the RowNumber function in Reporting Services.

Since SQL Server 2005 this can be done with ROW_NUMBER() in the SQL code too.

Neither way is better for simple groups, but if you have dynamic groupings that you're better with the SSRS RowNumber which will calculate over the group/scope