Ability to Run Script of Account Based Calculation...

Hi Experts,

I would like to run a caluclation based on on whether a value is > or < than zero.

For example,

if Accounts Receivable is >0 (e.g. 100) do nothing

If Accounts Receivable (AR) is <0 (-25) than record 25 to the AR account and -25 to the AP account.

Essentially reclass from negative receivable sto payables.

Issue is that I understand the script logic is limited to conditions for dimension values or properties but cannot read amounts.

We went through this in NW, triedthe formulas provided and eventually, used ABAP to successfully perform the desired calcuation.

(See http://scn.sap.com/thread/3595642).

Anyone have relatively simple ideas for performing the calculation in the MS version?

Regards,

Eyal

SAP Managed Tags:
SAP Business Planning and Consolidation, version for the Microsoft platform

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (0)

Answers (1)

Hi Eyal,

similar script can be built also in MS version, if needed instead of abap programming you can use stored procedures for complex instructions.

Regards

Roberto

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Thanks Roberto, will give the format below another try before stored procedures or alternate solutions.

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION = %VALUE% < 0 ? %VALUE% * -1 : 0, DATASRC = RECL)

*REC(EXPRESSION = %VALUE% < 0 ? %VALUE% : 0, ACCOUNT = ACCOUNT.RECLASS, DATASRC = RECL)

*ENDWHEN

Regards,

Eyal

Hi Roberto,

Thank you.

Here is the script I drafted.

It’s generating records

What is missing? Or Syntax required?

2) In this case the comparison is a simple base member test of >0. What happens when I need to check a parent? Can you provide the correct *REC if syntax?
The example is if ACCOUNT PARENT 717 is <0 than I will reclass each account 717_01 to x_01 717_02 to x_05 etc

3) I may need to select multiple AUDITID – How d owe select those?

Thank you.

Regards,

Eyal

// RECLASS PAY TO RECEIVABLE FOR MAAM TAX

// RECLASS_TAX14.LGF

//**SELECT ("%AUDITID_LIST%","[ID]","AUDITID","REC_AI='Y'")

*XDIM_MEMBERSET TIME = %time_set%

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%

*XDIM_MEMBERSET FLOW = F99

*XDIM_MEMBERSET CONSOSCOPE = G_NONE

//*XDIM_MEMBERSET AUDITID=%AUDITID_LIST%

*XDIM_MEMBERSET AUDITID= INPUT

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*WHEN CURRENCY

*IS LC

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=[ACCOUNT.712]>0 ? 712: 0, ACCOUNT="485_01",AUDITID="REC_C14")

*REC(EXPRESSION=-[ACCOUNT.712]>0 ? 712: 0, ACCOUNT="712",AUDITID="REC_C14")

*ENDWHEN

// EXAMPLE 1:

// *REC(EXPRESSION=%VALUE%>0 ? %VALUE% : 0, SOMEDIM=TARGETMEMBER1) //%VALUE% is the current base member value.

//EXAMPLE

//WHEN CURRENCY

// *IS "LC"

// *REC(FACTOR=1,ACCOUNT="808_02",AUDITID="REC_C1")

// *ENDWHEN

//*COMMIT

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

DATA SET

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

ENTITY 1020 = -100 -> Do nothing

ENTITY 1050 = 80 --> greater than zero so reclass to account 485_01 and reverse the balance in 712

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

LOG FILE

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

****************************************************************************************************

Start time --->17:35:42 - Date:2016-08-22 (build version:10.1.3.0)

****************************************************************************************************

User:DS-INVEST\EyalF

Environment:MEITAVDASH_210716

Model:Consolidation

Logic mode:1

Logic by:

Scope by:

Data File:

Debug File:U:\MSSQL\Data\WebFolders\MEITAVDASH_210716\Consolidation\PrivatePublications\EyalF\TempFiles\DebugLogic_4896_982541070.Log

Logic File:U:\MSSQL\Data\WebFolders\MEITAVDASH_210716\Consolidation\\..\AdminApp\Consolidation\RECLASS_TAX14.LGF

Selection:U:\MSSQL\Data\WebFolders\MEITAVDASH_210716\Consolidation\PRIVATEPUBLICATIONS\EyalF\TempFiles\FROM_4896_.TMP

