Enhance
the Foodmart Sales Cube to Support a Hierarchical Date Picklist in Reporting
Services
1.
Right-click
the new Param_Support_Sales cube.
2.
Select Edit
... from the context menu that appears, as shown in Illustration 5.
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_TimePLDispName_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. In addition, it makes it apparent that it has been created
to support picklists ("pX"), and that this particular
calculated member exists to generate a picklist display name ("PLDispName")
in our report(s) for the various levels of the Time dimension. The
specific convention we use in our own business environments should conform to
the needs and conventions of the respective environment, of course.
6.
Type the
following MDX into the Value Expression section of the Calculated
Member Builder:
Space([Time].Currentmember.Level.Ordinal * 3) + [Time].CurrentMember.Name
The MDX expression above returns, via our calculated
member, a display name. We will use this expression to generate the picklist
that the information consumers see, as selection options upon 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 2.
|
Expression
|
Meaning
|
|
Space([Time].Currentmember.Level.Ordinal * 3)
|
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 three
(3).
Our
objective here is cosmetic - we are simply making the levels of the Time dimension
appear more intuitively hierarchical for the consumers, by "indenting"
(by the assigned space size) each of the various levels of the Time
dimension in accordance with its levels in the hierarchy.
|
|
+ [Time].CurrentMember.Name
|
This section of our expression actually generates the name
of the current member ...
|
Table 2: 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 partially shown in Illustration
7.
Illustration 7:
Calculated Member Builder (Partial View) with Complete MDX Expression
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_TimeMSASName_Hier
We are
again using a naming convention for the member to make it apparent, both inside
Analysis Services and in the reporting application, 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 "qualified
name" (the Unique Name) within MDX.
11.
Type the
following MDX into the Value Expression section of the Calculated
Member Builder:
[Time].CurrentMember.UniqueName
The MDX expression will return, via our calculated member,
the unique name (a member property) that we will use as the value
field in a Dataset query, upon which we base our ultimate report. In other
words, it generates the qualified / full name that corresponds to the "user-friendlier"
picklist name that the user selects at runtime.
The Calculated
Member Builder appears, with our input, as partially depicted in Illustration
8.
Illustration 8:
Calculated Member Builder (Partial View) with Complete MDX Expression
12.
Click OK to
close the Calculated Member Builder.
The
new Calculated Members appear in the tree within the Calculated
Members folder as shown in Illustration 9.
Illustration 9:
Calculated Members in Place for Picklist Name and MSAS Name
(Compressed View)
13.
Select File
-> Save to save our additions to the cube structure.
Now that
we have the two calculated members in place, let's create a named set to
contain them conveniently for use in the reporting application.
14.
Right-click
the Named Sets folder within Cube Editor.
15.
Select New
Named Set ... from the context menu that appears, as depicted in Illustration
10.
Illustration 10: Select
New Named Set ... from the Context Menu
The Named
Set Builder opens.
16.
Type the
following into the Set name box:
RS_pX_Time_Hier
As we
saw to be the case with the calculated members earlier, the name of the set 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 from the reporting application (Reporting
Services in this article), that the named set exists for use in the
reporting ("RS") application. IN addition, it makes
it apparent that it has been created to support picklists ("pX"),
and that this particular named set exists to support picklists in our reports
for the various hierarchical levels of the Time dimension.
17.
Type the
following MDX (using the graphical design tools, if useful) into the Set
Expression section of the Named Set Builder:
{[Measures].[RS_pX_TimePLDispName_Hier],[Measures].[RS_pX_TimeMSASName_Hier]}The set we specify above will return, via our named set,
both the picklist name (which the consumers will see) and the "qualified"
MSAS name. The former name provides the list from which the intended audience
can select the Time levels they wish to appear in the report; the latter
name supplies the MDX- "qualified" name required to filter the report
appropriately.
The Named
Set Builder appears, with our input, as partially shown in Illustration 11.
Illustration 11: Named
Set Builder (Partial View) with Complete MDX Expression
18.
Click the Check
button to ascertain the correctness of the syntax.
A
confirmation dialog should appear, indicating that Syntax is OK, as
depicted in Illustration 12.
Illustration 12:
Confirmation Dialog Indicates Syntax is Correct
19.
Click OK
to close the confirmation dialog.
20.
Click OK
to close the Named Set Builder, and to save our new named set.
The new
named set appears in the tree as shown in Illustration 13.
Illustration 13: The New
Named Set Appears
21.
Select File
-> Save once again to save the cube with the new addition.
22.
Select Tools
-> Process Cube to process the clone cube.
NOTE: The cube must be processed
before we can designate it as a data source in the next section.
The Process
a Cube dialog appears, as depicted in Illustration 14, with the processing method
defaulted to Full Process. Full processing is the only option, as this
is the first processing cycle of our cloned cube.
Illustration 14: Full
Process Selected in the Process a Cube Dialog
23.
Click OK
to begin processing.
Processing
begins. The Process viewer displays various logged events, then presents
a green Processing completed successfully message, as shown in Illustration 15.
Illustration 15:
Indication of Successful Processing Appears
24.
Click Close
to dismiss the viewer.
25.
Select File
-> Exit to close the Cube Editor.
26.
Exit Analysis
Services, as desired.
» 
Comment and Contribute
