MDX - SQL Server Analysis Services 2012 Cube Development - page 205

2. Next, modify the query slightly to include the breakdown of reseller sales by
a calendar year:
SELECT [Measures].[Reseller Sales Amount]
ON 0,
[Date].[Calendar].[Calendar Year].members
ON 1
FROM [Adventure Works]
Notice that I have used the members function to retrieve a set consisting of
every member of the [Calendar Year] hierarchy within the date dimen-
sion. The members function does not include any calculated members; you
will learn more about calculated members later in this chapter. To include
calculated members in the output, use the allmembers function instead of
members .
3. MDX also allows returning sets of multiple hierarchies on each axis using the
CROSSJOIN function. CROSSJOIN accepts two sets as parameters and re-
turns their cross product. For example, the following query will return a cross
product of each promotion category with each calendar year on rows and the
reseller sales amount measure on columns:
SELECT {[Measures].[Reseller Sales
Amount]} ON 0,
CROSSJOIN
({[Promotion].[Promotions].[Category].members},
{[Date].[Calendar].[Calendar
Year].members}) ON 1
FROM [Adventure Works]
You could nest multiple CROSSJOIN functions to create a more complex set
consisting of multiple dimensions. You could also include cross-joined sets
of multiple dimensions on columns. When using a CROSSJOIN in MDX, it is
important to group all hierarchies from the same dimension together instead
of spreading them apart. This will allow the engine to select a more efficient
algorithm for the join and should result in improved performance and reduced
memory consumption.