locked
Mdx Running Total RRS feed

  • Question

  • Is there any way to achieve the following:

    PeriodStart     TimeCharged     CumulativeTotal
    Oct-07                  10                        10
    Oct-14                  15                        25
    Oct-21                  25                        50
    Oct-28                    5                        55

    I am not an advanced MDX user so forgive me
    if this is a dumb question.

    Friday, October 13, 2006 8:47 PM

Answers

  • WITH MEMBER [Measures].[CumulativeTimeCharged] AS
    'SUM({NULL:[Date].[Week].CurrentMember},[Time Charged])'

    SELECT NON EMPTY
    {
         [Measures].[CumulativeTimeCharged]
    }
    ON COLUMNS,
    NON EMPTY
    {
        (
              [Project].[Project Description].[Project Description].
    ALLMEMBERS *
              [Date].[Week].[Week].
    ALLMEMBERS
         )
    }
    ON ROWS
    FROM
    (
        
    SELECT
        
    (
             
    NULL:STRTOMEMBER(@ToDate, CONSTRAINED)
         )
    ON COLUMNS
        
    FROM
        
    (
             
    SELECT
              (
                  
    STRTOSET(@Project, CONSTRAINED)
              )
    ON COLUMNS
            
    FROM [Timesheet_Cube]
         )
    )

    The key is the currentmember operator.
    This query takes 2 parameters, the cutoff date and a dimension
    (in my case a Project) for which you want your measure (in my
    case TimeSheet Data) to be cumulatively shown.

    This will give you an output like:
    The column in "{ }" is not actually part of the output
    from the query above, but is shown so you get an idea how
    the Cumulative total numbers are generated.

    Project             Week        { TimeCharged}     CumulativeTotal
    FooProject      Oct-07                  10                        10
    FooProject      Oct-14                  15                        25
    FooProject      Oct-21                  25                        50
    FooProject      Oct-28                    5                        55

    Cheers.

    Wednesday, October 18, 2006 9:13 PM

All replies

  • Define CumulativeTotal as a calculated measure in the form of sum(periodstodate(...), TimeCharged).  You can also use one of the shortcut variants of periodstodate such as ytd, qtd, mtd, etc.

    Friday, October 13, 2006 10:36 PM
  • From my example, this suggestion clearly does not
    solve the problem.  Using period to date would always
    result in the same value for the cumulative column:

    PeriodStart     TimeCharged     CumulativeTotal
    Oct-07                  10                        55
    Oct-14                  15                        55
    Oct-21                  25                        55
    Oct-28                    5                        55

    Which is not what I'm looking for.

    Thanks.

    Sunday, October 15, 2006 9:23 PM
  • Since TimeCharged clearly changes with respect to PeriodStart, why can't you make PeriodsToDate returns different sets based on PeriodStart?
    Monday, October 16, 2006 7:23 PM
  • WITH MEMBER [Measures].[CumulativeTimeCharged] AS
    'SUM({NULL:[Date].[Week].CurrentMember},[Time Charged])'

    SELECT NON EMPTY
    {
         [Measures].[CumulativeTimeCharged]
    }
    ON COLUMNS,
    NON EMPTY
    {
        (
              [Project].[Project Description].[Project Description].
    ALLMEMBERS *
              [Date].[Week].[Week].
    ALLMEMBERS
         )
    }
    ON ROWS
    FROM
    (
        
    SELECT
        
    (
             
    NULL:STRTOMEMBER(@ToDate, CONSTRAINED)
         )
    ON COLUMNS
        
    FROM
        
    (
             
    SELECT
              (
                  
    STRTOSET(@Project, CONSTRAINED)
              )
    ON COLUMNS
            
    FROM [Timesheet_Cube]
         )
    )

    The key is the currentmember operator.
    This query takes 2 parameters, the cutoff date and a dimension
    (in my case a Project) for which you want your measure (in my
    case TimeSheet Data) to be cumulatively shown.

    This will give you an output like:
    The column in "{ }" is not actually part of the output
    from the query above, but is shown so you get an idea how
    the Cumulative total numbers are generated.

    Project             Week        { TimeCharged}     CumulativeTotal
    FooProject      Oct-07                  10                        10
    FooProject      Oct-14                  15                        25
    FooProject      Oct-21                  25                        50
    FooProject      Oct-28                    5                        55

    Cheers.

    Wednesday, October 18, 2006 9:13 PM
  • Thanks for the query - it's the only "Running Total"-like query I've found that worked for me.

     

    My question is - why isn't there a simple way to create calculated members that, when spliced by Time, show the running total instead of the total for that time period?  I've scoured the forums and help and books but haven't found anything that solves this yet.

     

     

    Wednesday, March 28, 2007 10:12 PM
  • It worked perfectly for me, but I need this accumulated value to be fixed and not change with the use of filters
    Tuesday, June 26, 2018 1:34 PM