Home > Articles > Data > SQL Server

  • Print
  • + Share This
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.

  1. Modify the query to look like this:

    SELECT {[Store Cost], [Store Sales], [Unit Sales]} ON COLUMNS,
    [Store].[Store State].Members ON ROWS FROM Sales

    We'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.

  2. Click the Run Query button. Figure 32 shows the results.

    Figure 32Figure 32 The result set, specifying both axes.

  3. 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 Sales
  4. Click the Run Query button to view the effect of the changes (scroll to the USA Store State rows). Figure 33 shows the results.

    Figure 33Figure 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.

  5. 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 Sales
  6. Click the Run Query button. Figure 34 shows the results.

    Figure 34Figure 34 The result set, sans empty cells.

  7. Add comments, if desired, and save the query as MDX04-4.MDX.

We 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).

  • + Share This
  • 🔖 Save To Your Account