Ranked Report?

[Arsenal]

Hi All,

I have a sort of complex requirement. Have done my best to recreate the scenario below although it's hard to do so in a forum post! Have also put down my thoughts at the end on what I've come up with so far. Basically, it is a sort of grouped report with probably column A grouped but the report only needs the top 10 performing cities in Column A and then out of the Column B groupings it needs to have the top 4 peforming branches and all others rolled in "Others." The percentages next to each is that row's amount/ sub group's total times 100. While amount's on the database, the percentage isn't
The percentage next to the sub group's total is the sub group's total/ the report total  times 100. The last column is the prior month's column. Date is in a varchar form like YYYYMMDD on the database so not a true date field


City-----------Branch--------------July 2010--------June 2010
Atlanta           Top Branch 1           30%  10,000       xx   xxxx
                     Top Branch 2           20%  9,000         yy  yyyy
                    Top Branch 3            10%  8,000         zz   zzzz
                    Top Branch 4             5%   7,000         aa  aaaa
                    Others                      35%  12,000      bb   bbbb

Atlanta Total                                  CC CC%           DD DD%

New York




New York Total
.
.
.

.
.

Grand Total                                     1,00,0000        2,00,0000



My thoughts:
1) I think that the month chosen and prior month will probably need a join. So, second query will probably have something like _add_months(cast(dataprompt,date),-1) and I can join that to the query containing the month of the date chosen
2) I'm thinking that also need to create a data item say called Rank1 and say rank ([amount] for city) and then use a filter in the query that will say Rank1 <1=10 and set the filtyerto after auto aggregation so it brings back the top 10 cities


correct so far..is the join idea a good one? Not expected to have too many roaws because the model has a data filter in place already to restrict the regions

how the hell do I get the percentages next to the amounts for the branches? I know I need to divide each branch's amount by the total for that city but not sure how to translate that into an expression. The group's sub total and grand total will have the same data item name..can I use that?

[MFGF]

how the hell do I get the percentages next to the amounts for the branches? I know I need to divide each branch's amount by the total for that city but not sure how to translate that into an expression. The group's sub total and grand total will have the same data item name..can I use that?

Hi Arsenal,

I'd add a query calculation with syntax like total([Your Measure] for [Branch]) / total([Your Measure] for [City])

Regards,

MF.

[Arsenal]

ok, I worked on it today. MFGF, thanks as always for your suggestion  ;D

I have the percentages working ..for the sub total one's, I used a (total(measure for city)/total(measure for report)*100) and it worked corrrectly.

Will appreciate someone, anyone's help for the following  :):

Can't get the report to put in the "other" in the branches column. I can get the report, without the branches, to filter to the top 10 but when I try to put in logic from the branches column, all I get is one branch per city and the "other" never shows up even though multiple branches do show up for some cities if I use just the description field instead of the calculation to show 'others'

also somehow I can't get the join to work..keeps whining about circular logic so I am doing something wrong obviously...what i am doing is in query (call it current) i have the data items filtered by data picked in prompt, then creating another query (prior), copying the dataitems from mainquery1 and pasting it there and then adding the measure again (comes in as measure1). Added in a filter using _add_months to get prior month. Then I created a join in the query explorer, dropped in current and prior and named the joined query as current&prior and created the join. Then dropped in data items from current and prior into current&prior, changed the list to point to current&prior query, added in measure1, added in percentage column and then did the grouping and subtotalling

obviously, something's wrong...guess I'll work on it some more tomorrow
if anyone has any ideas, I would love to hear it

Thanks

[Arsenal]

Hi All,

Have been working on this report on and off. The report is near completion but the funny thing is that though there is a join within the report, the native SQL shows 2 select statements with no join clause. It's almost as if Cognos is considering this as a stitched query wherein it fires off 2 select statements and is then doing the join locally and attempts to bring back the result set...except the result set never comes back and I've been told once or twice that the BiBus is consuming 100% CPU.

This happens when I put in the nested Rank (wherein, I am saying that if the rank of branch for city>4 then Others else branch)..that is, once this is in, the result set never returns. With just a filter set as rank of city <=15 the result set does return although there is still no join in the native sql.

Is the join not comign in because Rank is a local function? Also, how can I make Cognos come back with a resultset when I apply the nested rank? ???