Where clause in MDX

Lets take a look on how the where clause in MDX works. IN MDX we can filter on dimension members like in the sample below.

SELECT [Measures].[Internet Total Sales] ON 0,
NON EMPTY [Geography].[City].[City] ON 1
FROM [Internet Sales]  
WHERE [Date].[Calendar Year].&[2007]

This will give sales per city for year 2007.

We can also use a custom time interval.

SELECT [Measures].[Internet Total Sales] ON 0,
NON EMPTY [Geography].[City].[City] ON 1
FROM [Internet Sales]  
WHERE ([Date].[Date].&[2005-01-05T00:00:00] : [Date].[Date].&[2006-03-08T00:00:00])

If you need to have more statements this can be done like this

SELECT [Measures].[Internet Total Sales] ON 0,
NON EMPTY [Geography].[City].[City] ON 1
FROM [Internet Sales]  
WHERE 
	(
	   {[Date].[Calendar Year].&[2006], [Date].[Calendar Year].&[2007]}
	 , {[Date].[Month Name].&[April], [Date].[Month Name].&[June]}
	 , [Product Category].[Product Category Name].&[Bikes]
)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s