Rank in dimensional reporting

[cognos05]

Hi All,
I have a crosstab with Salesreps level in row and revenue as measure and product set in columns,
I wanted to rank reps based on total revenue made by reps by product set.

Expression used in rank data item is :
rank (aggregate([Revenue] within set([Product])) for [shiptorepLevel])
It gives a result of rank1 for all the reps.

Thanks,
Nithya

[MFGF]

Hi All,
I have a crosstab with Salesreps level in row and revenue as measure and product set in columns,
I wanted to rank reps based on total revenue made by reps by product set.

Expression used in rank data item is :
rank (aggregate([Revenue] within set([Product])) for [shiptorepLevel])
It gives a result of rank1 for all the reps.

Thanks,
Nithya

Hi,

1. Make sure your rank calculation is a column not a row
2. Use the expression rank([Revenue] within set [shiptorepLevel])

Cheers!

MF.

[cognos05]

Hi MFGF,

Thanks for the reply .

Now the rank is working (rank([Revenue] within set[Reps])) but I dont no how  it is calculating  based on the product set,because product set is not in part of rank expression .

I am using a cross tab  with measure value as Revenue. Reps in rows and Product Set in column1 and Rank in column 2 .

Expression for Reps is Descendants(set(East,West,North),Rep Level)
Expression for Product Set in column is set(A,b,c,d,e,f)
Expression for Rank column is  rank([Revenue] within set[Reps])

Now my cross tab result is showing reps with their ranks, but I  have to sort the reps based on their ranks in ascending order.

Clicked on rank column and sort in layout ascending . Did not work.

Used order( Descendants(region,replevel),Rank,Asc)   throwing an error There is a circular reference in 'Query1' involving the following items: '[Query1].[Rank]->[Query1].[shiptorep]->[Query1].[Rank]' .

How to sort the reps dataitem in ascending .

Thanks,
Nithya

[katdbc]

Hi Nithya,

Did you try  rank([Revenue] DESC within set[Reps])

[cognos05]

Yes , I tried even that.

It seems like it is sorting but the issue is.

my reps expression is Descendants( set( East,West),Repslevel)

so this will give me all the reps in east and west region only,,

so my hierarchy looks like    Region -> Distributors-> Reps

so sorting happens by reps belonging to distributors in east and then reps belonging to distributors in west.

say if there are 5 distributors in east and 30 reps in them first these reps are listed and then 4 distributors in west and 40 reps within them are listed.

Is there a way to get them sorted say from 1 to 100 irrespective of their grouping.

Thanks,
Nithya