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?
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.
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.
Does any one know the solution??