cancel
Showing results for 
Search instead for 
Did you mean: 

Calculation on Parent nodes via script logic

former_member493004
Participant
0 Kudos
327

Hi All,

Product version: BPCNW 10.1 Classic on S4 HANA

BW :Release 753 and SP level 0002.

In the model Consolidation, we have dimensions Entity,Account,Audit trail,Time,Version,Cost center,Profit center,intercompany,scope,flow and Currency

Script to be launched by Data Manager package.

Issue:- I need calculations on few parent nodes using script logic which is expected to divide few nodes to get the results.

Logic example: GP gets divided by REVENUE to get GP_Margin(target account). Here GP and REVENUE are parent nodes, when I am writing the code as per below, I am not getting the correct results.

I have to get these kind of calculations for many parent nodes, can anyone help me as how can I get the value from the parent members.

Also want to mention that recently client has upgraded S4HANA from 1610 to 1809. On previous 1610 version, this code is working perfectly.

*XDIM_MEMBERSET VERSION = ACTUAL, %VERSION_SET%
*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*WHEN AUDIT_TRAIL
*IS INPUT
*REC(EXPRESSION = [ACCOUNT].[GP]/[ACCOUNT].[REVENUE],ACCOUNT = GP_Margin,AUDIT_TRAIL=BPC_CALCS)
*REC(EXPRESSION = [ACCOUNT].[OP]/[ACCOUNT].[REVENUE],ACCOUNT = OP_Margin,AUDIT_TRAIL=BPC_CALCS)
*ENDWHEN
*COMMIT

Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
0 Kudos

The issue is solved in the note:

https://launchpad.support.sap.com/#/notes/0002591677

  • Go to the transaction SPRO
  • Go to the Environment Section and select the BPC Environment (Example: EnvironmentShell)
  • Add the parameter "K2_SUPPORT_PARENT_MEMBER" and set this to a value of "X"
  • Save this change.
former_member493004
Participant
0 Kudos

Hi Vadim,

Thank you so much for your help.

This resolved my issue with parent members.

Thanks again,

Ambika

Answers (2)

Answers (2)

former_member186338
Active Contributor
0 Kudos

Sample of more or less correct code:

*XDIM_MEMBERSET VERSION=ACTUAL,%VERSION_SET%
*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET ENTITY = %ENTITY_SET%
*XDIM_MEMBERSET AUDIT_TRAIL = INPUT
*XDIM_MEMBERSET ACCOUNT = GP,OP //Parent nodes list

*WHEN ACCOUNT
*IS GP
*REC(EXPRESSION = %VALUE%/[ACCOUNT].[REVENUE],ACCOUNT = GP_Margin,AUDIT_TRAIL=BPC_CALCS)
*IS OP
*REC(EXPRESSION = %VALUE%/[ACCOUNT].[REVENUE],ACCOUNT = OP_Margin,AUDIT_TRAIL=BPC_CALCS)
*ENDWHEN
former_member493004
Participant
0 Kudos

Hi Vadim,

Actually i did scope the accounts (parent nodes) but it is not giving me any results. i tested the code as per below:-

XDIM_MEMBERSET VERSION = ACTUAL, %VERSION_SET%
*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET ENTITY = %ENTITY_SET%
*XDIM_MEMBERSET AUDIT_TRAIL =BPC_INPUT,INPUT
*XDIM_MEMBERSET ACCOUNT = T3111,T3110

*WHEN ACCOUNT
	*IS GP
	*REC(EXPRESSION = %VALUE%/[ACCOUNT].[T3111],ACCOUNT = GP_Margin,AUDIT_TRAIL=BPC_CALCS)
	*IS OP
	*REC(EXPRESSION = %VALUE%/[ACCOUNT].[T3111],ACCOUNT = OP_Margin,AUDIT_TRAIL=BPC_CALCS)
	*ENDWHEN
	*COMMIT

