Using Sets in MDX Queries - Page 3August 18, 2003 Working with Sets in Our QueriesWe 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: SELECT 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.
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: SELECT <<Member>>.Children [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: SELECT [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: SELECT [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.
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.)
33. Click the Run Query button to execute the query. We obtain the results partially shown in Illustration 6 (scrolling to the appropriate rows):
34. Save the query as MXAS06-3.MDX. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
