Community
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

Script Logic: To post data to dimension member property of property

Go to solution
Former Member

on ‎2020 Jan 29 7:29 AM

0 Likes
1,345

Hello Experts,

Product Version:-

CPMBPC 810 0015 SAPK-81015INCPMBPC (BPC10.1 SP 15)

SAP_BW 740 0020 SAPKW74020

Netweaver: 740

In Head-Count Model we have the dimensions HC_Account (A), CATEGORY (C), COST CENTER(User-def), FUNCTIONAL AREA (User-def), TIME(T), ENTITY (E), HC_Employee (user defined)

In the HC_Account (A) dimension there are members: HCOUNT, BONUS, BONUS_PCT, BONUS_PAY

In COSTCENTER dimension we have a property called BONUS_CC (Bonus Cost Center) and Functional Area.

We want to calculate BONUS via Data Manager Package. User provides the COSTCENTER, CATEGORY,TIME and BONUS is posted to BONUS_CC.

Script:

*SELECT(%YR%,"YEAR","CATEGORY",[ID=%CATEGORY_SET%])

//Use the year variable above to create current year time periods. 

*SELECT(%TIM%,"ID","TIME",[YEAR=%YR%])

//Variable for Bonus Costcenters

*SELECT(%BC%,[BONUS_CC],"COSTCENTER","[ID]=%COSTCENTER_SET%")

//Scoping of Cost centers with bonus cost centers populated. Scoping of Entity, Functional Area and 
Profit Center based on these Cost Centers. 

*SELECT(%CSTCTR%,"ID","COSTCENTER","[ID]=%COSTCENTER_SET% AND [BONUS_CC]<>""")
*SELECT(%ENT%,[ENTIY], "COSTCENTER","[ID]=%CSTCTR%")
*SELECT(%FA%,[FUNC_AREA],"COSTCENTER","[ID]=%CSTCTR%")
*SELECT(%PCTR%,[PROFIT_CENTER],"COSTCENTER","[ID]=%CSTCTR%")

*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%
*XDIM_ADDMEMBERSET CATEGORY= ACTUAL
*XDIM_MEMBERSET TIME =%TIMESET%,
*XDIM_MEMBERSETCOSTCENTER=%CSTCTR%
*XDIM_MEMBERSET HC_EMPLOYEE= BAS(EMPLOYEE)
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_MEMBERSET ENTITY=%ENT%
*XDIM_MEMBERSET FUNCTIONALAREA=%FA%
*XDIM_MEMBERSET PROFITCENTER=%PCTR%
*XDIM_ADDMEMBERSET HC_ACCOUNT = HCOUNT

//Bonus calculation //*FOR %CCTR%=%COSTCTR%
*WHEN HC_ACCOUNT
*IS HCOUNT
*WHEN AUDITTRAIL
*IS INPUT
*WHEN CATEGORY
*IS %CATEGORY_SET%
*WHEN COSTCENTER
*IS %CSTCTR%
*WHEN RPTCURRENCY
*IS LC
*WHEN FUNCTIONALAREA
*IS %FA%
// //BONUS CALC 
*REC(EXPRESSION=(%VALUE%>0)*(([HC_ACCOUNT].[6100000],[AUDITTRAIL].[CALC],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PCT],[AUDITTRAIL].[INPUT],[TIME].[%YR%.INP],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PAY],[JOB_CODE].[NO_JOB_CODE],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])),HC_ACCOUNT=BONUS,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA=COSTCENTER.FUNC_AREA)

*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*COMMIT

Dimension member Mapping Example: Cost Center (200) has BONUS_CC (500) and Functional Area (2)

Cost Center dimension (500) {which is also a BONUS_CC property of COSTCENTER dimension} has Functional Area (5)

Data Manager Prompts: Costcenter is 200 (which has a mapping to Functional Area 2 and BONUS_CC 500)

Results: Bonus is posted to BONUS_CC(500) and Functional Area (2)

Expected Result: To post BONUS to BONUS_CC (500) and Functional Area mapped to BONUS_CC which 5.

Regards,

Richa Pathak

Know the answer?

Help others by sharing your knowledge.

Answer

Need more details?

Request clarification before answering.

Show replies
Show replies
former_member186338
Active Contributor
‎2020 Jan 29 7:35 AM
0 Likes

Better, but please provide sample of initial data in the model and expected results.

former_member186338
Active Contributor
‎2020 Jan 29 7:38 AM
0 Likes

P.S. In your script it's useless to repeat in *WHEN *IS scope that is already defined by *XDIM_MEMBERSET

former_member186338
Active Contributor
‎2020 Jan 29 8:02 AM
0 Likes

P.P.S.

And what do you mean by:

*XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%

*XDIM_ADDMEMBERSET CATEGORY= ACTUAL //???????????????????????
...
*WHEN CATEGORY
*IS %CATEGORY_SET%

former_member186338
Active Contributor
‎2020 Jan 29 8:04 AM
0 Likes

Sorry, but unable to understand

Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
‎2020 Jan 29 8:25 PM
0 Likes

Yes, BONUS_FA property of COSTCENTER is the only solution!

But the script has a lot of other issues!

Look on the corrected code:

//%COSTCENTER_SET%=CC1,CC2 - both with not empty BONUS_CC
*SELECT(%CSTCTR%,"ID","COSTCENTER","[ID]=%COSTCENTER_SET% AND [BONUS_CC]<>""") //%CSTCTR%=CC1,CC2
*SELECT(%ENT%,[ENTITY],"COSTCENTER","[ID]=%CSTCTR%") //%ENT%=E1,E2
*SELECT(%FA%,[FUNC_AREA],"COSTCENTER","[ID]=%CSTCTR%") //%FA%=FA1,FA2
*SELECT(%PCTR%,[PROFIT_CENTER],"COSTCENTER","[ID]=%CSTCTR%") //%PCTR%=PC1,PC2

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET% //single CATEGORY member
*XDIM_MEMBERSET TIME = %TIMESET% //number of members calculated from single CATEGORY member
*XDIM_MEMBERSET COSTCENTER=%CSTCTR% //CC1,CC2
*XDIM_MEMBERSET ENTITY=%ENT% //E1,E2
*XDIM_MEMBERSET FUNCTIONALAREA=%FA% //FA1,FA2
*XDIM_MEMBERSET PROFITCENTER=%PCTR% //PC1,PC2
//Result - all possible combinations will be scoped: CC1,E1,FA1,PC1; CC1,E2,FA1,PC1; CC1,E1,FA2,CC1....
//Hope that only valid combinations have some value in HCOUNT
*XDIM_MEMBERSET HC_EMPLOYEE = BAS(EMPLOYEE)
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_MEMBERSET HC_ACCOUNT = HCOUNT
*XDIM_MEMBERSET AUDITTRAIL=INPUT

*WHEN HC_ACCOUNT
*IS *
*BEGIN
*REC(EXPRESSION=(%VALUE%>0) ? ([HC_ACCOUNT].[6100000],[AUDITTRAIL].[CALC])*
([HC_ACCOUNT].[BONUS_PCT],[TIME].[%YR%.INP])*
([HC_ACCOUNT].[BONUS_PAY],[JOB_CODE].[NO_JOB_CODE],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE])
: 0,
HC_ACCOUNT=6101300,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA=COSTCENTER.BONUS_FA)
*END
*ENDWHEN
Show replies
Show replies

Answers (7)

Answers (7)

Former Member
‎2020 Jan 30 3:32 AM
0 Likes

Thank you so much Vadim!!! I very much appreciate your help and willingness to share the knowledge. I will definitely now start working on optimizing the script.

Regards,

Richa Pathak

Show replies
Show replies
Former Member
‎2020 Jan 29 12:54 PM
0 Likes

One of the solution I can think of is to create an additional property called Bouns_Functional Area in Cost Center dimension.

Show replies
Show replies
Former Member
‎2020 Jan 29 12:51 PM
0 Likes

My apologies. I revisited this. User can select multiple CostCenter but Single Category. %timeset% has been used in other scripts which are executed post calculating Bonus. This script is one of the multiple scripts called one by one through data manager package.

//Use YEAR properties in Category dimension to define year values. 
*SELECT(%YR%,"YEAR","CATEGORY",[ID=%CATEGORY_SET%])
*SELECT(%YR1%,"YEAR1","CATEGORY",[ID=%CATEGORY_SET%])
*SELECT(%YR2%,"YEAR2","CATEGORY",[ID=%CATEGORY_SET%])
*SELECT(%YR3%,"YEAR3","CATEGORY",[ID=%CATEGORY_SET%])
*SELECT(%YR4%,"YEAR4","CATEGORY",[ID=%CATEGORY_SET%])
*SELECT(%YR5%,"YEAR5","CATEGORY",[ID=%CATEGORY_SET%])


//Use the year variable above to create current year time periods. 
*SELECT(%TIM%,"ID","TIME",[YEAR=%YR%])
*SELECT(%SMNTH%, [STARTMNTH], CATEGORY, ID = %CATEGORY_SET%)
*SELECT(%TIMEID%,[TIMEID],"TIME","[MONTHNUM]=%SMNTH% AND [LEVEL]='MONTH' AND [YEAR=%YR%]")
*SELECT(%TIMEID2%,[ID],"TIME","[MONTHNUM]=%SMNTH% AND [LEVEL]='MONTH' AND [YEAR=%YR%]")
*SELECT(%TIMESET%,[ID],"TIME","[TIMEID]>= %TIMEID% AND [YEAR]=%YR% AND [CALC]='N'")
*SELECT(%TIMESET2%,[ID],"TIME","[TIMEID]< %TIMEID% AND [YEAR]=%YR% AND [LEVEL]='MONTH' AND [CALC]='N'")


//Variable for Bonus Cost centers
*SELECT(%BC%,[BONUS_CC],"COSTCENTER","[ID]=%COSTCENTER_SET%")
//Varuable for current year december used in 401K calcs
*SELECT(%401T%,[ID],"TIME","[MONTHNUM]=12 AND [LEVEL]='MONTH' AND [YEAR=%YR%]")


//Scoping of LRP years based on the Year variables above. 
*SELECT(%TIMESET4%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR1%] AND [CALC]='N'")
*SELECT(%TIMESET6%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR2%] AND [CALC]='N'")
*SELECT(%TIMESET7%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR3%] AND [CALC]='N'")
*SELECT(%TIMESET8%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR4%] AND [CALC]='N'")
*SELECT(%TIMESET9%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR5%] AND [CALC]='N'")


*SELECT(%TIMESET10%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR%] AND [MONTHNUM]=12")
*SELECT(%TIMESET11%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR1%] AND [MONTHNUM]=12")
*SELECT(%TIMESET12%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR2%] AND [MONTHNUM]=12")
*SELECT(%TIMESET13%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR3%] AND [MONTHNUM]=12")
*SELECT(%TIMESET14%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR4%] AND [MONTHNUM]=12")


