[SOLVED] Row number within an SSRS report

Row number within an SSRS report

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.

Solution

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