Featured Database Articles

Intrinsic Member Properties: The MEMBER_KEY Property - Page 3

By William Pearson

Procedure: Use the MEMBER_KEY Property to Provide Parameter
Picklist Support in Reporting Services

We again have a need that we can readily answer using the MEMBER_KEY
property, in conjunction with the MEMBER_CAPTION property and a
relative function, .CurrentMember. The solution also includes the .UniqueName
function. We will be targeting the Caption column in the resulting
dataset (well call it Product Category  Full) for the name that is
displayed in the selector of the parameter picklist. We will
also include a simple Product Category  Key column, to illustrate the most
basic use of the property, alongside the Product Category  Full column
that it will support.

The Unique Name column of the returned dataset (the
qualified MDX name for each Category member of the Product dimension,
Product Categories attribute hierarchy), which we call Product
Category  MDX Qual Name in the query we construct, will serve as the value
that is actually passed to the cube in the MDX of the query. A benefit of
being able to pass the MDX-qualified name to Analysis Services, while
presenting the information consumers the friendly name combination at run
time, is that we insulate them from the MDX altogether, while providing them ad
hoc selection of a Product Category upon which to filter the report
data.

Our first step is to construct a query to return the
requested Product Category list, presenting the selector Captions
and Unique Names, (as well as the pure expressions from which our
concatenated caption is derived), in side-by-sidecolumns. The corresponding Product Category members
of the Product dimension (Product Categories attribute hierarchy)
will inhabit the row axis, as we shall see.

We see the Product Category - Full captions,
the output of the Product Category - Full calculated member, populating
the third data column. The associated components of the
Product Category - Full calculated member, underlying Product
Category  Key and Product Category  Caption,appear,
respectively, in the two columns to its immediate left. Finally, Product
Category  MDX Qual Name, which contains the qualified name that
will be passed to Analysis Services upon selection of a given caption
within the ultimate parameter picklist, lies to the far right. The Product
Category members themselves occupy the row axis, as the client has
requested.

The calculated member Product
Category  Key employs the MEMBER_KEY
property in the simplest manner. The expression underlying this calculated
member is concatenated with that underlying the Product Category  Caption to
produce the results delivered by Product Category - Full calculated
member. (We employ the VBA CStr() function to convert the Product
Category  Key output to a string, before concatenating it, via the +
operator, with the string output of Product Category  Caption.)

In the expression underlying the Product
Category  MDX Qual Name calculated member, we employ the .UniqueName
function, in conjunction with the relative .CurrentMember function, to
produce the MDX qualified name that our parameter picklist will pass in
the ultimate runtime report to Analysis Services as a filter. Finally,
the juxtaposition of the members (via the MDX .Members function) of the Product
dimension, Product Categories hierarchy, and Category level in
our row axis, as we can easily see from our practical example, results in a
combination list of the captions / qualified names of the members
that we specify in our row axis. (Similarly,
if we had specified the Subcategory or Product levels of the
Product Categories hierarchy in the row axis instead, we would have
obtained a list of the members of those levels as a result). Intersecting the
calculations with the members under consideration can, of course, be leveraged,
in similar fashion, to produce sophisticated results within more elaborate
structures and processes.

3.
Select File
> SaveAs …, name the file MDX067-01,
and place it in a meaningful location.

Our
client colleagues express satisfaction with our initial solution, and state
that it satisfactorily displays the new, concatenated captions of the Product
Categories, alongside the respective qualified / unique names within
Analysis Services. We suggest one more enhancement, however, as we
realize that the dataset we have produced will ultimately appear within a parameter
picklist in one or more reports: We suggest that we order the dataset by Product
Category  Key to make it a bit more user-friendly for information
consumers at runtime.

4.
Select File
> SaveAs, name the file MDX067-02,
and place it in a meaningful location.

5.
In the top
line of the query (the first of two commented lines) modify MDX067-01
to MDX067-02.

6.
At the end of the
second commented line, change the wording a picklist selection to an
ORDERED picklist selection.

The
comment lines atop the query appear, with our changes, as depicted in Illustration
3.

Illustration 3: The Comment Lines with Our Modifications …

Next,
lets use the MDX Order() function to order our row axis by Product
Category Key.

7.
Replace the
current row axis specification, which currently appears as:

We see, within the red rectangle in Illustration
5, that the dataset is now ordered by Product Category  Key. This
will support the same ordering in the ultimate parameter picklist, which
should make selections easier for the ultimate information consumers.