MDX in Analysis Services: Intermediate Concepts - Part 1 - Page 6

Next, working within our current example, let's look further at retrieving values, by taking the following steps.

23.  Swap the Store dimension and the Measures dimension.

The Store dimension appears in the rows axis, presenting the results set shown below.

24.  Highlight MyCalcMem2 in the Calculated Members folder of the Budget cube.

25.  Select the Value property for MyCalcMem2.

26.  Click the ellipsis button ("..") once again, to raise the Calculated Member Builder dialog.

27.  Type the following into the Value Expression box.

([Account].[Net Income],[Store].[Store Country].[Mexico])

28.  Click OK to close the Calculated Member Builder.

The Data Viewing pane appears as shown in Illustration 11 below.

The total for the Mexican locations' Net Income (or, as we explained earlier, the Total Expenses, as no Revenues amounts are stored in the Budget cube) appears in every cell of the MyCalcMem2 row. Besides the two dimensions that we specified (Net Income and Mexico), the current member is implicitly used to satisfy the requirement for the identification of the remaining dimensions' members. In other words, the two dimensions specified in the tuple, together with the current members for any unspecified dimensions, compose the coordinates for the cell, and, contextually, the value of the cell.

29.  Select the Parent Dimension property of Calculated Member MyCalcMem2.

30.  Select the Account dimension via the drop-down arrow.

31.  Click any place on the Data tab, Data Viewing pane, to refresh.

32.  Swap the Measures dimension with the Store dimension, placing the Measures dimension in the rows axis.

The results set appears as below.

As we see in the illustration, the MyCalcMem2 column shows the Net Income figure for the Mexican stores; the presence of the Mexico dimension in MyCalcMem2 specifies the Mexico member of the Store dimension as part of the context within which the value is derived.