Run Allocation: Percentage logic

[former_member211812]

We have a requirement where business want to have allocation on GL as well as Cost centre base.

Let me explain you the current allocation process:

1)Enter Transaction data on Operating expense sheet.

For example :

ACCOUNT COST_CENTRE AMOUNT

A CC_001 1000

B CC_002 2000

2)Enter Allocation %

For Example:

COST_CENTRE Allocation %

CC_003 30%

CC_004 30%

CC_005 40%

3)Run Allocation Logic :

*XDIM_MEMBERSET VERSION=%VERSION_SET%
*XDIM_MEMBERSET COST_CENTER=%COST_CENTER_SET%
*XDIM_MEMBERSET LEDGER=%LEDGER_SET%
*XDIM_MEMBERSET TIME=%TIME_SET%
*XDIM_MEMBERSET P_ACCOUNT=BAS(A_12)

//POOL ALL OPERATING COSTS

*WHEN COST_CENTER
*IS %COST_CENTER_SET%
*REC(FACTOR = 1,COMPANY_CODE="CO_NONE",COST_CENTER="CC_NONE",FUNCTIONAL_AREA="FA_NONE",PROFIT_CENTER="PC_NONE",SEGMENT="SEG_NONE",LINE_OF_BUSINESS="LOB_NONE", RISK_LOCATION="RL_NONE",RISK_REGION="RR_NONE",CLAIM_YEAR="CY_NONE",AUDIT_TRAIL="AT_BPC_CC_ALLOC")
*ENDWHEN

//ALLOCATE OPERATING COSTS TO COST CENTERS
*XDIM_MEMBERSET VERSION=%VERSION_SET%
*XDIM_MEMBERSET LEDGER=%LEDGER_SET%
*XDIM_MEMBERSET TIME=%TIME_SET%

*RUNALLOCATION
*FACTOR = USING
*DIM_NONAGGR P_ACCOUNT WHAT=BAS(A_12);WHERE=<<<;USING=S_ALLOC
*DIM COMPANY_CODE WHAT=CO_NONE;WHERE=>>>;
*DIM COST_CENTER WHAT=CC_NONE;WHERE=>>>;
*DIM PROFIT_CENTER WHAT=PC_NONE;WHERE=>>>;
//*DIM FUNCTIONAL_AREA WHAT=FA_NONE;WHERE=>>>;
*DIM SEGMENT WHAT=SEG_NONE;WHERE=>>>;
//*DIM LINE_OF_BUSINESS WHAT=LOB_NONE;WHERE=>>>;
*DIM RISK_LOCATION WHAT=RL_NONE;WHERE=>>>;
*DIM RISK_REGION WHAT=RR_NONE;WHERE=>>>;
*DIM CLAIM_YEAR WHAT=CY_NONE;WHERE=>>>;
*DIM LINE_OF_BUSINESS WHAT=LOB_NONE;WHERE=>>>;
*DIM AUDIT_TRAIL WHAT=AT_BPC_CC_ALLOC;WHERE=<<<;USING=AT_BPC_INPUT
*ENDALLOCATION

4)Result :

It will allocate the amounts on both A and B at the same % mentioned in COST_CENTER allocation % sheet.

For Example:

ACCOUNT COST_CENTRE AMOUNT

A CC_003 300

A CC_004 300

A CC_005 400

B CC_003 600

B CC_004 600

B CC_005 800

New requirement:

Now business wants to apply different % for different Accounts for allocation process.

1)Enter Transaction data on Operating expense sheet.

For example :

ACCOUNT COST_CENTRE AMOUNT

A CC_001 1000

B CC_002 2000

2)Enter Allocation %

For Example:

ACCOUNT COST_CENTRE Allocation %

A CC_003 30%

A CC_004 30%

A CC_005 40%

B CC_003 10%

B CC_004 60%

B CC_005 30%

3)Run Allocation Logic. (Which needs to be change)

4)Result :-

It will allocate the amounts on both A and B at the different % mentioned in COST_CENTER allocation % sheet.

For Example:

ACCOUNT COST_CENTRE AMOUNT

A CC_003 300

A CC_004 300

A CC_005 400

B CC_003 200

B CC_004 1200

B CC_005 600

Issues :