Run mode:1

Query size:0

Delim:,

Query type:0

Simulation:0

Calc diff.:0

Formula script:

Max Members:

Test mode:0

Is Modelling:1

Work status Check:0

Task name:DM

Number of logic calls: 1

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

Call no. 1, logic: U:\MSSQL\Data\WebFolders\MEITAVDASH_210716\Consolidation\\..\AdminApp\Consolidation\RECLASS_TAX14.LGF

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

signeddata is YTD

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

Building sub-query 1

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

Query Type: 0

Max members:

Region:

DIMENSION:CATEGORY

ACTUAL

DIMENSION:ENTITY

1050

DIMENSION:TIME

2016.DEC

DIMENSION:TIME

2016.DEC

DIMENSION:CATEGORY

ACTUAL

DIMENSION:FLOW

F99

DIMENSION:CONSOSCOPE

G_NONE

DIMENSION:AUDITID

INPUT

DIMENSION:ENTITY

1050

Loading TIME.TIMEID

Time to load properties: 0.00 sec

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

select ACCOUNT,AUDITID,CURRENCY,INTERCO,MIGZAR,TC,SIGNEDDATA

into #tblTempLogic_814831

from tblFactConsolidation

WHERE AUDITID=N'INPUT' AND CATEGORY=N'ACTUAL' AND CONSOSCOPE=N'G_NONE' AND CURRENCY=N'LC' AND ENTITY=N'1050' AND FLOW=N'F99' AND TIMEID=N'20161200'

insert into #tblTempLogic_814831 (ACCOUNT,AUDITID,CURRENCY,INTERCO,MIGZAR,TC,SIGNEDDATA)

select ACCOUNT,AUDITID,CURRENCY,INTERCO,MIGZAR,TC,SIGNEDDATA

from tblFactWBConsolidation

WHERE AUDITID=N'INPUT' AND CATEGORY=N'ACTUAL' AND CONSOSCOPE=N'G_NONE' AND CURRENCY=N'LC' AND ENTITY=N'1050' AND FLOW=N'F99' AND TIMEID=N'20161200'

and SOURCE = 0

insert into #tblTempLogic_814831 (ACCOUNT,AUDITID,CURRENCY,INTERCO,MIGZAR,TC,SIGNEDDATA)

select ACCOUNT,AUDITID,CURRENCY,INTERCO,MIGZAR,TC,SIGNEDDATA

from tblFAC2Consolidation

WHERE AUDITID=N'INPUT' AND CATEGORY=N'ACTUAL' AND CONSOSCOPE=N'G_NONE' AND CURRENCY=N'LC' AND ENTITY=N'1050' AND FLOW=N'F99' AND TIMEID=N'20161200'

select tmpTable.ACCOUNT,tmpTable.AUDITID,tmpTable.CURRENCY,tmpTable.INTERCO,tmpTable.MIGZAR,tmpTable.TC,sum(SIGNEDDATA) as SIGNEDDATA

from #tblTempLogic_814831 as tmpTable

group by tmpTable.ACCOUNT,tmpTable.AUDITID,tmpTable.CURRENCY,tmpTable.INTERCO,tmpTable.MIGZAR,tmpTable.TC

drop table #tblTempLogic_814831

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

Time to load Source data: 0.02 sec.

2 records to process

Time to prepare data process: 0.00 sec.

Time to initialize destination: 0.00 sec.

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

Time to get source data: 0.00 sec.

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

2 records scanned

0 records skipped

2 records processed

4 records generated

Time to scan data: 0.36 sec.

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

Time to prepare final strings: 0.00 sec.

Total processing time: 0.37 sec.

Model: CONSOLIDATION - Records to be posted are 4 (calc diff = 0)

CATEGORY,CONSOSCOPE,ENTITY,FLOW,TIME,ACCOUNT,AUDITID,CURRENCY,INTERCO,MIGZAR,TC,SIGNEDDATA

ACTUAL,G_NONE,1050,F99,2016.DEC,485_01,REC_C14,LC,I_NONE,M_1400,TC_NONE,0

ACTUAL,G_NONE,1050,F99,2016.DEC,712,REC_C14,LC,I_NONE,M_1400,TC_NONE,0

