Rounding off the value with only logic script

[Former Member]

Hi BPC experts,

I would like to round off the values to 2 decimals when running a currency translation or consolidation (which also requires a currency translation).

My logic script is the following :

for currency translation :

for consolidation (the screenshot only displays the currency translation part) :

These logic scripts work as for having only 2 decimals but it does not always round off the values in the right way :

For instance, I have 723337,19207 euros without the rounding process. Once it is rounded, it shows 723337,20 euros.

Normally, it should round to 723337,19 euros.

A second example is 164744,739327 euros but when rounded, it displays 164744,73 euros. Normally, it should display 164744,74 euros.

Therefore, is there something to add to this logic script ? Or is there another solution for rounding off the values ?

I also tried the BADI in the How to Guide but it did not work at all. I ran /CPMB/DEFAULT_FORMULAS and linked this package to the logic script defined in the guide but there was 0 record.

Please, help me out.

Thank in you in advance,

Dat

[former_member186338 · Accepted solution]

Hi Dat,

Please read

In my tests the rounding was fine.

Please test it in UJKT for some fixed value!

By the way looks like 723337,19207 is some parent node, and the rounding is done for base members...

Test for base member result!

Vadim

P.S. We are using write back badi with the ROUND property in account dimension - works fine with ABAP calculation engine!

[Former Member · Accepted solution]

Hi Vadim,

Thank you for your reply.

After having checked in details about base-level accounts, the only accounts that do not always round properly are the technical accounts that I used in Destination all accounts in Elimination and Adjustements business rule. My example with 723337,19207 is actually a technical account that eliminates payables/receivables.

Parent-level members are fine as long as they don't have these technical accounts.

Therefore, if I understand it well, the logic script currently used follows this process :

Local Currency --> Currency Translation with 2 decimals --> Consolidation.

Is it possible that BPC does the following process :

Local Currency --> Currency Translation with no limitation of decimals --> Consolidation --> Rounding to 2 decimals ?

In this case, the write back BADI could do that ?

Dat

[former_member186338 · Accepted solution]

Hi Dat,

Please don't mix everything!

First of all ensure that rounding is working correctly without any additional calculations (Currency Translation, Consolidation).

Put 723337,19207 into some intersection of base members using input form and then test script with rounding only for this intersection only!

Test script like:

//Define scope of the intersection here with number of *XDIM_MEMBERSET...

//First WHEN/ENDWHEN to show value before rounding

*WHEN ACCOUNT

*IS * //have to be already scoped

*REC(EXPRESSION=%VALUE%) //723337,19207 has to be shown

*ENDWHEN

//Second WHEN/ENDWHEN to show value after rounding

*WHEN ACCOUNT

*IS * //have to be already scoped

*REC(EXPRESSION=1*(%VALUE%).toFixed(2)) //723337,19 has to be shown

*ENDWHEN

Look on the results in UJKT - with EXECUTE!

Vadim

P.S.

"Therefore, if I understand it well, the logic script currently used follows this process :

Local Currency --> Currency Translation with 2 decimals --> Consolidation." - incorrect

You have rounding after both: after Currency Translation and after Consolidation

Please show you full consolidation script and not as a picture!

[Former Member · Accepted solution]

Hi Vadim,

I applied your method with input form and UJKT transaction.

1) I used an input form and put the amount in a empty base-level account. the *XDIM_MEMBERSET is set to scope this account.

2) Then, I separated the two logic scripts :

a) I firstly applied logic script with *REC(EXPRESSION=%VALUE%).

This leads to 723337,19 and not 723337,19207 in UJKT. The BADI from the How To Guide is still active although it is not supposed to work. Does this BADI only apply to data coming from input form ? Is it why it only displays 2 decimals ?

b) Secondly, the rounding logic script gives the expected result (with the right rounding). It does change the original amount to 2 decimals. the first logic script displays 2 decimals but still keeps the 5 original decimals when I refresh data.

3) Is there any way to copy-paste in SAP forums ?

Here is my logic script with rounding for consolidation :

*RUN_PROGRAM CURR_CONVERSION

CATEGORY = %CATEGORY_SET%

GROUP = %SCOPE_SET%

TID_RA=%TIME_SET%

RATEENTITY=GLOBAL

INCREMENTAL MODE=

*ENDRUN_PROGRAM