I am not able to understand what should be the changes I need to apply in RUN ALLOCATION script logic to be able to meet the requirements.

Thanks in advance.

[Former Member]

Hi Floks,

I tried changing this script by giving the account as user selection below is my code, But still no luck can anyone help me.?

*WHEN COST_CENTER
*IS %COST_CENTER_SET%
*REC(FACTOR = 1,COMPANY_CODE="CO_NONE",COST_CENTER="CC_NONE",FUNCTIONAL_AREA="FA_NONE",PROFIT_CENTER="PC_NONE",SEGMENT="SEG_NONE",LINE_OF_BUSINESS="LOB_NONE", RISK_LOCATION="RL_NONE",RISK_REGION="RR_NONE",CLAIM_YEAR="CY_NONE",AUDIT_TRAIL="AT_BPC_CC_ALLOC")
*ENDWHEN
*COMMIT
//ALLOCATE OPERATING COSTS TO COST CENTERS
*XDIM_MEMBERSET VERSION=%VERSION_SET%
*XDIM_MEMBERSET LEDGER=%LEDGER_SET%
*XDIM_MEMBERSET TIME=%TIME_SET%
*XDIM_MEMBERSET P_ACCOUNT=%P_ACCOUNT_SET%
//Run Allocation
*RUNALLOCATION
*FACTOR = USING
*DIM P_ACCOUNT WHAT=%P_ACCOUNT_SET%;WHERE=<<<; USING=S_ALLOC //Line added
*DIM COMPANY_CODE WHAT=CO_NONE;WHERE=>>>;
*DIM COST_CENTER WHAT=CC_NONE;WHERE=>>>;
*DIM PROFIT_CENTER WHAT=PC_NONE;WHERE=>>>;
*DIM SEGMENT WHAT=SEG_NONE;WHERE=>>>;
*DIM RISK_LOCATION WHAT=RL_NONE;WHERE=>>>;
*DIM RISK_REGION WHAT=RR_NONE;WHERE=>>>;
*DIM CLAIM_YEAR WHAT=CY_NONE;WHERE=>>>;
*DIM LINE_OF_BUSINESS WHAT=LOB_NONE;WHERE=>>>;
*DIM AUDIT_TRAIL WHAT=AT_BPC_CC_ALLOC;WHERE=<<<;USING=AT_BPC_INPUT
*ENDALLOCATION

Regards,

Toufq

[former_member186338]

Sorry, but if you don't want to read help and correct the syntax errors - I am unable to help.

[former_member186338]

Sorry, but what do you mean by the following syntax:

*FACTOR = USING
//*DIM P_ACCOUNT WHAT=BAS(A_12);WHERE=>>>; //This Need to be changed.
*DIM COMPANY_CODE WHAT=CO_NONE;WHERE=>>>;
*DIM COST_CENTER WHAT=CC_NONE;WHERE=>>>;

Please read at least help for RUNALLOCATION

[former_member211812]

Hey Vadim,

I was waiting for your reply only, As of now allocation is happing at cost center level and business wants to have allocation at account level also which i have explained above.

"*FACTOR = USING
//*DIM P_ACCOUNT WHAT=BAS(A_12);WHERE=>>>; //This Need to be changed.
*DIM COMPANY_CODE WHAT=CO_NONE;WHERE=>>>;
*DIM COST_CENTER WHAT=CC_NONE;WHERE=>>>;"

Here we are trying to give flexibility to the user to select through prompt, But no luck.
"This Need to be changed"--> At this particular line we have made changes, so just for our understanding I have made this comment.

I am unable find the where I need to change

Regards,

Toufiq

[former_member186338]

You are using incorrect syntax for RUNALLOCATION

Once again, read help and correct the syntax. Then we can discuss other things.

[former_member211812]

Hi Joaquin,

The above script is validating fine, But allocation is not happing.

And I tried another method by changing the Non-Aggregate value through prompt, so that user can select the account from this also no luck.

Can you please help me on this.

Here is my script:

*XDIM_MEMBERSET VERSION=%VERSION_SET%
*XDIM_MEMBERSET COST_CENTER=%COST_CENTER_SET%
*XDIM_MEMBERSET LEDGER=%LEDGER_SET%
*XDIM_MEMBERSET TIME=%TIME_SET%
//*XDIM_MEMBERSET P_ACCOUNT= BAS(A_12)
*XDIM_MEMBERSET P_ACCOUNT=%P_ACCOUNT_SET%

