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:

`%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

B.R. Vadim

Great Vadim!

Thanks for sharing,

Regards,

Lucas

Be advised, in NW 10.0 SP7 the engine was switched from javascript to ABAP based and no longer supports Math.round or other javascript functions. See SAP note 1691570. It would be nice if the engine would now except round(val = XXX) but it only supports the ABAP math functions listed in the note.

Hi Kyle,

I’ve seen this note after your post. It looks extremely strange to spend number of years to write very simple arithmetic expression calculator on ABAP to improve precision and calculation speed (2-4 times) and again skip the rounding operator. The rounding is a must for any financial application and in case of BPC can’t be easily replicated by any BADI.

It’s also possible even for NW 10.0 SP7 to switch back to old Java Script calculation engine.

B.R. Vadim

Agree with the need for true rounding in the ABAP engine. I just wanted to let people know who may try Math.round in later SPs the issue, especially since ABAP is the default engine.

While, SP8 or later can be modified to use javascript engine instead of ABAP…I am not sure if I would advise clients to use the javascript engine instead of ABAP because it seems like it may eventually not be an option in later versions of BPC. Note the request for examples that work in JS and not in ABAP. How long are they going to maintain two engines for script logic for NW? Will new script logic commands (like FLD(), because I can hope they finally add this) be available in both engines.

I wonder if combining mod and an if condition you could replicate rounding inline with the ABAP engine, it would not be pretty.

You mean something like:

((%VALUE%*10**%N%+0.5) DIV 1)/10**%N%, where %N% is the number of decimal digits to round?

Not sure that I’ve seen operators “**” and “DIV” in the note as a supported operators, but I can’t test it now – BPC 10 is not available for me.

Anyway, the ABAP calculator is faster then Java Script, and I will also prefer to use this engine.

B.R. Vadim

I was thinking of something more simplistic and ugly.

%VALUE%%1 > 0.5 ? %VALUE% – %VALUE%%1 + 1 : %VALUE% – %VALUE%%1

so with numbers:

5.7 mod 1 = .7 —> true —> 5.7-.7+1 = 6

5.3 mod 1 = .3 —> false —> 5.3-.3 = 5

-5.7 mod 1 = .3 –> false –> -5.7-.3 = -6

-5.3 mod 1 = .7 –> false –> -5.7-.7+1 = -5

I know the mod looks strange for the negative values, but I believe this is how ABAP implements mod for negative numbers. Did a simple test and it works, but I have not tested completely. I think there may be an issue with numbers like -5.5 rounding, but I think that could be handled if needed.

Hi Kyle,

Yess, using only %,<,>,?,: it’s possible to implement rounding to integer. The correct formula for all cases will be:

%VALUE% > 0 ? (%VALUE%%1 < 0.5 ? %VALUE% – %VALUE%%1 : %VALUE% – %VALUE%%1 + 1) : (%VALUE%%1 > 0.5 ? %VALUE% – %VALUE%%1 + 1 : %VALUE% – %VALUE%%1)

%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.7 true -5.3-0.7+1=-5

-5.5 mod 1 = 0.5 false -5.5-0.5=-6

-5.7 mod 1 = 0.3 true -5.7-0.3=-6

But in order to implement rounding to the variable number of decimal digits we need power operator like 10**N. In the note there is nothing about support of power. Without this operator we have to hard-code the power of 10. For example, to round some value to 2 decimal digits the formula will be:

(%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 + 1 : %VALUE%*100 – (%VALUE%*100)%1))/100

Definitely not a pretty looking solution.

B.R. Vadim

Good stuff. I should clarify my comment on the round -5.5. Rounding to -6 or -5 is neither right or wrong. It just depends on the definition of rounding and it is more important that it is consistent. If the definition is round = floor(x+.5) then -5 is right. Of course, if the client wants round to even or round to odd then who knows.

I agree to round the 10, 100, .01 would need to be hard coded.

If we are talking about financial rounding (like ROUND in Excel) then ROUND(-5.5,0)=-6.

Hi Vadim,

A very informative post.

We have BPC 10,SP 07.So i used the round formula but it is giving dumps.

below log file

LGX:

*WHEN ACCOUNT*IS 18DIGIT_QTY_SPLIT*REC(EXPRESSION=(%VALUE% > 0 ? (%VALUE%%1 < 0.5 ? %VALUE% – %VALUE%%1 : %VALUE% – %VALUE%%1 + 1) : (%VALUE%%1 > 0.5 ? %VALUE% – %VALUE%%1 + 1 : %VALUE% – %VALUE%%1)))*ENDWHEN————————————————————————————————————————————-

LOG:

[INFO] GET_DIM_LIST(): I_APPL_ID=”STANDARD_COST”, #dimensions=14

ACCOUNT,ACTIVITY,BRAND,BUSINESS_DIV,CARRY_OVER,CAR_MODEL_YEAR,CATEGORY,COMPANY,EIM,MEASURES,MPS,NSC,RPTCURRENCY,TIME

#dim_memberset=0

REC :(%VALUE% > 0 ? (%VALUE%%1 < 0.5 ? %VALUE% – %VALUE%%1 : %VALUE% – %VALUE%%1 + 1) : (%VALUE%%1 > 0.5 ? %VALUE% – %VALUE%%1 + 1 : %VALUE% – %VALUE%%1))

CALCULATION BEGIN:

———— ABAP Code Generation Error:4 ————-

MESSAGEGZGIncomplete arithmetic expression: “)” missing at end of statement.

Line14 WordCOMPUTE

——– 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.

“(%VALUE% > 0 ? (%VALUE%%1 < 0.5 ? %VALUE% – %VALUE%%1 : %VALUE% – %VALUE%%1 + 1

data:

C1 type decfloat34 value ‘0.5’.

RET = ( P1.if RET > 0.RET = ( P1 MOD 1 < C1 ? P1 – P1 MOD 1.else.RET = P1 – P1 MOD 1 + 1 ) : ( P1 MOD 1 > C1 ? P1 – P1 MOD 1 + 1 : P1 – P1 MOD 1 ) ).endif.endmethod.

endclass.

——– Input formulas ———

(%VALUE% > 0 ? (%VALUE%%1 < 0.5 ? %VALUE% – %VALUE%%1 : %VALUE% – %VALUE%%1 + 1) : (%VALUE%%1 > 0.5 ? %VALUE% – %VALUE%%1 + 1 : %VALUE% – %VALUE%%1))

———— ABAP Code Generation Error Ends ————

UJK_EXECUTION_EXCEPTION:Runtime error Program generation error———————————————————————–I feel that system is unable to do proper IF and ELSE on the backend and throwing the error.However when i only did the “>0” part seprately without the “<0” ,it works fine.

Thanks

Aamer

Hi Aamer,

I have tested my code in the real BPC NW 10 environment in UJKT and found:

1. The proposed formula is working without errors like you have.

2. The results for negative numbers is incorrect.

I have adjusted the formula to get correct results for negative numbers and tested it:

Correct formula:

%VALUE% > 0 ? (%VALUE%%1 < 0.5 ? %VALUE% – %VALUE%%1 : %VALUE% – %VALUE%%1 + 1) : (%VALUE%%1 > -0.5 ? %VALUE% – %VALUE%%1 : %VALUE% – %VALUE%%1-1)

%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

Test script:

*WHEN INACCT //ACCOUNT Dimension in the test system

*IS *

*REC(EXPRESSION=5.3) //5.5,5.7,0,-5.3,-5.5,-5.7

*ENDWHEN

*WHEN INACCT

*IS *

*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))

*ENDWHEN

B.R. Vadim

Hi Vadim,

I have checked the same code in my client system which is on

BPC 10 SP07and giving the ABAP dump error.(no setting’s were done in IMG)However the same code works fine on

BPC 10 SP08 (internal demo server)Moreover,I have also noticed that it is still able to run Rounding functions of VB,which should not run if the system is on SP08 as per the above note..(no setting’s were done in IMG).

Thanks

Aamer

one more thing

for example i am entering 100 and then splitting it based on the percentages to the children like in allocation…

now the total should show the same value 100 but after rounding it can be 99 or 101….

if the value increases or decreses after the rounding is done can we fix it ?

thanks

Aamer

Hi Aamer,

I can’t test the code with “ABAP” rounding on SP07, on SP08 it works correctly. I will test later the java script rounding code on SP08 and see the results (I think you mean java script not VB in this sentence “still able to run Rounding functions of

VB“)Vadim

And yes, if you have number of values with many decimal digits with the total sum of 100 and you round them individually the sum after rounding can be 99 or 101 or even 98 and 102.

The traditional approach to have 100 after rounding is:

Ex.: You have N numbers before rounding Round(Sum(A1,..,An))=100.

You round only N-1 numbers, and for the last number you calculate it as An= Round(Sum(A1,..,An)) – Sum(Round(A1),…,Round(An-1))

B.R. Vadim

I have tested and corrected the article text for BPC NW 10 SP07, 08. It looks like your SP07 system has ABAP engine enabled and SP08 – has default Java engine.

Due to missing support of nested ternary operator the simple code like:

*REC(EXPRESSION=%VALUE%>5?(%VALUE%>10?1:2):3)

will generate ABAP error unable to parse second ternary operator %VALUE%>10?1:2

B.R. Vadim

Hi Vadim,

I want my results to be saved with two decimal points. How would I address with this formula?

We are on BPC 10.0 NW SP12

My *REC statement is

*WHEN HRPOSITION

*IS %HRPSTN_SS%,%HRPSTN_WW%

*REC(EXPRESSION=(([AUDITTRAIL].[INPUT],[CI].[CI6111000])+([AUDITTRAIL].[INPUT],[CI].[CI6121000])+([AUDITTRAIL].[CALC],[CI].[CI6111910],[TYPE].[REQUEST])+([AUDITTRAIL].[CALC],[CI].[CI6111920],[TYPE].[REQUEST]))<LOOKUP(SSM) ? (([AUDITTRAIL].[INPUT],[CI].[CI6111000])+([AUDITTRAIL].[INPUT],[CI].[CI6121000])+([AUDITTRAIL].[CALC],[CI].[CI6111910],[TYPE].[REQUEST])+([AUDITTRAIL].[CALC],[CI].[CI6111920],[TYPE].[REQUEST]))*LOOKUP(SSC) : LOOKUP(SSM)*LOOKUP(SSC), AUDITTRAIL=CALC,CI=CI6143000,TYPE=REQUEST)

*ENDWHEN

Please, open a new discussion, hard to answer here!

Vadim

Great Post Vadim.

I saw a case where the customer uses the following code.

*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)

If the input value is 146.575, (%VALUE%*100)%1 = 0.50. But the system will consider 0.50 < 0.5 and return true. Not sure if it’s because of the float type.

To overcome it, we change the code to (%VALUE%*1000)%10 < 5 so no float is generated.