//Scoping of Cost centers with bonus cost centers populated. Scoping of Entity, Functional Area and Profit Center based on these Cost Centers. 
*SELECT(%CSTCTR%,"ID","COSTCENTER","[ID]=%COSTCENTER_SET% AND [BONUS_CC]<>""")
*SELECT(%ENT%,[ENTITY],"COSTCENTER","[ID]=%CSTCTR%")
*SELECT(%FA%,[FUNC_AREA],"COSTCENTER","[ID]=%CSTCTR%")
*SELECT(%PCTR%,[PROFIT_CENTER],"COSTCENTER","[ID]=%CSTCTR%")




*INCLUDE HC_CALC_SAL
*INCLUDE HC_CALC_MARCHTI
*INCLUDE HC_CALC_BONUS (this is the script for bonus calculation which we are discussing in this forum)
*INCLUDE HC_CALC_401K
*INCLUDE HC_CALC_401KMAX
*INCLUDE HC_CALC_ASSUMP
*INCLUDE HC_CALC_DEFCOMP
*INCLUDE HC_CALC_EXPCALC
*INCLUDE HC_CALC_CARALLOW
*INCLUDE HC_CALC_FLATTAX
*INCLUDE HC_CALC_MAXTAX
*INCLUDE HC_CALC_FINALTAX
*INCLUDE HC_CALC_FXTRANS
*INCLUDE HC_CALC_TRFRCCP

And we are discussing HC_CALC_BONUS as mentioned below.

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_ADDMEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET TIME = %TIMESET%,
*XDIM_MEMBERSET COSTCENTER=%CSTCTR%
*XDIM_MEMBERSET HC_EMPLOYEE = BAS(EMPLOYEE)
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_MEMBERSET ENTITY=%ENT%
*XDIM_MEMBERSET FUNCTIONALAREA=%FA%
*XDIM_MEMBERSET PROFITCENTER=%PCTR%
*XDIM_ADDMEMBERSET HC_ACCOUNT = HCOUNT


//EACH *ENDWHEN STATEMENT WILL POST DATA TO DATABASE TO BE USED IN CALCS LATER IN THE SCRIPT. 
//Bonus calculation to post to CC AAAA20099
//*FOR %CCTR%=%COSTCTR%
*WHEN HC_ACCOUNT
*IS HCOUNT
*WHEN AUDITTRAIL
*IS INPUT
*WHEN CATEGORY
*IS %CATEGORY_SET%
*WHEN COSTCENTER
*IS %CSTCTR%
*WHEN RPTCURRENCY
*IS LC
*WHEN FUNCTIONALAREA
*IS %FA%
                                                                                                                        
// //BONUS CALC for AAAA20099
*REC(EXPRESSION=(%VALUE%>0)*(([HC_ACCOUNT].[6100000],[AUDITTRAIL].[CALC],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PCT],[AUDITTRAIL].[INPUT],[TIME].[%YR%.INP],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PAY],[JOB_CODE].[NO_JOB_CODE],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])),HC_ACCOUNT=6101300,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA=COSTCENTER.FUNC_AREA)


//8% Tax on Bonus posted to incentive CC
//Bonus calc above * 8%


*REC(EXPRESSION=(%VALUE%>0)*(([HC_ACCOUNT].[6100000],[AUDITTRAIL].[CALC],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PCT],[AUDITTRAIL].[INPUT],[TIME].[%YR%.INP],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PAY],[JOB_CODE].[NO_JOB_CODE],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])*(.08)),HC_ACCOUNT=6100300,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA=COSTCENTER.FUNC_AREA)
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*COMMIT


//NEW SCOPE TO CALCULATE BONUS FOR ACTUAL PERIODS. 
//ACTUAL HEADCOUNT*SAL_EXP*BONUS% FOR CATEGORY_SET POSTED TO BONUS_EXP AND CATEGORY_SET
*XDIM_ADDMEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET TIME = %TIMESET2%
*XDIM_MEMBERSET COSTCENTER=%CSTCTR%
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_MEMBERSET ENTITY=%ENT%
*XDIM_MEMBERSET FUNCTIONALAREA=%FA%
*XDIM_MEMBERSET PROFITCENTER=%PCTR%
*XDIM_MEMBERSET HC_EMPLOYEE=BAS(EMPLOYEE)
*XDIM_ADDMEMBERSET AUDITTRAIL = FLATFILE
*XDIM_ADDMEMBERSET HC_ACCOUNT=HCOUNT
//EACH *ENDWHEN STATEMENT WILL POST DATA TO DATABASE TO BE USED IN CALCS LATER IN THE SCRIPT. 


*WHEN HC_ACCOUNT
*IS HCOUNT
*WHEN AUDITTRAIL
*IS FLATFILE
*WHEN CATEGORY
*IS ACTUAL
*WHEN RPTCURRENCY
*IS LC
*WHEN FUNCTIONALAREA
*IS %FA%
                                                                                                                        
// //BONUS CALC for AAAA20099
*REC(EXPRESSION=(%VALUE%>0)*(([HC_ACCOUNT].[SAL_EXP],[AUDITTRAIL].[CALC],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PCT],[AUDITTRAIL].[INPUT],[TIME].[%YR%.INP],[CATEGORY].[%CATEGORY_SET%])),HC_ACCOUNT=BONUS_EXP,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,CATEGORY=%CATEGORY_SET%, FUNCTIONALAREA=COSTCENTER.FUNC_AREA)
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*COMMIT


//NEW EMPLOYEE BONUS CALC


