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)),
Crossjoin(Descendants([Product],[Product].levels(0)),
Descendants([Scenario],[Scenario].levels(0))))"
amount "([East],[Total_Expenses])"
target ""
range "CrossJoin({[Sales],[Cost_of_Goods_Sold]},
Descendants([South],[Market].levels(0)))"

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
Glenn,

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?

http://docs.oracle.com/cd/E17236_01/epm.1112/esb_tech_ref/maxl_execalloc.html
http://docs.oracle.com/cd/E17236_01/epm.1112/esb_tech_ref/maxl_alloc_numeric.html

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
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)"

Rgds
• ###### 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.