*RUN_PROGRAM CONSOLIDATION

CATEGORY=%CATEGORY_SET%

GROUP=%SCOPE_SET%

TID_RA=%TIME_SET%

*ENDRUN_PROGRAM

*WHEN RPTCURRENCY

*IS EUR

*REC(EXPRESSION=(1*(%VALUE%).toFixed(2)))

*ENDWHEN

Dat

[former_member186338 · Accepted solution]

Sorry, but hope you have nothing in default.lgf?

[Former Member · Accepted solution]

No, there is nothing in default.lgf

[former_member186338 · Accepted solution]

I have made some tests!

"a) I firstly applied logic script with *REC(EXPRESSION=%VALUE%).

This leads to 723337,19 and not 723337,19207 in UJKT."

Actually, the figures are rounded in the UJKT log In the cell you will have 723337,19207!

Test scenario:

Initial data (BPC 7.5 for Javascript calculation engine):

Script log in UJKT (without rounding):

LGX:

*XDIM_MEMBERSET ADV=ADVDUMMY

*XDIM_MEMBERSET BE=BE1000

*XDIM_MEMBERSET PERIODS=2005.OCT

*XDIM_MEMBERSET PLANTYPE=ACTUAL

*XDIM_MEMBERSET TITLES=1110

*WHEN ADV

*IS *

*REC(EXPRESSION=%VALUE%)

*ENDWHEN

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

LOG BEGIN TIME:2015-10-16 15:49:00

FILE:\ROOT\WEBFOLDERS\SIM170215 \ADMINAPP\ADVSALES\TEST.LGF

USER:IMAD\V.KALININ

APPSET:SIM170215

APPLICATION:ADVSALES

[INFO] GET_DIM_LIST(): I_APPL_ID="ADVSALES", #dimensions=6

ADV,BE,MEASURES,PERIODS,PLANTYPE,TITLES

#dim_memberset=5

ADV:ADVDUMMY,1 in total.

BE:BE1000,1 in total.

PERIODS:2005.OCT,1 in total.

PLANTYPE:ACTUAL,1 in total.

TITLES:1110,1 in total.

REC :%VALUE%

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 141.00 ms. 1  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

CALCULATION TIME IN TOTAL :0.00 ms.

1  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 1  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ADV    BE    PERIODS    PLANTYPE    TITLES    SIGNEDDATA

ADVDUMMY    BE1000    2005.OCT    ACTUAL    1110    723337.19 <- incorrect, on screen rounding!

1  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :156.00  ms.

SCRIPT RUNNING TIME IN TOTAL:0.41 s.

LOG END TIME:2015-10-16 15:49:01

Result:

Script with rounding:

LGX:

*XDIM_MEMBERSET ADV=ADVDUMMY

*XDIM_MEMBERSET BE=BE1000

*XDIM_MEMBERSET PERIODS=2005.OCT

*XDIM_MEMBERSET PLANTYPE=ACTUAL

*XDIM_MEMBERSET TITLES=1110

*WHEN ADV

*IS *

*REC(EXPRESSION=1*(%VALUE%).toFixed(2))

*ENDWHEN

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

LOG BEGIN TIME:2015-10-16 15:55:44

FILE:\ROOT\WEBFOLDERS\SIM170215 \ADMINAPP\ADVSALES\TEST.LGF

USER:IMAD\V.KALININ

APPSET:SIM170215

APPLICATION:ADVSALES

[INFO] GET_DIM_LIST(): I_APPL_ID="ADVSALES", #dimensions=6

ADV,BE,MEASURES,PERIODS,PLANTYPE,TITLES

#dim_memberset=5

ADV:ADVDUMMY,1 in total.

BE:BE1000,1 in total.

PERIODS:2005.OCT,1 in total.

PLANTYPE:ACTUAL,1 in total.

TITLES:1110,1 in total.

REC :1*(%VALUE%).toFixed(2)

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 141.00 ms. 1  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

CALCULATION TIME IN TOTAL :15.00 ms.

1  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 1  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ADV    BE    PERIODS    PLANTYPE    TITLES    SIGNEDDATA

ADVDUMMY    BE1000    2005.OCT    ACTUAL    1110    723337.19

1  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :156.00  ms.

SCRIPT RUNNING TIME IN TOTAL:0.42 s.

