Using Sets in MDX Queries - Page 3

August 18, 2003

Working with Sets in Our Queries

We can generate queries that are far more sophisticated by adding sets of members to one or both of the axes. Let's explore this, with the following steps:

10. Select Query -> New from the top menu to set up a new Query pane.

11. Type in the following query once again, for starters:


FROM Warehouse

NOTE: I often intentionally add generous space between rows, and between components of the coding, in my lessons. This is to make the coding easier to follow.

12. Placing the cursor behind word SELECT, type in one space.

13. Expand (by clicking on the "+" sign to its left) the Set folder in the Syntax Examples list on the right side of the Metadata pane.

14. Select the Members-Level function by double-clicking.

The function appears, amid our initial query, in the Query pane, as shown in Illustration 3.

Illustration 3: Building the Query: Inserting a Set Function

15. Click the <<Level>> token once to highlight it.

16. Expand the Store dimension in the Metadata tree to expose its member levels.

17. Double-click the Store State level, within the expanded Stores dimension.

18. Type another space just behind the .Members function of the top row.

19. Type the words ON ROWS.

Now we will arrange the query a bit, as we build it further.

20. Click to the left of [Store], just after SELECT.

21. Press the ENTER key.

22. Place the cursor behind the SELECT statement.

23. Press the ENTER key again, to create a blank row under SELECT.

24. Expand the Set folder in the Syntax Examples list, once again, if necessary.

25. Double-click the Children function.

The function populates the newly created row.

At this stage, the statement should duplicate the following:



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

FROM Warehouse

26. Select the <<Member>> token with a single-click.

27. Double-click the Store Type dimension in the Metadata tree to select it into the <<Member>> token.

Our statement should now appear as below:


[Store Type].Children

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

FROM Warehouse

28. Type in a space, then the words ON COLUMNS after Children in the above statement.

29. Add a comma (",") immediately after the word COLUMNS.

Our statement now appears as follows:


[Store Type].Children ON COLUMNS,

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

FROM Warehouse

The ON COLUMNS addition specifies that the second set be added to the column axis. Anytime that we create multiple axes, as we are doing within this step of our example, we are required to designate the respective axis with which each such string is associated. Order in the query is not as important as the fact that the designation itself is made. (A single axis need not be defined, as it defaults to Column axis, similarly to the case we observed in the first query in this lesson).

30. Select Query --> Run from the main menu.

We obtain the results shown in Illustration 4 below.

Illustration 4: The Results of the Two-Axis Query

31. Save the query as MXAS06-2.MDX.

In addition to using other members' functions (explore these in the Set folder of the Syntax Examples list as you find time), we can make a simple alteration to our query statement to cause it to return the entire membership of the Store dimension.

32. Remove "[Store State]." from the query (including the "." delimiter that follows the right bracket) from the query we have designed.

Our modified statement appears in the Query pane as shown in Illustration 5. (I have created a composite picture, removing many of the empty rows, to conserve space.)

Illustration 5: The Modified Query

33. Click the Run Query button to execute the query.

We obtain the results partially shown in Illustration 6 (scrolling to the appropriate rows):

Illustration 6: The Store Dimension's Entire Population is Retrieved (Composite View)

34. Save the query as MXAS06-3.MDX.