Quick question on pulling in a list of records from a seperate query

[jcrouch]

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:

Code Select Expand

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:

Code Select Expand

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

[blom0344]

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  :(  )