Using Javascript to enhance Script Logic

[Former Member]

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:



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

 




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



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

 



 

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.

 



I hope it helps.

Samuel Matioli