*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_ADDMEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET TIME = %TIMESET%
*XDIM_MEMBERSET COSTCENTER=%CSTCTR%
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_MEMBERSET ENTITY=%ENT%
*XDIM_MEMBERSET FUNCTIONALAREA=%FA%
*XDIM_MEMBERSET PROFITCENTER=%PCTR%
*XDIM_MEMBERSET HC_EMPLOYEE = BAS(STAT_EMP)
*XDIM_ADDMEMBERSET HC_ACCOUNT=HCOUNT


//EACH *ENDWHEN STATEMENT WILL POST DATA TO DATABASE TO BE USED IN CALCS LATER IN THE SCRIPT. 
//Bonus calculation to post to CC AAAA20099
*WHEN HC_ACCOUNT
*IS HCOUNT
*WHEN AUDITTRAIL
*IS INPUT
*WHEN CATEGORY
*IS %CATEGORY_SET%
*WHEN COSTCENTER
*IS %CSTCTR%
*WHEN RPTCURRENCY
*IS LC
*WHEN FUNCTIONALAREA
*IS %FA%
                                                                                                                        
// //BONUS CALC for AAAA20099
//BONUS CALC          
	*REC(EXPRESSION=(%VALUE%>0)*((([HC_ACCOUNT].[SALES_INC],[TIME].[%YR%.INP])>0)*([HC_ACCOUNT].[SALES_INC],[AUDITTRAIL].[INPUT],[TIME].[%YR%.INP],[CATEGORY].[%CATEGORY_SET%])/12),HC_ACCOUNT=6101350,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA=COSTCENTER.FUNC_AREA)


	*REC(EXPRESSION=(%VALUE%>0)*((([HC_ACCOUNT].[SALES_INC],[TIME].[%YR%.INP])<1)*((([HC_ACCOUNT].[6100000],[AUDITTRAIL].[CALC],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PCT],[JOB_CODE].[NO_JOB_CODE],[TIME].[%YR%.INP],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PAY],[JOB_CODE].[NO_JOB_CODE],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])))),HC_ACCOUNT=6101300,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA=COSTCENTER.FUNC_AREA)


//8% Tax on Bonus posted to incentive CC
//Bonus calc above * 8%


	*REC(EXPRESSION=(%VALUE%>0)*((([HC_ACCOUNT].[SALES_INC],[TIME].[%YR%.INP])<1)*((([HC_ACCOUNT].[6100000],[AUDITTRAIL].[CALC],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PCT],[JOB_CODE].[NO_JOB_CODE],[TIME].[%YR%.INP],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PAY],[JOB_CODE].[NO_JOB_CODE],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])))*(.08)),HC_ACCOUNT=6100300,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA=COSTCENTER.FUNC_AREA)


*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*COMMIT




//LRP YEARS


*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_ADDMEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET TIME = %TIMESET4%,%TIMESET6%,%TIMESET7%,%TIMESET8%,%TIMESET9%
*XDIM_MEMBERSET COSTCENTER=%CSTCTR%
*XDIM_MEMBERSET HC_EMPLOYEE = BAS(EMPLOYEE)
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_MEMBERSET ENTITY=%ENT%
*XDIM_MEMBERSET FUNCTIONALAREA=%FA%
*XDIM_MEMBERSET PROFITCENTER=%PCTR%
*XDIM_ADDMEMBERSET HC_ACCOUNT=HCOUNT


//EACH *ENDWHEN STATEMENT WILL POST DATA TO DATABASE TO BE USED IN CALCS LATER IN THE SCRIPT. 
//Bonus calculation to post to CC AAAA20099
*WHEN HC_ACCOUNT
*IS HCOUNT
*WHEN AUDITTRAIL
*IS INPUT
*WHEN CATEGORY
*IS %CATEGORY_SET%
*WHEN COSTCENTER
*IS %CSTCTR%
*WHEN RPTCURRENCY
*IS LC
*WHEN FUNCTIONALAREA
*IS %FA%
                                                                                                                        
// //BONUS CALC for AAAA20099
*REC(EXPRESSION=(%VALUE%>0)*(([HC_ACCOUNT].[6100000],[AUDITTRAIL].[CALC],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PCT],[AUDITTRAIL].[INPUT],[TIME].[%YR%.INP],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PAY],[JOB_CODE].[NO_JOB_CODE],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])),HC_ACCOUNT=6101300,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA=COSTCENTER.FUNC_AREA)




//8% Tax on Bonus posted to incentive CC
//Bonus calc above * 8%


*REC(EXPRESSION=(%VALUE%>0)*(([HC_ACCOUNT].[6100000],[AUDITTRAIL].[CALC],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PCT],[AUDITTRAIL].[INPUT],[TIME].[%YR%.INP],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PAY],[JOB_CODE].[NO_JOB_CODE],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])*(.08)),HC_ACCOUNT=6100300,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA=COSTCENTER.FUNC_AREA)


*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*COMMIT


//NEW EMPLOYEE BONUS CALC




*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_ADDMEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET TIME = %TIMESET4%,%TIMESET6%,%TIMESET7%,%TIMESET8%,%TIMESET9%
*XDIM_MEMBERSET COSTCENTER=%CSTCTR%
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_MEMBERSET ENTITY=%ENT%
*XDIM_MEMBERSET FUNCTIONALAREA=%FA%
*XDIM_MEMBERSET PROFITCENTER=%PCTR%
*XDIM_MEMBERSET HC_EMPLOYEE = BAS(STAT_EMP)
*XDIM_ADDMEMBERSET HC_ACCOUNT=HCOUNT


