Using script logic to multiply data at 2 different level and write back to detail level

[Former Member]

Hi All,

I am hoping for some assistance with a script logic calculation that I am working on. Scenario is : We have a Work Force Planning model which is used to calculate the Employee Cost at a detail level during the budget process.

For the Forecast, the basic idea is to flex the Employee cost by multiplying it with a % increase which is input at a higher level than what the employee cost is at.

i.e. The % increase is input at a valid Cost Centre and Functional area only (as seen in the attached snapshot)

I need to Multiply A * B to get C

The destination record C needs to get its Cost Centre and Functional Area from B and rest of the dimension members from the A record.

Currently I am using 2 for loops to achieve this as seen in the code. This is throwing up a out of memory error (which i expected to)

BPC version is 10.1 NW

Quite sure Allocation wont be an option due to multiple source members.

Would appreciate some suggestions / pointers

//Scope the TIME horizon
*SELECT(%TIME_HOR001%,HORIZON_Y1,VERSION,"ID = '%VERSION_SET%'") //- 2017.Jan to 2017.Dec
*SELECT(%TIME_HOR002%,HORIZON_Y2,VERSION,"ID = '%VERSION_SET%'") //- 2018.Jan to 2018.Dec


*SELECT(%A_TYPE%,ID,ACT_TYPE,"IS_SELECTABLE = 'Y'")  //- 1011,2011,3011 etc
*SELECT(%CC_R%,ID,COSTCENTRE,"CCATEGY = 'NSP','CSP','AM','AO'") //-CC_1,CC_2,CC_3 .. CC_1300
*SELECT(%FUNC_A%,ID,FUNC_AREA,"CALC = 'N'") //- AC0054,AB0052 etc


//Scope dimension members
*XDIM_MEMBERSET VERSION = %VERSION_SET%
*XDIM_MEMBERSET TIME = %TIME_HOR001%,%TIME_HOR002%
*XDIM_MEMBERSET AUDITID = CALC_LABOUR,GLB_FCT_PERC
*XDIM_MEMBERSET PROFIT_CTR = NO_PC
*XDIM_MEMBERSET ACCOUNT = BAS(OC_TOT_EMP_COST),SKF_FCT_PERC
*XDIM_MEMBERSET ACT_TYPE = %A_TYPE%,NO_ACTTYPE
*XDIM_MEMBERSET CC_SENDER = BAS(CS_ALLCC) 
*XDIM_MEMBERSET EMPLOYEE = BAS(ALL_EMP),NO_EMPLOYEE
*XDIM_MEMBERSET POSITIONS = BAS(TOT_POS),NO_POS
*XDIM_MEMBERSET COSTCENTRE = CC_NONE,%CC_R%
*XDIM_MEMBERSET FUNC_AREA = NO_FA,%FUNC_A%


// =  =  =  =  = 
*FOR %C% = %CC_R%
 *FOR %F% = %FUNC_A%
	*WHEN AUDITID
	 *IS CALC_LABOUR
	  *WHEN ACCOUNT
	   *IS <> SKF_FCT_PERC
	    *WHEN ACT_TYPE
	     *IS %A_TYPE%
	      *WHEN EMPLOYEE
	       *IS <> NO_EMPLOYEE
	        *WHEN POSITIONS
	         *IS NO_POS
	          *WHEN COSTCENTRE
	           *IS CC_NONE
	            *WHEN FUNC_AREA
	             *IS <> NO_FA
			*REC(EXPRESSION = %VALUE%*([AUDITID].[GLB_FCT_PERC],[ACCOUNT].[SKF_FCT_PERC],[ACT_TYPE].[NO_ACTTYPE],[EMPLOYEE].[NO_EMPLOYEE],[COSTCENTRE].[%C%],[FUNC_AREA].[%F%],[POSITIONS].[NO_POS]), COSTCENTRE = %C%,FUNC_AREA = %F%,AUDITID = FCST_INTERIM_1)
		    *ENDWHEN
		   *ENDWHEN
	          *ENDWHEN
	        *ENDWHEN
	      *ENDWHEN
	    *ENDWHEN
	  *ENDWHEN
    *NEXT
*NEXT
working.png

[former_member186338]

"I believe Many to Many allocation isnt supported in BPC NW" - wrong! Search better 🙂

https://launchpad.support.sap.com/#/notes/1903167/E

[Former Member]

Hi Vadim,

This works like a charm 🙂 - and I have learnt something new. This keyword is going to definitely be very handy *DIM_NONAGGR

thanks again for your help on this.

Regards,

Nilesh D

[former_member186338]

And why do you select your answer as correct???

[Former Member]

Sorry I meant to say the answer you posted was correct and my question was answered - Sorry about that

[former_member186338]

Then correct it, now it looks strange!

Unaccept incorrect answer and accept the correct one!

[Former Member]

Hi Vadim, Sorry how do I accept your answer as the correct one? I dont see any action option there. Please let me know

[former_member186338]

Under my answer starting with "Something like: <code>"

You will find "Accept"

[Former Member]

Hi Vadim,

