locked
First and last members of time dimension RRS feed

  • Question

  • I created the following MDX calculated member on a Reporting Services report:

    SUM(STRTOMEMBER(@FromTimeDATE, CONSTRAINED) : STRTOMEMBER(@ToTimeDATE, CONSTRAINED), [Measures].[Counter])

    where @FromTimeDATE and @ToTimeDATE are report parameters based on users selection, in the format [Time].[DATE].&[2008-01-01T00:00:00].

    I want to move this calculated member inside the cube of Analysis Services and substitute the two parameters with the first and the last members of the current context of the Time dimension.

    It's possible to achieve this task?

    Thanks a lot, Carl.
    Monday, November 10, 2008 8:19 PM

Answers

  • Thanks for your response. The expression you mentioned works, but what I was trying to do is create a calculated member
    not sliced by time dimension.
    So it's necessary to determine the first and the last member of the time dimension and sum over that period.

    I solved my problem in the following way.

    I created a set of current context date dimension members, excluding the All and the Unknown members:

    Code Snippet

    CREATE SET [CURRENTPERIOD] AS
    EXCEPT(EXISTS([Time].[DATE].MEMBERS), {[Time].[DATE].[All], [Time].[DATE].[Unknown]})


    At this point the first member and the last member can be calculated using the ITEM property (in my case, these calculated members are used only for debug purpose):

    Code Snippet

    CREATE MEMBER [FIRSTDAY] AS
    [CURRENTPERIOD].ITEM(0).MEMBERVALUE

    CREATE MEMBER [LASTDAY] AS
    [CURRENTPERIOD].ITEM(COUNT([CURRENTPERIOD])-1).MEMBERVALUE


    The sum of the measure over the current period became the following expression:

    Code Snippet

    CREATE MEMBER [TOTALPERIOD] AS
    SUM([CURRENTPERIOD].ITEM(0) : [CURRENTPERIOD].ITEM(COUNT([CURRENTPERIOD])-1), [Measures].[Counter])


    I'm not sure this is the best solution, but it works Smile

    Thanks, Carl.
    Saturday, November 15, 2008 5:31 PM

All replies

  •  

    Hm,

     

    I think it would be very difficult to pull that off. Yet, maybe we can simplify things.

     

    How about this approach. You want this parameters to be substituted with first and last members of current context. Now, in that expression, they form a range. Instead of suffering to get (useful!) members in your MDX script in some way, why not just change the range to represent the current context. Furthermore, we don't have to do that in a cube, we can define it right there in expression. And that expression (calculated member) can then be placed in MDX script, if you want so.

     

    Here it is:

     

    Code Snippet

    Sum( Existing [Time].[Date].MEMBERS, [Measures].[Counter] )

     

     

     

     

    Tomislav Piasevoli

    Business Intelligence Specialist

    http://www.softpro.hr

     

    Monday, November 10, 2008 9:47 PM
    Answerer
  • Thanks for your response. The expression you mentioned works, but what I was trying to do is create a calculated member
    not sliced by time dimension.
    So it's necessary to determine the first and the last member of the time dimension and sum over that period.

    I solved my problem in the following way.

    I created a set of current context date dimension members, excluding the All and the Unknown members:

    Code Snippet

    CREATE SET [CURRENTPERIOD] AS
    EXCEPT(EXISTS([Time].[DATE].MEMBERS), {[Time].[DATE].[All], [Time].[DATE].[Unknown]})


    At this point the first member and the last member can be calculated using the ITEM property (in my case, these calculated members are used only for debug purpose):

    Code Snippet

    CREATE MEMBER [FIRSTDAY] AS
    [CURRENTPERIOD].ITEM(0).MEMBERVALUE

    CREATE MEMBER [LASTDAY] AS
    [CURRENTPERIOD].ITEM(COUNT([CURRENTPERIOD])-1).MEMBERVALUE


    The sum of the measure over the current period became the following expression:

    Code Snippet

    CREATE MEMBER [TOTALPERIOD] AS
    SUM([CURRENTPERIOD].ITEM(0) : [CURRENTPERIOD].ITEM(COUNT([CURRENTPERIOD])-1), [Measures].[Counter])


    I'm not sure this is the best solution, but it works Smile

    Thanks, Carl.
    Saturday, November 15, 2008 5:31 PM