33.
Select Canada
(under "All Stores") in the filter field for the Store
dimension atop the Data Viewing pane. The pane appears as shown below.
Illustration 13:
The Tuple Value Remains even with the Canada Filter
We see that MyCalcMem2
retains the tuple value of ([Account].[Net Income],[Store].[Store Country].[Mexico]), even when the Canada
filter is in place, for the results set. This demonstrates clearly that the expression,
and not the current member for the Store dimension, determines
the context of the value.
Say we
are presented with a requirement to quantify the difference in Net
Income between Mexico and Canada stores. We can create an expression
within our calculated member to calculate the difference and return the
amount. This will provide an added illustration of the use of tuples from our
cube, along with an appropriate operator, to build an expression to fit a
simple business need.
34.
Select Value property
for MyCalcMem2 once again.
35.
Click the ellipses button.
The Calculated
Member Builder dialog appears.
36.
Type in the following into the
Value Expression box:
([Account].[Net Income],[Store].[Store
Country].[Mexico])-([Account].[Net Income],[Store].[Store
Country].[Canada])
37.
Rename the Calculated Member "NAFTA
Partners Difference" using the Member Name box in the dialog.
The Calculated
Member Builder dialog appears as shown below.
Illustration 14:
The Calculated Member Builder Dialog with Modifications
38.
Click OK to enact
changes.
The Calculated
Member Builder dialog closes, leaving the result set shown in Illustration 15.
Illustration 15:
The New Expression Renders the Difference, as Expected
We
see, when we determine the difference in this way, that the figure delivered by
MDX (-761,869.84) indeed agrees with the independent calculation (the
difference between -790,921.84 and -29,052.00). We obtain the
difference regardless of the presence of conflicting filters in the top
of the Data Viewing pane, because we have explicitly specified
dimensions in our expression.
39.
Click File ` Exit from the top menu to leave the Cube
Editor.
40.
Click No
to abandon, or Yes to save, changes to the cube.
41.
Close Analysis
Manager as desired by clicking Console ` Exit in the upper top menu.
Next in Our Series ...
In this tutorial, we took MDX beyond the retrieval of member
names and properties, and began to focus on leveraging the capabilities of the
language to return values from a multidimensional cube. We
created calculated measures whose values were based upon a
constant, then upon current members, and explored additional uses of calculated
members and measures. We practiced returning values from cells
based upon the specification of dimensions within MDX expressions, to extend
the expressions' utility within the context of reporting from an OLAP data
source. Moreover, we examined various aspects of the MDX notation system along
the way.
Our next tutorial expands
further the intermediate topics we introduced in this lesson. We will take on practice
examples where we will delve into handling hierarchical relationships in
our expressions. We will also discuss a way to identify empty members,
and illustrate why this is important in building expressions.
»
See All Articles by Columnist William E. Pearson, III
» 
Comment and Contribute
