none
extract a year from a date RRS feed

  • Question

  • I have a date

     [Period Closed].[Period Closed] in a cube. This table contain only one values which might be  a certain date. Not today's date.

    I have another date table where I have to use the date value from [Period Closed].[Period Closed]  and pass in to another date table so that I can drive off all my data based on the date value. There is no hierarchy in [Period Closed].[Period Closed]  table. How can I extract year and month from [Period Closed].[Period Closed]  so that I can pass to main date table.

    Thursday, August 14, 2014 8:09 PM

Answers

  • Hi 1234Alex,

    According to your description, you are going to extract the year and month from the members on the dimension, right?

    In this case, please try the query below.

    with Member [Measures].[Year] as 'Year([Date].[Calendar].currentmember.name)'
    Member [Measures].[Month] as 'Month([Date].[Calendar].currentmember.name)'
    select {[Measures].[Year],[Measures].[Month]} on 0,
    [Date].[Calendar].[Date].members on 1
    from
    (
    select 
    {[Date].[Calendar].[Date].&[20050102]
    ,[Date].[Calendar].[Date].&[20060412]
    ,[Date].[Calendar].[Date].&[20070619]} on 0
    from 
    [Adventure Works]
    )

    Reference
    http://www.ssas-info.com/analysis-services-faq/27-mdx/78-how-write-mdx-query-that-uses-execution-datetime-now-as-parameter

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, August 18, 2014 7:02 AM
    Moderator

All replies

  • Try something like this:

    StrToMember('[Other Date].[Other Date]&[' + Format(CDate([Period Closed].[Period Closed]), 'yyyyMMdd') + ']')

    This assumes that the key for [Other Date].[Other Date] is an integer with YYYYMMDD.

    Hope this helps.

    Reeves


    Denver, CO

    Thursday, August 14, 2014 11:14 PM
  • Hi 1234Alex,

    According to your description, you are going to extract the year and month from the members on the dimension, right?

    In this case, please try the query below.

    with Member [Measures].[Year] as 'Year([Date].[Calendar].currentmember.name)'
    Member [Measures].[Month] as 'Month([Date].[Calendar].currentmember.name)'
    select {[Measures].[Year],[Measures].[Month]} on 0,
    [Date].[Calendar].[Date].members on 1
    from
    (
    select 
    {[Date].[Calendar].[Date].&[20050102]
    ,[Date].[Calendar].[Date].&[20060412]
    ,[Date].[Calendar].[Date].&[20070619]} on 0
    from 
    [Adventure Works]
    )

    Reference
    http://www.ssas-info.com/analysis-services-faq/27-mdx/78-how-write-mdx-query-that-uses-execution-datetime-now-as-parameter

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, August 18, 2014 7:02 AM
    Moderator