MDX Sub Select ?

All,

I'm using SSAS 2005 and trying to achive the foll

For a given day, find all trades that has the status as Open, Cancelled and get the correspoding TradeGroup associated to it.

Now, list all trades that falls into the above TradeGroup list irrespective of their status.

I have the foll dimensions : TradeStatus, Trade, TradeGroup , Date

I'm trying to acheive the above in a single MDX to be used in SSRS. Any idea?

Code Snippet

SELECT NON EMPTY {
[Measures].[Value]
} ON 0,
NON EMPTY
(

[TradeStatus].[TradeStatus].[Name],
[TradeGroup].[TradeGroup].[Name],

[Trade].[Trade Code].[Trade Code]
[Calendar].[Date].[Date].&[2007-08-02]
) ON 1
FROM MyCube

Any help is much appreciated.

Thanks in advance,

Cheers,

Arun

Code Snippet

SELECT NON EMPTY {
[Measures].[Value]
} ON 0,
NonEmpty

([Trade].[Trade Code].[Trade Code],

NonEmpty([TradeGroup].[TradeGroup].[Name],

{[TradeStatus].[TradeStatus].[Open],

[TradeStatus].[TradeStatus].[Cancelled]})

) ON 1
FROM MyCube

where [Calendar].[Date].[Date].&[2007-08-02]

|||

Hi Deepak,

Sorry, if I wasnt clear.

I need to display all trades from TradeGroup that has got Open and Cancelled trades. i.e. similar to the below Pseudo Code

Code Snippet

Select Trade, TradeStatus, TradeGroup

from MyCube

Where TradeGroup in (Select TradeGroup from MyCube Where Exists TradeStatus in ('Open', 'Cancelled') )

Thanks,

Arun

|||

Hi Arun,

What did the query I provided return - versus what you wanted? Your pesudo-code seems to be SQL-based; so if you could explain the fact table/measure group mapping in the cube, and the desired layout of the query results, that would help.

|||It works fine.. Thanks...