October 27, 2011 at 9:58 pm
Hi All, I have a dataset by market, by clients followed by some numbers. I have a list grouped by market, then a table in the list grouped by clients. This way it'll generate one table for each market, and each table is grouped by clients.
What i want to do is have a row number on each table starting from 1. If i use rownumber(nothing) or rownumber(dataset), the row number corresponds with the row number of dataset and not the table e.g.
AUSTRALIA
1 xxx xxx
2 xxx xxx
3 xxx xxx
4 xxx xxx
CHINA
5 xxx xxx
6 xxx xxx
7 xxx xxx
8 xxx xxx
while what i want is
AUSTRALIA
1 xxx xxx
2 xxx xxx
3 xxx xxx
4 xxx xxx
CHINA
1 xxx xxx
2 xxx xxx
3 xxx xxx
4 xxx xxx
Can this be done without me having to create one dataset for each market?
October 28, 2011 at 12:19 am
Will this work?
ROW_NUMBER() OVER(PARTITION BY <Market> ORDER BY <sorting column>) AS 'Row Number'
October 28, 2011 at 12:22 am
Hi Lian,
Forgot to mention that my database is sybase and as far as i know i can't do row number within the query using partition. I meant putting the Rownumber() function provided by SSRS into the tablecell
October 28, 2011 at 12:35 am
Sybase also has window functions. See below:
Or are you trying to add the row numbers in SSRS?
October 28, 2011 at 12:39 am
indeed Sybase has window functions however i believe my version is older and still does not support window functions (i get Incorrect syntax near keyword 'OVER')
select ROW_NUMBER() OVER (PARTITION BY Market ORDER BY ClientName) from xxx
Yes i'm trying to add Row Number in SSRS
October 28, 2011 at 12:43 am
Sorry, the only other thing I can think of is using RANK() instead of ROW_NUMBER().
October 30, 2011 at 7:44 pm
thanks. Is there no way to do this in SSRS? Seems like an obvious issue if you can't use this with a list
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply