Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 20, 2008

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-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.


Illustration 1: Our Query in the Query Pane …

2.  Execute the query by clicking the Execute (!) button in the toolbar.

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


Illustration 2: Results Dataset: Combined Use of MEMBER_KEY, MEMBER_NAME, .UniqueName and .CurrentMember

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 > Save As …, 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 > Save As, 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:

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

With the following syntax:

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.


Illustration 4: The Row Axis Specification with Our Modifications …

8.  Execute the query by clicking the Execute (!) button in the toolbar, once again.

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


Illustration 5: Results Dataset – Modified Query that Orders the Product Category Key

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.

9.  Select File > Save MDX067-02 to save our last modifications.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date