Now that we have the
number of units sold, let's create a calculated member that we will use as the
vehicle to illustrate how MDX can help us support time-based analysis. But
first, let's vary our requirement from the last lesson and change to a view of
expenses, versus the current revenues perspective.
14.
Select Total
Expense in the filter field for the Account dimension atop
the Data Viewing pane.
The
contents of the Data Viewing pane now appear as shown in Illustration
7.
Click for larger image
Illustration 7: The Data
Viewing Pane Contents after Changing the Accounts Filter (Compressed View)
15.
Select Insert
-> Calculated Member from the main menu once again.
The Calculated
Member Builder appears.
16.
Type Avg Total
Cost Per Unit into the Member Name box. In the Value Expression
box, type the following expression:
[Amount]/[Units Sold]
17.
Click OK
to accept the expression entered.
The Data
Viewing pane appears as shown in Illustration 8.
Illustration 8: The Total
Expense Amount with the Avg Total Cost per Unit Calculated Measure
We note
that, while all else appears to be working reasonably (at least from a cursory
level), the Avg
Total Cost Per Unit for
Canada and Mexico is suffering from the "divide by zero" malady we
discussed in Lesson
Three: Intermediate Concepts - Part 2. Let's handle this "undefined"
condition by adapting the expression behind our Avg Total Cost Per Unit
calculated member to handle the occasions where we are presented with zeros in
the member denominators.
18.
Right-click the Avg Total Cost Per Unit calculated member.
19.
Select Edit from the
context menu that appears
The
Calculated Member Builder reappears.
20.
In the Value Expression
box, replace the existing expression with the following:
IIF(IsEmpty(([Units
Sold],[Store].CurrentMember)), 0, [Amount]/ ([Units Sold],
[Store].CurrentMember))
21.
Click OK to apply the
changes.
The result set that is
retrieved should appear as shown in Illustration 9.
Illustration 9: The New
Result Set from the Data Viewing Pane
(Remember to take
advantage of the Check button to make sure that the correct number of
parentheses, etc., is placed anytime you work with the Value Expression
in the Calculated Member Builder.)
We obtain the desired results for Avg Total Cost Per
Unit for the Stores
depicted, noting that the Canadian and Mexican stores now display zeros
instead of confusing results, both at this and the various drill-down levels.
Let's make a few
cosmetic adjustments while we're here, before moving on to our primary focus.
22.
Ensure the Avg Total Cost Per Unit calculated member is selected in the Cube tree.
23.
Select the Basic tab of
the Properties pane, which appears underneath the Cube tree, if not
already selected.
24.
Ensure that the Measures
dimension is selected for the Parent Dimension property.
25.
Click the Advanced tab
in the Properties pane.
26.
Select the Format String
property of Avg Total Cost Per Unit.
27.
Click the drop-down arrow.
28.
Select Currency
as the format for the string.
The Advanced tab
in the Properties pane appears as shown in Illustration 10.
Illustration
10: The Format String Setting in the Advanced Properties Pane
29.
Select Units Sold in
the Calculated Members folder for the Budget cube.
30.
Select the Basic tab in
the Properties pane.
31.
Select the Parent Dimension
property of Units Sold, on the Basic tab.
32.
Ensure that the Measures
dimension is selected (via the drop-down arrow).
33.
Click the Advanced tab
in the Properties pane.
34.
Select the Format String
property of Units Sold.
35.
Click the drop-down arrow.
36.
Select #,# (whole
units with thousands separator - no decimal placed) as the format for the
string.
37.
Press Enter.
The updated Data Viewing
Pane appears as shown in Illustration 11.
Illustration
11: The Dataset with New Formatting Refinements
We see both the Units
Sold and the Avg Total Cost Per
Unit calculated members appear,
complete with our formatting specifications.
The forgoing steps have
served as both preparation for primary objective of our lesson and a hands-on
review and practice of some of the things we have learned about calculated
members before this lesson. We will move now to our exploration of the MDX time
functions.
IE 7
Firefox 2.0