1 Reply Latest reply: Aug 20, 2012 5:21 PM by 938723 RSS

    Cumualtive Sum over User Defined Time Dimension

    938723
      Hoping that someone could explain how to use an Olap expression to determine the cumulative sum over a hierarchy.

      I have a Date Dimension with levels:

      DAY,WEEK,MONTH,YEAR

      And a Time dimension with levels:

      ALL_TIME,HOUR,MIN

      How can I perform a cumulative SUM of a measure over my Time Dimension?

      I get the following error:

      An error has occurred on the server
      Error class: Express Failure
      Server error descriptions:
      DPR: cannot create server cursor, Generic at TxsOqDefinitionManager::generic<CommitRoot>
      INI: XOQ-02000: PrimaryDimension "SH_OLAP.TM" is not a valid TimeDimension for the time series expression., Generic at TxsOqSyntaxPrinter::checkIsTimeDimension

      With the following expression.

      SUM(SH_OLAP.SALES) OVER (HIERARCHY TM.STANDARD BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN LEVEL)

      TM in this case is my Time dimension.
        • 1. Re: Cumualtive Sum over User Defined Time Dimension
          938723
          Well it seems that the solution to this is to simply populate the TIME_SPAN and END_DATE fields.

          My Time dimension uses minute and hours, and therefore I am using a TIME_SPAN of 1/1440 for minutes and 1/24 for hours.

          My END_DATE is based on a TIMESTAMP type (rather than DATE) and I am using 01-JAN-2000 for my date portion and the appropriate time portion.