- About the Series
- Introduction
- Introducing MDX Queries
- Key Concepts and Terminology
- Introducing Sets
- Understanding Metadata
- Working with MDX Queries
- Working with Member Sets
- Specifying Members in a Set
- Using the CrossJoin Function to Combine Multidimensional Sets
- Looking Ahead to Calculated Members
- Next in This Series
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Using the CrossJoin Function to Combine Multidimensional Sets
Now let's bring multiple dimensions into the row axis, to replicate additional parts of the PivotTable list's functionality. We do this with yet another function (CrossJoin) that combines two sets into one set, with multiple members in each tuple that it references.
Modify the query to look like this:
SELECT {[Store Cost], [Store Sales], [Unit Sales]} ON COLUMNS,
[Store].[Store State].Members ON ROWS FROM SalesWe're simply combining the last statement—where we specify the multiple member set for the column axis—with a statement using a set function (hence the absence of braces) to specify the row axis.
Click the Run Query button. Figure 32 shows the results.
Figure 32 The result set, specifying both axes.Now we need to combine two sets into the row axis set. Modify the query to add the CrossJoin function just before the ON ROWS instruction, as shown below (the CrossJoin function appears in bold):
SELECT {[Store Cost], [Store Sales], [Unit Sales]}ON COLUMNS, CrossJoin([Store].[Store State].Members,
[Product].[Product Family].Members) ON ROWS FROM SalesClick the Run Query button to view the effect of the changes (scroll to the USA Store State rows). Figure 33 shows the results.
Figure 33 The result set, with a little help from the CrossJoin
function.
The CrossJoin function has enabled the combination of two sets, from two dimensions, into one set on the row axis. This nests the Product Family level members into the Store State levels.
Let's go one step further for the sake of finesse, and remove the many empty spaces in the new report. This is a simple matter of replacing the CrossJoin function with a derivative function, the NonEmptyCrossJoin function, as shown below (the change is shown in bold):
SELECT {[Store Cost], [Store Sales], [Unit Sales]}ON COLUMNS, NonEmptyCrossJoin([Store].[Store State].Members,
[Product].[Product Family].Members) ON ROWS FROM SalesClick the Run Query button. Figure 34 shows the results.
Figure 34 The result set, sans empty cells.Add comments, if desired, and save the query as MDX04-4.MDX.
)
Figure 32)
Figure 33)
Figure 34We now have a report that resembles (where relevant) the model we sought to replicate. The CrossJoin function creates a report that allows comparison of multiple dimensions, placing two dimensions on one axis (in this case, the row axis).