//EACH *ENDWHEN STATEMENT WILL POST DATA TO DATABASE TO BE USED IN CALCS LATER IN THE SCRIPT. 
//Bonus calculation to post to CC AAAA20099
*WHEN HC_ACCOUNT
*IS HCOUNT
*WHEN AUDITTRAIL
*IS INPUT
*WHEN CATEGORY
*IS %CATEGORY_SET%
*WHEN COSTCENTER
*IS %CSTCTR%
*WHEN RPTCURRENCY
*IS LC
*WHEN FUNCTIONALAREA
*IS %FA%
                                                                                                                        
//BONUS CALC for AAAA20099
//BONUS CALC          
	*REC(EXPRESSION=(%VALUE%>0)*((([HC_ACCOUNT].[SALES_INC],[TIME].[%YR%.INP])>0)*([HC_ACCOUNT].[SALES_INC],[AUDITTRAIL].[INPUT],[TIME].[%YR%.INP],[CATEGORY].[%CATEGORY_SET%])/12),HC_ACCOUNT=6101350,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA= COSTCENTER.FUNC_AREA)


	*REC(EXPRESSION=(%VALUE%>0)*((([HC_ACCOUNT].[SALES_INC],[TIME].[%YR%.INP])<1)*((([HC_ACCOUNT].[6100000],[AUDITTRAIL].[CALC],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PCT],[JOB_CODE].[NO_JOB_CODE],[TIME].[%YR%.INP],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PAY],[JOB_CODE].[NO_JOB_CODE],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])))),HC_ACCOUNT=6101300,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA=COSTCENTER.FUNC_AREA)


//8% Tax on Bonus posted to incentive CC
//Bonus calc above * 8%


	*REC(EXPRESSION=(%VALUE%>0)*((([HC_ACCOUNT].[SALES_INC],[TIME].[%YR%.INP])<1)*((([HC_ACCOUNT].[6100000],[AUDITTRAIL].[CALC],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PCT],[JOB_CODE].[NO_JOB_CODE],[TIME].[%YR%.INP],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PAY],[JOB_CODE].[NO_JOB_CODE],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])))*(.08)),HC_ACCOUNT=6100300,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA=COSTCENTER.FUNC_AREA)


*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*COMMIT








*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_MEMBERSET TIME = %TIM%,%TIMESET4%,%TIMESET6%,%TIMESET7%,%TIMESET8%,%TIMESET9%
*XDIM_MEMBERSET COSTCENTER=%COSTCENTER_SET%
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_ADDMEMBERSET HC_ACCOUNT=6100000,HCOUNT


*FOR %BON1%=%TIMESET%
*RUN_PROGRAM CALC_ACCOUNT
 CATEGORY = %CATEGORY_SET%
 TID_RA = %BON1%
 CALC=BON_EXP
*ENDRUN_PROGRAM
*NEXT
*COMMIT
Show replies
Show replies
Former Member
‎2020 Jan 29 11:08 AM
0 Likes

This was just a part of 500 lines of codes. I only provided this much of code to summarize the issue. My whole script logic looks like this actually....So, it doesn't crash on providing multiple member selection

//Use YEAR properties in Category dimension to define year values.
*SELECT(%YR%,"YEAR","CATEGORY",[ID=%CATEGORY_SET%])
*SELECT(%YR1%,"YEAR1","CATEGORY",[ID=%CATEGORY_SET%])
*SELECT(%YR2%,"YEAR2","CATEGORY",[ID=%CATEGORY_SET%])
*SELECT(%YR3%,"YEAR3","CATEGORY",[ID=%CATEGORY_SET%])
*SELECT(%YR4%,"YEAR4","CATEGORY",[ID=%CATEGORY_SET%])
*SELECT(%YR5%,"YEAR5","CATEGORY",[ID=%CATEGORY_SET%])

//Use the year variable above to create current year time periods. 
*SELECT(%TIM%,"ID","TIME",[YEAR=%YR%])
*SELECT(%SMNTH%, [STARTMNTH], CATEGORY, ID = %CATEGORY_SET%)
*SELECT(%TIMEID%,[TIMEID],"TIME","[MONTHNUM]=%SMNTH% AND [LEVEL]='MONTH' AND [YEAR=%YR%]")
*SELECT(%TIMEID2%,[ID],"TIME","[MONTHNUM]=%SMNTH% AND [LEVEL]='MONTH' AND [YEAR=%YR%]")
*SELECT(%TIMESET%,[ID],"TIME","[TIMEID]>= %TIMEID% AND [YEAR]=%YR% AND [CALC]='N'")
*SELECT(%TIMESET2%,[ID],"TIME","[TIMEID]< %TIMEID% AND [YEAR]=%YR% AND [LEVEL]='MONTH' AND [CALC]='N'")

//Variable for Bonus Cost centers
*SELECT(%BC%,[BONUS_CC],"COSTCENTER","[ID]=%COSTCENTER_SET%")
//Variable for current year december used in 401K calcs
*SELECT(%401T%,[ID],"TIME","[MONTHNUM]=12 AND [LEVEL]='MONTH' AND [YEAR=%YR%]")


//Scoping of LRP years based on the Year variables above. 
*SELECT(%TIMESET4%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR1%] AND [CALC]='N'")
*SELECT(%TIMESET6%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR2%] AND [CALC]='N'")
*SELECT(%TIMESET7%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR3%] AND [CALC]='N'")
*SELECT(%TIMESET8%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR4%] AND [CALC]='N'")
*SELECT(%TIMESET9%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR5%] AND [CALC]='N'")


