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
Did u Hide any Data items in the Report...
I didn't hide data items for this report, please help :(
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.
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!
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..
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.
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.