Crossjoin (MDX)
Returns the cross product of one or more sets.
Syntax
Standard syntax
Crossjoin(Set_Expression1 ,Set_Expression2 [,...n] )
Alternate syntax
Set_Expression1 * Set_Expression2 [* ...n]
Arguments
Set_Expression1
A valid Multidimensional Expressions (MDX) expression that returns a set.
Set_Expression2
A valid Multidimensional Expressions (MDX) expression that returns a set.
Remarks
The Crossjoin function returns the cross product of two or more specified sets. The order of tuples in the resulting set depends on the order of the sets to be joined and the order of their members. For example, when the first set consists of {x1, x2,...,xn}, and the second set consists of {y1, y2, ..., yn}, the cross product of these sets is:
{(x1, y1), (x1, y2),...,(x1, yn), (x2, y1), (x2, y2),...,
(x2, yn),..., (xn, y1), (xn, y2),..., (xn, yn)}
Important
If the sets in the cross join are composed of tuples from different attribute hierarchies in the same dimension, this function will return only those tuples that actually exist. For more information, see Key Concepts in MDX (Analysis Services).
Examples
The following query shows simple examples of the use of the Crossjoin function on the Columns and Rows axis of a query:
SELECT
[Customer].[Country].Members *
[Customer].[State-Province].Members
ON 0,
Crossjoin(
[Date].[Calendar Year].Members,
[Product].[Category].[Category].Members)
ON 1
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]
The following example shows the automatic filtering that takes place when different hierarchies from the same dimension are crossjoined:
SELECT
Measures.[Internet Sales Amount]
ON 0,
//Only the dates in Calendar Years 2003 and 2004 will be returned here
Crossjoin(
{[Date].[Calendar Year].&[2003], [Date].[Calendar Year].&[2004]},
[Date].[Date].[Date].Members)
ON 1
FROM [Adventure Works]
The following three examples return the same results - the Internet Sales Amount by state for states within the United States. The first two use the two cross join syntaxes and the third demonstrates the use of the WHERE clause to return the same information.
Example 1
SELECT CROSSJOIN
(
{[Customer].[Country].[United States]},
[Customer].[State-Province].Members
) ON 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]
Example 2
SELECT
[Customer].[Country].[United States] *
[Customer].[State-Province].Members
ON 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]
Example 3
SELECT
[Customer].[State-Province].Members
ON 0
FROM [Adventure Works]
WHERE (Measures.[Internet Sales Amount],
[Customer].[Country].[United States])