*SELECT(%TIMESET10%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR%] AND [MONTHNUM]=12")
*SELECT(%TIMESET11%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR1%] AND [MONTHNUM]=12")
*SELECT(%TIMESET12%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR2%] AND [MONTHNUM]=12")
*SELECT(%TIMESET13%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR3%] AND [MONTHNUM]=12")
*SELECT(%TIMESET14%,[ID],"TIME","[LEVEL]=MONTH AND [YEAR=%YR4%] AND [MONTHNUM]=12")


//Scoping of Cost centers with bonus cost centers populated. Scoping of Entity, Functional Area and Profit Center based on these Cost Centers. 
*SELECT(%CSTCTR%,"ID","COSTCENTER","[ID]=%COSTCENTER_SET% AND [BONUS_CC]<>""")
*SELECT(%ENT%,[ENTITY],"COSTCENTER","[ID]=%CSTCTR%")
*SELECT(%FA%,[FUNC_AREA],"COSTCENTER","[ID]=%CSTCTR%")
*SELECT(%PCTR%,[PROFIT_CENTER],"COSTCENTER","[ID]=%CSTCTR%")

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_ADDMEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET TIME = %TIMESET%,
*XDIM_MEMBERSET COSTCENTER=%CSTCTR%
*XDIM_MEMBERSET HC_EMPLOYEE = BAS(EMPLOYEE)
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_MEMBERSET ENTITY=%ENT%
*XDIM_MEMBERSET FUNCTIONALAREA=%FA%
*XDIM_MEMBERSET PROFITCENTER=%PCTR%
*XDIM_ADDMEMBERSET HC_ACCOUNT = HCOUNT


//EACH *ENDWHEN STATEMENT WILL POST DATA TO DATABASE TO BE USED IN CALCS LATER IN THE SCRIPT. 
//Bonus calculation 
//*FOR %CCTR%=%COSTCTR%
*WHEN HC_ACCOUNT
*IS HCOUNT
*WHEN AUDITTRAIL
*IS INPUT
*WHEN CATEGORY
*IS %CATEGORY_SET%
*WHEN COSTCENTER
*IS %CSTCTR%
*WHEN RPTCURRENCY
*IS LC
*WHEN FUNCTIONALAREA
*IS %FA%
                                                                                                                        
// //BONUS CALC for AAAA20099
*REC(EXPRESSION=(%VALUE%>0)*(([HC_ACCOUNT].[6100000],[AUDITTRAIL].[CALC],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PCT],[AUDITTRAIL].[INPUT],[TIME].[%YR%.INP],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PAY],[JOB_CODE].[NO_JOB_CODE],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])),HC_ACCOUNT=6101300,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA=COSTCENTER.FUNC_AREA)


//8% Tax on Bonus posted to incentive CC
//Bonus calc above * 8%


*REC(EXPRESSION=(%VALUE%>0)*(([HC_ACCOUNT].[6100000],[AUDITTRAIL].[CALC],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PCT],[AUDITTRAIL].[INPUT],[TIME].[%YR%.INP],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PAY],[JOB_CODE].[NO_JOB_CODE],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])*(.08)),HC_ACCOUNT=6100300,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA=COSTCENTER.FUNC_AREA)
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*COMMIT


//NEW SCOPE TO CALCULATE BONUS FOR ACTUAL PERIODS. 
//ACTUAL HEADCOUNT*SAL_EXP*BONUS% FOR CATEGORY_SET POSTED TO BONUS_EXP AND CATEGORY_SET
*XDIM_ADDMEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET TIME = %TIMESET2%
*XDIM_MEMBERSET COSTCENTER=%CSTCTR%
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_MEMBERSET ENTITY=%ENT%
*XDIM_MEMBERSET FUNCTIONALAREA=%FA%
*XDIM_MEMBERSET PROFITCENTER=%PCTR%
*XDIM_MEMBERSET HC_EMPLOYEE=BAS(EMPLOYEE)
*XDIM_ADDMEMBERSET AUDITTRAIL = FLATFILE
*XDIM_ADDMEMBERSET HC_ACCOUNT=HCOUNT
//EACH *ENDWHEN STATEMENT WILL POST DATA TO DATABASE TO BE USED IN CALCS LATER IN THE SCRIPT. 


*WHEN HC_ACCOUNT
*IS HCOUNT
*WHEN AUDITTRAIL
*IS FLATFILE
*WHEN CATEGORY
*IS ACTUAL
*WHEN RPTCURRENCY
*IS LC
*WHEN FUNCTIONALAREA
*IS %FA%
                                                                                                                        
// //BONUS CALC for AAAA20099
*REC(EXPRESSION=(%VALUE%>0)*(([HC_ACCOUNT].[SAL_EXP],[AUDITTRAIL].[CALC],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PCT],[AUDITTRAIL].[INPUT],[TIME].[%YR%.INP],[CATEGORY].[%CATEGORY_SET%])),HC_ACCOUNT=BONUS_EXP,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,CATEGORY=%CATEGORY_SET%, FUNCTIONALAREA=COSTCENTER.FUNC_AREA)
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*COMMIT


//NEW EMPLOYEE BONUS CALC


*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_ADDMEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET TIME = %TIMESET%
*XDIM_MEMBERSET COSTCENTER=%CSTCTR%
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_MEMBERSET ENTITY=%ENT%
*XDIM_MEMBERSET FUNCTIONALAREA=%FA%
*XDIM_MEMBERSET PROFITCENTER=%PCTR%
*XDIM_MEMBERSET HC_EMPLOYEE = BAS(STAT_EMP)
*XDIM_ADDMEMBERSET HC_ACCOUNT=HCOUNT


