- 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: An Integrated Example
Let's take a look at a real-world example using an MDX expression within the Budget cube. One common interest of management everywhere is the minimization of expense in operating the business. We'll begin by looking at the percentage that each Store within the international store chain contributed to the total expense amount that was incurred by the enterprise. We'll see how easy this is to accomplish with an MDX expression.
First, let's rearrange the layout of the data viewing pane to organize the presentation to suit our needs.
Make sure that the filter field for the Year dimension selects 1998. Then drag the Store dimension to the row axis, replacing the Measures dimension.
Expand the Store Country, Store State, Store City, and Store Name headings by double-clicking each heading, to reach the Store Name level.
Scroll over in the Data tab until the Net Income column appears (ignore MyCalcMem2 for now).
The data viewing pane should now resemble that partially shown in Figure 15. We've "exploded" the Store dimension down to its lowest level in order to build a calculated member that anticipates percentage of expenses belonging to the lowest level, individual stores of the Store dimension.
Figure 15 The fully expanded data tab (partial view).Select the Parent dimension of calculated member MyCalcMem2.
Select the Account dimension from the drop-down list.
Select the Value property for MyCalcMem2.
Click the ellipsis (...) button.
When the Calculated Member Builder appears, replace the contents of the Value Expression box with the following expression:
([Net Income], Ancestor([Store].CurrentMember, [Store].[(All)]))
Click the Check button to ascertain proper MDX syntax. The message in Figure 16 should appear.
Figure 16 Syntax verified.This message indicates that the expression entered doesn't violate any general syntax rules. While the checking process doesn't 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.
)
Figure 15)
Figure 16The expression represents a tuple that retrieves the value whose "address" is "Measures dimension: Net Income member," and "Store dimension: All level member." (The current member "pinch hits" for all other cube dimensions by default, as noted earlier.) This expression constructs the first of two components that we'll need to create the calculation to display a contribution to the total of all Store members' expenses. This will serve as the "Total Expense for All Stores" (shown in bold) that will occupy the following general calculation:
% Contributed Expense = (Expense for Current Store) / (Total Expense for All Stores)
The Total Expense for All Stores component will always return the same amount, while the Expense for Current Store will vary, depending on the Store that's acting as the current member for any given cell. Let's take a look at the result set that we obtain at this point.
Click OK to close the Calculated Member Builder and display the result set in Figure 17.
Figure 17 The result set for the Total Expense for All Stores
expression.Notice that the All Store Total in the Net Income column equals that shown in the cells of the MyCalcMem2 column. We have thus returned the denominator of the expression that we formulated above, the Total Expense for All Stores.
We're now ready to add the second component to our calculation, which will act to retrieve the expense totals for the individual Store current members.
Return to the Value Expression box by clicking the Value property ellipsis (...) button for MyCalcMem2.
Modify the expression to the following:
[Net Income] / ([Net Income], Ancestor([Store].CurrentMember, [Store].[(All)]))
In essence, we're adding the Measures dimension Net Income as a numerator in the equation; as a stand-alone member, this component acts as a full-fledged tuple that will return the value for the current member for all the dimensions involved. (Remember that parentheses are needed only for tuples containing multiple dimensions.) The end result is that this component varies to retrieve the Net Income amount for each Store member, while the denominator in our expression remains fixed in returning the Total Net Income (actually Expense, due to the reasons I mentioned earlier) for All Stores.
Click OK to save the modified expression and display the result set partially shown in Figure 18.
Figure 18 The result set for the complete expression.Now, let's clean up the formatting for the new values.
Select the MyCalcMem2 calculated member.
Click the Advanced tab in the Properties pane.
Select the Format String property of MyCalcMem2.
Click the drop-down arrow and select Percent as the format for the string (see Figure 19).
Figure 19 Format the value as a percentage.While we're at it, let's rename the calculated member to reflect its new role.
Click the Basic tab in the Properties pane.
Click the Name property.
Modify the name of the calculated member to Expense %.
Press Enter. The results in the data viewing pane should resemble those in Figure 20.
Figure 20 The modified view in the data viewing pane.Double-click the Store Country heading of the row axis to zoom to the Store Country view of the Store dimension. The results, as shown in Figure 21, show that the calculated member that we've created to show Expense % is context-sensitive to the hierarchical rollups.
Figure 21 Zooming demonstrates context sensitivity in the calculated
member.
)
Figure 17)
Figure 18)
Figure 19)
Figure 20)
Figure 21As a part of reviewing any work I do for a client, I try to anticipate possible user actions in making my design flexible, yet "foolproof." Let's consider the fact that users might drag other dimensions to the row area, or make other alterations that might affect the calculated member's value. To ensure that the Expense % calculated member correctly states the percentage of expenses contributed by each current member (for example, if we also had a Product dimension), we might want to specify further dimensions in the expression based on those in the upper part of the data viewing pane to take potential "swaps" into consideration. Our model, however, is simple. We will leave things as they are, with a note to memory to keep this consideration in mind when dealing with more elaborate models.