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
, [Amount]
18.
Enclose the
entire expression in parentheses.
The
final expression appears in Illustration 19 below:
Illustration 19: The
Expression as it Appears in the Value Expression Box
19.
Click OK
to accept the expression as displayed.
The
results dataset appears in the Data Viewing pane as depicted in
Illustration 20.
Illustration 20: The
Results in the Data Viewing Pane
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.
Illustration 21: The
Expression Takes Form
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:
Illustration 22: The
Expression as it Appears in the Value Expression Box
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.
Illustration 23:
The Expression as it Appears in the Value Expression Box
16.
Press Enter
to apply the modification.
The new
results dataset appears in the Data Viewing pane, as depicted in
Illustration 24.
Illustration 24: The
Results in the Data Viewing Pane
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.
» 
Comment and Contribute
