CODEWITHSUNDEEP
ssasmdx
ilija veselica
ilija veselica

Reputation: 9574

MDX select and display property

SELECT 
   [Measures].[BudgetAmount] ON COLUMNS,
   NON EMPTY [Date].[Calendar].[Month].MEMBERS.Properties("YYYYMM")
    --DIMENSION PROPERTIES [Date].[Calendar].[YYYYMM] 
   ON ROWS
FROM [WH_Cube]

How can I display a property directly in a query results?

Upvotes: 0

Views: 189

Answers (2)

whytheq
whytheq

Reputation: 35557

Disclaimer: This is not pretty (UNDERSTATEMENT) + not quite the format you want - I'm guessing you want the dates to appear like this "2014 September" ?

WITH
MEMBER [Measures].[DateValueX] as
  VBA!cstr(
    VBA!YEAR(
      VBA!cdate(format(
      VBA!cdate(
        [Date].[Calendar].[Month].CURRENTMEMBER.member_value),
        "dd MMMM yyyy"
      )
      )
    )
  )
+
  VBA!cstr(
    VBA!MONTH(
      VBA!cdate(format(
      VBA!cdate(
        [Date].[Calendar].[Month].CURRENTMEMBER.member_value),
        "dd MMMM yyyy"
      )
      )
    )
  )
SELECT 
  {
  [Measures].[DateValueX],
  [Measures].[BudgetAmount]
  }     
  ON COLUMNS,
NON EMPTY 
  DESCENDANTS(
    [Date].[Calendar].[Month].[201409], //<<you might need to amend [201409] to your cube
    [Date].[Calendar].[Calendar Day]    //you might need to amend [Calendar Day] to your cube
    )
  ON ROWS
FROM [WH_Cube]

Although if you want just the month's key, so that next to a date such as "25 September 2014" it would show as "201409" then that is relatively easy in our cube:

WITH MEMBER [Measures].[DateValueY] as
  [Date].[Calendar].CURRENTMEMBER.Parent.Properties('key0', TYPED)
SELECT 
  {
  [Measures].[DateValueY],
  [Measures].[BudgetAmount]
  }     
  ON COLUMNS,
NON EMPTY 
  DESCENDANTS(
    [Date].[Calendar].[Month].[201409], //<<you might need to amend [201409] to your cube
    [Date].[Calendar].[Calendar Day]    //you might need to amend [Calendar Day] to your cube
    )
  ON ROWS
FROM [WH_Cube]

Upvotes: 1

nsousa
nsousa

Reputation: 4544

You need to define your property as a measure:

With Member [Measures].[YYYYMM] as [Date].[Calendar].CurrentMember.Name

or

With Member [Measures].[YYYYMM] as [Date].[Calendar].CurrentMember.Properties("Some Custom Property")

Upvotes: 1

Related Questions