MDX in Analysis Services: Intermediate Concepts - Part 2 - Page 3

10.  Click the Check button to ascertain proper MDX syntax.

The Syntax is OK message box appears, indicating that the expression entered at least does not violate any general syntax rules. While the "check" process does not validate the propriety or accuracy of the expression beyond the context of syntax, it still serves as a great way to detect missing or unpaired parentheses, and other typical keystroke errors.

11.  Click OK.

The message box closes, placing us back at the Calculated Member Builder dialog.

12.  Click OK again.

The Calculated Member Builder dialog closes.

13.  In the Cube tree, select Expense % in the Calculated Members folder for the Budget cube, if it is not already selected.

The Basic tab of the Properties pane appears underneath Expense %.

14.  Select the Parent Dimension property of Expense %, on the Basic tab.

15.  Ensure that the Measures dimension appears in the selector box for the Parent Dimension property.

16.  Click the Advanced tab in the Properties pane.

17.  Select the Format String property of Expense %.

18.  Select Percent as the format for the string, by insuring the word appears in the selector box for the Format String property.

19.  Press Enter.

The result set appears as shown in Illustration 5.

20.  Double-click the Mexico member of the Store Country level (Rows axis) to explode to the Mexico Store State view of Store dimension.

We have now exploded to the second level for Mexico in the Store hierarchy, that of the individual Mexican Store State members. The results should appear as shown below.

We note that, while the Expense % for the individual Mexican Store States does, indeed, equal 100% at the All Stores Total rollup (the top dimension level), our calculated member does not extend the same treatment to the hierarchical levels. That is, the Expense % for the Mexican Store States does not indicate the relative contribution of the Store States to their respective immediate rollup levels. (In other words, the Mexican Store States' percentages do not total to 100% at their hierarchical rollup level Mexico Total - they show their respective contributions to the top level, All Stores, only, in that they simply add to a total of 76.21%, the Mexico Total contribution).

We will modify our expression to use an extended version of the Ancestor function to 1) identify the hierarchical Parent for the Store States and 2) calculate context sensitive rollup percentages, based upon our position in the drill-down of the hierarchy. The distance version of the Ancestor function has the same meaning as the Parent function, when the "distance," or the number of levels removed from the original member (in our case, the current member) to the target member (in our case, the Parent member) is 1.

21.  Right-click the Expense % calculated member.

22.  Select Edit from the appearing Context Menu.

The Calculated Member Builder reappears.

23.  In the Value Expression box, input the following expression:

	[Amount]/([Amount], Ancestor([Store].CurrentMember,1))

24.  Click OK to apply the changes.

	[Amount]/([Amount], Ancestor([Store].CurrentMember,1))

The results of the modified expression should appear as shown below.

Now the percentages for the Mexican Store States add to 100%. We have achieved our objective, in that the group of the Store Country level Expense % values, as well as the group of the Store State level Expense % values, total 100% of their respective rollup levels (the level Totals). This scenario demonstrates how MDX allows us to leverage the hierarchical structure of the OLAP data source in our expressions.