//POOL ALL OPERATING COSTS

*WHEN COST_CENTER
*IS %COST_CENTER_SET%
*REC(FACTOR = 1,COMPANY_CODE="CO_NONE",COST_CENTER="CC_NONE",FUNCTIONAL_AREA="FA_NONE",PROFIT_CENTER="PC_NONE",SEGMENT="SEG_NONE",LINE_OF_BUSINESS="LOB_NONE", RISK_LOCATION="RL_NONE",RISK_REGION="RR_NONE",CLAIM_YEAR="CY_NONE",AUDIT_TRAIL="AT_BPC_CC_ALLOC")
*ENDWHEN

//ALLOCATE OPERATING COSTS TO COST CENTERS
*XDIM_MEMBERSET VERSION=%VERSION_SET%
*XDIM_MEMBERSET LEDGER=%LEDGER_SET%
*XDIM_MEMBERSET TIME=%TIME_SET%
*XDIM_MEMBERSET P_ACCOUNT=%P_ACCOUNT_SET%

*RUNALLOCATION
*FACTOR = USING
*DIM COMPANY_CODE WHAT=CO_NONE;WHERE=>>>;
*DIM COST_CENTER WHAT=CC_NONE;WHERE=>>>;
*DIM PROFIT_CENTER WHAT=PC_NONE;WHERE=>>>;
*DIM SEGMENT WHAT=SEG_NONE;WHERE=>>>;
*DIM RISK_LOCATION WHAT=RL_NONE;WHERE=>>>;
*DIM RISK_REGION WHAT=RR_NONE;WHERE=>>>;
*DIM CLAIM_YEAR WHAT=CY_NONE;WHERE=>>>;
*DIM LINE_OF_BUSINESS WHAT=LOB_NONE;WHERE=>>>;
*DIM AUDIT_TRAIL WHAT=AT_BPC_CC_ALLOC;WHERE=<<<;USING=AT_BPC_INPUT
*ENDALLOCATION

//OFFSET ALLOCATION
*XDIM_MEMBERSET VERSION=%VERSION_SET%
*XDIM_MEMBERSET LEDGER=%LEDGER_SET%
*XDIM_MEMBERSET TIME=%TIME_SET%
*XDIM_MEMBERSET P_ACCOUNT=%P_ACCOUNT_SET%

*RUNALLOCATION
*FACTOR = -1
This is Changed.
*DIM COST_CENTER WHAT = %COST_CENTER_SET% ;WHERE = <<<
*DIM COMPANY_CODE WHAT <> CO_NONE ;WHERE = <<<
*DIM SEGMENT WHAT <> SEG_NONE ; WHERE = <<<
*DIM PROFIT_CENTER WHAT <> PC_NONE ; WHERE = <<<
*DIM AUDIT_TRAIL WHAT = AT_BW_GL_SOURCE,AT_BPC_INPUT,AT_BPC_CALC ;WHERE = AT_BPC_CC_ALLOC
*ENDALLOCATION

Regards,

Toufiq

[Former Member]

Looking with more attention your original script, I have a few questions:

1. The first step it's a pre-aggregate of values to allocate?
2. Why does FUNCTIONAL_AREA and LINE_OF_BUSINESS are commented inside the allocation
3. To "where" is supposed to allocate data? It seems to me that currently is registering data to a lot of NONE members... if any.
4. The allocation factors (S_ALLOC) are saved only on ACCOUNT/COSTCENTER dimensions? with the rest in NONE members?

Please provide a sample of REAL data of the values to be allocated and of the allocation drivers (S_ALLOC). Full dimensionality required.

Best regards,

Joaquín.

[former_member211812]

Hi Joaquin,

Now we have modified the script as per the requirement and it working fine.

Below is the Script, But when we are running this same script in quality system its throwing an error

unidentified: "Reference Direction is not correct in region 'USING'OF DIMENSION COMPANY_CODE"

