Add sequence number in Reporting

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Add sequence number in Reporting

Author

Topic

danielmeresa
Starting Member

10 Posts

Posted - 2007-09-12 : 11:12:18

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-12 : 11:54:14

Are you on SQL Server 2000 or 2005?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

danielmeresa
Starting Member

10 Posts

Posted - 2007-09-12 : 12:01:41

I'm using SQL Server 2000

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-12 : 12:22:16

Are those the only 2 columns in your dataset or do you have more? do you have any unique column ?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

danielmeresa
Starting Member

10 Posts

Posted - 2007-09-12 : 13:24:32

Those are the only two column that I have in my dataset, and I need to add one column that's SEQ_nbr after I group by Cust_Name.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-12 : 13:50:17

Get the data into a table variable with identity column..


Declare @T Table (rowid int identity,Cust_Name varchar(10), Month2 int)
Insert into @T 
Select 'Gilbert' , 5 union all
Select 'Gilbert' , 4 union all
Select 'Gilbert' , 7 union all
Select 'Martin' , 3 union all
Select 'Martin' , 9

Select (Select count(*) From @T T2 Where T2.Cust_Name = T.Cust_Name And T2.rowid <= T.rowid ) As RowNum, *
from @T T

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jhermiz

3564 Posts

Posted - 2007-09-12 : 22:10:44

quote:
Originally posted by danielmeresa

I want to add sequence number to a table below:

Cust_Name | Month
=======================
Gilbert | 5
Gilbert | 4
Gilbert | 7
Martin | 3
Martin | 9

and the result i need is:

SEQ_nbr | Cust_Name | Month
================================
1 | Gilbert | 4
2 | Gilbert | 5
3 | Gilbert | 7
1 | Martin | 3
2 | Martin | 9

How can I do it in reporting service to show like the above?

Thanks,
Daniel

Or you could use the RowNumber() function that is included with RS.
No need to do this in the backend, this is a display issue, displaying should be done in your front end / report.

Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

danielmeresa
Starting Member

10 Posts

Posted - 2007-09-13 : 08:37:56

Thank You very much, you help me a lot.

danielmeresa
Starting Member

10 Posts

Posted - 2007-09-13 : 08:39:21

http://download.microsoft.com/documents/uk/technet/learning/downloads/it_forum/2005-01-13/IT_Forum_Highlights_Peter_Blackburn_SQL_Server_Reporting_Services.ppt#318,20,Page Break After N Rows

Nice link how to use a RowNumber () function.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources