CODEWITHSUNDEEP
mdx
user1508682
user1508682

Reputation: 1371

In MDX, What is the best way to display the member_key of all dimension members?

In the following example, how can I use the "member_key" for displaying the [Ad Name Dim].[Ad Name].[Name].ALLMEMBERS? in other words, to display ALL ads unique IDs instead of it's names ?

WITH 
  SET [Selected Measures] AS 
{
  [Measures].[Cost]
 ,[Measures].[Clicks]
} 
  MEMBER [Measures].[MinDate] AS 
Head
(
  NonEmpty
  (
    [Time Dim].[Time Dim].[Month] //<<to return minimum month but could be changed to a different level
   ,(
      [Selected Measures]
     ,[Ad Name Dim].[Ad Name].CurrentMember
    )
  )
 ,1
).Item(0).Item(0).Member_Caption 
SELECT 
  {
    [Selected Measures]
   ,[Measures].[MinDate]
   } ON COLUMNS
 ,NonEmpty
  (
    (
      [Ad Name Dim].[Ad Name].[Name].ALLMEMBERS
     ,
        ClosingPeriod
        (
         [Time Dim].[Time Dim].[Month]
        ,[Time Dim].[Time Dim].[All Time]
    ).Lag(3)
      : 
        ClosingPeriod
        (
          [Time Dim].[Time Dim].[Month]
         ,[Time Dim].[Time Dim].[All Time]
       )
   )
 ) ON ROWS
FROM [CubeName];

Upvotes: 1

Views: 1218

Answers (1)

whytheq
whytheq

Reputation: 35557

Reference: http://bisherryli.com/2012/05/07/mdx-6-use-unique_name-or-member_key-member-property/

Hopefully this helps:

WITH 
SET [Selected Measures] AS 
{
  [Measures].[Cost]
 ,[Measures].[Clicks]
} 
MEMBER [Measures].[MinDate] AS 
Head
(
  NonEmpty
  (
    [Time Dim].[Time Dim].[Month] //<<to return minimum month but could be changed to a different level
   ,(
      [Selected Measures]
     ,[Ad Name Dim].[Ad Name].CurrentMember
    )
  )
 ,1
).Item(0).Item(0).Member_Caption 
MEMBER [Measures].[MemberKey] AS 
  [Ad Name Dim].[Ad Name].currentmember.member_key
SELECT 
  {
   [Measures].[MemberKey]
   ,[Selected Measures]
   ,[Measures].[MinDate]
   } ON COLUMNS
 ,NonEmpty
  (
    (
      [Ad Name Dim].[Ad Name].[Name].ALLMEMBERS
     ,
        ClosingPeriod
        (
         [Time Dim].[Time Dim].[Month]
        ,[Time Dim].[Time Dim].[All Time]
    ).Lag(3)
      : 
        ClosingPeriod
        (
          [Time Dim].[Time Dim].[Month]
         ,[Time Dim].[Time Dim].[All Time]
       )
   )
 ) ON ROWS
FROM [CubeName];

Upvotes: 1

Related Questions