Hi All,
In our BPC system we have to do a lot of calculations where rounding of values is required. We have to develop custom WRITE BACK BADI to do rounding based on the property of the Account dimension member (custom property ROUNDING with the number of digits to round). The disadvantage of this method is that if you want to round some preliminary result in the expression you have to calculate the expression in steps and after each step store the result in some member with the required rounding property.
Now we found another way with real inline rounding. The idea is simple: when BPC executes *REC at the end of the line processing after all substitutions where done there is a text variable containing the expression from *REC with all dimension members replaced by values from cube. This text expression is sent to Java Script engine for evaluation and the calculated by Java Script engine result is stored in the cube.
We checked the possibility to include Java Script method directly in the script text and success - the trick is working! Example:
*XDIM_MEMBERSET ACCOUNT = 2020202020607
*WHEN ACCOUNT
*IS *
*REC(EXPRESSION=Math.round([ACCOUNT].[2020202020607]*100)/100)
*ENDWHEN
Some additional notes:
1. Java Script is case sensitive: Math.round <> math.round ...
2. Only methods like MethodName(SingleParameter) will pass validation.
After some additional tests we found that method .toFixed(n), where n - number of digits after decimal point, is also working fine:
*XDIM_MEMBERSET ACCOUNT = 2020202020607
*WHEN ACCOUNT
*IS *
*REC(EXPRESSION=([ACCOUNT].[2020202020607]).toFixed(2))
*ENDWHEN
When .toFixed(n) method is used, the result is not a number but a string (in the example above there is no difference). To use the result as a number it's better to multiply it by 1.
*REC(EXPRESSION=1*([ACCOUNT].[2020202020607]).toFixed(2)+1*([ACCOUNT].[2020202020608]).toFixed(2))
Without multiplication by 1 the line above will result in the text string like 123.45136.78 (123.45 concatenated with 136.78) and the script will terminate with ABAP Dump (trying to write to signed data the value 123.45136.78)
It was found that toFixed function can perform incorrect rounding due to JavaScript IEEE 754 standard. Use Math.round!
For BPC NW 10 SP7 and higher with ABAP calculation engine the formula for inline rounding CURRENTLY CAN'T BE IMPLEMENTED because ABAP engine does not support nested ternary operator like A>B ? (C>D ? E : F) : (G>K ? L : M) - tested on SP08!
!!! In the notes 1691570, 1748676 it was written that ABAP can be used as a default calculation engine since SP07. But Java engine is used by default. In order to enforce usage of ABAP engine you have to follow instructions in the note 1748676:
1. Transaction SPRO
2. Goto 'Planning and Consolidation' -> 'Configuration Parameters' -> 'Set Global Parameters'
3. Change the value 'K2_CALC_ENGINE' to 'ABAP'. Create it if does not exist.
If you want to switch engine back to Java Script change the value from 'ABAP' to 'JS'
After creation of 'K2_CALC_ENGINE' with 'ABAP if you try to execute Java instructions you will get error in UJKT:
------------ ABAP Code Generation Error:4 -------------
MESSAGEG18"TOFIXED(" expected, not "TOFIXED ("
Line12 WordTOFIXED
-------- Code ---------
program.
class main definition.
public section.
methods METH1 importing
P1 type decfloat34
exporting RET type decfloat34
raising CX_SY_ZERODIVIDE.
endclass.
class main implementation.
method METH1.
"([INACCT].[2020202020704]).toFixed(1)
RET = ( P1 ) .toFixed ( 1 ).
endmethod.
endclass.
-------- Input formulas ---------
([INACCT].[2020202020704]).toFixed(1)
------------ ABAP Code Generation Error Ends ------------
UJK_EXECUTION_EXCEPTION:Runtime error Program generation error
The following code will properly run with Java calculation engine and WILL run with ABAP engine when it will support nested ternary operator:
*REC(EXPRESSION=(%VALUE% > 0 ? ((%VALUE%*100)%1 < 0.5 ? %VALUE%*100 - (%VALUE%*100)%1 : %VALUE%*100 - (%VALUE%*100)%1 + 1) : ((%VALUE%*100)%1 > -0.5 ? %VALUE%*100 - (%VALUE%*100)%1 : %VALUE%*100 - (%VALUE%*100)%1-1))/100)
or for simple INT:
*REC(EXPRESSION=%VALUE% > 0 ? (%VALUE%%1 < 0.5 ? %VALUE% - %VALUE%%1 : %VALUE% - %VALUE%%1 + 1) : (%VALUE%%1 > -0.5 ? %VALUE% - %VALUE%%1 : %VALUE% - %VALUE%%1-1))
Explanation:
%VALUE% > 0
5.3 mod 1 = 0.3 true 5.3-0.3=5
5.5 mod 1 = 0.5 false 5.5-0.5+1=6
5.7 mod 1 = 0.7 false 5.7-0.7+1=6
%VALUE% = 0
0 mod 1 = 0 true 0-0=0 (0)
%VALUE% < 0
-5.3 mod 1 = -0.3 true -5.3+0.3=-5
-5.5 mod 1 = -0.5 false -5.5+0.5+1=-6
-5.7 mod 1 = -0.7 false -5.7+0.7+1=-6
Updated: Now ABAP engine support AND "&&" and OR "||" conditions in the conditional part of ternary operator:
https://launchpad.support.sap.com/#/notes/2228643
And it's possible to perform inline rounding using the following code (INT sample):
*REC(EXPRESSION=(%VALUE% > 0 && %VALUE%%1 < 0.5) || (%VALUE% <= 0 && %VALUE%%1 > -0.5) ? %VALUE% - %VALUE%%1 : 0)
*REC(EXPRESSION=%VALUE% > 0 && %VALUE%%1 >= 0.5 ? %VALUE% - %VALUE%%1 + 1 : 0)
*REC(EXPRESSION=%VALUE% <= 0 && %VALUE%%1 <= -0.5 ? %VALUE% - %VALUE%%1 - 1 : 0)
B.R. Vadim