//EACH *ENDWHEN STATEMENT WILL POST DATA TO DATABASE TO BE USED IN CALCS LATER IN THE SCRIPT. 
//Bonus calculation to post to CC AAAA20099
*WHEN HC_ACCOUNT
*IS HCOUNT
*WHEN AUDITTRAIL
*IS INPUT
*WHEN CATEGORY
*IS %CATEGORY_SET%
*WHEN COSTCENTER
*IS %CSTCTR%
*WHEN RPTCURRENCY
*IS LC
*WHEN FUNCTIONALAREA
*IS %FA%
                                                                                                                        
// //BONUS CALC for AAAA20099
//BONUS CALC          
	*REC(EXPRESSION=(%VALUE%>0)*((([HC_ACCOUNT].[SALES_INC],[TIME].[%YR%.INP])>0)*([HC_ACCOUNT].[SALES_INC],[AUDITTRAIL].[INPUT],[TIME].[%YR%.INP],[CATEGORY].[%CATEGORY_SET%])/12),HC_ACCOUNT=6101350,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA=COSTCENTER.FUNC_AREA)


	*REC(EXPRESSION=(%VALUE%>0)*((([HC_ACCOUNT].[SALES_INC],[TIME].[%YR%.INP])<1)*((([HC_ACCOUNT].[6100000],[AUDITTRAIL].[CALC],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PCT],[JOB_CODE].[NO_JOB_CODE],[TIME].[%YR%.INP],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PAY],[JOB_CODE].[NO_JOB_CODE],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])))),HC_ACCOUNT=6101300,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA= COSTCENTER.FUNC_AREA)


//8% Tax on Bonus posted to incentive CC
//Bonus calc above * 8%


	*REC(EXPRESSION=(%VALUE%>0)*((([HC_ACCOUNT].[SALES_INC],[TIME].[%YR%.INP])<1)*((([HC_ACCOUNT].[6100000],[AUDITTRAIL].[CALC],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PCT],[JOB_CODE].[NO_JOB_CODE],[TIME].[%YR%.INP],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PAY],[JOB_CODE].[NO_JOB_CODE],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])))*(.08)),HC_ACCOUNT=6100300,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA= COSTCENTER.FUNC_AREA)


*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*COMMIT




//LRP YEARS


*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_ADDMEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET TIME = %TIMESET4%,%TIMESET6%,%TIMESET7%,%TIMESET8%,%TIMESET9%
*XDIM_MEMBERSET COSTCENTER=%CSTCTR%
*XDIM_MEMBERSET HC_EMPLOYEE = BAS(EMPLOYEE)
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_MEMBERSET ENTITY=%ENT%
*XDIM_MEMBERSET FUNCTIONALAREA=%FA%
*XDIM_MEMBERSET PROFITCENTER=%PCTR%
*XDIM_ADDMEMBERSET HC_ACCOUNT=HCOUNT


//EACH *ENDWHEN STATEMENT WILL POST DATA TO DATABASE TO BE USED IN CALCS LATER IN THE SCRIPT. 
//Bonus calculation to post to CC AAAA20099
*WHEN HC_ACCOUNT
*IS HCOUNT
*WHEN AUDITTRAIL
*IS INPUT
*WHEN CATEGORY
*IS %CATEGORY_SET%
*WHEN COSTCENTER
*IS %CSTCTR%
*WHEN RPTCURRENCY
*IS LC
*WHEN FUNCTIONALAREA
*IS %FA%
                                                                                                                        
// //BONUS CALC for AAAA20099
*REC(EXPRESSION=(%VALUE%>0)*(([HC_ACCOUNT].[6100000],[AUDITTRAIL].[CALC],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PCT],[AUDITTRAIL].[INPUT],[TIME].[%YR%.INP],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PAY],[JOB_CODE].[NO_JOB_CODE],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])),HC_ACCOUNT=6101300,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA=COSTCENTER.FUNC_AREA)




//8% Tax on Bonus posted to incentive CC
//Bonus calc above * 8%


*REC(EXPRESSION=(%VALUE%>0)*(([HC_ACCOUNT].[6100000],[AUDITTRAIL].[CALC],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PCT],[AUDITTRAIL].[INPUT],[TIME].[%YR%.INP],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PAY],[JOB_CODE].[NO_JOB_CODE],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])*(.08)),HC_ACCOUNT=6100300,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA= COSTCENTER.FUNC_AREA)


*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*COMMIT


//NEW EMPLOYEE BONUS CALC




*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_ADDMEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET TIME = %TIMESET4%,%TIMESET6%,%TIMESET7%,%TIMESET8%,%TIMESET9%
*XDIM_MEMBERSET COSTCENTER=%CSTCTR%
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_MEMBERSET ENTITY=%ENT%
*XDIM_MEMBERSET FUNCTIONALAREA=%FA%
*XDIM_MEMBERSET PROFITCENTER=%PCTR%
*XDIM_MEMBERSET HC_EMPLOYEE = BAS(STAT_EMP)
*XDIM_ADDMEMBERSET HC_ACCOUNT=HCOUNT


