Skip to Content
-1

BPC Script Logic Issue

Hi All,

Working on BPC 10.0 NW

As in the below screenshot, I am entering 4 records in the input form.

And need to populate the output in the Report section using the calculation as follows:

Row 1 and Row 2 in Report (This section works fine)

Sum up values at PC's(DCAT,DCAM) for each specific FUNC_AREA and post negative values

1100 : DCAT(200) + DCAM(600) = 800

1590: DCAT(200) + DCAM(300) = 500

Row 3,4,5 : The input values are multiplied with corresponding factors for each PC vs FUNC_AREA from the FACTORS input form

E.g.: Values at Row 3 for DC99,9150 combinations is calculated as:

(DCAT,1100) *(DCAT,9150) + (DCAT,1590) *(DCAT,9150) +

(DCAM,1100) *(DCAM,9150) + (DCAM,1590) *(DCAM,4250) = 200*0.1 + 200*0.1 +600*0.22 +300*0.22 = 238

However, the result I get is 952 which is 4 times the expected value.

The script logic is triggered through a Data Manager Package, where user can select multiple values of PC, FUNC_AREA. For this particular run, 2 PC's (DCAT, DCAM) and 2 FUNC_AREA(1100,1590) were selected and the result was quadrupled. If i select one PC less, then the values are tripled. If the selection contains only 1 PC and 1 FUNC_AREA, the values are doubled.

FACTORs Input Form

*XDIM_MEMBERSET AUDITTRAIL = PLAN_INPUT
*XDIM_MEMBERSET CATEGORY = PLAN
*XDIM_MEMBERSET COST_CENTER = CC_NONE
*XDIM_MEMBERSET ENTITY = E_NONE
*XDIM_MEMBERSET P_ACCOUNT = C_NONE
*XDIM_MEMBERSET RPTCURRENCY = USD
*XDIM_MEMBERSET PROFITCENTER = %PROFITCENTER_SET%
*XDIM_MEMBERSET FUNC_AREA AS %T_FA% = 9150,3090,4250           
*XDIM_MEMBERSET FUNC_AREA = %FUNC_AREA_SET%, %T_FA%
*XDIM_MEMBERSET CONT_COUNTRY = %CONT_COUNTRY_SET%
*XDIM_MEMBERSET TIME = %TIME_SET%

*WHEN PROFITCENTER.DC_PC
	*IS DCUS
	*WHEN CONT_COUNTRY
		*IS * 
		*BEGIN
			*REC
			(
			EXPRESSION = -%VALUE%,
			PROFITCENTER = DC99, AUDITTRAIL = DUAL_CREDIT
			)
		*END
	*ENDWHEN
*ENDWHEN

*WHEN PROFITCENTER.DC_PC
	*IS DCUS
	*WHEN CONT_COUNTRY
		*IS *
		*FOR %PC% = %PROFITCENTER_SET%
			*FOR %FA% = %FUNC_AREA_SET%
				*FOR %F_T_FA% = %T_FA%
					*BEGIN
						*REC
						(
						EXPRESSION = - 
						([AUDITTRAIL].[SKF_DATA],[PROFITCENTER].[%PC%],[P_ACCOUNT].[DC_FA_PERC],[FUNC_AREA].[%F_T_FA%],[TIME].[%TIME_SET%],[CONT_COUNTRY].[%CONT_COUNTRY_SET%],[COST_CENTER].[CC_NONE],[ENTITY].[E_NONE],[RPTCURRENCY].[USD],[CATEGORY].[ACTUAL]) *
						([AUDITTRAIL].[PLAN_INPUT],[PROFITCENTER].[%PC%],[FUNC_AREA].[%FA%],[TIME].[%TIME_SET%]),
						 AUDITTRAIL = DUAL_CREDIT, PROFITCENTER = DC99,FUNC_AREA = %F_T_FA%
						)
					*END
				*NEXT
			*NEXT
		*NEXT
	*ENDWHEN
*ENDWHEN 

