Return cross product of two or more sets in MDX using “CrossJoin”

One of the functions I use almost all the time when I am writing MDX is the crossjoin function. This function returns the cross product of two or more sets in MDX.

Lets look at an example

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Date].[Calendar].[Calendar Year] ON 1
FROM [Adventure Works]

This code will give us sales per year. But what if we want sales per year per city? Lets introduce the crossjoin function!

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY CrossJoin([Date].[Calendar].[Calendar Year], [Customer].[City].[City])  ON 1
FROM [Adventure Works]¨

And that would gives ut the result we want

How about sales per contry per city per year? Then we will have to nest 2 crossjoins like this.

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY CrossJoin([Customer].[Country].[Country], CrossJoin([Customer].[City].[City], [Date].[Calendar].[Calendar Year]))  ON 1
FROM [Adventure Works]

Instead of crossjoin you can also use a ‘*’. That is the way I ususally write my code

Here is an example on that

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Customer].[Country].[Country] * [Customer].[City].[City] * [Date].[Calendar].[Calendar Year]  ON 1
FROM [Adventure Works]
Advertisements