LOG END TIME:2015-10-16 15:55:44

Result:

Vadim

[former_member186338 · Accepted solution]

P.S. Same results in BPC 10 with javascript calculation engine!

[Former Member · Accepted solution]

That's exactly what I had ! It is relevant with what I wrote in 2) b). Glad to see that it was actually correct

Then, I still don't understand why 90% of the rounding are correct and 10% which are not...

Furthermore I found a non-technical base-level account where the rounding is not correct either.

In the Cube :

In a Report :

Column C is data that are not rounded.

Column F is the rounded data.

Column G is the difference between C and F.

Is my Consolidation logic script correct in my previous post ?

Dat

[former_member186338 · Accepted solution]

"Furthermore I found a non-technical base-level account where the rounding is not correct either" - to check rounding ALL dimensions have to be on the BASE level, not only account

You will not find incorrect rounding for the base intersections!

Vadim

May be your consolidation script is correct it depends on your requirements and other consolidation settings...

[Former Member · Accepted solution]

Hi Vadim,

Sorry for the late reply ! I did not receive a notification

So, I checked as you stated : check base-level for all dimensions. You got it right it seems that rounding will always have a slight difference. For instance, 723.337,20 :

It is not possible to get 723 337,19.

I still have a question though : the rounding in the picture below seems a bit strange with 142 217,495 :

3314 and 138 903,5 are completely right but the rounding for 142 217,495 should be 142 217,50. Is it because the 3rd decimal is 5 and the 2 first decimals are 49 so it won't round if off to 50 ?

Dat

[former_member186338 · Accepted solution]

It's an issue with JavaScript toFixed(n)

Use: Math.round(%VALUE% * 100) / 100

[former_member186338 · Accepted solution]

I have corrected:

[Former Member · Accepted solution]

Hi Vadim,

I checked Math.round with UJKT with the following script :

*XDIM_MEMBERSET ACCOUNT = DUMMY

*WHEN RPTCURRENCY

*IS EUR

*REC(EXPRESSION=Math.round(%VALUE%)*100/100)

*ENDWHEN

And I put 142 217,495 in input form for DUMMY. The result is the one I expect : 142 217,50. However, it does not work when I run my consolidation package. The result is 142 217,49.

Therefore, I used the following script for currency translation and tested on UJKT :

*XDIM_MEMBERSET ACCOUNT = DUMMY

*RUN_PROGRAM CURR_CONVERSION

CATEGORY=%CATEGORY_SET%

TID_RA=%TIME_SET%

RATEENTITY=GLOBAL

INCREMENTAL_MODE=

*ENDRUN_PROGRAM

*WHEN RPTCURRENCY

*IS EUR

*REC(EXPRESSION=Math.round(%VALUE%*100/100)

*ENDWHEN

The record shown in UJKT is unfortunately 142 217,49. The local currency is 174 650 and the rate is 0,8143.

Is there something I have forgotten ?

Dat

[former_member186338 · Accepted solution]

Incorrect syntax:

Not:

*REC(EXPRESSION=Math.round(%VALUE%)*100/100)

But:

*REC(EXPRESSION=Math.round(%VALUE%*100)/100)

Vadim

[former_member186338 · Accepted solution]

Real test:

Value before rounding:

Script:

*XDIM_MEMBERSET PLANTYPE=ACTUAL

*XDIM_MEMBERSET PERIODS=2006.JAN

*XDIM_MEMBERSET CORPDIR=DIRECT

*XDIM_MEMBERSET INACCT=INDUMMY

*XDIM_MEMBERSET BE=BE1000

*XDIM_MEMBERSET TITLES=1110

*WHEN INACCT

*IS *

*REC(EXPRESSION=Math.round(%VALUE%*100)/100)

*ENDWHEN

Value after script run:

Vadim

[Former Member · Accepted solution]

My bad for the syntax.

I corrected it and I tried it again with UJKT by using the two methods I posted just before :

1) just using input form in EUR and put 142 217,495 in DUMMY account.

Result : 142 217,50 as expected. This is what you showed me.

2)  using input form with 174 650 as LC, rate as 0,8143 and by running Currency translation with the logic script detailed in my previous post.

Result : 142 217,49 which is unfortunately not the right one.

Is there anything wrong ?

Dat

[former_member186338 · Accepted solution]

