MDX TopCount problem...

[miskomaric]

Hi all,
I need to calculate TopCount within TopCount based on the different levels from the same dimension. Here is an example from the FoodMart db:

WITH
SET [TOP 1] AS 'TopCount ([Customers].[City].members, 3,[Measures].[Unit Sales] )'
SET [TOP 2] AS 'Generate({[TOP 1]},TopCount (Descendants([TOP 1].currentmember, 1), 2, [Measures].[Unit Sales]))'
SELECT
{[Measures].[Unit Sales]} on columns,
{[TOP 1], [TOP 2]} on rows
from sales

As you can see, year and quarter are stored in one column - I need two columns, one for year and one for qtr. So the result should be like:

City Name UnitSales
Olympia Name1
Olympia Name2
Olympia Name3


I tried to use CrossJoin but couldn't make it work.
Thanks

 

[justins57]

Misko,

Try this query.

WITH
SET [TOP 1] AS 'TopCount ([Customers].[City].members, 3,[Measures].[Unit Sales] )'
SET [TOP 2] AS 'Generate({[TOP 1]},TopCount (Descendants([TOP 1].currentmember, 1), 2, [Measures].[Unit Sales]))'
SET ColSet As 'Crossjoin({[Unit Sales]},Ascendants([Time].[1997].[Q4]))'
SELECT
ColSet on columns,
{[TOP 1], [TOP 2]} on rows
from sales

hope this helps,

Justin