Using Sets in MDX Queries - Page 5

August 18, 2003

Using the CrossJoin Function to Nest Multidimensional Sets

Now, let's nest multiple dimensions into the row axis, to demonstrate a useful approach to meeting business needs in this way, if required. We can do this with the CrossJoin() function.

39. Modify the query to match the following:


{[Measures].[Warehouse Cost] , [Measures].[Warehouse Sales], 
[Measures].[Units Shipped]} ON COLUMNS,

[Store].[Store State].Members ON ROWS

FROM Warehouse

Here we are simply juxtaposing our last statement, where we are specifying the multiple member set for the column axis, with a statement using a set function (.Members) to specify the row axis. Because we are using a set function, note that we can leave off the braces.

40. Click the Run Query button.

The results appear as shown in Illustration 9.

Illustration 9: The Result Set, Specifying Both Axes

Now, let's say we need to nest two sets in the row axis set. We will do this with the CrossJoin() function we discussed above.

41. Modify the query to add the CrossJoin() function, just before the ON ROWS instruction, as shown below (the CrossJoin() function appears in blue):


{[Measures].[Warehouse Cost] , [Measures].[Warehouse Sales], 
[Measures].[Units Shipped]} ON COLUMNS,

CrossJoin([Store].[Store State].Members, [Product].[Product Family].Members) ON ROWS

FROM Warehouse

42. Click the Run Query button to view the effect of the changes (scroll to the USA Store State rows).

The results appear as partially depicted in Illustration 10.

Illustration 10: The Result Set, With a Little Help from the CrossJoin Function

The CrossJoin() function has enabled us to juxtapose two sets, from two dimensions, as if it were one combined set, on the row axis. This produces the nesting of the Product Family level members into the Store State levels that we see above, and gives us a practical preview of the power of the CrossJoin() function.

Now, let's go one step further for the sake of finesse, and remove the many empty spaces in our new report. This is a simple matter of replacing the CrossJoin function with a derivative function, the NonEmptyCrossJoin() function, as shown below (I have highlighted the only change over the last query in blue):


{[Measures].[Warehouse Cost] , [Measures].[Warehouse Sales], 
[Measures].[Units Shipped]} ON COLUMNS,

NonEmptyCrossJoin([Store].[Store State].Members, [Product].[Product Family].Members) ON ROWS

FROM Warehouse

43. Modify the query to reflect the change in the function name noted above.

44. Click the Run Query button.

The results appear as depicted in Illustration 11.

45. Save the query as MXAS06-4.MDX, and exit the Sample Application as desired.

We now have a report, albeit simple in structure that hints at the potential available to us within MDX for multidimensional analysis. We have used the CrossJoin() function to create a report that allows us to simultaneously analyze multiple dimensions, as well as to nest two of these dimensions on one axis (in our case the Row axis) as part of the objective.

Next in Our Series ...

In this lesson, we began an exploration of MDX queries. We discussed the differences between MDX expressions and MDX query statements, and as part of this overview, discussed key MDX concepts and terminology. We followed our overview with an exploration of MDX query building from the ground up, using the MDX Sample Application as a vehicle for crafting our statements and practicing their use.

We delved into set functions, and the creation and use of sets, discussing their importance in our MDX queries. We progressively built the specification of members, and the combination of multiple dimensions, into our row and column axes, to add truly multidimensional capabilities to the reports we produce for ourselves and for the information consumers we support.

Our next topic will span a three-article set. In these three lessons, we will focus on building and using calculated members within our queries to meet various business needs. We will begin by previewing the creation of dynamic calculated members to set the stage for the functionality and processes we will explore together.

Throughout the Building Calculated Members articles, we will explore and practice the creation of increasingly sophisticated calculated members, discussing various types and their uses. We will further transit into the realm of using functions within our calculated members, practicing different uses for the components we have explored. Finally, we will preview member functions, in preparation for lessons to follow Building Calculated Members.

» See All Articles by Columnist William E. Pearson, III

The Network for Technology Professionals


About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers