- About the Series
- Introduction
- Specifying Cells in MDX
- Returning Values Based on Specified Dimensions
- Specification Made Simple: Single Dimensions
- Specification Made Simple: Multiple Dimensions
- Specification Made Simple: An Integrated Example
- Next in This Series
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Specification Made Simple: Multiple Dimensions
Next, let's look at an instance where we specify more than one dimension to retrieve a value, picking up where we left off with the last example.
Drag the Store dimension to the row axis, replacing the Measures dimension (see Figure 10).
Figure 10 The Store dimension replaces the Measures dimension (contracted
view).Highlight MyCalcMem2 in the Calculated Members folder of the Budget cube.
Select the Value property MyCalcMem.
Type the tuple shown below:
([Net Income], [Mexico])
Press Enter. Figure 11 shows the data viewing pane.
Figure 11 The tuple at work in the calculated member.The total for the Mexican locations' Net Income (or, as explained earlier, the Total Expenses, as no Revenues amounts are stored in the Budget cube) appears in every cell of the MyCalcMem2 row. In addition to the two dimensions that we specified (Net Income and Mexico), our tuple implicitly used the current member to satisfy its requirements for the identification of the other dimensions' members (the coordinates for the cell/value that the tuple defines).
Select the Parent dimension of calculated member MyCalcMem2.
Select the Account dimension from the drop-down list.
Press Enter.
Drag the Measures dimension to the row axis, replacing the Store dimension. See Figure 12 for the result.
Figure 12 The tuple value doesn't change (contracted view).The MyCalcMem2 column shows the Net Income figure for the Mexican stores, even though the Mexico dimension has been removed from the grid.
Select Canada in the filter field for the Store dimension at the top of the data viewing pane (see Figure 13).
Figure 13 The tuple value remains, even with the Canada filter applied
(contracted view).MyCalcMem2 retains the tuple value of ([Net Income], [Mexico]) even when the Canada filter is in place for the result set. This makes it obvious that the tuple with the member [Mexico] overrides the current member for the Store dimension.
Change the Value property for the MyCalcMem2 calculated member to the following:
([Net Income], [Mexico])- ([Net Income], [Canada])
Compare the result set to Figure 14.
Figure 14 The expression renders the difference as expected (contracted
view).
)
Figure 10)
Figure 11)
Figure 12)
Figure 13)
Figure 14Suppose we want to quantify the difference in Net Income between Mexico and Canada. The above expression calculates the difference and returns the amount in MyCalcMem2 above. This is a simple illustration of the way that we can specify more than one tuple in a given expression. We can check the answer simply by looking up the totals for Mexico and Canada, and performing the math independently. When we determine the difference in this way, the figure delivered by MDX (-761,869.84) agrees with the independent calculation (the difference between -29,052.00 and -790,921.84). We obtain the difference regardless of the presence of conflicting filters in the top of the data viewing pane, because explicitly specified dimensions override.
As stated earlier, we'll commonly use MDX expressions that are made up of multiple independent values (in the form of tuples) combined by an operator. The opportunities are great within the realm of multidimensional analysis, and MDX stands ready as the tool of analytical choice!