MDX in Analysis Services: Measuring Change over Time - Page 3

July 22, 2003

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.