Using subqueries in MDX

Did you know that you can write subqueries in MDX? It might be handy if you have some complex dimension filters.

Lets take a basic sample

SELECT [Measures].[Internet Total Sales] ON 0,
[Date].[Calendar Year].[Calendar Year] ON 1
FROM [Adventure Works Internet Sales Model]

And the result will look like this

Subqueries 1

If you only want to get sales for the year of 2007 it can be achieved with a subquery.

SELECT [Measures].[Internet Total Sales] ON 0,
[Date].[Calendar Year].[Calendar Year] ON 1
FROM 
( 
	SELECT [Date].[Calendar Year].&[2007] ON 0
	FROM [Adventure Works Internet Sales Model]
)

Subqueries 2

Yes I know this can be achieved in simpler ways 🙂 But I had some problems figuring out better samples

Advertisements