CODEWITHSUNDEEP
sqloracle-databasemdxmsdnmdxstudio
sam140
sam140

Reputation: 249

Unable to retrieve the month from date parameter using vba function in mdx

I am trying to retrieve month from parametrized date , by using :

 vba!month(${parDate}) or

 vba!datePart(m,${parDate}) or

 vba!format(${parDate},'mmm')

None of the above is working.Can you guide, what is the right approach to do this?

Upvotes: 0

Views: 319

Answers (1)

whytheq
whytheq

Reputation: 35587

(try uppercase 'MM' rather than 'mmm' - even 'mm' is wrong as it will look for minutes rather than months)

This question and answer looks at working with dates: Changing a date format to a shorter date

MSDN is a good reference for the available vba functions in mdx that you can use to play around with dates. Current link is here: http://msdn.microsoft.com/en-us/library/hh510163.aspx

I'm assuming you have a date dimension and would like to create a calculated measure that returns a numeric value that is the month.
Using AdWks I can do the following:

WITH 
  MEMBER [Measures].[DateValue] AS 
    [Date].[Calendar].CurrentMember.MemberValue 
  MEMBER [Measures].[DateKey] AS 
    [Date].[Calendar].CurrentMember.Member_Key 
  MEMBER [Measures].[DateMONTH] AS 
    Mid
    (
      [Measures].[DateKey]
     ,5
     ,2
    ) 
SELECT 
  {
    [Measures].[DateValue]
   ,[Measures].[DateKey]
,[Measures].[DateMONTH]
  } ON 0
 ,Order
  (
    {
      Exists
      (
        [Date].[Date].MEMBERS
       ,[Date].[Calendar Year].&[2010]
      )
    }
   ,[Date].[Calendar].CurrentMember.MemberValue
   ,BDESC
  ) ON 1
FROM [Adventure Works];

But maybe you'd just like to play around with today's date and extract the month:

WITH 
MEMBER [Measures].[DateValue] AS 
    [Date].[Calendar].CurrentMember.MemberValue 
  MEMBER [Measures].[TodayKey] AS 
    format(Now(),'yyyMMdd')
  MEMBER [Measures].[TodayMONTH] AS 
    Mid
    (
      [Measures].[TodayKey]
     ,5
     ,2
    ) 
SELECT 
  {
    [Measures].[DateValue]
   ,[Measures].[TodayKey]
,[Measures].[TodayMONTH]
  } ON 0
 ,Order
  (
    {
      Exists
      (
        [Date].[Date].MEMBERS
       ,[Date].[Calendar Year].&[2010]
      )
    }
   ,[Date].[Calendar].CurrentMember.MemberValue
   ,BDESC
  ) ON 1
FROM [Adventure Works];

Upvotes: 1

Related Questions