CG displaying Top N/Other for multiple time periods

[JRO061601]

This might be a little convoluted. I need a report that shows top 5 states and other states by a simple metric by months. In the below case date is synonymous with month. I'm able to do this using two reports with a metric qualification filter on a metric built using the MSTR 'Rank' function. One report shows top 5, the other shows all excluding top 5, and when I remove Property State from the grid but not the report, I just get the totals. I know these two reports can be 'combined' through web in an html document using stylesheets.

What I'd like to do is be able to create a custom group along the row header that accomplishes this in one report object. I have not been able to find a way to do so. Since the metric with the rank function needs to be set to 'break by <date>', the custom group displays repeating dates for some reason if a filter using this metric is used as a CG element.

Any ideas if this is doable?

Below is an example of the top 5 report. Notice that the top 5 states changes month to month, and in months where it is not part of the top 5, a null value displays. This is OK.

Report Description:

Report Filter (Local Filter):
(Rank of {Rank UPB} Top 5)

Report Limits:
Filter is empty.

Date 1/31/2004 2/29/2004
Metrics Current UPB Current UPB
Property State
RI $10,000,000
NJ $5,477,328 $5,477,328
NY $5,460,636 $5,460,636
CA $4,711,487 $4,711,487
TX $1,516,506 $1,516,506
CT $1,411,277

Now to just be able to add an 'Other' line and display the totals for other states...

Thanks!

 

[entaroadun]

For stuff like this, I would populate a table in the pre-SQL and model attributes against it to get exactly what you want. For example, I would create a stored procedure to calculate the rank of every state per month and use that value as an attribute, with a custom group on Rank <= 5 and Rank > 5.

There are techniques that you can use to capture prompt answers for the report if necessary.

There are also techniques for mapping attributes to local temp tables so you don't have to worry about two users running the report at the same time stepping on each other.

Let me know if you need guidance with either.

 

[JRO061601]

So I guess this isn't possible within the typical tool functionality then, we'd have to go a little above and beyond to get this accomplished. Thanks for the tips.

 

[nlim]

JRO061601, actually I tried something similar in the tutorial project. I was able to make it work. I think the trick was the "not top 5 filter", and the "outline" mode. See if it's what you are looking for

1) simple metric M1
2) simple filter F1. Rank of M1 top 5, output at the state level, break by month.
3) empty filter F2
4) simple filter. F2 "AND NOT" F1
5) custom group CG. 2 elements filtered by F1 and F3. Do not display individual items.
6) on your report, place the CG and state on the template in addition to metrics and month. Turn on subtotals to be on top of level, and also in outline mode.

The report looks like this


Date 1/31/2004 2/29/2004
Metrics Current UPB Current UPB
Top 5 States CG element
Property State
RI $10,000,000
NJ $5,477,328 $5,477,328
NY $5,460,636 $5,460,636
CA $4,711,487 $4,711,487
TX $1,516,506 $1,516,506
CT $1,411,277
Other states
RI $900,000
CT $1,411,277
other states....

 

[JRO061601]

nlim,

so close. it works, but what i really need is to display the top five states individually and display one total line for all the other states.

sorta on topic, but can you explain why the engine displays the break by attribute (in this case date) instead of the output level attribute (state), when you choose to display the individual items in the custom group elements? is that a bug or is it working as designed?

thanks, jro

 

[nlim]

the way to get the totals for the "rest of" states is to collapse the outline for the "rest of" states CG element.

then you get

top 5
total 1645454546
a 4654656455
x 4654646
f 456456
g 45454
non top 5 121213123

 

[JRO061601]

but i can only do that in web, correct? in desktop i can only expand or collapse both elements together.

 

[JRO061601]

sorry, i answered my own question, you can double-click on the CG element to collapse or expand only one.

however, i can't save the report such that only one is expanded and the other is collapsed the next time it is run.

 

[nlim]

yeah, I think you can't save the 2 different collapse expand settings...sorry.

 

[JRO061601]

thanks, i'll log an enhancement request with microstrategy then.