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.
message box closes, placing us back at the Calculated Member Builder
Member Builder dialog closes.
In the Cube
tree, select Expense % in the Calculated Members
folder for the Budget cube, if it is not already selected.
tab of the Properties pane appears underneath Expense %.
Select the Parent
Dimension property of Expense %, on the Basic tab.
the Measures dimension appears in the selector box for the Parent
Click the Advanced
tab in the Properties pane.
Select the Format
String property of Expense %.
as the format for the string, by insuring the word appears in the selector box
for the Format String property.
result set appears as shown in Illustration 5.
Illustration 5: The Modified Result Set from the Data Viewing
the Mexico member of the Store Country
level (Rows axis) to explode to the Mexico Store State view of Store dimension.
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.
Illustration 6: Data View Drilled Down to the Mexican Store State Members
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
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.
Right-click the Expense %
Select Edit from the
appearing Context Menu.
The Calculated Member Builder reappears.
In the Value
Expression box, input the following expression:
to apply the changes.
The results of the
modified expression should appear as shown below.
Illustration 7: The New Result Set from the Data Viewing Pane
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.