scn2.jpg (13.8 kB)
scn3.jpg (32.6 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Oct 20, 2016 at 10:34 AM

    Hi Vadim,

    Values are input by user in H13,H14,H15,H16 and saved to server.

    There is a DM Package which will run and do the below calculations and the end result is seen in Rows 19-23.

    User have provision to select multiple values of Profit Center and Functional Area.

    For the current test scenario: PC: DCAT, DCAM and FUNC_AREA = 1100,1590

    Row 19-20 (This section works fine)

    Sum up input values at PC's(DCAT,DCAM) for each specific FUNC_AREA and post negative values

    DCAT,1100(H13) + DCAM,1100(H15) = 200+600 -> Save the value @ DC99,1100(H19) = -800

    DCAT,1590(H14) + DCAM,1590(H16) = 200+300 -> Save the value @ DC99,1590(H20) = -500

    Row 3,4,5 :

    All the input values at multiple PC and multiple Functional Areas has to be accumulated into sub Functional Areas(9150,3090,4250)

    The input values(H3,H14,H15,H16) are then allocated to multiple sub Functional Areas(9150,3090,4250) multiplied with corresponding factors for each PC -FUNC_AREA combination from the FACTORS input form below. Values

    E.g.: Values at H21 for DC99,9150 combinations is calculated as

    (DCAT,1100) *(DCAT,9150) + (DCAT,1590) *(DCAT,9150) +

    (DCAM,1100) *(DCAM,9150) + (DCAM,1590) *(DCAM,9150) = H13*0.1 + H14*0.1 +H15*0.22 +H16*0.22 = 200*0.1 + 200*0.1 +600*0.22 +300*0.22 =238

    However, the result I get in cell H21 is 952 which is 4 times the expected value.

    Data Manager Package Log

    LOG BEGIN TIME:2016-10-20 04:53:53
    FILE:\ROOT\WEBFOLDERS\PLANCONSOL_3M\ADMINAPP\PLANNING_OP\DUAL_CREDIT_OFFSET.LGF
    USER:A6KDFZZ
    APPSET:PLANCONSOL_3M
    APPLICATION:PLANNING_OP
    [INFO] GET_DIM_LIST(): I_APPL_ID="PLANNING_OP", #dimensions=11
    AUDITTRAIL,CATEGORY,CONT_COUNTRY,COST_CENTER,ENTITY,FUNC_AREA,MEASURES,PROFITCENTER,P_ACCOUNT,RPTCURRENCY,TIME
    
    #dim_memberset=10
    AUDITTRAIL:PLAN_INPUT,1 in total.
    CATEGORY:PLAN,1 in total.
    COST_CENTER:CC_NONE,1 in total.
    ENTITY:E_NONE,1 in total.
    P_ACCOUNT:C_NONE,1 in total.
    RPTCURRENCY:USD,1 in total.
    PROFITCENTER:DCAM,DCAT,2 in total.
    FUNC_AREA:1100,1590,3090,4250,9150,5 in total.
    CONT_COUNTRY:US,1 in total.
    TIME:2016.08,1 in total.
    
    REC :-%VALUE%
    
    CALCULATION BEGIN:
    QUERY PROCESSING DATA
    QUERY TIME : 0.00 ms. 4  RECORDS QUERIED OUT.
    QUERY REFERENCE DATA
    CALCULATION TIME IN TOTAL :0.00 ms.
    2  RECORDS ARE GENERATED.
    CALCULATION END.
    
    ENDWHEN ACCUMULATION: 2  RECORDS ARE GENERATED.
    
    DATA TO WRITE BACK:
    AUDITTRAIL	CATEGORY	CONT_COUNTRY	COST_CENTER	ENTITY	FUNC_AREA	PROFITCENTER	P_ACCOUNT	RPTCURRENCY	TIME	SIGNEDDATA
    DUAL_CREDIT	PLAN	US	CC_NONE	E_NONE	1100	DC99	C_NONE	USD	2016.08	- 800.00
    DUAL_CREDIT	PLAN	US	CC_NONE	E_NONE	1590	DC99	C_NONE	USD	2016.08	- 500.00
    2  RECORDS HAVE BEEN WRITTEN BACK.
    WRITING TIME :1.00  ms.
    [INFO] GET_DIM_LIST(): I_APPL_ID="PLANNING_OP", #dimensions=11
    AUDITTRAIL,CATEGORY,CONT_COUNTRY,COST_CENTER,ENTITY,FUNC_AREA,MEASURES,PROFITCENTER,P_ACCOUNT,RPTCURRENCY,TIME
    
    #dim_memberset=10
    AUDITTRAIL:PLAN_INPUT,1 in total.
    CATEGORY:PLAN,1 in total.
    COST_CENTER:CC_NONE,1 in total.
    ENTITY:E_NONE,1 in total.
    P_ACCOUNT:C_NONE,1 in total.
    RPTCURRENCY:USD,1 in total.
    PROFITCENTER:DCAM,DCAT,2 in total.
    FUNC_AREA:1100,1590,3090,4250,9150,5 in total.
    CONT_COUNTRY:US,1 in total.
    TIME:2016.08,1 in total.
    
    REC :-([AUDITTRAIL].[SKF_DATA],[PROFITCENTER].[DCAT],[P_ACCOUNT].[DC_FA_PERC],[FUNC_AREA].[9150],[TIME].[2016.08],[CONT_COUNTRY].[US],[COST_CENTER].[CC_NONE],[ENTITY].[E_NONE],[RPTCURRENCY].[USD],[CATEGORY].[ACTUAL]) *([AUDITTRAIL].[PLAN_INPUT],[PROFITCENTER].[DCAT],[FUNC_AREA].[1100],[TIME].[2016.08])
    REC :-([AUDITTRAIL].[SKF_DATA],[PROFITCENTER].[DCAT],[P_ACCOUNT].[DC_FA_PERC],[FUNC_AREA].[3090],[TIME].[2016.08],[CONT_COUNTRY].[US],[COST_CENTER].[CC_NONE],[ENTITY].[E_NONE],[RPTCURRENCY].[USD],[CATEGORY].[ACTUAL]) *([AUDITTRAIL].[PLAN_INPUT],[PROFITCENTER].[DCAT],[FUNC_AREA].[1100],[TIME].[2016.08])
    REC :-([AUDITTRAIL].[SKF_DATA],[PROFITCENTER].[DCAT],[P_ACCOUNT].[DC_FA_PERC],[FUNC_AREA].[4250],[TIME].[2016.08],[CONT_COUNTRY].[US],[COST_CENTER].[CC_NONE],[ENTITY].[E_NONE],[RPTCURRENCY].[USD],[CATEGORY].[ACTUAL]) *([AUDITTRAIL].[PLAN_INPUT],[PROFITCENTER].[DCAT],[FUNC_AREA].[1100],[TIME].[2016.08])
    REC :-([AUDITTRAIL].[SKF_DATA],[PROFITCENTER].[DCAT],[P_ACCOUNT].[DC_FA_PERC],[FUNC_AREA].[9150],[TIME].[2016.08],[CONT_COUNTRY].[US],[COST_CENTER].[CC_NONE],[ENTITY].[E_NONE],[RPTCURRENCY].[USD],[CATEGORY].[ACTUAL]) *([AUDITTRAIL].[PLAN_INPUT],[PROFITCENTER].[DCAT],[FUNC_AREA].[1590],[TIME].[2016.08])
    REC :-([AUDITTRAIL].[SKF_DATA],[PROFITCENTER].[DCAT],[P_ACCOUNT].[DC_FA_PERC],[FUNC_AREA].[3090],[TIME].[2016.08],[CONT_COUNTRY].[US],[COST_CENTER].[CC_NONE],[ENTITY].[E_NONE],[RPTCURRENCY].[USD],[CATEGORY].[ACTUAL]) *([AUDITTRAIL].[PLAN_INPUT],[PROFITCENTER].[DCAT],[FUNC_AREA].[1590],[TIME].[2016.08])
    REC :-([AUDITTRAIL].[SKF_DATA],[PROFITCENTER].[DCAT],[P_ACCOUNT].[DC_FA_PERC],[FUNC_AREA].[4250],[TIME].[2016.08],[CONT_COUNTRY].[US],[COST_CENTER].[CC_NONE],[ENTITY].[E_NONE],[RPTCURRENCY].[USD],[CATEGORY].[ACTUAL]) *([AUDITTRAIL].[PLAN_INPUT],[PROFITCENTER].[DCAT],[FUNC_AREA].[1590],[TIME].[2016.08])
    REC :-([AUDITTRAIL].[SKF_DATA],[PROFITCENTER].[DCAM],[P_ACCOUNT].[DC_FA_PERC],[FUNC_AREA].[9150],[TIME].[2016.08],[CONT_COUNTRY].[US],[COST_CENTER].[CC_NONE],[ENTITY].[E_NONE],[RPTCURRENCY].[USD],[CATEGORY].[ACTUAL]) *([AUDITTRAIL].[PLAN_INPUT],[PROFITCENTER].[DCAM],[FUNC_AREA].[1100],[TIME].[2016.08])
    REC :-([AUDITTRAIL].[SKF_DATA],[PROFITCENTER].[DCAM],[P_ACCOUNT].[DC_FA_PERC],[FUNC_AREA].[3090],[TIME].[2016.08],[CONT_COUNTRY].[US],[COST_CENTER].[CC_NONE],[ENTITY].[E_NONE],[RPTCURRENCY].[USD],[CATEGORY].[ACTUAL]) *([AUDITTRAIL].[PLAN_INPUT],[PROFITCENTER].[DCAM],[FUNC_AREA].[1100],[TIME].[2016.08])
    REC :-([AUDITTRAIL].[SKF_DATA],[PROFITCENTER].[DCAM],[P_ACCOUNT].[DC_FA_PERC],[FUNC_AREA].[4250],[TIME].[2016.08],[CONT_COUNTRY].[US],[COST_CENTER].[CC_NONE],[ENTITY].[E_NONE],[RPTCURRENCY].[USD],[CATEGORY].[ACTUAL]) *([AUDITTRAIL].[PLAN_INPUT],[PROFITCENTER].[DCAM],[FUNC_AREA].[1100],[TIME].[2016.08])
    REC :-([AUDITTRAIL].[SKF_DATA],[PROFITCENTER].[DCAM],[P_ACCOUNT].[DC_FA_PERC],[FUNC_AREA].[9150],[TIME].[2016.08],[CONT_COUNTRY].[US],[COST_CENTER].[CC_NONE],[ENTITY].[E_NONE],[RPTCURRENCY].[USD],[CATEGORY].[ACTUAL]) *([AUDITTRAIL].[PLAN_INPUT],[PROFITCENTER].[DCAM],[FUNC_AREA].[1590],[TIME].[2016.08])
    REC :-([AUDITTRAIL].[SKF_DATA],[PROFITCENTER].[DCAM],[P_ACCOUNT].[DC_FA_PERC],[FUNC_AREA].[3090],[TIME].[2016.08],[CONT_COUNTRY].[US],[COST_CENTER].[CC_NONE],[ENTITY].[E_NONE],[RPTCURRENCY].[USD],[CATEGORY].[ACTUAL]) *([AUDITTRAIL].[PLAN_INPUT],[PROFITCENTER].[DCAM],[FUNC_AREA].[1590],[TIME].[2016.08])
    REC :-([AUDITTRAIL].[SKF_DATA],[PROFITCENTER].[DCAM],[P_ACCOUNT].[DC_FA_PERC],[FUNC_AREA].[4250],[TIME].[2016.08],[CONT_COUNTRY].[US],[COST_CENTER].[CC_NONE],[ENTITY].[E_NONE],[RPTCURRENCY].[USD],[CATEGORY].[ACTUAL]) *([AUDITTRAIL].[PLAN_INPUT],[PROFITCENTER].[DCAM],[FUNC_AREA].[1590],[TIME].[2016.08])
    
    CALCULATION BEGIN:
    QUERY PROCESSING DATA
    QUERY TIME : 0.00 ms. 4  RECORDS QUERIED OUT.
    QUERY REFERENCE DATA
    QUERY TIME : 0.00 ms. 10  RECORDS QUERIED OUT.
    CALCULATION TIME IN TOTAL :1.00 ms.
    3  RECORDS ARE GENERATED.
    CALCULATION END.
    
    ENDWHEN ACCUMULATION: 3  RECORDS ARE GENERATED.
    
    DATA TO WRITE BACK:
    AUDITTRAIL	CATEGORY	CONT_COUNTRY	COST_CENTER	ENTITY	FUNC_AREA	PROFITCENTER	P_ACCOUNT	RPTCURRENCY	TIME	SIGNEDDATA
    DUAL_CREDIT	PLAN	US	CC_NONE	E_NONE	9150	DC99	C_NONE	USD	2016.08	- 952.00
    DUAL_CREDIT	PLAN	US	CC_NONE	E_NONE	3090	DC99	C_NONE	USD	2016.08	- 1112.00
    DUAL_CREDIT	PLAN	US	CC_NONE	E_NONE	4250	DC99	C_NONE	USD	2016.08	- 1272.00
    3  RECORDS HAVE BEEN WRITTEN BACK.
    WRITING TIME :0.00  ms.
    
    SCRIPT RUNNING TIME IN TOTAL:3.00 s.
    LOG END TIME:2016-10-20 04:53:56
    
    
    Add comment
    10|10000 characters needed characters exceeded

    • Hi Vadim,

      The logic is the same as in the excel screenshot.

      Users will input credit value for different PC'(DCAT, DCAM) for a particular functional area(1100)

      These credit value has to be distributed to different functional areas(9150, 4250, 3090) based on % values, which are the factors maintained in the system. DCAT have 10%,20%,30% maintained for 9150, 4250, 3090 and DCAM have 22% maintained for 3 of them. This was shown in the screenshot in the beginning of the post.

      For the current scenario, I hardcoded DCAM in the For Loop

      Multiply the input Credit value for DCAM in Row3 with the corresponding FACTOR value in Row 6 = 200*0.22 = 44

      However output I get after running DM Package is 88

  • Oct 21, 2016 at 12:56 PM

    Please read my answers and look on the sample here: https://archive.sap.com/discussions/thread/3948352

    This is the correct way to perform allocations using script logic!

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Vadim,

      I am deleting yestrday's post as requested. W.r.t to this original post of doubling up values, the issue is resolved. However, I need to still find an option of writing down to a dimension member defined by the dimension property dynamically. Here are the details:

      DIMENSIONS

      FUNCTIONAL AREA : Source FA :1100, 1590

      Target FA : 3090,4250,9150

      AUDITTRAIL: PLAN_INPUT(for input), DUAL_CREDIT(for output), SKF_DATA(factors)

      PROFITCENTER : Source PC's : 1080, 2470, 6100

      Target PC's : DCAD, DCAM, DC16, DC34

      PROFITCENTER has a property DC_MSC_PC1 for handling the Target PC's.

      SCENARIO

      User inputs credit value for the 3 Source PC for multiple Functional Areas(FA). For each source FA, the data is then distributed to a fixed set 3 Target FA's by multiplying the input data with appropriate FACTOR values. The output is then saved at the 3 Target FA's(fixed set) and Target PC(retrieved by property)

      I need to loop throught the Source PC's(1080,2470,6100) and write down to the respective Target PC.

      For 2470,6100 we have same Target PC and then we need to add up the values. However, when we use For Loop and within it WHEN/ENDWHEN, it will overwrite the values.
      Is there a way I can accumulate the values without overwriting....Alternate approach.

      INPUT

      COSTCENTER, ENTITY, ACCOUNT, COUNTRY, TIME, FUNC_AREA, AUDITTRAIL, CATEGORY, RPTCURRENCY,SIGNEDDATA

      CC_NONE, E_NONE, C_NONE, 1080, KR, 2016.08, 1100, PLAN_INPUT, PLAN, USD, 200

      CC_NONE, E_NONE, C_NONE, 2470, KR, 2016.08, 1100, PLAN_INPUT, PLAN, USD, 400

      CC_NONE, E_NONE, C_NONE, 6100, KR, 2016.08, 1100, PLAN_INPUT, PLAN, USD, 600

      OUTPUT

      CC_NONE, E_NONE, C_NONE, DC16, KR, 2016.08, 3090, DUAL_CREDIT, PLAN, USD

      CC_NONE, E_NONE, C_NONE, DC16, KR, 2016.08, 4250, DUAL_CREDIT, PLAN, USD

      CC_NONE, E_NONE, C_NONE, DC16, KR, 2016.08, 9150, DUAL_CREDIT, PLAN, USD

      CC_NONE, E_NONE, C_NONE, DC34, KR, 2016.08, 3090, DUAL_CREDIT, PLAN, USD

      CC_NONE, E_NONE, C_NONE, DC34, KR, 2016.08, 4250, DUAL_CREDIT, PLAN, USD

      CC_NONE, E_NONE, C_NONE, DC34, KR, 2016.08, 9150, DUAL_CREDIT, PLAN, USD