Best way to sum multiple members in default logic?

[alexander_b_white]

What is the best way to sum up multiple members in default logic and store the results in a separate member?

This is easy for the Account dimension since all Accounts are automatically included in default logic, but what about for other dimensions?

For example, let's say we have a user-defined dimension EXAMPLEDIM, and we want to sum up MEMBER1, MEMBER2, and MEMBER3 and store the results in MEMBERX.  We want this to work in default logic so that the values are summed whenever someone submits a record to any of the 3 members.  Seems simple enough, right?

At first, one would think this could work:

*WHEN EXAMPLEDIM

*IS MEMBER1, MEMBER2, MEMBER3

*REC( EXPRESSION=%VALUE%, EXAMPLEDIM="MEMBERX")

*ENDWHEN

The problem with this is that if a user were to enter a value of 100 into MEMBER1, then a value of 100 would be written to MEMBERX, regardless of whether or not MEMBER2 and MEMBER3 contain a preexisting value.

Okay, so we can preface this with some scoping, right?

*XDIM_MEMBERSET EXAMPLEDIM = MEMBER1, MEMBER2, MEMBER3

While that would work, it would lead to this calculation being performed every time someone triggers default logic.  One can imagine how this would degrade the performance of a Model if many such calculations were being performed in default.

Another approach would be to write a separate REC statement for each member like this:

*WHEN EXAMPLEDIM

*IS MEMBER1

*REC( EXPRESSION=%VALUE% + GET(EXAMPLEDIM="MEMBER2") + GET(EXAMPLEDIM="MEMBER3"), EXAMPLEDIM="MEMBERX")

*IS MEMBER2

*REC( EXPRESSION=%VALUE% + GET(EXAMPLEDIM="MEMBER1") + GET(EXAMPLEDIM="MEMBER3"), EXAMPLEDIM="MEMBERX")

*IS MEMBER3

*REC( EXPRESSION=%VALUE% + GET(EXAMPLEDIM="MEMBER1") + GET(EXAMPLEDIM="MEMBER1"), EXAMPLEDIM="MEMBERX")

*ENDWHEN

But this could lead to doubling of data if the user were to submit values to more than one of these members at a time.  Plus, can you imagine trying to maintain this for more than a handful of members?

Likewise, we could try referencing the destination member in our expression:

*WHEN EXAMPLEDIM

*IS MEMBER1, MEMBER2, MEMBER3

*REC( EXPRESSION=%VALUE% + GET(EXAMPLEDIM="MEMBERX"), EXAMPLEDIM="MEMBERX")

*ENDWHEN

But this could lead to the same doubling problem if the user submits a record to more than one of the source members.

Do you have any ideas?  Note that we cannot use a hierarchy or dimension logic since the requirement is to calculate and store the results, since hierarchies and calculated members can often change.

Thanks!

Alex

[alexander_b_white]

Does anyone have further ideas?  I am very curious as to whether there is an elegant solution for this.

To summarize, the requirements are as follows:

• Sum up several members and store the result in another member
• This should run in default logic so that the sum is always up-to-date
• The logic should only be triggered when values are submitted to the relevant source members (i.e., we don't want this to run every time default logic is invoked)
• This should function correctly regardless of whether the user has submitted values to all of the source members, or only one of the source members

Cheers,

Alex

[Former Member]

Hi,

I would add a new property to EXAMPLEDIM and for each base member identify the member which should show the sum. So for your MEMBER1, MEMBER2 and MEMBER3 this new property would always show MEMBERX.

Then I would use a script like

*WHEN EXAMPLEDIM

*IS *

*REC(EXPRESSION=%VALUE%,EXAMPLEDIM=EXAMPLEDIM.NEWPROPERTY)

*ENDWHEN

BR,
Arnold