//EACH *ENDWHEN STATEMENT WILL POST DATA TO DATABASE TO BE USED IN CALCS LATER IN THE SCRIPT. 
//Bonus calculation to post to CC AAAA20099
*WHEN HC_ACCOUNT
*IS HCOUNT
*WHEN AUDITTRAIL
*IS INPUT
*WHEN CATEGORY
*IS %CATEGORY_SET%
*WHEN COSTCENTER
*IS %CSTCTR%
*WHEN RPTCURRENCY
*IS LC
*WHEN FUNCTIONALAREA
*IS %FA%
                                                                                                                        
//BONUS CALC for AAAA20099
//BONUS CALC          
	*REC(EXPRESSION=(%VALUE%>0)*((([HC_ACCOUNT].[SALES_INC],[TIME].[%YR%.INP])>0)*([HC_ACCOUNT].[SALES_INC],[AUDITTRAIL].[INPUT],[TIME].[%YR%.INP],[CATEGORY].[%CATEGORY_SET%])/12),HC_ACCOUNT=6101350,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA= COSTCENTER.FUNC_AREA)


	*REC(EXPRESSION=(%VALUE%>0)*((([HC_ACCOUNT].[SALES_INC],[TIME].[%YR%.INP])<1)*((([HC_ACCOUNT].[6100000],[AUDITTRAIL].[CALC],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PCT],[JOB_CODE].[NO_JOB_CODE],[TIME].[%YR%.INP],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PAY],[JOB_CODE].[NO_JOB_CODE],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])))),HC_ACCOUNT=6101300,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA= COSTCENTER.FUNC_AREA)


//8% Tax on Bonus posted to incentive CC
//Bonus calc above * 8%


	*REC(EXPRESSION=(%VALUE%>0)*((([HC_ACCOUNT].[SALES_INC],[TIME].[%YR%.INP])<1)*((([HC_ACCOUNT].[6100000],[AUDITTRAIL].[CALC],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PCT],[JOB_CODE].[NO_JOB_CODE],[TIME].[%YR%.INP],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])*([HC_ACCOUNT].[BONUS_PAY],[JOB_CODE].[NO_JOB_CODE],[FUNCTIONALAREA].[NO_FA],[COSTCENTER].[NO_COSTCENTER],[PROFITCENTER].[NO_PROFITCENTER],[ENTITY].[NO_ENTITY],[HC_EMPLOYEE].[NO_EMP],[HC_EMP_TYPE].[NO_TYPE],[CATEGORY].[%CATEGORY_SET%])))*(.08)),HC_ACCOUNT=6100300,AUDITTRAIL=CALC,COSTCENTER=COSTCENTER.BONUS_CC,FUNCTIONALAREA= COSTCENTER.FUNC_AREA)


*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*COMMIT








*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_MEMBERSET TIME = %TIM%,%TIMESET4%,%TIMESET6%,%TIMESET7%,%TIMESET8%,%TIMESET9%
*XDIM_MEMBERSET COSTCENTER=%COSTCENTER_SET%
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_ADDMEMBERSET HC_ACCOUNT=6100000,HCOUNT


*FOR %BON1%=%TIMESET%
*RUN_PROGRAM CALC_ACCOUNT
 CATEGORY = %CATEGORY_SET%
 TID_RA = %BON1%
 CALC=BON_EXP
*ENDRUN_PROGRAM
*NEXT
*COMMIT
Show replies
Show replies
former_member186338
Active Contributor
‎2020 Jan 29 11:36 AM
0 Likes

If user will select 2 members of CATEGORY dimension:

*SELECT(%YR%,"YEAR","CATEGORY",[ID=%CATEGORY_SET%]) //%YR% will contain 2 years: 2017,2018 for example
*SELECT(%SMNTH%, [STARTMNTH], CATEGORY, ID = %CATEGORY_SET%) //%SMNTH% will contain 2 months: 1,2 for example
*SELECT(%TIMEID%,[TIMEID],"TIME","[MONTHNUM]=%SMNTH% AND [LEVEL]='MONTH' AND [YEAR=%YR%]") //%TIMEID% will contain 4 members: 20170100,20170200,20180100,20180200

Meaningless!

Same issue with COSTCENTER dimension

Also I do not see %TIME_SET% usage...

Former Member
‎2020 Jan 29 9:13 AM
0 Likes

No, he can select multiple members.

Show replies
Show replies
former_member186338
Active Contributor
‎2020 Jan 29 9:58 AM
0 Likes

Selecting multiple: COSTCENTER, CATEGORY will crash the script...

Former Member
‎2020 Jan 29 8:28 AM
0 Likes

Now it's your code....I agree 😞 ...will re-work on the suggestions....Please refer attachment for sample data.

Show replies
Show replies
former_member186338
Active Contributor
‎2020 Jan 29 8:46 AM
0 Likes

"User provides the COSTCENTER, CATEGORY,TIME" - hope only single member for each mentioned dimensions?

Former Member
‎2020 Jan 29 8:07 AM
0 Likes

I am actually working on someone else's code who has left the project. It seems he wanted to have ACTUAL category in addition to %category_set%

XDIM_MEMBERSET CATEGORY=%CATEGORY_SET%

*XDIM_ADDMEMBERSET CATEGORY= ACTUAL.

Also, can you please suggest how do I add a table in comments. Actually when I try to copy Sample data in tabular format....It never stays in table form after posting and it's all text.

Show replies
Show replies
former_member186338
Active Contributor
‎2020 Jan 29 8:17 AM
0 Likes

Now it's your code! And adding ACTUAL is absolutely useless and looks like a complete misunderstanding how script logic works.

To add table:

Make Excel screenshot

Save it to file

"Insert Image" - select saved file

Ask a Question