How to show top ten rows and aggregate remaining as others - Report Studio

[leoraj]

Hi,
Can any one help me with this?
In a crosstab report (Report Studio-Cognos8.3) I want to show top 10 rows based on measure and aggregate the remaining rows into other and show on the report. Also show the 'top ten' and 'others' together on a pie chart.

Any ideas?

[rockytopmark]

Query 1:
Take your existing Query and add a Data Item, which applies the Rank() function.  Use the context help for Rank() to implement it properly for your situation.  Name it "Ranker" for this example.

Query 2:
This is a reference query that uses Query 1 as its source.  Bring all necessary columns over from Query 1, except the new Ranker data item, and the data item that has the names for your graph/list, where you want to see 'Other' if Ranker > 10. 
Add a new Data Item to Query 2, and use an If, Then, Else in the expression, that looks at the value of the [Query1].[Ranker] data item.  If [Query1].[Ranker] <= 10 then [Query1].[name data item], else 'Other'

Base your graph/list off of Query 2, while using the [name data item] for your columns/series in your object.

[leoraj]

Thanks for the Solution.
This query converts the content in the data item to "Other" when the rank is greaterthan 10, but when I group the data item it shows Other =1 (measure).
In my case the measure is a count(abc_key). is there a way taht it adds up all "Other" together when I group.

Thanks.

[leoraj]

Does any one know the solution??