Rounding and Offsetting to different Datasource/ AuditID.

[Eyal_Feiler]

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

[Eyal_Feiler]

Hi Vadim,

For clarity,

I intended to respond on the last comment, appears I replied to an earlier posting.

Disregard earlier.

Scenario is as follows in non-syntax:

If %VALUE% is >0

If <0.5

Then –%VALUE%%

Else 1- %VALUE%%

If -%VALUE% <0

If <0.5

Then -%VALUE%%

Else excelexample.jpg-%VALUE%%

Both results post to AUDITID=AD_RND1

The formula I am working with:

I tested each if separately and it works, now just to put it all together.

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

A working simple sample with two ifs:

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

Thanks

Eyal

[former_member186338]

Sorry, but I still unable to understand the required logic - Excel screenshot is not clear.

Can you provide complete text of if then else? Write it in notepad with correct tabulation and post a screenshot. Your script formula is syntactically incorrect.

[former_member186338]

P.S. And I don’t understand the idea to copy the same statement with obvious errors in each post!

Even missing “%” is not corrected here ... %VALUE%%1: -VALUE%%1 < 0. ....

[Eyal_Feiler]

Hi Vadim,

I’m performing the calculation another way, splitting to 2 parts within section 1A below.

Then post to the desired AUDITID part 2

This is working as I want it to. So we can conclude this issue here.

It would be nice to combine 1A to 2 REC statements – that was the issue I was trying to address.

//Part 1A Split to AuditID

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*XDIM_MEMBERSET CURRENCY = %CURRENCY_SET%

*XDIM_MEMBERSET CATEGORY = ACTUAL

*XDIM_MEMBERSET AUDITID = BAS(AD5000)

*WHEN SCOPE

*IS S_NONE // replace

// SPLIT POSITIVE AND NEGATIVE

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

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

*ENDWHEN

*COMMIT

//PART 2 RECORD TO SINGLE AUDITID AD_RND1

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*XDIM_MEMBERSET CURRENCY = %CURRENCY_SET%

*XDIM_MEMBERSET CATEGORY = ACTUAL

*XDIM_MEMBERSET AUDITID = BAS(AD_SUBCALC)

*WHEN SCOPE

*IS S_NONE // replace

*REC(FACTOR=1, AUDITID=AD_RND1)

*ENDWHEN

*COMMIT

Thanks

Eyal

[former_member186338]

Sorry, but you don't read my posts and don't answer questions. I will stop participation in this endless discussion.

[Eyal_Feiler]

Hi Vadim,

Thanks for your help. I found a solution based on your feedback and posted above so others can see. It would be helpful to script in one REC for efficiency but for now I am using the 2 lines REC lines as I posted above.

Regards,

Eyal

[former_member186338]

It can be done in the single line, but you have to use correct syntax and to avoid typo mistakes.

[former_member186338]

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

...

[former_member186338]

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)

[former_member234894]

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)

T

Thanks

Eyal

[former_member186338]

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

[Eyal_Feiler]

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

[former_member186338]

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

[Eyal_Feiler]

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

[former_member186338]

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?

[former_member186338]

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

[Eyal_Feiler]

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

[Eyal_Feiler]

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

[former_member186338]

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!!!!

[Eyal_Feiler]

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

[former_member186338]

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

Use Math.round

[Eyal_Feiler]

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

[former_member186338]

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

[Eyal_Feiler]

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

[former_member186338]

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

[former_member186338]

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.