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 unique name
(the "qualified name") within MDX.
11.
Type the following MDX into the
Value Expression section of the Calculated Member Builder:
[Store].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 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 depicted in Illustration 8.
Illustration 8:
Calculated Member Builder with Complete MDX Expression (Compressed View)
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_ns_PX_GeogStore_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, 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
levels of the Store 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_StoreGeogPLName_Hier],
[Measures].[RS_PX_StoreGeogMSASName_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 Store levels they wish to appear in the report; the
latter name supplies the MDX- "qualified" name required to filter the
report appropriately.
18.
Click the Check
button to ascertain the correctness of the syntax.
A
confirmation dialog should appear, indicating that Syntax is OK, as
shown in Illustration 11.
Illustration
11: Confirmation Dialog Indicates Syntax is Correct
19.
Click OK
to close the confirmation dialog.
20.
Click OK
to close the Named Set Builder, and save our new named set.
The new
named set appears in the tree as depicted in Illustration 12.
Illustration
12: 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.
23.
Click No
on the dialog that appears, as shown in Illustration 13; we will not
design aggregations at present.
Illustration
13: Click "No" to Designing Aggregations
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
24.
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
25.
Click Close
to dismiss the viewer.
26.
Select File
--> Exit to close the Cube Editor.
27.
Close Analysis
Services, as desired.
We will
leave Analysis Manager opened, as we begin a report in Reporting Services
next.
Verification
and Use
We can
verify the adequacy of our MSAS picklist support structures using the named set
(and thus the calculated members that compose it) in a simple report, as we
will do in the following steps.
» 
Comment and Contribute
