COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: cognos05 on 17 Sep 2014 11:17:23 AM

Title: Rank in dimensional reporting
Post by: cognos05 on 17 Sep 2014 11:17:23 AM
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
Title: Re: Rank in dimensional reporting
Post by: MFGF on 30 Sep 2014 05:52:33 AM
Quote from: nithya1224 on 17 Sep 2014 11:17:23 AM
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.
Title: Re: Rank in dimensional reporting
Post by: cognos05 on 01 Oct 2014 03:40:43 PM
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
Title: Re: Rank in dimensional reporting
Post by: katdbc on 01 Oct 2014 07:09:27 PM
Hi Nithya,

Did you try  rank([Revenue] DESC within set[Reps])
Title: Re: Rank in dimensional reporting
Post by: cognos05 on 02 Oct 2014 07:37:19 AM
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