0

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?

whytheq
  • 34,466
  • 65
  • 172
  • 267
sam140
  • 219
  • 1
  • 5
  • 27
  • can any one tell me what i m doing wrong ? – sam140 Nov 21 '14 at 18:31
  • 1
    What environment are you working in? What language? This does not look like MDX statements, neither like SQL statements, just like some code fragments from whatever. Could you give a sample what may be contained in variable `parDate`? – FrankPl Nov 21 '14 at 19:10
  • I think in relation to this question this is a complete answer: http://msdn.microsoft.com/en-us/library/hh510163.aspx What is the context of your snippets? – whytheq Nov 22 '14 at 02:19
  • 1
    Where does Oracle come into play here? –  Nov 23 '14 at 14:31

1 Answers1

1

(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];
Community
  • 1
  • 1
whytheq
  • 34,466
  • 65
  • 172
  • 267