Script Logic to calculate assumptions

[rogerio_stein1]

Hi, I would like any tips or suggestions to perform a calculation in SAP BPC using script logic. I have a payroll expense application with the following dimensions: employee, account, position, cost center, category, and time. The model has the employee list, summarized as follows:

EmployeeID Position Account CostCenter Category Time Value
Emp00001 Position01 Acc_CurrentSalary CC_0001 Actual 2020.01 1000
Emp00002 Position01 Acc_CurrentSalary CC_0001 Actual 2020.01 2000
Emp00003 Position01 Acc_CurrentSalary CC_0002 Actual 2020.01 3000

And in the same model I have assumptions such as percent of readjustment. All of these assumptions are defined by cost center and should apply to all employees who are in the same cost center. For example:

EmployeeID Position Account CostCenter Category Time Value
EmpDummy PositionDummy Acc_Readjustment CC_0001 Budget 2020.01 0,04
EmpDummy PositionDummy Acc_Readjustment CC_0001 Budget 2020.02 0,04
EmpDummy PositionDummy Acc_Readjustment CC_0001 Budget 2020.03 0,04
EmpDummy PositionDummy Acc_Readjustment CC_0001 Budget 2020.04 0,04
....
EmpDummy PositionDummy Acc_Readjustment CC_0001 Budget 2020.11 0,04
EmpDummy PositionDummy Acc_Readjustment CC_0001 Budget 2020.12 0,04
EmpDummy PositionDummy Acc_Readjustment CC_0002 Budget 2020.01 0,05
EmpDummy PositionDummy Acc_Readjustment CC_0002 Budget 2020.02 0,05
....
EmpDummy PositionDummy Acc_Readjustment CC_0002 Budget 2020.12 0,05

I need to apply the readjustment percentage to each employee according to their respective cost center assumptions and save to an account Acc_NewSalary. For example:

EmployeeID Position Account CostCenter Category Time Value
Emp00001 Position01 Acc_CurrentSalary CC_0001 Budget 2020.01 1000 * 0,04

I tried to do it using RUNALLOCATION but the volume is too large and causes a dump. I have no idea how to do it and if anyone has any suggestions I would be very grateful.

[former_member186338]

Please post your RUNALLOCATION code to start with something.

[former_member186338]

P.S. And not clear about TIME... Do you have values only in ACTUAL 2020.01?

[former_member186338 · Accepted solution]

Alternative without RUNALLOCATION (can be slower, but you have to test and compare):

*XDIM_MEMBERSET CostCenter=CC_0001
*XDIM_MEMBERSET Time = 2020.01
*XDIM_MEMBERSET EmployeeID = BAS(Emp_Total)
*XDIM_MEMBERSET Position = BAS(Position_Total)
*XDIM_MEMBERSET Account = Acc_CurrentSalary
*XDIM_MEMBERSET Category = Actual

*WHEN ACCOUNT
*IS *
*REC(EXPRESSION=%VALUE%*([EmployeeID].[EmpDummy],[Position].[PositionDummy],[Account].[Acc_Readjustment],[Category].[Budget]),Category=Budget)
*ENDWHEN

[rogerio_stein1]

Thanks a lot for the help. Both worked. however the code using the WHEN structure was much faster. I imagined it would be the other way around.

Furthermore,with RUNALLOCATION I could not execute the script for all cost centers simultaneously because of the data volume and would have to place the command in a FOR/ NEXT structure. With WHEN structure, I calculate all cost centers in one single step. Another advantage is that I need to perform several types of calculations in addition to readjustment. In the WHEN block I do everything in one execution.

[former_member186338]

Try the following script (start with single CostCenter):

*XDIM_MEMBERSET CostCenter=CC_0001
*XDIM_MEMBERSET Time = 2020.01

*RUNALLOCATION
*FACTOR = USING
*DIM EmployeeID WHAT = EmpDummy; WHERE = BAS(Emp_Total); USING = BAS(Emp_Total)
*DIM Position WHAT = PositionDummy; WHERE = BAS(Position_Total); USING = BAS(Position_Total)
*DIM Account WHAT = Acc_Readjustment; WHERE = Acc_CurrentSalary; USING = Acc_CurrentSalary
*DIM Category WHAT = Budget; WHERE = Budget; USING = Actual
*ENDALLOCATION

[rogerio_stein1]

Yes, you're right.

This is one of the attempts I made. Would you have any suggestions for resolving this issue? I need to get the value of the assumption for each cost center and apply to all employees of that same cost center. In your experience, is the best way to use RUNALLOCATION or do you have a better method?

[rogerio_stein1]

Hi Vadim, thank you for your answer. Sorry, I have values in all months for ACTUAL 2020 and these values are replicated in BUDGET category for each month.

My Script Logic:

*RUNALLOCATION
*FACTOR =
*DIM EmployeeID WHAT = EmpDummt; WHERE = BAS(Emp_Total);
*DIM Position WHAT = PositionDummy; WHERE = BAS(Position_Total);
*DIM Account WHAT = Acc_Readjustment; WHERE = Acc_Readjustment;
*ENDALLOCATION

*COMMIT

Time and category dimensions are scoped by data manager

[former_member186338]

Sorry, but your script is absolutely meaningless...