Need help with a script to write data to multiple accounts

[Former Member]

Hi,

the basic requirement:

we have a number of groups of accounts. For each group we need to check the total value and if the total value is negative, we need to adjust the accounts in that group and post their values to other accounts.

Let me build an example:

account	account.netacc	group	amount	adjustment to be made
acc1	acc7	GRP01	-100
acc2	acc8	GRP01	50
acc3	acc9	GRP01	75
acc4	acc10	GRP02	25	-25
acc5	acc11	GRP02	-50	50
acc6	acc12	GRP02	-50	50
acc7
acc8
acc9
acc10				-25
acc11				50
acc12				50
WK_01			25 (generated by BR)
WK_02			-75 (generated by BR)

acc1, acc2 and acc3 are the first group and acc4,acc5,acc6 are the second group. At the moment I do not have hierarchy nodes for the group totals but I could add those in an additional hierarchy.

As the total for the accounts in GRP01 is positive (25) no adjustments need to be made on these accounts.

As the total for the accounts in GRP02 is negative (-75) the adjustments shown above need to be made on a separate datasource. The adjustment on each source account needs to be offset by an adjutsment on the account given in the netacc property for the source account.

My current attempt at a solution:

I use the account based calculation business rule to work out the total for each group and store it on an account but only on I_NONE so I only have one value.

I then tried this script

*SELECT(%WK_01%,[ID],"ACCOUNT","[DIMLIST_WK] = 'WK_01'")

*WHEN ACCOUNT *IS WK_01

*FOR %A% = %WK_01%

*WHEN ACCTDETAIL

*IS F_CHKSUM

*REC(EXPRESSION = ([ACCOUNT].[WK_01],[ACCTDETAIL].[F_CHKSUM]),ACCOUNT = %A%,ACCTDETAIL=F_CHKSUM,DATASRC=INPUT_WK)

*ENDWHEN

*NEXT

*ENDWHEN

However, that does not give me the result I am looking for, in fact, it doesn't seem to be doing anything. Can anyone help me?

Thanks

Arnold

[Former Member]

My latest attempt:

*SELECT(%WK_01%,[ID],"ACCOUNT","[DIMLIST_WK]='WK_01'")

*SELECT(%NETACC2",[NETACC2],"ACCOUNT","[DIMLIST_WK]='WK_01'")

*XDIM_MEMBERSET ACCOUNT=WK_01

*XDIM_ADDMEMBERSET ACCOUNT=%NETACC2%

*XDIM_MEMBERSET ACCTDETAIL=F_CHKSUM

*XDIM_MEMBERSET DATASRC=INPUT

*XDIM_MEMBERSET MEASURES=YTD

*FOR %A%=%WK_01%

*FOR%B%=%NETACC2%

*WHEN ACCOUNT

*IS WK_01

*REC(EXPRESSION=%VALUE%<0 ? ([ACCOUNT].[%A%],[ACCTDETAIL].[F_CLO])*-1:0,ACCOUNT=%A%,DATASRC=INPUT_WK,ACCTDETAIL=F_CLO)

*REC(EXPRESSION=%VALUE%<0 ? ([ACCOUNT].[%A%],[ACCTDETAIL].[F_CLO]):0,ACCOUNT=%B%,DATASRC=INPUT_WK,ACCTDETAIL_F_CLO)

*ENDWHEN

*NEXT

*NEXT

My problem now is that I have several source accounts but BPC only writes the value of the final of those onto all target accounts.

[Former Member]

So I then tried

*SELECT(%WK_01%,[ID],"ACCOUNT","[DIMLIST_WK]= 'WK_01'")

*XDIM_MEMBERSET ACCOUNT=WK_01

*XDIM_MEMBERSET ACCTDETAIL=F_CHKSUM

*XDIM_MEMBERSET DATASRC=INPUT

*FOR %A%=%WK_01%

*WHEN ACCOUNT

*IS WK_01

*REC(EXPRESSION=%VALUE%<0 ? ([ACCOUNT].[%A%],[ACCTDETAIL].[F_CLO])*-1:0,ACCOUNT=$A$,DATASRC=INPUT_WK)

*REC(EXPRESSION=%VALUE%<0 ? ([ACCOUNT].[%A%],[ACCTDETAIL].[F_CLO]):0,ACCOUNT=[ACCOUNT].[%A%].NETACC,DATASRC=INPUT_WK)

*ENDWHEN

*NEXT

But that generates an error, saying [ACCOUNT].[%A%].NETACC does not exist. The actual message replaces the %A% with the account code and comes when I try to run the script.

Thanks,

Arnold

[Former Member]

Maybe one thing to add, the example talks about two groups, in reality we will have around 50 or so. And each group can have more than 3 accounts.

Thanks,

Arnold

[Former Member]

I can manage what I need to achive by hardcoding accounts:

*XDIM_MEMBERSET ACCOUNT=WK_02

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=%VALUE%<0 ? [ACCOUNT].[ACC4]*-1:0,ACCOUNT=ACC4,DATASRC=INPUT_WK)
*REC(EXPRESSION=%VALUE%<0 ? [ACCOUNT].[ACC4]:0,ACCOUNT=ACC10,DATASRC=INPUT_WK)

...

*ENDWHEN

The ... would repeat the two *REC but use ACC5/ACC11  and ACC6/ACC12 instead. However that way is not very easy to write and maintain, so I am looking for a way to make it dynamic.

Thanks,

Arnold