Thank you for this but I believe Many to Many allocation isnt supported in BPC NW and the result data is incorrect. I have experienced this in the past and have read on a few blogs on this forum.

Please correct me if wrong.

I will anyways give this a try.

Regards,

Nilesh D

[former_member186338]

Something like:

*SELECT(%TIME_HOR001%,HORIZON_Y1,VERSION,"ID = '%VERSION_SET%'") //- 2017.Jan to 2017.Dec
*SELECT(%TIME_HOR002%,HORIZON_Y2,VERSION,"ID = '%VERSION_SET%'") //- 2018.Jan to 2018.Dec
*SELECT(%A_TYPE%,ID,ACT_TYPE,"IS_SELECTABLE = 'Y'")  //- 1011,2011,3011 etc
*SELECT(%CC_R%,ID,COSTCENTRE,"CCATEGY = 'NSP','CSP','AM','AO'") //-CC_1,CC_2,CC_3 .. CC_1300
*SELECT(%FUNC_A%,ID,FUNC_AREA,CALC=N AND ID<>NO_FA) //- AC0054,AB0052 etc BUT NOT NO_FA!!!!!!!!

*XDIM_MEMBERSET VERSION = %VERSION_SET%
*XDIM_MEMBERSET TIME = %TIME_HOR001%,%TIME_HOR002%
*XDIM_MEMBERSET PROFIT_CTR = NO_PC
*XDIM_MEMBERSET CC_SENDER = BAS(CS_ALLCC)

*RUNALLOCATION
*FACTOR=USING
*DIM COSTCENTRE WHAT=CC_NONE; WHERE=%CC_R%; USING=%CC_R%
*DIM FUNC_AREA WHAT=NO_FA; WHERE=%FUNC_A%; USING=%FUNC_A%
*DIM AUDITID WHAT=CALC_LABOUR; WHERE=FCST_INTERIM_1; USING=GLB_FCT_PERC
*DIM_NONAGGR ACCOUNT WHAT=BAS(OC_TOT_EMP_COST); WHERE=BAS(OC_TOT_EMP_COST); USING=SKF_FCT_PERC
*DIM_NONAGGR ACT_TYPE WHAT=%A_TYPE%; WHERE=%A_TYPE%; USING=NO_ACTTYPE
*DIM_NONAGGR EMPLOYEE WHAT=BAS(ALL_EMP); WHERE=BAS(ALL_EMP); USING=NO_EMPLOYEE
*DIM_NONAGGR POSITIONS WHAT=BAS(TOT_POS); WHERE=BAS(TOT_POS); USING=NO_POS
*ENDALLOCATION

Hope that NO_POS is not in BAS(TOT_POS) and NO_EMPLOYEE is not in BAS(ALL_EMP)

[Former Member]

working.png

Hi Vadim,

I have attached a screen shot.

Section A is my employee cost

Section B is the % to be multiplied with

Section C is the destination slice

Hope this helps.

Thank you for your time.

Regards,

Nilesh D

[former_member186338]

Also not clear: I see in your code:

*WHEN COSTCENTRE
	           *IS CC_NONE

Is the data to be multiplied is in CC_NONE?

Can you provide a report with data to be multiplied and percent?

[Former Member]

Hi Vadim,

Using allocations I would have to use multiple for loops for EMPLOYEE,POSITIONS,ACT_TYPE,ACCOUNT since there will be multiple members in WHAT region with 1 member in the USING region.

Am I on the correct lines?

Thanks for the pointers on this.

Regards,

nilesh D

[former_member186338]

No! You have

*DIM_NONAGGR instead of *DIM for this case! No FOR/NEXT loops!

[former_member186338]

2800*300=840000 - for sure you will get out of memory!

Try allocation!

[Former Member]

Hi Vadim,

Sorry if I wasnt clear but you are spot on till here.

Valid CC's = 2800

Func Areas = 300

Thank you very much for assisting on this.

(I have tried with the 2 for loops and using *XDIM_MAXMEMBERS = 10 but I still get memory errors)

Regards,

Nilesh D

[former_member186338]

Not easy to understand but I will try:

PERCENT is entered:

For each valid combination of COSTCENTRE and FUNC_AREA

with:

AUDITID=GLB_FCT_PERC
ACCOUNT=SKF_FCT_PERC
ACT_TYPE=NO_ACTTYPE
EMPLOYEE=NO_EMPLOYEE
POSITIONS=NO_POS

How many COSTCENTRE and FUNC_AREA ID's you have?

.

P.S.

Common dimensions are:

*XDIM_MEMBERSET VERSION = %VERSION_SET%
*XDIM_MEMBERSET TIME = %TIME_HOR001%,%TIME_HOR002%
*XDIM_MEMBERSET PROFIT_CTR = NO_PC
*XDIM_MEMBERSET CC_SENDER = BAS(CS_ALLCC)

Data to be multiplied:

AUDITID = CALC_LABOUR
ACCOUNT = BAS(OC_TOT_EMP_COST)
ACT_TYPE = %A_TYPE%
EMPLOYEE = BAS(ALL_EMP)
POSITIONS = BAS(TOT_POS)

Is it correct?