MDX Select - To use in Client App

We have a client app that used MDX to display data in the fromtend in grid. And we have requirement to filter by a dimension value (multiselect) + also display the same in the grid. But I'm aware that this is not possible using basic MDX.

I'm trying to acheive something similar to below query i.e. A dimension in slicer Axis and also in rows. Is it possible?

SELECT { [Measures].[LTD Value]} ONCOLUMNS,

NONEMPTY

(

[Currency].[Currency].[Currency].Members ,

[Trade].[Source Trade Code].[Source Trade Code].Members

) ONROWS

FROM [MyCube]

WHERE (

[Calendar].[Calendar].[Date].&[2007-01-04T00:00:00]

,{[Currency].[Currency].[Currency].[EUR], [Currency].[Currency].[Currency].[GBP] }

);

thanks,

Arun

You can define an explicit set on an axis and do a cross product of that set against another:

Code Snippet

select

[Measures].[Reseller Sales Amount] onColumns,

{[Date].[Calendar].[Month].[May 2004],

[Date].[Calendar].[Month].[June 2004]} * [Product].[Category].[Category].MembersonRows

from [Adventure Works]

If you need to make the set more dynamic, look at the STRTOMEMBER() and STRTOSET() functions.

Thanks,

Bryan

|||

Hi Arun,

Have you tried using a sub-select MDX query, like:

select

{[Measures].[Average Rate]} on 0,

[Destination Currency].[Destination Currency Code].[Destination Currency Code] on 1

from (select

{[Destination Currency].[Destination Currency Code].[EUR],

[Destination Currency].[Destination Currency Code].[GBP]} on 0

from [Adventure Works])

where [Date].[Calendar].[Date].[January 4, 2004]

--

Average Rate
EUR .92
GBP 1.46

|||Thanks guys. Both the above solution works.