Using Sets in MDX Queries - Page 4

August 18, 2003

Specifying Members in a Set

As we see from the fruits of our labor so far, we can assign sets to axes with relatively little programming effort. This is particularly true when we can rely upon many of the default member settings to "fill in the blanks," with regard to the requirement to supply "addresses" for the values we seek to retrieve, in terms of all dimensions in the cube. However, to approach the content of a report that leverages multi-dimensional concepts to produce denser information in a useful form, we must go further than this. We must be able to specify individual members, for purposes of extracting precise information from the wealth of cube data, as well as to be capable of combining multiple dimensions on a single axis. MDX rises to the occasion, and supports our need for precise control of the presentation quite well.

Let's explore meeting these further requirements, with the following steps:

35. Start a new query using the New Query button.

36. Type the following into the Query pane:


{[Measures].[Warehouse Cost]} ON COLUMNS

FROM Warehouse

We enclose the set we want to retrieve in braces ({}). The braces inform the application that the enclosed member(s) represent a set.

The results we obtain should be a single "grand total" for Warehouse Cost, and should duplicate those shown in Illustration 7.

Illustration 7: the Grand Total of the Store Cost Measure for the Cube

The focus here is the selection of an individual measure for placement on the column axis. Our enclosing the measure in braces defines its status as a set, as we have noted, which is a requirement for anything that is placed on an axis within that status.

Now let's say that our business requirement changes: we are asked to display not only the Warehouse Cost value, but two additional values, Warehouse Sales and Units Shipped. We will deliver these tuples with the following enhancement to our query.

37. Add the two additional measures, separated by commas and spaces, to the query as follows:


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

FROM Warehouse

38. Click the Run Query button.

The results appear as shown in Illustration 8.

Illustration 8: Three Tuples Yield Three Measure Columns

Our set now consists of three tuples. We have thus presented a multiple member set, identifying those members specifically in our query.