PS: I do have the full code where I have scoped the Accounts and this code is working fine in my system which is on 1610 HANA but now when system is upgraded to 1809, my below code is not working at all. I am kind of thinking if the parent nodes to be calculated someway in different way, can you please help.

All T accounts are the parent nodes in Account dimension.

*XDIM_MEMBERSET VERSION = ACTUAL, %VERSION_SET%
*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*WHEN AUDIT_TRAIL
*IS INPUT
*WHEN ACCOUNT
	*IS T3111
	*REC(EXPRESSION = [ACCOUNT].[T3110]/[ACCOUNT].[T3111],ACCOUNT = GP_Margin,AUDIT_TRAIL=BPC_CALCS)
	*REC(EXPRESSION = [ACCOUNT].[T3400]/[ACCOUNT].[T3111],ACCOUNT = OP_Margin,AUDIT_TRAIL=BPC_CALCS)
	*REC(EXPRESSION = [ACCOUNT].[T3300]/[ACCOUNT].[T3111],ACCOUNT = IBT_Margin,AUDIT_TRAIL=BPC_CALCS)
	*REC(EXPRESSION = [ACCOUNT].[T3121]/[ACCOUNT].[T3111],ACCOUNT = SGA_Margin,AUDIT_TRAIL=BPC_CALCS)
	*REC(EXPRESSION = [ACCOUNT].[T3111]/([ACCOUNT].[ZINHC],[AUDIT_TRAIL].[AT_FROM_HCM]),ACCOUNT = SPHC,AUDIT_TRAIL=BPC_CALCS)
	*REC(EXPRESSION = [ACCOUNT].[T3121]/([ACCOUNT].[ZINHC],[AUDIT_TRAIL].[AT_FROM_HCM]),ACCOUNT = SGAHC,AUDIT_TRAIL=BPC_CALCS)
	*REC(EXPRESSION = [ACCOUNT].[T3110]/([ACCOUNT].[ZINHC],[AUDIT_TRAIL].[AT_FROM_HCM]),ACCOUNT = GPHC,AUDIT_TRAIL=BPC_CALCS,AUDIT_TRAIL=BPC_CALCS)
	*REC(EXPRESSION = [ACCOUNT].[T3400]/([ACCOUNT].[ZINHC],[AUDIT_TRAIL].[AT_FROM_HCM]),ACCOUNT = OPHC,AUDIT_TRAIL=BPC_CALCS)
	*REC(EXPRESSION = %VALUE%,ACCOUNT = SALES,AUDIT_TRAIL=BPC_CALCS)
	*IS T3110
	*REC(EXPRESSION = %VALUE%,ACCOUNT = GROSS_PROFIT,AUDIT_TRAIL=BPC_CALCS)
	*IS T3400
	*REC(EXPRESSION = %VALUE%,ACCOUNT = OPERATING_PROFIT,AUDIT_TRAIL=BPC_CALCS)
	*IS T3300
	*REC(EXPRESSION = %VALUE%,ACCOUNT = IBT,AUDIT_TRAIL=BPC_CALCS)
	
	*IS T1000
	//EQ_RATIO: Shareholders Equity (T2400)/T1000
	*REC(EXPRESSION = [ACCOUNT].[T2400]/[ACCOUNT].[T1000], ACCOUNT = EQ_RATIO,AUDIT_TRAIL=BPC_CALCS)
	//DEBT_RATIO: LIABILITIES/T1000
	*REC(EXPRESSION = ([ACCOUNT].[T2100]+[ACCOUNT].[T2200])/[ACCOUNT].[T1000], ACCOUNT = DEBT_RATIO,AUDIT_TRAIL=BPC_CALCS)
 *ENDWHEN
 *ENDWHEN

former_member186338
Active Contributor
0 Kudos

ambika.raina

Please test the code with fixed scope in UJKT:

XDIM_MEMBERSET VERSION=ACTUAL
*XDIM_MEMBERSET TIME = 2019.12 //some value
*XDIM_MEMBERSET ENTITY = someentity
*XDIM_MEMBERSET AUDIT_TRAIL =INPUT
*XDIM_MEMBERSET ACCOUNT = GP //Is it a real parent account? Do you have value?
*WHEN ACCOUNT
*IS GP
	*REC(EXPRESSION =%VALUE%/[ACCOUNT].[T3111],ACCOUNT = GP_Margin,AUDIT_TRAIL=BPC_CALCS)
*ENDWHEN

Post log!

P.S. Never use COMMIT in BPC NW 🙂

former_member186338
Active Contributor
0 Kudos

P.P.S.

ambika.raina

*XDIM_MEMBERSET ACCOUNT = T3111,T3110
*WHEN ACCOUNT
*IS GP

Absolutely meaningless. *IS GP is not scoped!!!

former_member493004
Participant
0 Kudos

sorry for the confusion, my real parent nodes are T3111 and T3110 i had used GP and OP just for example purpose.

let me test the code and will post the log.

thanks.

former_member186338
Active Contributor
0 Kudos

Then the correct code will be:

DIM_MEMBERSET VERSION=ACTUAL
*XDIM_MEMBERSET TIME = 2019.12 //some value
*XDIM_MEMBERSET ENTITY = someentity
*XDIM_MEMBERSET AUDIT_TRAIL =INPUT
*XDIM_MEMBERSET ACCOUNT = T3110,T3400 //real parent accounts! Do you have value?

*WHEN ACCOUNT
*IS T3110
*REC(EXPRESSION =%VALUE%/[ACCOUNT].[T3111],ACCOUNT = GP_Margin,AUDIT_TRAIL=BPC_CALCS)
*IS T3400
*REC(EXPRESSION =%VALUE%/[ACCOUNT].[T3111],ACCOUNT = OP_Margin,AUDIT_TRAIL=BPC_CALCS)
*ENDWHEN
former_member186338
Active Contributor
0 Kudos

You are calculating ratios! Strange idea to do it using script logic.

Use dimension member formulas to calculate ratios.

P.S. Absolutely strange script - ACCOUNT dimension is not scoped, resulting in calculation for any account line!

former_member493004
Participant
0 Kudos

so after executing the code in UJKT, got the below log with no results posted.

PS: I do have data present at the nodes.Below is the sample report screenshot showing that I have data at nodes T3111, T3110 and T3400.

LGX:
*XDIM_MEMBERSET VERSION = ACTUAL
*XDIM_MEMBERSET TIME = 2019.11
*XDIM_MEMBERSET ENTITY = CO2900
*XDIM_MEMBERSET AUDIT_TRAIL =INPUT
*XDIM_MEMBERSET ACCOUNT = T3111,T3110
*WHEN ACCOUNT
*IS T3110
*REC(EXPRESSION = %VALUE %/[ACCOUNT].[T3111],ACCOUNT = GP_Margin,AUDIT_TRAIL=BPC_CALCS)
*IS T3400
*REC(EXPRESSION =%VALUE%/[ACCOUNT].[T3111],ACCOUNT = OP_Margin,AUDIT_TRAIL=BPC_CALCS)
*ENDWHEN

-------------------------------------------------------------------------------------------------------------------------------------
LOG:
LOG BEGIN TIME:2020-01-22 05:20:08
FILE:\ROOT\WEBFOLDERS\HTNA_CONSOLDATION \ADMINAPP\HTA_Consol\TEST.LGF
USER:VOT65708
APPSET:HTNA_CONSOLDATION
APPLICATION:HTA_Consol
[INFO] GET_DIM_LIST(): I_APPL_ID="HTA_Consol", #dimensions=12 
ACCOUNT,AUDIT_TRAIL,COST_CENTER,CURRENCY,ENTITY,FLOW,INTERCO,MEASURES,PROFITCTR,SCOPE,TIME,VERSION


