CALC_DUMMY_ORG replacement

[Former Member]

Hi guys,

Second issue for the day...

This old logic from 7.5 MS has the very useful CALC_DUMMY_ORG in it. I have tried a couple of ideas, but I can't replicate this in NW at all.

*SELECT(%BANK_TYPE%,"distinct [ID]","ACCOUNTL","[RATETYPE]='BC_END'")

*XDIM_MEMBERSET DATASRCL = BC

*XDIM_MEMBERSET ACCTDETAIL = F_CLO

*XDIM_MEMBERSET ACCOUNTL = %BANK_TYPE%

*CALC_DUMMY_ORG ACCOUNTL = PARENTH1

*WHEN GET(ACCOUNTL=# + ACCOUNTL.PARENTH1)

*IS < 0

*REC(FACTOR=-1,DATASRCL = "REALLOCATIONS")

*REC(FACTOR=1,DATASRCL="REALLOCATIONS",ACCOUNTL="40001")

*ENDWHEN

The purpose of this script is to evaluate the total of each group of bank accounts (they are separated into parents for each bank "brand": e.g. Barclays, Lloyds, Citibank, etc.)

These accounts are evaluated at a total level, and if the total is less than zero a reversing entry should go into the children accounts, in the REALLOCATIONS AuditID, and the total into the account "40001".

This is my current attempt in NW:

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET AUDITIDC = BC

*XDIM_MEMBERSET FLOW = F_CLO

*XDIM_MEMBERSET ACCOUNTC = %BANK_TYPE%

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%

*WHEN ??????

*IS ??????

*REC(EXPRESSION = %VALUE%<0 ? %VALUE%*-1 :0, AUDITIDC = "REALLOCATIONS")

*REC(EXPRESSION = %VALUE%<0 ? %VALUE% :0, AUDITIDC = "REALLOCATIONS", ACCOUNTC = "40001")

*ENDWHEN

I would like to keep this dynamic, so I have tried a few ideas, including replacing the items in bold with a tuple instead, but this would have to be hard-coded, as .Property("<value") doesn't work.

Any suggestions?? I could suggest that this be done in a BaDI, but this would have to be sent out to someone else as I don't know enough about it.

If I can keep it in script that would be ideal.

Thanks very much,

Jason

[JohnL · Accepted solution]

Hi Jason,

I've never tried this before but can you see if this works:

*WHEN AUDITIDC

*IS BC

*REC(EXPRESSION = [ACCOUNTL].[BANKTYPEPARENTMEMBER]<0 ? %VALUE%*-1 :0, AUDITIDC = "REALLOCATIONS")

*REC(EXPRESSION = [ACCOUNTL].[BANKTYPEPARENTMEMBER]<0 ? %VALUE% :0, AUDITIDC = "REALLOCATIONS", ACCOUNTC = "40001")

*ENDWHEN

You would need to do this for each set of Bank parent members and children

HTH

[former_member186338 · Accepted solution]

Hi John,

The issue is - "You would need to do this for each set of Bank parent members and children"

WHEN/ENDWHEN - only for base members!

It's better to accumulate parent value in some temporary base member for each Bank, then loop temporary base members.

Vadim

[Former Member · Accepted solution]

Thanks John - as far as I understand that would work perfectly, but it's not dynamic. These base members change regularly, so to do this manually would be a no-go for my client.

Thanks very much for the suggestion though

Jason

[Former Member · Accepted solution]

Thanks Vadim - I tried a version of this already, but my loops were too big.

But you've given me an idea... I will test it later and let you know.

Thanks a lot!

Jason

[Former Member · Accepted solution]

Hi Vadim,

One question: so I need to evaluate the TOTAL for each bank, but if the total is less than zero I must post the reversing entries to the individual base members... so if I post to temporary ones first I lose that information... correct? Or am I missing something? I guess I could reallocate the data back down, using the original values as my USING?

Thanks,

Jason

[former_member186338 · Accepted solution]

Do it in 2 steps:

1. Write multiple base members to the single base member for each bank (simulation of parent member as base member using first WHEN/ENDWHEN loop). The result may be positive or negative.

2. For all base members - test sign using ternary operator in another WHEN/ENDWEN loop and write result to the required destination depending on sign.

You can also reverse records based on calculated result...

Vadim

P.S. Can you provide more details about this line:

*XDIM_MEMBERSET ACCOUNTC = %BANK_TYPE%

[Former Member · Accepted solution]

Got it thanks - will try it as soon as I'm back at my desk.

The BANK_TYPE variable is populated from this:

*SELECT(%BANK_TYPE%,ID,ACCOUNTC,[RATETYPE]='BC_END')

So it is just filtering out the base members that have a rate type of BC_END.

Thanks again!

Jason

[former_member186338 · Accepted solution]

Then please show the structure of your account dimension - to understand the hierarchy...

"The purpose of this script is to evaluate the total of each group of bank accounts (they are separated into parents for each bank "brand": e.g. Barclays, Lloyds, Citibank, etc.)" - not clear!

[Former Member · Accepted solution]

Hi Vadim,

Sorry, forgot about this.

I'm not logged into the system at the moment, but the account hierarchy goes something like this:

Barclays parent

          Barclays account 1

         Barclays account 2

         Barclays account 3

         Barclays account 4

         Barclays account 5

Citibank parent

          Citibank account 1

          Citibank account 2

          Citibank account 3

etc.

I also created new dummy base members in a parent called BANK_TEMP; one for each banking company (Barclays, Citibank, etc - 50 or so in total).

I then updated the individual accounts with a property called BANKTEMP, which stores the "dummy parent" for each account.

The base members are updated fairly regularly, but the script should check the total at each parent level, and the base members will be reallocated to one of two different accounts, depending on the value of the parent.

The idea I had was something like this (this is partly old script and partly me typing from memory...):

//Save parent totals to dummy base members - this piece works perfectly

*INCLUDE SYSTEM_CONSTANTS.LGF

*SELECT(%BANK_TYPE%,ID,ACCOUNTC,[BANKTEMP]<>'')

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET AUDITIDC = BC

*XDIM_MEMBERSET FLOW = F_CLO

*XDIM_MEMBERSET ACCOUNTC = %BANK_TYPE%

*XDIM_MEMBERSET LEGALENTITY = %LEGALENTITY_SET%

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%

*XDIM_MEMBERSET GROUPS = %GROUPS_SET%

*XDIM_MEMBERSET INTERCO = BAS(ALL_INTERCO)

*XDIM_MEMBERSET RPTCURRENCY=LC

*WHEN FLOW

*IS F_CLO

*REC(FACTOR=1,ACCOUNTC=ACCOUNTC.BANKTEMP) //BANKTEMP is the temporary member for the parent totals. There is one for each bank company (Barclays, Cititbank, etc, 50 in total)

*ENDWHEN

//___________________________________________________________________

//Evaluate the dummy parent members, and post account values accordingly

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET AUDITIDC = BC

*XDIM_MEMBERSET FLOW = F_CLO

*XDIM_MEMBERSET ACCOUNTC AS %TEMP% = BAS(BANK_TEMP)

*XDIM_MEMBERSET ACCOUNTC = %BANK_TYPE%,%TEMP%

*XDIM_MEMBERSET LEGALENTITY = %LEGALENTITY_SET%

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%

*XDIM_MAXMEMBERS ACCOUNTC = 10

*FOR %ACCTS% = %BANK_TYPE%

*FOR %BANKS% = %TEMP%

*WHEN ACCOUNTC

*IS %ACCTS%

*REC(EXPRESSION = [ACCOUNTC].[%BANKS%] <0 ? %ACCTS%*-1 :0, AUDITIDC = "REALLOCATIONS")

*REC(EXPRESSION = [ACCOUNTC].[%BANKS%] <0 ? %ACCTS% :0, AUDITIDC = "REALLOCATIONS", ACCOUNTC = "40001")

*ENDWHEN

*NEXT

*NEXT

This last set of loops is from memory, so the spelling my not be 100%.

I tried a variation of this, first with only one bank parent and one account, which worked OK. But as I started to expand the dataset, it took longer and longer - even with MAXMEMBERS included.

The last attempt timed-out, so I must've got carried away with my loops!

Will update later when I'm back online.

Thanks,

Jason

[former_member186338 · Accepted solution]

Sorry, I have already forgot the logic...

Can you explain me:

If you have the account structure like:

Barclays parent

          Barclays account 1

         Barclays account 2

         Barclays account 3

         Barclays account 4

         Barclays account 5

What about [RATETYPE]='BC_END' for 1, 2, 3...?

Vadim

[former_member186338 · Accepted solution]

P.S. Just checking - do you have a parent for [RATETYPE]='BC_END' - the value that can be checked like in John proposal...

Then I can propose some dynamic solution. 50 parents is not a huge number!

Vadim