Skip to Content
Author's profile photo Former Member

Using Javascript to enhance Script Logic

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

Assigned tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Arun Kumar Suryanarayana Rao
      Arun Kumar Suryanarayana Rao

      Hi Samuel,

      Thanks for sharing the information.

      Arun

      Author's profile photo John Leggio
      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

      Author's profile photo Former Member
      Former Member
      Blog 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.

      Author's profile photo Prasad P
      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)

      Author's profile photo Former Member
      Former Member

      Hi Samuel,

      Thanks so much for the information.

      Best Regards,

      Karsten