#dim_memberset=5 
VERSION:ACTUAL,1 in total.
TIME:2019.11,1 in total.
ENTITY:CO2900,1 in total.
AUDIT_TRAIL:INPUT,1 in total.
ACCOUNT:T3110,T3111,2 in total.


#dim_memberset=5 
VERSION:ACTUAL,1 in total.
TIME:2019.11,1 in total.
ENTITY:CO2900,1 in total.
AUDIT_TRAIL:INPUT,1 in total.
ACCOUNT:T3110,T3111,2 in total.


SCRIPT RUNNING TIME IN TOTAL:0.00 s.
LOG END TIME:2020-01-22 05:20:08


former_member186338
Active Contributor
0 Kudos

Sorry, but why do you always using incorrect code??

*XDIM_MEMBERSET ACCOUNT = T3111,T3110
*WHEN ACCOUNT
*IS T3110
*REC(EXPRESSION = %VALUE %/[ACCOUNT].[T3111],ACCOUNT = GP_Margin,AUDIT_TRAIL=BPC_CALCS)
*IS T3400
*REC(EXPRESSION =%VALUE%/[ACCOUNT].[T3111],ACCOUNT = OP_Margin,AUDIT_TRAIL=BPC_CALCS)
*ENDWHEN

You don't need to scope T3111, but you have to scope T3400

Scope is related to %VALUE% in REC!!!!

former_member186338
Active Contributor
0 Kudos

P.S. And there is something wrong with your log!

#dim_memberset

is duplicated!

former_member493004
Participant
0 Kudos

I corrected the scope command but still did not get any results.

I am getting the below log with duplicated #dim_memberset but i do not know as how to rectify it. Can you please suggest or help as how can i make this code working.

LGX:


*XDIM_MEMBERSET VERSION = ACTUAL
*XDIM_MEMBERSET TIME = 2019.11
*XDIM_MEMBERSET ENTITY = CO2900
*XDIM_MEMBERSET AUDIT_TRAIL =INPUT
*XDIM_MEMBERSET ACCOUNT =T3110,T3400
*WHEN ACCOUNT
*IS T3110
*REC(EXPRESSION = %VALUE %/[ACCOUNT].[T3111],ACCOUNT = GP_Margin,AUDIT_TRAIL=BPC_CALCS)
*IS T3400
*REC(EXPRESSION = %VALUE%/[ACCOUNT].[T3111],ACCOUNT = OP_Margin,AUDIT_TRAIL=BPC_CALCS)
*ENDWHEN


-------------------------------------------------------------------------------------------------------------------------------------
LOG:


LOG BEGIN TIME:2020-01-22 06:35:12
FILE:\ROOT\WEBFOLDERS\HTNA_CONSOLDATION \ADMINAPP\HTA_Consol\TEST.LGF
USER:VOT65708
APPSET:HTNA_CONSOLDATION
APPLICATION:HTA_Consol
[INFO] GET_DIM_LIST(): I_APPL_ID="HTA_Consol", #dimensions=12 
ACCOUNT,AUDIT_TRAIL,COST_CENTER,CURRENCY,ENTITY,FLOW,INTERCO,MEASURES,PROFITCTR,SCOPE,TIME,VERSION


#dim_memberset=5 
VERSION:ACTUAL,1 in total.
TIME:2019.11,1 in total.
ENTITY:CO2900,1 in total.
AUDIT_TRAIL:INPUT,1 in total.
ACCOUNT:T3110,T3400,2 in total.


#dim_memberset=5 
VERSION:ACTUAL,1 in total.
TIME:2019.11,1 in total.
ENTITY:CO2900,1 in total.
AUDIT_TRAIL:INPUT,1 in total.
ACCOUNT:T3110,T3400,2 in total.


SCRIPT RUNNING TIME IN TOTAL:0.00 s.
LOG END TIME:2020-01-22 06:35:12