BPC Script Logic- Lookup across model for cost allocation

[former_member711292]

Hello experts! I am new to BPC and encountered this issue while writing the script logic. Please let me know if any alternative to solve this logic. Any help will be really appreciated! Thank you very much for your time and help in advance!

Purpose & Issue Encountered:

As we are working on the cost allocation exercise, the purpose of the logic script is to retrieve the ratio from ratio table (ZALC_RATIO_Test) to transaction table (ZTRANS_Test) so as to calculate the cost allocated to the subsequent flows in the transaction table. So the problem we encountered here is that we failed to get the value from another model as an expression or factor and thus the calculation cannot be proceeded. Script will run as default.lgf.

Version:

[BPC 11.1] BPC4HANA 200 SP 4, BW4HANA 200 SP 4 ABAP

Current Model:

In the model ZTRANS_Test we have the following dimension:
RPTCURRENCY (Reporting Currency) (R)
TIME (Time) (T)
ZACQMAIN (Acquisition Maintenance Cost) (User Def)
ZALC_FLOW (Cost Allocation Flow) (S)
ZAMTTYPE (Amount Type) (User Def)
ZBIZTYPE (Business Type) (User Def)
ZCAMP_CO (Campaign Code) (User Def)
ZCATEGORY (Category) (C)
ZCHANNELS (Channels) (User Def)
ZCOA (GL Accounts) (A)
ZCOMP_CODE (Company Code) (E)
ZDATASOURCE (Data Source) (D)
ZDID (Direct and Indirect Cost) (User Def)
ZDND (Due and Not Due) (User Def)
ZHPROFITCTR (Profit Center Hierarchy) (User Def)
ZLEDGER (Ledger) (User Def)
ZREPORTMEASURE (Report Measure) (User Def)
ZUCOSTCENTER (Target Cost Center) (User Def)
ZVERSION (Version) (User Def)
ZYEAROFPLAN (Year of planning) (User Def)

While for model ZALC_RATIO_Test, we have the following dimension:
RPTCURRENCY (Reporting Currency) (R)
TIME (Time) (T)
ZACQMAIN (Acquisition Maintenance Cost) (User Def)
ZALC_FLOW (Cost Allocation Flow) (S)
ZCATEGORY (Category) (C)
ZCOA (GL Accounts) (A)
ZCOSTCENTER (Target Cost Center) (User Def)
ZDATASOURCE (Data Source) (D)
ZHPROFITCTR (Profit Center Hierarchy) (User Def)
ZREPORTMEASURE (Report Measure) (User Def)
ZUCOSTCENTER (Target Cost Center) (User Def)
ZVERSION (Version) (User Def)
ZYEAROFPLAN (Year of planning) (User Def)

We want to get the corresponding ratio from ZALC_RATIO_Test to complete the cost allocation exercise in ZTRANS_Test according to the flow, e.g.:

If the ratio for cost center A to different step is (from model ZALC_RATIO_Test😞

The we expect that when we input the original amount(i.e. amount for step 0) in ZTRANS_Test, after running the script the cost will be allocated to subsequent steps automatically like this

Actual Result:

No value can be retrieved from the ratio table and thus calculation is not working

The script:

*XDIM_MEMBERSET ZVERSION =v2
*XDIM_MEMBERSET ZCOMP_CODE =1010 
*XDIM_MEMBERSET TIME =2020.12 
*XDIM_MEMBERSET ZYEAROFPLAN =2020 
*XDIM_MEMBERSET ZALC_FLOW =STEP_0 
*XDIM_MEMBERSET RPTCURRENCY =ADP 
*XDIM_MEMBERSET ZCATEGORY =CA_Plan 
*XDIM_MEMBERSET ZDATASOURCE =Input 
*XDIM_MEMBERSET ZBIZTYPE =DIRECT 
*XDIM_MEMBERSET ZCAMP_CO =ALL 
*XDIM_MEMBERSET ZCHANNELS =AG 
*XDIM_MEMBERSET ZDID =DIR 
*XDIM_MEMBERSET ZDND =DUE
*XDIM_MEMBERSET ZHPROFITCTR =0000009999 
*XDIM_MEMBERSET ZLEDGER =0L 
*XDIM_MEMBERSET ZREPORTMEASURE =AMOUNT 
*XDIM_MEMBERSET ZCOA =ZG011 
*XDIM_MEMBERSET ZUCOSTCENTER =1010CCCC 

*LOOKUP ZALC_RATIO_Test
*DIM ZVERSION =v2
*DIM ZYEAROFPLAN =2020 
*DIM TIME =2020.12
*DIM ZHPROFITCTR =0000009999    
*DIM ZALC_FLOW=STEP_0
*DIM ZCOA =ZG011
*DIM ZRATET:ZUCOSTCENTER =1010CCCC
*ENDLOOKUP

*WHEN ZUCOSTCENTER 
*IS 1010CCCC
*REC(FACTOR=LOOKUP(ZRATET), ZALC_FLOW =STEP_A)
*ENDWHEN

*COMMIT

Result in UJKT :

#dim_memberset=18 
ZVERSION:v2,1 in total.
ZCOMP_CODE:1010,1 in total.
TIME:2020.12,1 in total.
ZYEAROFPLAN:2020,1 in total.
ZALC_FLOW:STEP_0,1 in total.
RPTCURRENCY:ADP,1 in total.
ZCATEGORY:CA_Plan,1 in total.
ZDATASOURCE:Input,1 in total.
ZBIZTYPE:DIRECT,1 in total.
ZCAMP_CO:ALL,1 in total.
ZCHANNELS:AG,1 in total.
ZDID:DIR,1 in total.
ZDND:DUE,1 in total.
ZHPROFITCTR:0000009999,1 in total.
ZLEDGER:0L,1 in total.
ZREPORTMEASURE:AMOUNT,1 in total.
ZCOA:ZG011,1 in total.
ZUCOSTCENTER:1010CCCC,1 in total.


REC :%value%*(LOOKUP(ZRATET))


CALCULATION BEGIN:
QUERY PROCESSING DATA
QUERY TIME : 0.00 ms. 1  RECORDS QUERIED OUT.
QUERY REFERENCE DATA
QUERY LOOKUP DATA FROM APPLICATION: ZALC_RATIO_Test
QUERY TIME : 1.00 ms. 0  RECORDS QUERIED OUT.
CALCULATION TIME IN TOTAL :0.00 ms.
1  RECORDS ARE GENERATED.
CALCULATION END.


ENDWHEN ACCUMULATION: 1  RECORDS ARE GENERATED.


DATA TO WRITE BACK:
RPTCURRENCY	TIME	ZACQMAIN	ZALC_FLOW	ZAMTTYPE	ZBIZTYPE	ZCAMP_CO	ZCATEGORY	ZCHANNELS	ZCOA	ZCOMP_CODE	ZDATASOURCE	ZDID	ZDND	ZHPROFITCTR	ZLEDGER	ZREPORTMEASURE	ZUCOSTCENTER	ZVERSION	ZYEAROFPLAN	SIGNEDDATA
ADP	2020.12	ACQ	STEP_A	GROSS	DIRECT	ALL	CA_Plan	AG	ZG011	1010	Input	DIR	DUE	0000009999	0L	AMOUNT	1010CCCC	v2	2020	0.00 
1  RECORDS HAVE BEEN WRITTEN BACK.
WRITING TIME :0.00  ms.

[former_member186338 · Accepted solution]

I can see that:

ZCOSTCENTER (Target Cost Center) (User Def)

is not defined in the LOOKUP. This is the issue.

By the way, it's useless to define in LOOKUP the dimensions that will have the same value across models.

*LOOKUP ZALC_RATIO_Test
*DIM ZRATET:ZCOSTCENTER =???
*ENDLOOKUP

P.S. And it's a strange idea to name dimensions and models with Z prefix. Never required for BPC!

[former_member711292 · Accepted solution]

Got it! Thank you so much!