Default Script Logic Issue with looping WHEN

[Former Member]

Hello dear friends,

I hope you are all doing fine. I have come across an issue with BPC Logic Scripts for the version 10 NW when trying to run a certain code in the default.lgf file.

Imagine the following scenario:

You have the following accounts: Acc1, Acc2 and Acc3.

You need to maintain a certain formula in Script Logic that does the following calculation:

Acc3 = Acc1 + Acc2

So the script logic would be something like that:

*WHEN ACCOUNT

*IS Acc1, Acc2

*REC(EXPRESSION = %VALUE%, ACCOUNT = Acc3)

*ENDWHEN

(Looping through accounts 1 and 2 and summing them as Account3)

When I put this in the default.lgf file I get the following behavior:

• If I put Acc1 = 100 and Acc2 = 100 and I save, I get Acc3 = 200 which is correct.
• If I then change Acc1 = 150 and leave Acc2 as is, because the execution scope is only limited to the changed rows, I am getting Acc3 = 150. It's not reading a value for Acc2, therefore not doing the sum properly. This is a problem.

I try to solve the issue by doing the following:

*WHEN ACCOUNT

*IS Acc1, Acc2

*REC(EXPRESSION = [Acc1] + [Acc2], ACCOUNT = Acc3)

*ENDWHEN

I get the following behavior:

• If I put Acc1 = 100 and Acc2 = 100 and I save, I get Acc3 = 400 which is incorrect. The reason is because the loop is doing twice 100+100.
• If I put Acc1 = 150 and I keep Acc2 = 100 and I save, I get Acc3 = 250 which is correct.

After further refining I was able to solve the situation with this code:

*WHEN ACCOUNT

*IS Acc1

*REC(EXPRESSION = [Acc1] + [Acc2], ACCOUNT = Acc3)

*IS Acc2

*REC(EXPRESSION = [Acc1] + [Acc2], ACCOUNT = Acc3)

*ENDWHEN

I get the correct results in both cases, but the code is nasty! I mean it is not clean and the calculation itself is being done twice in case I have put values to the Acc1 and Acc2.

Can you suggest a better solution to my problem?

If this code was running outside the default, then I would have had no problem because the accounts would have been summed using the first scenario. Now because I have to adapt to data modification possibilities then my code is getting nasty. And this is just an example I gave you. I have formulas with more than 6 operands Imagine the code and the slowness...

I really appreciate it if someone can help

[former_member186338 · Accepted solution]

Hi Joseph,

The correct code is:

*WHEN ACCOUNT

*IS Acc1

*REC(EXPRESSION = [Acc1] + [Acc2], ACCOUNT = Acc3)

*ENDWHEN

*WHEN ACCOUNT

*IS Acc2

*REC(EXPRESSION = [Acc1] + [Acc2], ACCOUNT = Acc3)

*ENDWHEN

Two loops are required to prevent accumulation if both Acc1 and Acc2 are changed.

B.R. Vadim

[Former Member · Accepted solution]

Hello Vadim,

Thank you for your answer.

So I understand that with script logic, there is no other way. So if I have this formula:

Acc6=Acc1+Acc2+Acc3+Acc4+Acc5

I have to put:

*WHEN ACCOUNT

*IS Acc1

*REC(EXPRESSION = [Acc1] + [Acc2] + [Acc3] + [Acc4] + [Acc5], ACCOUNT = Acc6)

*ENDWHEN

*WHEN ACCOUNT

*IS Acc2

*REC(EXPRESSION = [Acc1] + [Acc2] + [Acc3] + [Acc4] + [Acc5], ACCOUNT = Acc6)

*ENDWHEN

*WHEN ACCOUNT

*IS Acc3

*REC(EXPRESSION = [Acc1] + [Acc2] + [Acc3] + [Acc4] + [Acc5], ACCOUNT = Acc6)

*ENDWHEN

*WHEN ACCOUNT

*IS Acc4

*REC(EXPRESSION = [Acc1] + [Acc2] + [Acc3] + [Acc4] + [Acc5], ACCOUNT = Acc6)

*ENDWHEN

*WHEN ACCOUNT

*IS Acc5

*REC(EXPRESSION = [Acc1] + [Acc2] + [Acc3] + [Acc4] + [Acc5], ACCOUNT = Acc6)

*ENDWHEN

Is that correct?

Thank you Vadim

[former_member186338 · Accepted solution]

Hi Joseph,

Yes, you are correct! The proposal of Kalyan looks nice (simple code), but the speed of MDX calculations is awful...

B.R. Vadim

[Former Member]

Hi Joseph,

Try using MDX

[ACCOUNT].[#Acc3] = [ACCOUNT].[Acc1]+[ACCOUNT].[Acc2]

Regards,

Kalyan.

[Former Member]

Hello Kalyan.

Thank you for your answer.

The problem is a bit more complicated because I have another dimension called UOM (or unit of measure)

So the whole issue is that this UOM dimension contains Units, Costs and lots of Ratios as dimension members (Cost/Unit, Unit/ton, Cost/ton, %Quality(Not calculated))

So for the same account we have multiple units of measures and the SUM formula applies only for some UOMs.

So basically in my script, I'm scoping first with the following line of code:

XDIM_Memberset UOM=Units, Costs

So my questions for you are the following:

• If if I want to use MDX, can I define this scoping too? From what I know, you cannot really set a tuple on the left side of the = operator in the MDX expressions
• Will the value be stored in the database if I use [ACCOUNT].[#Acc3] ? Isn't the # sign meaning a calculated membet?

Thank you in advance!

[former_member186338]

Hi Joseph,

It's a bad practice to scope with XDIM_MEMBERSET in the default.lgf. Use *IS Units, Costs instread of XDIM

And see my previous comment about MDX speed of calculations.

B.R. Vadim

P.S. The code will be:

*WHEN UOM

*IS Units, Costs

*WHEN ACCOUNT

*IS Acc1

*REC(EXPRESSION = [Acc1] + [Acc2] + [Acc3] + [Acc4] + [Acc5], ACCOUNT = Acc6)

*ENDWHEN

*ENDWHEN

repeated 5 times with different *IS AccX

Message was edited by: Vadim Kalinin - P.S. with sample added!

[Former Member]

Hello Vadim,

I found a solution that limits the code writing of Script Logic and decreases the loops the system has to do and probably increases the performance because the system hasn't to do MDX lookups for every loop iteration.

But I'm not sure what are the cons of such a code. Can you provide some feedback about the quality of such a method?

*XDIM_ADDMEMBERSET ACCOUNT = Acc1, Acc2 , Acc3,  Acc4, Acc5

*WHEN UOM

    *IS Units, Costs

     *WHEN ACCOUNT

         *IS Acc1, Acc2 , Acc3,  Acc4, Acc5

            *REC(EXPRESSION = %VALUE%, ACCOUNT = Acc6)

     *ENDWHEN

*ENDWHEN

*COMMIT

// After commit, the effect of the XDIM_ADDMEMBERSET is gone right? That's what I noticed when testing.

[former_member186338]

Hi Joseph,

First, your approach will work only for simple summing formula.

Second, with ADD_MEMBERSET you are changing the incoming scope (the same effect as with XDIM_MEMBEBERSET). It means, that calculations will be done indepent of the fact that acc1-accn were changed in reality.

And stop using COMMIT -useless with WHEN/ENDWHEN.

B.R. Vadim

P.S. I am currently on the vacations in India Goa, the internet access is not perfect.