MDX in Analysis Services: Measuring Change over Time - Page 5
July 22, 2003
In our expression, the .PrevMember function refers to the current member as a "starting point," and returns the immediately preceding member occupying the same level in the Time hierarchy. This is especially useful within the context of "year-end cutoffs," and other financial and accounting time range "boundaries;" the function is quite effective as it ignores such boundaries and returns the immediately preceding member, as is desired in most cases.
17. Append the following to the expression in the Value Expression box
18. Enclose the entire expression in parentheses.
The final expression appears in Illustration 19 below:
19. Click OK to accept the expression as displayed.
The results dataset appears in the Data Viewing pane as depicted in Illustration 20.
We can readily see that the values that appear in the Prior Amount column accurately reflect the prior period balance, as shown in the associated previous time period slot in the Amount column. For example, the Q4, Month 11 Prior Amount ($ 28,448.04) matches the Q4, Month 10 Amount. The same applies to the Quarter level rollups (except for periods for which the cube does not contain data for a prior period / a respective time member).
Now let's repeat the process for the Average Total Cost Per Unit calculated member.
1. Select Insert --> Calculated Member
The Calculated Member Builder appears.
2. Type Prior Avg into the Member Name box.
3. In the Functions tree, expand the Member folder, as we did earlier.
4. Double-click the PrevMember function within the Member folder.
5. Single-click the <<Member>> token, within the Value Expression box, to select it.
6. Double-click the CurrentMember function within the Member folder.
7. In the Value Expression box, single-click the newly appearing <<Dimension>> token to select it.
8. Double-click the Time dimension within the Data tree.
The Value Expression box of the Calculated Member Builder now appears as shown in Illustration 21.
9. Append the following to the expression in the Value Expression box
, [Avg Total Cost Per Unit]
10. Enclose the entire expression in parentheses.
The final expression appears in Illustration 22 below:
11. Click OK to accept the expression as displayed.
Let's free up some real estate on the screen.
12. Click the Units Sold calculated member in the cube tree to select it.
13. In the Properties pane, select the Advanced tab.
14. Click the Visible property.
15. Select False in the selector box for the Visible property.
The Properties pane - Advanced tab appears as depicted in Illustration 23.
16. Press Enter to apply the modification.
The new results dataset appears in the Data Viewing pane, as depicted in Illustration 24.
As we noted in our earlier example, we can see that the values that appear in the Prior Avg column accurately reflect the prior period balance, as shown in the associated previous time period slot in the Avg Total Cost Per Unit column. For example, the Q4 Total Prior Avg ($ 1.50) matches the Q3 Total Avg Cost Per Unit value. The same applies to the Year level rollups (both the 1997 Total Avg Cost Per Unit and 1998 Total Prior Avg rows reflect $ 1.49).
MDX handles "parallel period" time considerations in much the same fundamental way: through the comprehension of hierarchical relationships, which was built into its design. Let's take a look at how we can leverage this functionality yet further in the final section of this lesson.