- 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
Specifying Members in a Set
We can assign sets to axes with relatively little programming effort, particularly when relying on many of the default member settings to supply info. To combine multiple dimensions on a single axis, however, we must specify individual members:
Click the New Query button to start a new query.
If necessary, click the Pivot Results button to place the axes in the original position.
Type the following query in the Query pane:
SELECT {[Store Cost]} ON COLUMNS FROM SalesThe set we want to retrieve is enclosed in braces ({}). This is required to create a set without the benefit of a set function (which we used in the previous exercises). The braces tell the application that the enclosed members represent a set.
The result of this query should be the single grand total for Store Cost shown at the bottom of the final PivotTable list in the previous exercise (see Figure 30).
Figure 30 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. Enclosing the measure in braces defines its status as a set, which is a requirement for anything that's placed on an axis. But we also want to display two other positions in the PivotTable list, one each for Store Sales and Unit Sales.
Add a comment (if desired) and the two additional measures, separated by commas and spaces, as follows:
SELECT {[Store Cost], [Store Sales], [Unit Sales]} ON COLUMNS FROM SalesClick the Run Query button. Figure 31 shows the results.
Figure 31 Three tuples yield three measure columns.
)
Figure 30)
Figure 31Our multiple-member set consists of three tuples, with each tuple bearing a single measure as a member.