Skip to Content

Using Javascript to enhance Script Logic

Applies to SAP Business Planning & Consolidation 7.5 NW and SAP Business Planning & Consolidation 10 NW

Sometimes, when we are developing some script logic, we face a lot of limitations.

Suppose you need to calculate a standart deviation. The formula contains a square root. But how calculate the square root of some number with Script Logic? The answer is not well documented in the BPC manuals: JavaScript.

The engine that process script logic uses javascript classes to solve the EXPRESSION part of a REC sentence.

For this example, I created 4 accounts just to test this feature.

In account VL001, I inserted the value that I want to use in calculation:

/wp-content/uploads/2013/01/screen1_170851.png

After saving data, I executed the following Script Logic:

*XDIM_MEMBERSET ACCOUNT = VL001

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%

*XDIM_MEMBERSET TIME = BAS( %TIME_SET% )

*WHEN ACCOUNT

*IS VL001

*REC(EXPRESSION=javascript:Math.sqrt(%VALUE%), ACCOUNT=”VL004″)

*ENDWHEN

/wp-content/uploads/2013/01/screen2_170864.png

After the refresh, the account VL004 now have the value 10, which is the square root of 100:

/wp-content/uploads/2013/01/screen3_170865.png

Extending this example, we can use other mathematical functions of java script:

*XDIM_MEMBERSET ACCOUNT = VL001

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%

*XDIM_MEMBERSET TIME = BAS( %TIME_SET% )

*WHEN ACCOUNT

*IS VL001

*REC(EXPRESSION=javascript:Math.abs(%VALUE%), ACCOUNT=”VL002″)

*REC(ACCOUNT=”VL005″, EXPRESSION=javascript:Math.ceil(%VALUE% ) )

*REC(ACCOUNT=”VL006″, EXPRESSION=javascript:Math.floor(%VALUE% ) )

*REC(ACCOUNT=”VL007″, EXPRESSION=javascript:Math.round(%VALUE% ) )

*REC(ACCOUNT=”VL008″, EXPRESSION=javascript:Math.random( ) )

*ENDWHEN

/wp-content/uploads/2013/01/screen5_170866.png


But there is a problem. If the function requires more than one parameter, the script returns a error. For example, it is not possible to use the function Math.pow, because it requires two parameters.

This happens when engine that processes script logic breaks the *REC expression in commas, and then it breaks the expression.

/wp-content/uploads/2013/01/screen6_170870.png

I hope it helps.

Samuel Matioli

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. John Leggio

    Hi Samuel,

    Interesting article. As of BPC 10 SP07 the calculation engine was switched from JavaScript to ABAP (see note 1691570). You can still still use the JavaScript engine in later versions if needed (see note 1748676)

    Thanks,

    John

    (0) 
    1. Samuel Henrique Matioli Post author

      Thank you,

      It’s true.

      I run my test on 7.5 SP12 and 10.0 SP06.

      The SP07 has a lot of improvements in script logic engine – which in fact needs all of that improvement.

      I did not read all the notes, but BPC would can still use the javascript engine when its would be explicitly declared, like this examples. So many customers does not need to change their scripts.

      (0) 
    2. Prasad P

      Hi John

      thanks for sharing the OSS notes.

      we have one script logic for currency conversion. Where we are using math.round function. Currently in production we have BPC10 SP09. We are migrating to BPC10 sp16.

      during UAT we faced currency conversion issues. I have checked some blogs and got to know that math.round is not valid in ABAP engine. and also if we switch back it will cause performane issues.

      could you please advise, what can we replace for the below lines. as its urgent bit for golive.

      *WHEN CURRENCY

      *IS LC

      *WHEN COST_CTR.CURRENCY

      *IS CAD

      *REC(EXPRESSION=%VALUE%,CURRENCY=CAD)

      *IS USD

      *REC(EXPRESSION=Math.round(%VALUE%/LOOKUP(L_RATE_USD)*100)/100,CURRENCY=CAD)

      *IS GBP

      *REC(EXPRESSION=Math.round(%VALUE%/LOOKUP(L_RATE_GBP)*100)/100,CURRENCY=CAD)

      (0) 

Leave a Reply