6 Replies Latest reply on Feb 14, 2013 5:48 AM by ESST

    Essbase ASO Allocation

      Hi, all,

      I am writing an ASO allocation script, which is working now. However, I wonder whether there is any way to easily have some mathematics operations on some of the fields. For example, in the following allocation script, how can i use the negative figures of the amount to allocated out? For example, "([East],[Total_Expenses])" is a positive 1000. However, I would like to have the negative 1000 (e.g. -1000) to be allocated amount?

      execute allocation process on database DemoASO.Basic with
      pov "Crossjoin(Descendants([Year],[Year].levels(0)),
      amount "([East],[Total_Expenses])"
      target ""
      range "CrossJoin({[Sales],[Cost_of_Goods_Sold]},

      Is there a easy way to do such amount calculation in the ASO allocation script? Or I have to created additional members in outline to do such reversal of the sign?

      Appreciate any suggestions/ideas?

      Thanks a lot
        • 1. Re: Essbase ASO Allocation
          How about creating a member Neg Total Expensees with the formula [Total Expenses] * -1
          • 2. Re: Essbase ASO Allocation

            Thanks for the quick response.

            Yes. I already tried that. That way it worked. However, I have to change the hierarchy type from stored to dynamic or multiple-hierarchy enabled, since I do not want that new member to be consolidated. (Btw, in my case, it is not the account dimension). I just wonder whether there is any syntax that supports the simple mathematics computation, within the Maxl allocation script.

            Thanks again.
            • 3. Re: Essbase ASO Allocation
              Major caveat - I haven't used the function - but have you looked at the Technical Reference?


              The example there says that 'amount' is a numeric expression, i.e. you don't just have to list a member or tuple there. It looks like you can use an arithmetic expression, but if you do all members must be from the same dimension - so your {[East], [Total_Expenses]} might be problematic. But that's obviously a sample rather than your actual use case.
              • 4. Re: Essbase ASO Allocation
                Th. Reich
                The amount is defined as an ALLOC-NUMERIC: http://docs.oracle.com/cd/E17236_01/epm.1112/esb_tech_ref/maxl_alloc_numeric.html

                So could could just easily add a *-1 for the amount statement, than it should allocate the negative value of it:

                amount "([East],([Total_Expenses]*-1)"

                • 5. Re: Essbase ASO Allocation
                  Can you reference members from more than one dimension in an arithmetic expression (the Tech Ref suggests otherwise)?
                  • 6. Re: Essbase ASO Allocation
                    Thanks for all.

                    I have tried to run the Maxl with *-1. It gave the syntax error.