Question Re: Rounding and Offsetting to different Datasource/ AuditID. in Financial Management Q&A

Rounding and Offsetting to different Datasource/ AuditID.

Eyal_Feiler — Wed, 09 May 2018 14:53:07 GMT

Hi Experts

Is there a way to calculate the difference between the actual vs. round amounts and book to a different AUDITID/Datasource?

Goal is to retain original amounts and only book a rounding amount by account.

For example: Simple AUDITID hierarchy =

TOT_AUDITID

ADJ_RND

TOT_INPUT

INPUT2

INPUT

The sum of TOT_INPUT= 123.45. Rounded (0) should be 123. So we record the -.45 to ADJ_RND to result in 123.

I then wan to record an .45 to another account.

Is the best way to address via the BADI writeback?

Or can this be done with script logic?

Saw the custom BADI for rounding values but don’t think it’s relevant for this business case.

Seems like it records to the same value. Also can’t find the transport that it references.

Further saw the links below but don’t understand the math.round function referenced.

https://answers.sap.com/questions/75279/mathround-for-rounding-in-rec-experssion-question.html

https://blogs.sap.com/2012/10/08/inline-rounding-in-rec-expression/

We are using BPC NW 10.1 SP11 with BW 7.4 SP16

Thanks in advance.

Eyal

Re: Rounding and Offsetting to different Datasource/ AuditID.

former_member186338 — Wed, 09 May 2018 19:33:17 GMT

Please read my blog: https://blogs.sap.com/2012/10/08/inline-rounding-in-rec-expression/

In the resent BPC versions it's possible to loop parent members in WHEN/ENDWHEN script logic.

Re: Rounding and Offsetting to different Datasource/ AuditID.

Eyal_Feiler — Thu, 17 May 2018 18:09:40 GMT

Thanks Vadim,

I created a script with REC(EXPRESSION=%VALUE%%1,[DIMENSIONS...]) and am in the process of building and testing. Looks like what I needed.

Regards,

Eyal

Re: Rounding and Offsetting to different Datasource/ AuditID.

former_member186338 — Thu, 17 May 2018 18:37:14 GMT

I am unable to understand syntax of script logic in your post... what do you mean???

Re: Rounding and Offsetting to different Datasource/ AuditID.

Eyal_Feiler — Tue, 19 Jun 2018 15:36:12 GMT

Hi Vadim,

Thanks for the helpful link.

I tried using the logic below to obtain the desired results - round down if <.5 or calc difference from 1 if >=.5

Option 1: If .4<.5 (yes) than .4

Option 2 if .7 <.5 (no) than 1-.7=.3