ACTUAL,G_NONE,1050,F99,2016.DEC,485_01,REC_C14,LC,I_NONE,M_NONE,TC_NONE,0

ACTUAL,G_NONE,1050,F99,2016.DEC,712,REC_C14,LC,I_NONE,M_NONE,TC_NONE,0

Time to validate records: 0.00 sec.

Post Record Status

Save count : 4

Accept count : 4

Reject count : 0

Locked region(s) by work status: 0

Posting is OK

Time to post records: 2.03 sec.

call 1 completed and data posted in 2.44 sec.

Run completed in 2.44 sec.

****************************************************************************************************

End time --->17:35:44 - Date: 2016-08-22

****************************************************************************************************

File path: \MEITAVDASH_210716\Consolidation\PrivatePublications\EyalF\tempfiles\DebugLogic_4896_982541070.Log

Hi Eyal,

sorry I'm on holiday, I cannot test the script so you have to try by yourself.

Are you sure that *REC(EXPRESSION=[ACCOUNT.712]>0 ? 712: 0, ACCOUNT="485_01",AUDITID="REC_C14") is working? This works on NW but until 7.5 on MS version it does not work.

you should use GET function, try to give an example, instead of

*WHEN CURRENCY

*IS LC

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=[ACCOUNT.712]>0 ? 712: 0, ACCOUNT="485_01",AUDITID="REC_C14")

*REC(EXPRESSION=-[ACCOUNT.712]>0 ? 712: 0, ACCOUNT="712",AUDITID="REC_C14")

*ENDWHEN

something like (not sure what you want, description seems different from your script, but I hope you understand this example)

*WHEN CURRENCY

*IS LC

*WHEN GET(ACCOUNT="712") // obtain the value of this parent account

*IS > 0

*REC(EXPRESSION=GET(ACCOUNT="712"),ACCOUNT="485_01",AUDITID="REC_C14") //write this value on different account and audit

*REC(EXPRESSION=-GET(ACCOUNT="712"),ACCOUNT="712",AUDITID="REC_C14")

*ELSE

*REC(FACTOR=0,ACCOUNT="485_01",AUDITID="REC_C14")

*REC(FACTOR=0,ACCOUNT="712",AUDITID="REC_C14")

*ENDWHEN

"3) I may need to select multiple AUDITID – How d owe select those?"

*SELECT (%AUDITID_LIST%,"[ID]","AUDITID","REC_AI='Y'")

*XDIM_MEMBERSET AUDITID=%AUDITID_LIST%

your instructions are ok (without first double quote) if REC_AI it's a property of AUDITID

just add the related *WHEN

*WHEN AUDITID

*IS %AUDITID_LIST% ...

Regards

Roberto

Hi Roberto,

Enjoy vacation & thanks for the help. When you're back:

Moved onto creating without a condition in the meantime.

For reference: I tested, the first GET statement as a standalone. It did not bring the parent value of the 712 account:

*WHEN CURRENCY

*IS LC

*WHEN GET(ACCOUNT="712") // obtain the value of this parent account

*IS > 0

Tried using the GET formula with the multiple reclasses within the same selection and it looked like the result was multiplying the source amount by 5 100 source --> 500 etc.

not sure why.

e.g.

*REC(EXPRESSION=GET(ACCOUNT="117_03"), ACCOUNT="214_01",AUDITID="REC_C13")

*REC(EXPRESSION=GET(ACCOUNT="117_04"), ACCOUNT="214_05",AUDITID="REC_C13")

*REC(EXPRESSION=-GET(ACCOUNT="117_03"), ACCOUNT="117_03",AUDITID="REC_C13")

*REC(EXPRESSION=-GET(ACCOUNT="117_04"), ACCOUNT="117_04",AUDITID="REC_C13")

Any ideas on this?

Thanks

Eyal

Hi Eyal,

Wondering if for example your account 117_03 contains the same value on several members of another dimension? In that case it might help to specify one member of that dimension. Alternatively, add NOADD at the end of your *REC statament: ...AuditId="REC_C_13", NOADD)

/Cecilia

Ask a Question

Top Q&A Solution Author

User

Count