CrossJoin

Syntax

CrossJoin ( set1, set2 )

Parameters

set1: A set to cross with set2.
set2: A set to cross with set1. Must not include any dimension used in set1.

Notes

This function returns the cross-product of two sets from different dimensions. If the two sets share a common dimension, an error is returned.

If one of the input sets is empty, the output set will be empty as well. For example, the output will be empty if the input set is [Root Beer].children but [Root Beer] has no children.

The order of the sets (and their constituent tuples) provided to the CrossJoin function have an effect on the order of the tuples in the result set. For example,

CrossJoin({a, b}, {c, d})

returns {(a, c), (a, d), (b, c), (b, d)}

CrossJoin({a, b, c}, {d, e, f})

returns {(a, d), (a, e), (a, f), (b, d), (b, e), (b, f), (c, d), (c, e), (c, f)}

Be aware of the order of the output set when using the results of CrossJoin with other order-dependent set functions; for example, Head or Tail.

Example

Example 1

The following expression

CrossJoin({[Qtr1], [Qtr2]}, {[New York], [California]})

returns the set:

{([Qtr1], [New York]), ([Qtr1], [California]), 
 ([Qtr2], [New York]), ([Qtr2], [California])}

Therefore, the following query

SELECT
CrossJoin({[Qtr1], [Qtr2]}, {[New York], [California]})
ON COLUMNS
FROM sample.basic

returns the grid:

Table 4-49 Output Grid from MDX Example

Qtr1	Qtr1	Qtr2	Qtr2
New York	California	New York	California
1656	3129	2363	3288

Example 2

The following expression

CrossJoin({[Qtr1], [Qtr2], [Qtr3]}, {[New York], [California], [Texas]})

returns the set

{([Qtr1], [New York]), ([Qtr1], [California]), ([Qtr1], [Texas]), 
([Qtr2], [New York]), ([Qtr2], [California]), ([Qtr2], [Texas]), 
([Qtr3], [New York]), ([Qtr3], [California]), ([Qtr3], [Texas])}

Therefore, the following query

SELECT
CrossJoin({[Qtr1], [Qtr2], [Qtr3]}, {[New York], [California], [Texas]})
ON AXIS(0) 
FROM Sample.Basic

returns the grid:

Table 4-50 Output Grid from MDX Example

Qtr1	Qtr1	Qtr1	Qtr2	Qtr2	Qtr2	Qtr3	Qtr3	Qtr3
New York	California	Texas	New York	California	Texas	New York	California	Texas
1656	3129	1582	2363	3288	1610	1943	3593	1703

Example 3

The following expression

CrossJoin ([100].children, [Profit].children)

returns the set:

{([100-10], Margin), ([100-10], [Total Expenses]), 
 ([100-20], Margin), ([100-20], [Total Expenses]),
 ([100-30], Margin), ([100-30], [Total Expenses])}

Therefore, the following query

SELECT
 {([Market].levels(1).members)} 
ON COLUMNS, 
 CrossJoin ([100].children, [Profit].children)
ON ROWS
FROM Sample.Basic

returns the grid:

Table 4-51 Output Grid from MDX Example

(axis)	(axis)	East	West	South	Central
100–10	Margin	15762	8803	5937	8124
100–10	Total Expenses	4633	4210	2361	4645
100–20	Margin	1785	3707	2767	7426
100–20	Total Expenses	671	4241	1570	3495
100–30	Margin	871	1629	#Missing	3975
100–30	Total Expenses	458	2139	#Missing	1895