Hi All,
I have a crosstab query, and I need to show an individual product line, with the top #, and everything else in an Others group.
I have this so far in a data item:
CASE WHEN ([Product].[Line] In('Apples','Bananas','Oranges'))
THEN ([Product].[LineCode])
ELSE ('Others')
END
This works great when the top # of lines is static.
But this report is broken down by another measure, and I need the 'Apples' plus the top # in that measure.
I have a new query that uses the Rank function to rank the product lines 1-#. But I am having trouble figuring out how to combine the 2...
Something like this:
CASE WHEN ([Product].[Line] In('Apples') Or Product].[Line] In('List from the ranked query'))
THEN ([Product].[LineCode])
ELSE ('Others')
END
I know Ive asked similar questions before on top 5 in a crosstab, but please look at this.
Thanks again,
Livens
Use the Swiss pocket knife of BI reporting: the union ;)
Use 2 identical queries, each with their own filter expression, union the set and use the result as input for the crosstab.
No need to reference query B within query A (subselects as in SQL :( )