Home > MDX Challenges > MDX #6 – Use Unique_Name Or MEMBER_KEY member property

MDX #6 – Use Unique_Name Or MEMBER_KEY member property

Value field is needed in parameter pick list or drill-through hyperlinks

When working with Reporting Services, we often provide users with parameter pick list such as this one:

image

We also provide users with hyperlinks so users can drill through to see more details.

image 

In Reporting Services, your pick list can have a Value field, and a Label field.

image

The label field is what is shown in the lick list, while the value field is what will be passed to your parameterized report.

In the case of hyperlinks, you will need to pick one value field from the dataset to pass to the detail sub-report that will takes the value as a report parameter.

So what are the available choices for this Value field?

If you use the default parameter pick list from the MDX Query Designer without modification, you will most likely end up with:

  • Member property MEMBER_CAPTION will end up as the Label field, and
  • .UniqueName function will end up as the Value field

image

Member Property: Unique_Name and MEMBER_KEY

The member caption is a good choice to show in the pick list, but the fully qualified Unique Name which includes the unique key value should be the choice to pass as the parameter.

image

However, there are cases where I only need the key value without the Dimension.Hierarchy qualifier. This is

  • either because I have created my sub-report to take only the key value, or
  • I need to qualify the value with different Dimension.Hierarchy qualifier.
    To fully understand the different member properties and the related member functions, I created this MDX query to show different member properties: 

image

    image

    A few notes on this query:

    1. Most member properties have equivalent member functions, such as MEMBER_CAPTION and MEMBER_KEY and UNQIUE_NAME.
    2. DIMENSION PROERTIES clause will not cause the properties to display in the pivot result in SQL Management Studio, and it will not cause them to become separate columns either in the Reporting Services.
    3. In SQL Management Studio, you can double-click any row cell to see the member properties.

    The result of the above query is:

    image  

    Now that MEMBER_KEY is what I am looking for for some of my sub-reports.

    Categories: MDX Challenges
    1. No comments yet.
    1. No trackbacks yet.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    %d bloggers like this: