Rank on a crosstab

[reginoilano]

Hi,

I created a data item to get the rank of certain stores per MTD of measure with the expression like rank(measure), but as I generate the report it doesn't show the sequential number of rank. there are rankings that is missing. I need to get the top 50 of stores in right order.

example:

Rank  |  Store
1             A
3             B
6             C
7             D
9             E
12            F

[jahnavi]

Did u Hide any Data items in the Report...

[reginoilano]

I didn't hide data items for this report, please help :(

[blom0344]

In your example the rank is established before the aggregate is performed, hence to discontinous series of ranks. You need the agregated set first (yielding one row for each store ) and then apply the ranking.

An alternative is to sort the data descending according to the measure and add a running-count. Use a detail filter  running-count(1) < 51 with 'After auto aggregation' set on the filter.

[reginoilano]

Hi blom0344,

does this mean that instead of rank() function I will use the running-count() with filter of < 51. I will try this step and update you with the results.

Thanks!

[reginoilano]

I tried the running-count() in a crosstab but the result shows that the count overwrite the measure in the table

the expression is running-count(measure)
filter after aggregation: [Rcount] < 51

CrossTab Result:
                           FY
Rcount | Store | Measure
               A           1
               B           2
               C           3

suggestions are all welcome..

[blom0344]

I do not understand the crosstab use. You want top 50 stores based on a certain measure. That is a flat file, which translates to a list in Cognos. you are making this harder than it should be create a list and you'll see that it works.

[reginoilano]

Thanks blom0344,

I guess I really need to create this report in List Table, I tried it in CrossTab since splitting of rows is faster based on the requirements but it just give head ache in the creating the Rank.