- About the Series
- Introduction
- Introducing MDX Queries
- Key Concepts and Terminology
- Introducing Sets
- Understanding Metadata
- Working with MDX Queries
- Working with Member Sets
- Specifying Members in a Set
- Using the CrossJoin Function to Combine Multidimensional Sets
- Looking Ahead to Calculated Members
- Next in This Series
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Working with Member Sets
We can generate far more sophisticated queries by adding sets of members to one or both of the axes. Let's place a set on an axis as a means of generating a display of more than one cell in our results:
In the Query pane, highlight the query you just created.
Choose Edit, Copy (or press Ctrl+C) to copy the original query.
Choose Query, New (or click the New Query button) to set up a new Query pane.
Choose Edit, Paste (or press Ctrl+V) to paste the original query into the new Query pane, for use as a "boilerplate" to begin the next query.
Position the cursor immediately after the word SELECT and press the spacebar.
Click the plus (+) sign next to the Set folder in the Syntax Examples list on the right side of the Metadata pane. This action will expand the Set folder.
Double-click the Members-Level function to include the function in the query, following the SELECT statement (see Figure 22).
Figure 22 Building the query: inserting a set function.Click the <<Level>> token to select it.
Expand the Stores dimension in the Metadata tree to expose its member levels.
Double-click the Store State level within the expanded Stores dimension.
Position the cursor immediately after the word Members, press the spacebar, and type ON COLUMNS (see Figure 23).
Figure 23 The complete query.Click the Run Query button. Figure 24 shows the results.
Figure 24 The results of the query.The Store State labels appear as column headings. To more closely replicate a feature of the PivotTable list, move the labels to the row axis by clicking the Pivot Results button (see Figure 25) on the sample application toolbar. Figure 26 shows the new format.
Figure 25 The Pivot Results button from the sample application
toolbar.
Figure 26 Column labels are converted to row labels.
)
Figure 22)
Figure 23)
Figure 24)
Figure 25)
Figure 26You may wonder why the query seems to be ignoring the ON COLUMNS instructions. The explanation (without going into too much detail) is the fact that an MDX query with only one axis requires that the single axis be a column axis.
Now let's add a set to the second axis in our report:
Add a comma and a space after the word COLUMNS in the query.
If necessary, expand the Set folder in the Syntax Examples list.
Double-click the Children function. The statement should now look like this:
SELECT [Store].[Store State].Members ON COLUMNS, «Member».Children FROM Sales
Click the <<Member>> token to select it.
Double-click the Store Type dimension in the Metadata tree to select it into the <<Member>> token. Here's how the statement should look:
SELECT [Store].[Store State].Members ON COLUMNS, [Store Type].Children FROM Sales
Position the cursor after Children in the statement. Press the spacebar and then type the words ON ROWS. The statement should look like this:
SELECT [Store].[Store State].Members ON COLUMNS, [Store Type].Children ON ROWS FROM Sales
The ON ROWS addition specifies that the second set will be added to the row axis. This can be a little confusing. When you create two axes, you must designate one as the column axis and one as the row axis. Order in the query is not as important as the fact that the designation itself must be made. The Pivot Results functionality is still in place, so in effect we're still operating under the "swap" conditions imposed on the earlier, single-set query.
Click the Run Query button to execute the query (see Figure 27).
Figure 27 The results of the two-axis query.Save the query as MDX04-2.MDX.
In addition to using other member functions (you can explore these in the Set folder of the Syntax Examples list), we can make a simple alteration to the query statement to cause it to return the entire membership of the Store dimension.
Remove [Store State]. from the query. Then type the following text, in a line above the query statement:
-- Store Dimension Membership
Two forward slashes (//) work in the same way as two hyphens (--) in this comment-line notation, meaning "ignore the rest of this line for purposes of query execution." To perform the same thing for a comment that extends beyond a single line, or that consists of only a partial line, use /* at the beginning of the comment, and */ at the end. For example, the following comment would easily extend beyond a single line. We would enclose it between the /* and */ characters, as shown here:
/* The purpose of the following query is to generate sales values for each of the stores, then to compare the actual amounts to the budgeted amounts to compute a variance value. */
The keyword or description at the top line of a query appears in the Queries selector box in the toolbar. This is helpful when moving quickly between queries (or attempts at queries during design) for development or other purposes.
TIP
Add comments to any but the simplest queries to make it easier to understand the logic, the objectives in creating the query, and a myriad of other such facts. Such comments are very helpful when returning to the query after time has passed.
Figure 28 shows the modified statement in the Query pane.
Figure 28 The modified query with a new comment line.Click the Run Query button to execute the query. Figure 29 shows partial results.
Figure 29 The store dimension's entire population is
retrieved.Save the query as MDX04-3.MDX.
)
Figure 27)
Figure 28)
Figure 29