Let's
insert an argument to leverage further the flexibility of the expression.
16.
Return to the Value
Expression box for the Parallel Amount calculated member.
17.
Place the
following text between the parentheses for the argument that we discussed
earlier:
[Year]
The
completed expression should now appear as below:
(ParallelPeriod([Year]),[Amount])
18.
Click OK.
19.
Expand 1998
as we did for 1997 earlier, as shown in Illustration 29.
Illustration 29: Expanded
1998
20.
Drag the Parallel
Amount calculated measure to the top of the calculated measures listed in
the Cube tree, to make comparisons between Amount and Parallel Period
easier (simply drag and drop Parallel Amount over the top calculated
member to perform the desired substitution).
The
relevant portion of the results appears as shown in Illustration 30:
Illustration 30: The
Results with the Modified ParallelPeriod() Function
As we see
above, the Amount value (at each level) now has a matching Parallel
Amount in the respective reporting period of the previous year (except,
obviously, where no data exists within the cube to be retrieved).
We
will now conclude the lesson with the computation of a variance (often called a
"delta" or, basically enough, "growth") between the current
Amount and the Prior and Parallel Amounts. This is
handled via a simple subtraction of one calculated member from another,
as we see in the following steps:
21.
Double-click
the Quarter dimension label once again, to zoom up - and free real
estate for our next step.
22. Select Insert (top menu).
Select Calculated Member on the drop-down menu.
The Calculated
Member Builder appears.
23.
Type Chg
Over Parallel Period into the Member Name box.
24.
In the Value
Expression box, type the following expression:
[Amount]-[Parallel Amount]
25.
Click OK
to accept the expression entered.
Compare
the result set to that partially shown in Illustration 31. Keep in
mind the effects that are to be expected when dealing with debit / credit (+ or
-) signs.
Illustration 31: The New
Chg Over Parallel Period Column Appears
Notice
that we did not have to set the format string for the new calculated
member; it automatically assumed the format string of the two calculated
members from which it was computed.
26. Select Insert (top menu).
27. Select Calculated Member on
the drop-down menu.
The Calculated
Member Builder appears.
28.
Type Chg
Over Prior Period into the Member Name box.
29.
In the Value
Expression box, type the following expression:
[Amount]-[Prior Amount]
30.
Click OK
to accept the expression entered.
Compare
the result set to partially shown in Illustration 32.
Illustration 32: The New
Change Over Prior Period Column Appears
While the
examples above restrict themselves to simple variances, percentage variances
and a host of other embellishments might be added via a well-placed calculated
member in the same general manner.
31.
Save the work
accomplished in this session by selecting File -> Save from the top menu, as desired.
In
conclusion, our efforts have shown the relative ease with which we can embed
the concept of time within our MDX expressions to support the
time-based analysis needs of Information Consumers, such as the quantification
of change in values over time. The inherent grasp of hierarchical
relationships that is integral to the design of MDX makes handling time
members quite similar to handling members of any other dimension.
Next in Our Series ...
In this
tutorial, we expanded upon the topics we introduced in our previous lesson, Retrieve Data from Multiple Cubes. We began the lesson with another
integrated practice example, where we explored further the handling of hierarchical
relationships in our expressions. We practiced using conditional tests
to handle "divide-by-zero" scenarios. Finally, we introduced the
concept of time within the context of expression design, and
worked through examples of providing support for time-based analysis, such as
the quantification of change over time in values, with MDX functions designed
for that purpose.
Our
next lesson, Using
Named Sets,
will begin an exploration of MDX Sets. We will discuss the differences
between MDX expressions and MDX query statements,
and practice working with rudimentary queries. We will explore set functions
as part of our tutorial, as well as the general creation of sets and some of
their potential uses.
»
See All Articles by Columnist William E. Pearson, III
IE 7
Firefox 2.0