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

13.  Select the Store dimension within the Parent Dimension property, as shown below:

We see from the dynamic caption at the bottom of the Properties pane that we are selecting "the dimension that contains the calculated member."

14.  Press Enter.

15.  Select the Value property on the Basic tab, once again.

16.  Click the ellipsis button ("..") to raise the Calculated Member Builder dialog.

17.  Type the following into the Value Expression box.

[Store].[Store Country].[USA]/12

18.  Click OK, and compare the result set to that pictured in Illustration 7.

19.  Select 1998, in the filter field for the Year dimension atop the Data Viewing pane. The Data Viewing pane presents the results set shown below.

The amounts are now negative, as the sample Budget cube only reflects actual expense items in its measures for 1998 (the "current year" of the cube). We will see this more clearly as we work with lower levels of the Account dimension hierarchy in later lessons. (Our focus here is not the composition of the Net Income number, but the interaction of the various cubes members between themselves.)

20.  Swap (by dragging and dropping, as we did before) the Account dimension from the top pane down to replace the Measures dimension in the columns axis.

The value of MyCalcMem2 is automatically filled with the contents of the corresponding Net Income cell (the Assets and Liabilities amounts are not populated in the Budget cube). The value for the Net Income member of the current account has been retrieved and displayed.

21.  Swap the Store dimension in the rows axis with the Measures dimension.

Now let's specify MyCalcMem2 as the dimension for which we are retrieving values. (This became possible when we made Store the Parent dimension of MyCalcMem2.)

22.  Select MyCalcMem2 within the Store dimension filter box.

The results set appears as below.

We can see, in each cell, the value retrieved by the expression that composes MyCalcMem2. For Net Income, whose value is reflected as ($18,153.64) (again, negative because only expenses are included in the cube), the current member of the Measures dimension ([Amount]) is derived in the context of the row, while the current member of the Account dimension ([Net Income]) is derived in the context of the column. The filter fields in the top pane define the context of the dimensions with which they are associated. MyCalcMem2 represents the current member of the Store dimension, pointing to the [USA] member through the expression ([USA]/12), which defines the calculated member.