SQL Server Script: CROSSJOIN in MDX – Analysis Services

Hi Friends,

CROSSJOIN is a very powerful function in MDX, returning you the cross product of one more sets. Let us see some examples:

Example 1: Returns on the cross join of Year & month on rows axis

select
[Measures].[Internet Sales Amount]
on columns,
crossjoin([Date].[Calendar].[Calendar Year],[Date].[Month of Year].[Month of Year])
on rows
from
[Adventure Works]

Example 2: You can definitely add more measures to your code above:

select
{[Measures].[Internet Sales Amount],[Measures].[Reseller Sales Amount]}
on columns,
crossjoin([Date].[Calendar].[Calendar Year],[Date].[Month of Year].[Month of Year])
on rows
from
[Adventure Works]

Example 3: Things get interesting when you are cross-joining on both the axes:

select
crossjoin([Product].[Product Categories].[Category],{[Measures].[Internet Sales Amount],[Measures].[Reseller Sales Amount]})
on columns,
crossjoin([Date].[Calendar].[Calendar Year],[Date].[Month of Year].[Month of Year])
on rows
from
[Adventure Works]

Example 4: The beast of all – CROSSJOIN within CROSSJOIN

   
select
crossjoin([Sales Territory].[Sales Territory].[Country],crossjoin([Product].[Product Categories].[Category],{[Measures].[Internet Order Count],[Measures].[Reseller Order Count]}))
on columns,
crossjoin([Date].[Calendar].[Calendar Year],[Date].[Month of Year].[Month of Year])
on rows
from
[Adventure Works]

There are alternate syntax also for CROSSJOIN as follows:

Standard syntax
Crossjoin(Set_Expression1 ,Set_Expression2 [,...n] )
 
Alternate syntax
Set_Expression1 * Set_Expression2 [* ...n]

Example 5: Alternate syntax

select
crossjoin([Sales Territory].[Sales Territory].[Country],[Product].[Product Categories].[Category],{[Measures].[Internet Order Count],[Measures].[Reseller Order Count]})
on columns,
[Date].[Calendar].[Calendar Year]*[Date].[Month of Year].[Month of Year]
on rows
from
[Adventure Works]

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

Leave a Reply

Your email address will not be published.