Procedure
1.
Right-click the new Exp_Finance
cube
2.
Select Edit ... from the
context menu that appears, as depicted in Illustration 5.
Illustration
5: Select Edit from the Context Menu
The Cube
Editor opens.
3.
Right-click the Calculated
Members folder within Cube Editor.
4.
Select New Calculated Member
... from the context menu that appears, as depicted in Illustration 6.
Illustration
6: Select New Calculated Member from the Context Menu
The Calculated
Member Builder opens.
5.
Type the following into the Member
name box:
RS_PX_StoreGeogPLName_Hier
While
the name of the calculated member can obviously be anything useful in the
environment in which we are creating it, I typically use a name similar to the
above, in order to make it apparent, both inside Analysis Services and
in the reporting application (Reporting Services in this article), that
the calculated member exists for use in the reporting ("RS")
application, that it has been created to support picklists ("PX"),
and that this particular calculated member exists to generate a picklist
display name ("PLName") in our reports for the various levels
of the Store dimension.
6.
Type the following MDX into the
Value Expression section of the Calculated Member Builder:
Space([Store].Currentmember.Level.Ordinal * 6) + [Store].CurrentMember.Name
The MDX expression above returns, via our calculated
member, a display name that we will use to generate the picklists that the
information consumers see, as part of being prompted, each time they attempt to
generate a report in which we insert the picklist to support a parameter. An explanation of the
components of the above expression appears in Table 1.
|
Expression
|
Meaning
|
|
Space([Store].Currentmember.Level.Ordinal
* 6)
|
The Space() VBA function is used together with the level ordinal
property of the current member to generate a "space size."
We then multiply that size value by six (6).
Our
objective here is cosmetic - we are simply making the geographical levels of the Store
dimension appear more intuitively hierarchical for the consumers, by "indenting"
each of the various levels of the Store dimension in accordance with
its levels in the hierarchy.
|
|
+ [Store].CurrentMember.Name
|
This section of our expression actually
generates the name of the current member ...
|
Table 1: Expression Components Summary
NOTE: For an explanation of the .CurrentMember function,
see my article MDX Essentials: MDX Member Functions: "Relative"
Member Functions in
the MDX Essentials series at Database
Journal.
The Calculated
Member Builder appears with our input as depicted in Illustration 7.
Illustration 7:
Calculated Member Builder with Complete MDX Expression (Compressed View)
7.
Click OK to
close the Calculated Member Builder.
The
new Calculated Member appears in the tree within the Calculated
Members folder.
8.
Right-click the Calculated
Members folder within Cube Editor, once again.
9.
Select New Calculated Member
... again, from the context menu that appears.
The Calculated
Member Builder opens.
10.
Type the following into the Member
name box:
RS_PX_StoreGeogMSASName_Hier
» 
Comment and Contribute
