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]
