RowNumber issue - SQL Server Forums

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

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

RowNumber issue

Author

Topic

preethe
Starting Member

3 Posts

Posted - 2007-11-29 : 07:56:47

Hi All

We have a requirement of creating a report which has one data set
And the same data set is being used accross different tables in the report. The tables differ by the type of grouping applied on them.

My situation is
1. The table has two groups applied
a) table level group is on DIVISION
b) detail grouping is done based on two columns:
SYSTEM and NAME
2. When i use the expression rownumber("DIVISION") or rownumber("SYSTEM_NAME"), it does not return a proper sequence

I need the row numbers to be generated based on the grouping on (SYSTEM and NAME) for each DIVISON

Can someone please explain how this can be done?

tm
Posting Yak Master

160 Posts

Posted - 2007-11-29 : 10:43:04

Are you looking for something like this?

But with duplicates grouped as 1

For instance you may have data
1 A 1
1 A 1
1 A 2
1 B 3
1 B 3 .. ETC

DIV SYSTEM NAME ROWNUMBER

1 A 1 1
1 A 2 2
1 B 3 3
2 C 1 1
2 C 3 2
2 C 4 3

preethe
Starting Member

3 Posts

Posted - 2007-11-29 : 23:16:58

Hi ..
Thanks for the reply!

The output should be like ..
My Report output should be like

DISVION : 1
SYSTEM NAME ROWNUMNER
A TEST 1
A TEST-A 2
B TEST 3
C TEST-C 4

DIVISION: 2
SYSTEM NAME ROWNUMNER
D TEST-D 1
E TEST-E 2
E TEST-EE 3

How do i set the rownumber for this situation?

tm
Posting Yak Master

160 Posts

Posted - 2007-11-30 : 10:38:28

Hi Preethe,

It depends on your data.
I used the data you showed and it seems to work for me.

1. Use Table and create table level group called "Division" as you mentioned on your original post without detail grouping and it works for me.
I used expression =rownumber("Division") to get the row number in the detail section.

BUT .. if you have duplicate data such as

A TEST 1
A TEST 1
A TEST-A 2
A TEST-A 2

Then I could not get the rownumber correctly without creating a dataset that uses DISTINCT to get unique records so it rownumbers correctly. It seems that rownumber function goes through numbering all records therefore you need to group the records in the dataset and not the table grouping.

If you need additional help you need to post some sample data and how your dataset is constructed and if there are any additional columns that is also in the dataset.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources