Intrinsic Member Properties: The MEMBER_KEY Property - Page 3

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-side columns. The corresponding Product Category members of the Product dimension (Product Categories attribute hierarchy) will inhabit the row axis, as we shall see.

Let’s construct a query, therefore, to return the requested Product Category information, presenting the concatenated Product Category – Full, Product Category – Key, Product Category – Caption and Product Category – MDX Qual Name in four, side-by-side columns, with the corresponding Product Category member names as rows.

-- MDX067-01  Using .MEMBER_KEY, MEMBER_CAPTION, .CurrrentMember 
--     and .UniqueName to generate a picklist selection
 
WITH
MEMBER
   [Measures].[Product Category - Key]
AS
   '[Product].[Product Categories].CurrentMember.MEMBER_KEY'
 
MEMBER
   [Measures].[Product Category - Caption]
AS
   '[Product].[Product Categories].CurrentMember.MEMBER_CAPTION'
   
MEMBER
   [Measures].[Product Category - FULL]
AS
   'VBA!CStr([Product].[Product Categories].CurrentMember.MEMBER_KEY) + 
       " - " + [Product].[Product Categories].CurrentMember.MEMBER_CAPTION'
 
MEMBER
   [Measures].[Product Category - MDX Qual Name]
AS
   '[Product].[Product Categories].CurrentMember.UNIQUENAME '
 
SELECT
   { [Measures].[Product Category - Key], 
     [Measures].[Product Category - Caption], 
         [Measures].[Product Category - FULL],
       [Measures].[Product Category - MDX Qual Name]}  
     ON AXIS(0),
        
   {[Product].[Product Categories].[Category].MEMBERS}
     ON AXIS(1)
 
FROM 
   [Adventure Works]  
 

The Query pane appears, with our input, as depicted in Illustration 1.

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 2 appears.

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.

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.

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

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

{[Product].[Product Categories].[Category].MEMBERS}
     ON AXIS(1)

ORDER({[Product].[Product Categories].[Category].MEMBERS},
       ( [Measures].[Product Category - Key]), ASC)
 ON AXIS(1)

The affected portion of the query appears, with our modifications, as shown in Illustration 4.

The Results pane is populated by Analysis Services, and the dataset depicted in Illustration 5 appears.

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.

NOTE: For more information about the MDX Order() function, please see Basic Set Functions: The Order() Function , another member of my MDX Essentials series at Database Journal.