*XDIM_MEMBERSET VERSION=%VERSION_SET%
*XDIM_MEMBERSET COST_CENTER=%COST_CENTER_SET%
*XDIM_MEMBERSET LEDGER=%LEDGER_SET%
*XDIM_MEMBERSET TIME=%TIME_SET%
*XDIM_MEMBERSET P_ACCOUNT=%P_ACCOUNT_SET%
//POOL ALL OPERATING COSTS
*WHEN COST_CENTER
*IS %COST_CENTER_SET%
*REC(FACTOR = 1,COMPANY_CODE="CO_NONE",COST_CENTER="CC_NONE",FUNCTIONAL_AREA="FA_NONE",PROFIT_CENTER="PC_NONE",SEGMENT="SEG_NONE",LINE_OF_BUSINESS="LOB_NONE", RISK_LOCATION="RL_NONE",RISK_REGION="RR_NONE",CLAIM_YEAR="CY_NONE",AUDIT_TRAIL="AT_BPC_CC_ALLOC")
*ENDWHEN
*COMMIT
//ALLOCATE OPERATING COSTS TO COST CENTERS
*XDIM_MEMBERSET VERSION=%VERSION_SET%
*XDIM_MEMBERSET LEDGER=%LEDGER_SET%
*XDIM_MEMBERSET TIME=%TIME_SET%
*XDIM_MEMBERSET P_ACCOUNT=%P_ACCOUNT_SET% //

*RUNALLOCATION
*FACTOR = USING
*DIM_NONAGGR P_ACCOUNT WHAT=%P_ACCOUNT_SET%;WHERE=<<<; USING=S_ALLOC //Line added
*DIM COMPANY_CODE WHAT=CO_NONE;WHERE=>>>;
*DIM COST_CENTER WHAT=CC_NONE;WHERE=>>>;
*DIM PROFIT_CENTER WHAT=PC_NONE;WHERE=>>>;
*DIM FUNCTIONAL_AREA WHAT=FA_NONE;WHERE=>>>;
*DIM SEGMENT WHAT=SEG_NONE;WHERE=>>>;
*DIM RISK_LOCATION WHAT=RL_NONE;WHERE=>>>;
*DIM RISK_REGION WHAT=RR_NONE;WHERE=>>>;
*DIM CLAIM_YEAR WHAT=CY_NONE;WHERE=>>>;
*DIM LINE_OF_BUSINESS WHAT=LOB_NONE;WHERE=>>>;
*DIM AUDIT_TRAIL WHAT=AT_BPC_CC_ALLOC;WHERE=<<<;USING=AT_BPC_INPUT
*ENDALLOCATION

//OFFSET ALLOCATION
*XDIM_MEMBERSET VERSION=%VERSION_SET%
*XDIM_MEMBERSET LEDGER=%LEDGER_SET%
*XDIM_MEMBERSET TIME=%TIME_SET%
*XDIM_MEMBERSET P_ACCOUNT=%P_ACCOUNT_SET%

*RUNALLOCATION
*FACTOR = -1
*DIM COST_CENTER WHAT = %COST_CENTER_SET% ;WHERE = <<<
*DIM COMPANY_CODE WHAT <> CO_NONE ;WHERE = <<<
*DIM SEGMENT WHAT <> SEG_NONE ; WHERE = <<<
*DIM PROFIT_CENTER WHAT <> PC_NONE ; WHERE = <<<
*DIM AUDIT_TRAIL WHAT = AT_BW_GL_SOURCE,AT_BPC_INPUT,AT_BPC_CALC ;WHERE = AT_BPC_CC_ALLOC
*ENDALLOCATION

Thanks

Toufiq

[former_member186338]

Syntax is still incorrect!!!! Is it so hard to read help????

[former_member211812]

Hi Vadim,

I am unable to pin point the exact line where it is incorrect. I think that's why we post in SCN.

If its syntax is wrong then how its working fine in development and its not working in Quality.

Regards

Toufiq

[former_member186338]

I have already posted the sample of the incorrect lines!

Do you understand the meaning of ">>>" operator? Explained in help Sample 1.

Do you understand that each line need to have the same number of parameters?

[Former Member]

Hi Mohammed.

With a very little understanding, I think you need to change the USING parameter on the account dimension... try the next code:

*DIM_NONAGGR P_ACCOUNT WHAT=BAS(A_12);WHERE=<<<;USING=<<<

Best regards,

Joaquín.