3 Replies Latest reply: Feb 9, 2014 11:34 PM by Phulsebosch RSS

BSO Script : How to perform currency conversion only when needed (depending of the selected entity)

Tipiak Newbie
Currently Being Moderated

Hello,

 

Here my Entity dimension :

 

Entity

     EUR_entities

          Entity1

          Entity2

          Entity3

          Entity4

     Local_currency

          Entity2_local

 

Entity2 contain Entity2_local data after performing currency conversion formula

 

I'm using a Planning BSO cube for my budget and this rule is executed each time a user save & submit data.

 

=> If user is on Entity2_local, execute this script (put data on Entity2 then execute currency conversion, then AGG both entities)

FIX("Budget", "FY13)

DATACOPY "Entity2_local TO "Entity2";

"Entity2" = "Entity2" / "currency_rate" ;

ENDFIX

 

FIX("Budget", "FY13, @list("Entity2_local","Entity2"))

AGG ("Product", "CC","Partner");

ENDFIX

 

=> If user is not on the local currency entity,only execute this part of the script :

FIX("Budget", "FY13, {VAR_ENTITY})

AGG ("Product", "CC","Partner");

ENDFIX

 

The thing is that including DATACOPY in IF() function is not permitted and I cannot FIX on entity dimension when I'm doing "Entity2" = "Entity2" / "currency_rate" ; because Essbase is saying that I'm trying to perform calculation with restricted member on entity.

I need a focused script base on the entity member selected by the user and cannot not perform full calculation of the entity dimension because this script should execute quickly as it is launched each time a user submit data.

I tried to used custom defined macro to replace part of the script depending of the entity member but macros don't support Calc commands, maybe custom defined functions could work but I don't know many things about this feature...

 

 

 

 

Thanks for your help!!

  • 1. Re: BSO Script : How to perform currency conversion only when needed (depending of the selected entity)
    Phulsebosch Pro
    Currently Being Moderated

    Hello Tipiak,

     

    Maybe you are in the position to change your design. .

    Currency

         Local

         EUR

     

    Entity

              Entity1 UDA: USD

              Entity2 UDA: GBP

              Entity3 UDA: EUR

              Entity4 UDA: EUR

     

    You load your data on Local.

    Then you datacopy (when Currency is sparse) and multiply the value with the Rate according to the Entity Currency type.

     

    But, if you want to proceed on your current design, then try this

    "Entity2" = "Entity2_local" / "currency_rate";

     

    Regards,

    Philip Hulsebosch

  • 2. Re: BSO Script : How to perform currency conversion only when needed (depending of the selected entity)
    Tipiak Newbie
    Currently Being Moderated

    Hello Philip,

     

    thanks for your answer.

     

    - I prefer to do not add a currency dimension since the application is already live and this issue only concern a small part of the users of this application.

     

    - About doing : "Entity2" = "Entity2_local" / "currency_rate"; This formula last too long, around 1 minute vs 2 sec when I'm doing:

    DATACOPY "Entity2_local" to "Entity2";

    "Entity2" = "Entity2" / "currency_rate";

     

    Is there is any way to variabilize a full part of a script, like :

    if VAR_ENTITY = Entity2_local, execute :

    FIX("Budget", "FY13)

    DATACOPY "Entity2_local TO "Entity2";

    "Entity2" = "Entity2" / "currency_rate" ;

    ENDFIX

     

    FIX("Budget", "FY13, @list("Entity2_local","Entity2"))

    AGG ("Product", "CC","Partner");

    ENDFIX

     

    else

     

    FIX("Budget", "FY13, {VAR_ENTITY})

    AGG ("Product", "CC","Partner");

    ENDFIX

     

     

     

    Thanks!!

  • 3. Re: BSO Script : How to perform currency conversion only when needed (depending of the selected entity)
    Phulsebosch Pro
    Currently Being Moderated

    Hello Tipiak,

    I see you have replaced the IF with a VAR and kept the rest of your code.

    Your question goes now to the optimization of the script with variables. I assume you can use substitution variables for Budget = CurrScen and FY13 = Planyear1

    If your list of Entities is a bit longer and you look to make this more generic from Entity2 and Entity2_local, then it will be a different story. You will need to find out if you can work with @CURRMBR and @CONCATENATE with @MEMBER.

     

    Regards,

    Philip Hulsebosch.

     


Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points