8.4 - Top 20 & Other - Cube - Crosstab - Hierarchy

[kado]

Cognoise:

Within Cognos 8.4, Report Studio, Sourcing a Transformer cube ...

I have a Crosstab with drillable Rows for 6 levels within a hierarchy:

Level 1 (5 rows)
Level 2 (15 rows)
Level 3 (160 rows)
Level 4 (2,000 rows)
Level 5 (6,000 rows)
Level 6 (100,000 rows)

The columns are for 3 different measures (sales, returns, counts) for the most recent 5 days of transactions.

The requirement is to show the Top 20 rows (descending by total sales) and then put every other row in an 'Other' row. This doesn't matter for the first 2 Levels (since there are only 5 and 15 rows) and the 'Other' row should not be seen. Once you hit the 3rd level and beyond only the Top 20 should be shown with the rest aggregated in an 'Other' row.

I tried building a Union using the rank function along the various levels of the hierarchy [1 query for everything < 21 and then everything > 20 (for the other bucket)] but could not effectively get the report to drill. I then went to Analysis Studio built the Crosstab and then opened it in Report Studio. The solution works but seems to be over-engineered. Analysis Studio built 15 new data items for my hierarchy (and a bunch of other data items) ...

I am inclined to think that there is a simpler solution and am curious if anyone has any recommendations or has done something similar. The Analysis Studio solution works but maintaining and updating it could be a bear.

THANKS,
MC

[rockytopmark]

Unions and Joins will eliminate the OLAP effect of Cubes/DMR modeled data.
Rank is a relational function... since you are authoring Dimensional reports, use Dimensional functions.

TopCount() will give you the Top 20...

TopCount([Item/level to limit to 20 rows],20,[Item to use for basing the top 20, usually a measure])

You should then be able to get your other data grouped into 1 "member" with aggregate() and except() functions

Aggregate( [measure item] within set Except([level, same as above],[Top Count Item]))

[kado]

Thanks Rocky ... good point about eliminating OLAP effectiveness with a Union (i didn't really think about that)