You are doing something wrong, because when you first perform Currency conversion the value 142217,495 is written to the cube. Then it's read from the cube and rounded!

Launch first Currency conversion without rounding in UJKT and check report in Excel! Don't look on the value in UJKT log - it will be always rounded to 2 digits.. May be Currency conversion is generating something like: 142217,4949999

Then launch rounding in UJKT and check report!

Vadim

[former_member186338 · Accepted solution]

Tested myself - everything is correctly calculated!

[Former Member · Accepted solution]

I wish I could easily find what went wrong...

Here are 2 pictures of this amount :

1) Report WITHOUT rounding with local currency being translated :

2) BW WITH rounding but we can also see 142 217,495 with 7 digits :

I am still looking for what kind of mistakes is influencing this figure.

Is there something to do with global parameters SPRO ? There is nothing defined in those parameters except RFC_DESTINATION : BPC. I don't think this is related but we never know...

Dat

[former_member186338 · Accepted solution]

Sorry - do you read my messages? Can you perform EXACTLY what I have written?

1. Report after currency conversion without rounding - launch only currency conversion script in UJKT

2. Script text for rounding - not sure it's correct, sorry!

3. Report after you launch only rounding script (without currency conversion) - launch it in UJKT

Vadim

[Former Member · Accepted solution]

I did what you told me to do but I thought a picture of BW would be more relevant.

So let me restate what happened (and I did again the whole process to be sure I am not missing anything)  :

1. I put the currency conversion logic script WITHOUT rounding in UJKT and execute it. I made a report to check the amount (see first picture in previous post).

2. What do you mean ?

3. Next attempt is to apply rounding with UJKT (no currency conversion, ONLY rounding script). Here is the report :

Dat

[former_member186338 · Accepted solution]

Sorry, but do you want to tell me that there is a difference between manually entered 142217,495 and the same value as a result of currency conversion ? Your post:

2. What do you mean ? - I mean rounding script text you execute in  UJKT! Show it! I am not sure it's correct! And better a screenshot of UJKT with this script!

As I already posted I have performed all the tests and found no issues. Currency conversion and rounding are performed correctly...

[Former Member · Accepted solution]

Vadim Kalinin wrote:

Sorry, but do you want to tell me that there is a difference between manually entered 142217,495 and the same value as a result of currency conversion  ? Your post: Re: Rounding off the value with only logic script

Yes this is actually the issue. I guess I was not clear enough but this is it !

2. Here are what I used for UJKT :

at first, execution of currency conversion without rounding :

secondly, rounding execution :

I know you told me that everything is fine. As for this case, only this account has a bad rounding. The others are all correct !

Dat

Message was edited by: Dat Truong

[former_member186338 · Accepted solution]

In the rounding scripts please limit scope to the single base member intersection using *XDIM_MEMBERSET...

Like (for all dimensions in the model):

*XDIM_MEMBERSET ACCOUNT=CALU70600000

*XDIM_MEMBERSET TIME=2014.12

*XDIM_MEMBERSET CATEGORY=ACTUAL

*XDIM_MEMBERSET FLOW=...

...

I want to be 100% sure that you are working with a single intersection!

[Former Member]

Hi Dat,

I have a question for you. If you use the rounding, how do you plan to deal with rounding differences that occur on currency conversion and consolidation?

Thanks,

Arnold

[Former Member]

Hi Arnold,

The client used another consolidation tool in previous year. This tool has also rounded the amounts.

Therefore, the purpose is to make BPC reproduce exactly the same figures.

Dat

[former_member186338]

I am not sure that you will be able to reproduce exactly the same figures... BPC is limited to 7 digits after decimal point...

[Former Member]

Hi Dat,

how did the old tool deal with rounding differences then? I can easily construct a balance sheet that balances in local currency but shows a difference after being converted and rounded.

Thanks,

Arnold

[former_member186338]

Hi Arnold,

The main question is - to what number of decimals the old system was rounding figures when storing in the database? If this number is not 7 then you will not be able to have exactly same results in the reports... But I don't thing it's a big difference

Vadim

[Former Member]

Hi Vadim,

it's not big, but an auditor won't like a balance sheet that is even out by 1 pence. And it can easily be above 50 pence in which case you would see it on a EUR level (replace with any other currency).

BR,

Arnold