Skip to Content

Script Logic : Writing down to a destination using dimension property value

Hi All,

Need help in script logic, where we need to write down values to a destination profit center which is basically the property value of the Source Profit Center.

As seen below, for a particular FUNC_AREA(1100), we are inputing credit value against multiple ProfitCenter. On the right hand side, we can see multiplication factor for different scenario(DC_CC_PC, DC_MSC_PC1,DC_MSC_PC2)

DC_CC_PC is a Property of PC dimension which contains the Target PC values.

For Source PC '1080', DC_CC_PC value is 'DCAD'

For Source PC '2470', DC_CC_PC value is 'DCAM' and so on.

The below code works fine for Scenario 1 section. However, in the second scenario(DC_MSC_PC1) we have a requirement, where the destination PC identified by Property: DC_MSC_PC1 is the same for both Source PC's(2470, 6100) as DC34(See the image above) So we need the values to get added up in DC34 for both Source PC's. However, the above code is having a WHEN/ENDWHEN structure inside the FOR LOOP and hence only the last WHEN/END WHEN scenario for 6100 will prevail as it will overwrite those by 2470.

DM Package Results:

Scenario 1 :

DCAM KR 1100 400

DCJV KR 1100 600

DCAD KR 1100 200

Scenario 2 :

DC16 KR 1100 -200

DC34 KR 1100 -400 (over written by next value)

DC34 KR 1100 -300

Expected Value:

Scenario 2 :

DC16 KR 1100 -200

DC34 KR 1100 -700 Calc : 400*(-1) + 600*(-0.5)

*SELECT(%T_CTRY%,[DC_MIISC],CONT_COUNTRY,"[ID] = '%CONT_COUNTRY_SET%'")     
*SELECT(%T_PC1%,[ID],PROFITCENTER,"[DC_PC] = 'DCUS'")                       
*SELECT(%T_PC2%,[ID],PROFITCENTER,"[DC_PC] = 'DC'")                         
*SELECT(%T_CC_PC%,[DC_CC_PC],PROFITCENTER,"[ID] = '%T_PC2%'")               
*SELECT(%T_MSC_PC1%,[DC_MSC_PC1],PROFITCENTER,"[ID] = '%T_PC2%'")                 
*XDIM_MEMBERSET PROFITCENTER = %T_PC1%, %T_PC2%
*XDIM_MEMBERSET CONT_COUNTRY = %CONT_COUNTRY_SET%
*XDIM_MEMBERSET TIME = %TIME_SET%
*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 FUNC_AREA AS %T_FA% = 3090,4250,9150
*XDIM_MEMBERSET FUNC_AREA AS %NSAL% = 1100   //Can contain multiple values 
*XDIM_MEMBERSET FUNC_AREA = %NSAL%,%T_FA%

//Scenario 1 Code
*FOR %F_PC2_1% = %T_PC2%
	*XDIM_MEMBERSET PROFITCENTER = %F_PC2_1%
	*XDIM_MEMBERSET FUNC_AREA = 1100
	*WHEN PROFITCENTER
		*IS %F_PC2_1%
		*BEGIN
			*REC
			(
			EXPRESSION = 
			([AUDITTRAIL].[PLAN_INPUT],[PROFITCENTER].[%F_PC2_1%],[FUNC_AREA].[1100],[TIME].[%TIME_SET%])*
			([AUDITTRAIL].[SKF_DATA],[P_ACCOUNT].[DC_PERC_CTRY1],[PROFITCENTER].[%F_PC2_1%],[TIME].[%TIME_SET%],[CONT_COUNTRY].[NOCOUNTRY],[CATEGORY].[ACTUAL],[FUNC_AREA].[F_NONE]),
			PROFITCENTER = PROFITCENTER.DC_CC_PC, AUDITTRAIL = DUAL_CREDIT
			)
		*END
	*ENDWHEN
*NEXT

//Scenario 2 Code

*FOR %F_PC2_3% = %T_PC2%
	*WHEN PROFITCENTER
		*XDIM_MEMBERSET PROFITCENTER = %F_PC2_3%
		*XDIM_MEMBERSET FUNC_AREA = 1100
		*IS %F_PC2_3%
		*BEGIN
			*REC
			(
			EXPRESSION = -
			([AUDITTRAIL].[PLAN_INPUT],[PROFITCENTER].[%F_PC2_3%],[FUNC_AREA].[1100],[TIME].[%TIME_SET%])*
			([AUDITTRAIL].[SKF_DATA],[P_ACCOUNT].[DC_PERC_MSC1],[PROFITCENTER].[%F_PC2_3%],[TIME].[%TIME_SET%],[CONT_COUNTRY].[NOCOUNTRY],[CATEGORY].[ACTUAL],[FUNC_AREA].[F_NONE]),
			PROFITCENTER = PROFITCENTER.DC_MSC_PC1, AUDITTRAIL = DUAL_CREDIT,CONT_COUNTRY = %T_CTRY% 
			)
		*END
	*ENDWHEN
*NEXT
   


scn-111.jpg (34.5 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Nov 02, 2016 at 06:24 PM

    Hi Arun,

    You are asking approximately the same question second time and the common solution is to ask moderator to remove this question.

    The main issue is that your explanation is absolutely unclear, Also instead of asking about some specific logic you are trying to present some complex script and ask what is wrong.

    Please split your requirements into some steps and explain the logic of each step. Don't try to solve everything at once.

    Add comment
    10|10000 characters needed characters exceeded