*REC(EXPRESSION=(%VALUE%%1 < 0.5 ? %VALUE% : -1+ %VALUE%%1,AUDITID=AD_RND1)

I receive an error in UJKT and BPC - unknown or unimplemented keyword *REC(Expression(%VALUE%%1 < 0.5 ? %VALUE% : -1+ %VALUE%%1,AUDITID=AD_RND1).

Using BPC 10.1 NW SP11. I do have an old global setting for K2_CALC_ENGINE=JS, if that is still relevant.

Is the conditional syntax correct?

Regards,

Eyal

Re: Rounding and Offsetting to different Datasource/ AuditID.

former_member186338 — Tue, 19 Jun 2018 17:17:41 GMT

Not sure that %1 is supported. For JS (JavaScript) calculation engine you can use rounding functions like in my blog!

Re: Rounding and Offsetting to different Datasource/ AuditID.

Eyal_Feiler — Wed, 20 Jun 2018 18:17:50 GMT

Hi Vadim,

Thanks for your response.

The script logic successfully executes a one part “if” such as below.

*REC(EXPRESSION=%VALUE%%1 < 0.5 ? %VALUE% :-1+ %VALUE%%1,AUDITID=AD_RND1)

However, I am trying to use 2 conditions to calculate the decimals in column 2 below. Goal is to book the rounding amount in another AuditID.

123.45 -.45

(123.45) .45

123.75 .25

(123.75) -.25

Here is the testing script. The DM is returning an error

RUN_LOGIC:LINE 30 syntax error: " missing : in conditional expression"

line 30 is the *END row – used it to view the script more easily.

Each piece works so something is not right with the syntax. Any ideas?

*XDIM_MEMBERSET ENTITY = 1010

*XDIM_MEMBERSET CURRENCY = USD

*XDIM_MEMBERSET SCOPE = S_NONE

*XDIM_MEMBERSET CATEGORY = ACTUAL

*XDIM_MEMBERSET AUDITID = AD_RND1

*WHEN SCOPE

*IS S_NONE // replace

*BEGIN

*REC(EXPRESSION= %VALUE% >0 ?

%VALUE%%1 < 0.5 ? -%VALUE%%1,AUDITID=AD_RND1 : 1- %VALUE%%1,AUDITID=AD_RND1:

%VALUE%%1 < 0.5 ? -%VALUE%%1,AUDITID=AD_RND1 : -1+ %VALUE%%1,AUDITID=AD_RND1)

*END

Thanks

Eyal

Re: Rounding and Offsetting to different Datasource/ AuditID.

former_member186338 — Wed, 20 Jun 2018 18:37:00 GMT

Sorry, but %1 is related to ABAP calculation engone, not to Javascript. For Javascript use Javascript function!

Use Math.round

Re: Rounding and Offsetting to different Datasource/ AuditID.

Eyal_Feiler — Wed, 15 Aug 2018 15:12:29 GMT

Hi Vadim,

I did more testing logic will work but now there’s a syntax problem with trying to define the AUDITID to record the result.

If I want to book the rounding to a separate AUDITID then a simple script like (1) below will work but if I place the AUDITID on both sides of the condition (2) then I get an error “missing : in conditional expression”.

(1) *REC(EXPRESSION=%VALUE%%1 < 0.5 ? -%VALUE%%1 : 1- %VALUE%%1,AUDITID=AD_RND1)

(2) *REC(EXPRESSION=%VALUE%%1 < 0.5 ? -%VALUE%%1,AUDITID=AD_RND1 : 1- %VALUE%%1,AUDITID=AD_RND1)

Result: “missing : in conditional expression”.

I tried using the example you reference below and adapted it to my scenario – script (3).

https://archive.sap.com/discussions/message/16921487#16921487

(3)

*REC(EXPRESSION=(%VALUE% >0)?((%VALUE%%1 < 0.5 )? %VALUE%%1 : 1- %VALUE%%1):0)

*REC(EXPRESSION=(%VALUE%<0)?((%VALUE%%1 < 0.5) ? %VALUE%%1 : -1+ %VALUE%%1):0)

This worked but of course it updates the same AUDITID.

So in (4) and other variations, I tested with adding the AUDITID=AD_RND1 in all and then some of the places.

Each time I get an error including the one below. Based on (1) the logic would be that you can insert an AUDITID only in the second condition which I tried but didn’t work…

(4)

*REC(EXPRESSION=(%VALUE% >0)?((%VALUE%%1 < 0.5 )? %VALUE%%1,AUDITID=AD_RND1 : 1- %VALUE%%1,AUDITID=AD_RND1):0)
*REC(EXPRESSION=(%VALUE%<0)?((%VALUE%%1 < 0.5) ? %VALUE%%1,AUDITID=AD_RND1 : -1+ %VALUE%%1):0)

WITHOUT BEGIN END

Result: missing : in conditional expression

(5)

*REC(EXPRESSION=(%VALUE% >0)?((%VALUE%%1 < 0.5 )? %VALUE%%1 : 1- %VALUE%%1,AUDITID=AD_RND1):0)
*REC(EXPRESSION=(%VALUE%<0)?((%VALUE%%1 < 0.5) ? %VALUE%%1: -1+ %VALUE%%1,AUDITID=AD_RND1):0)

Result:

UJK_VALIDATION_EXCEPTION:Member "AD_RND1):0" not exist

What do you suggest? Is it possible to select AUDITID? (Also tried with”” or ‘’ doesn’t work)

I can do this differently but each time I need to use a condition at some point that specifies more/less than the amount to book to a separate AUDITID.

Thanks in advance

Eyal

Re: Rounding and Offsetting to different Datasource/ AuditID.

former_member186338 — Wed, 15 Aug 2018 20:54:17 GMT

Sorry, but you are using absolutely incorrect syntax of ternary operator. I have provided hundred of samples in my answers, is it so hard to compare? Destination dimension can’t be included inside ternary operator!!!!

Re: Rounding and Offsetting to different Datasource/ AuditID.

Eyal_Feiler — Thu, 16 Aug 2018 06:36:04 GMT

Hi Vadim,

Thanks for clarification.

Is there another way to perform the greater than less than with a key figure?

As I wrote in my initial description, we need to post to another AUDITID.

If this is not possible than we will have to look into the ABAP route.

Regards,

Eyal

Re: Rounding and Offsetting to different Datasource/ AuditID.

former_member186338 — Thu, 16 Aug 2018 06:59:18 GMT

You have to use multiple REC statements with tests for conditions. If condition is true then write to required audittrail. If not - do nothing - for example write destination value to destination. Do you understand the idea?

Re: Rounding and Offsetting to different Datasource/ AuditID.

former_member186338 — Thu, 16 Aug 2018 07:01:53 GMT

P.S. do you understand the syntax error you have?

Re: Rounding and Offsetting to different Datasource/ AuditID.

Eyal_Feiler — Thu, 16 Aug 2018 11:09:06 GMT

HI Vadim,

Thanks.

I see the syntax based on the other ternary postings including (https://archive.sap.com/discussions/thread/3866675) .

I have 4 scripts that I tested that run separately (as an example for testing).

*REC(EXPRESSION=(%VALUE% >0)?((%VALUE%%1 < 0.5 )? -%VALUE%%1:0):0, AUDITID=AD_RND1)

*REC(EXPRESSION=(%VALUE% >0)?((%VALUE%%1 > 0.5 )? 1- %VALUE%%1:0):0,AUDITID=AD_RND2)

*REC(EXPRESSION=(%VALUE%<0)?((%VALUE%%1 < 0.5) ? %VALUE%%1 :0):0,AUDITID=AD_RND3)

*REC(EXPRESSION=(%VALUE% >0)?((%VALUE%%1 < 0.5 )? %VALUE%%1:0):0, AUDITID=INPUT3)

Now trying to put together in nested condition because I want them all to write to same AUDITID – if I use above then each later script records 0 on the above row’s selection.

Will continue to test - and post.

Regards,

Eyal

Re: Rounding and Offsetting to different Datasource/ AuditID.

Eyal_Feiler — Thu, 16 Aug 2018 13:23:59 GMT

Hi Vadim,

Ok we are closer.

If I run the following, it works as expected. Result are correct.

*REC(EXPRESSION=(%VALUE% >0)?((%VALUE%%1 < 0.5 )? -%VALUE%%1:0):0, AUDITID=AD_RND1)

*REC(EXPRESSION=(%VALUE% >0)?((%VALUE%%1 >= 0.5 )? 1- %VALUE%%1:0):0,AUDITID=AD_RND2)
*REC(EXPRESSION=(%VALUE%<0)?((%VALUE%%1 <=- 0.5) ? -1-%VALUE%%1 :0):0,AUDITID=AD_RND3)

*REC(EXPRESSION=(%VALUE% <0)?((%VALUE%%1 > -0.5 )? -%VALUE%%1:0):0, AUDITID=INPUT3)

======================================================================================

To record the result in the same AUDITID and to keep each row’s results I setup the script as follows in keeping with the syntax of a single Ternary:

*REC(EXPRESSION= (%VALUE% >0)?((%VALUE%%1 < 0.5) ? -%VALUE%%1 : 1- %VALUE%%1 : (VALUE%%1 < -0.5 ? -%VALUE%%1 : -1- %VALUE%%1,AUDITID=AD_RND1)

Test result: ANSWER: UJK_VALIDATION_EXCEPTION:LINE 9 syntax error: " missing ) in parenthetical"

======================================================================================

Modified -added bold close parenthesis and the false option before the AUDITID= to close the parenthetical and :0 at end,. But this doesn’t work either.

*REC(EXPRESSION= (%VALUE% >0)?((%VALUE%%1 < 0.5) ? -%VALUE%%1 : 1- %VALUE%%1) : (VALUE%%1 < -0.5 ? -%VALUE%%1 : -1- %VALUE%%1):0,AUDITID=AD_RND1)

Syntax error

======================================================================================

This works (reference)

*REC(EXPRESSION=(%VALUE% >0)?((%VALUE%%1 < 0.5 )? %VALUE%%1:0):0, AUDITID=AD_RND1)

======================================================================================

So what is missing?

Regards,

Eyal

Re: Rounding and Offsetting to different Datasource/ AuditID.

former_member186338 — Thu, 16 Aug 2018 13:55:51 GMT

Incorrect number of closing brackets. Sorry, please be accurate.

Re: Rounding and Offsetting to different Datasource/ AuditID.

Former Member — Tue, 21 Aug 2018 14:21:37 GMT

Hi Vadim,

I re-compared to the working template and tested this many many times with different variations and still can’t find the missing closing brackets.

The syntax of the Ternary below matches the working sample under it – with each section bracketed accordingly.

Why is it not working? I am out of testing time…. Can you identify the missing closed bracket please?

These are 2 samples – they both correspond to the working template – second with added inner brackets to isolate the conditions.

*REC(EXPRESSION= (%VALUE% >0)?((%VALUE%%1 < 0.5) ? -%VALUE%%1 : 1- %VALUE%%1: VALUE%%1 < -0.5 ? -%VALUE%%1) : -1- %VALUE%%1,AUDITID=AD_RND1)

UJK_VALIDATION_EXCEPTION:LINE 10 syntax error: " missing ) in parenthetical"

*REC(EXPRESSION= (%VALUE% >0)?((%VALUE%%1 < 0.5) ? -%VALUE%%1 : 1- %VALUE%%1: ((VALUE%%1 < -0.5) ? -%VALUE%%1)

: -1- %VALUE%%1,AUDITID=AD_RND1)

UJK_VALIDATION_EXCEPTION:LINE 10 syntax error: " missing ) in parenthetical"

WORKING SAMPLE

*REC(EXPRESSION=(%VALUE% >0)?((%VALUE%%1 < 0.5 )? %VALUE%%1:0):0, AUDITID=AD_RND1)

Thanks

Eyal

Re: Rounding and Offsetting to different Datasource/ AuditID.

former_member186338 — Tue, 21 Aug 2018 14:48:25 GMT

I will try to check it later todat (not on the mobile phone)

Re: Rounding and Offsetting to different Datasource/ AuditID.

former_member186338 — Tue, 21 Aug 2018 17:04:21 GMT

The following syntax:

*REC(EXPRESSION= (%VALUE% >0)?((%VALUE%%1 < 0.5) ? -%VALUE%%1 : 1- %VALUE%%1 : (VALUE%%1 < -0.5 ?-%VALUE%%1 : -1- %VALUE%%1,AUDITID=AD_RND1)

Will result in:

*REC(EXPRESSION=

(%VALUE% >0)?

(

(%VALUE%%1 < 0.5) ?

-%VALUE%%1

1- %VALUE%%1

: - what do you mean by “:”??? And extra errors later...

(VALUE%%1 < -0.5 ?-%VALUE%%1 : -1- %VALUE%%1,AUDITID=AD_RND1)

Can you explain the required logic in words, because I can’t understand it looking on the incorrect scripts?

Something like:

If value > 0

Then ...

Else...

...

Re: Rounding and Offsetting to different Datasource/ AuditID.

former_member186338 — Tue, 21 Aug 2018 17:30:53 GMT

Second statement:

*REC(EXPRESSION= (%VALUE% >0)?((%VALUE%%1 < 0.5) ? -%VALUE%%1 : 1- %VALUE%%1) : (VALUE%%1 < -0.5 ? -%VALUE%%1 : -1- %VALUE%%1):0,AUDITID=AD_RND1)

Result:

*REC(EXPRESSION=

(%VALUE% >0)?

(

(%VALUE%%1 < 0.5) ?

-%VALUE%%1

1- %VALUE%%1

)

(

“Missing (%”VALUE%%1 < -0.5”missing)” ?

-%VALUE%%1

-1- %VALUE%%1

)

: - not related to anything...

0,AUDITID=AD_RND1)