DM Package - Copy Values from Parent to Descendents

[former_member313059]

Hello Experts

We are running Netweaver Version 10.0, EPM Add In SP21.

I am trying to copy volume from stored against one Member beneath a given Parent over all other Members also beneath the same Parent.

Below shows the relationship:

I do not need to 'Allocate' volume based on weighting.

I have one stored value that needs copying to many Members provided they are underneath the same Parent..

I have written the attached Logic Script but it fails saying ' Invalidatate Selection' in the Error Log.

Do I need to use a LOOKUP to define the relationship between Source/Target relationship and use this in the Logic - and how would I do this?

Any help would be much appreciated.

Thanks!

UV006.txt.zip

[former_member313059 · Accepted solution]

Hello Everyone,

Thank you all for your comments.  As noted by Vadim, I will provide more information:

• NW Version 10.0, SP21
• ABAP Calc Engine
• Logic will be executed by DM Package

Our model 'UNIT_VALUE' is a Standard Model and includes the following Dimensions (Types):

• CATEGORY (Category)
• CUSTOMER (Entity)
• PRODUCT (User Defined)
• TIME (Time)
• UV_ACCT (Account)

Since my original post I have tidied up the code and have something that now works perfectly when scoping WHAT but is not tight enough on the WHERE,  as a result the volume of data being copied causes the package to Abort.  If I could just find a way of restricting the WHERE then I have a solution that works.. can anyone advise.

Here is the latest logic script:

*SELECT(%MODELRANGE%,[ID],PRODUCT,"[CALC]='N' AND [GROUP]='MODELRANGE'")

*SELECT(%SALES_REGION%,[ID],CUSTOMER,"[CALC]='Y' AND [GROUP]='SALES REGION'")

*XDIM_MEMBERSET CATEGORY = BUD_INV_WK

*XDIM_MEMBERSET TIME = 2016.TOTAL_I

*XDIM_MEMBERSET UV_ACCT = UA_015, UA_017, UA_019

*XDIM_MEMBERSET PRODUCT = %MODEL_RANGE%

*FOR %PARENTCUSTOMER% = %SALES_REGION%

*RUNALLOCATION

*FACTOR=1

*DIM CUSTOMER WHAT = %PARENTCUSTOMER%_INP; WHERE=BAS(%PARENTCUSTOMER%)

*ENDALLOCATION

*NEXT

*COMMIT

The script behaves in this way.... after Values have been keyed at the Parent Level (Sales Region) this logic will copy the same values to all of its children.

It will loop round for all Sales Regions (loops 40 times) and because I have used variables it is future proof in the event of more Sales Regions being created.

The WHAT scope is working exactly right.

The issue is for WHERE=BAS(%PARENTCUSTOMER%)

Using BAS includes levels beneath the Parent but I only want to copy to the Members at the node directly below.

I don't know how to incorporate a condition like this WHERE=BAS(%PARENTHCUSTOMER%), "[CALC]='N' AND [GROUP]='FORECASTGROUP'

Any help would be hugely appreciated ;o)

[former_member186338 · Accepted solution]

"to the Members at the node directly below"

Try to use WHERE condition with property.

I am not sure that this will work:

WHERE=[PARENTH1]="%PARENTHCUSTOMER%"

But you can create a new property

[former_member186338 · Accepted solution]

Just tested - it's impossible to use PARENTH1 as a property in RUNALLOCATION.

Then you have to create a new property (for example PAR) with the same text as in PARENTH1.

*DIM CUSTOMER WHAT=%PARENTCUSTOMER%_INP; WHERE=[PAR]=%PARENTCUSTOMER%

P.S. You can't combine BAS(...) and selection by property in WHERE

[former_member313059 · Accepted solution]

Hi Vadim

Your suggestion helpd me look at this a different way.

So, given that I know the WHERE scope works correctly, for the purpose of testing your suggestion I narrowed it to one hard-coded CUSTOMER Member (Member ID CU_SR_R35)

I added a SELECT statement to try and identify the Members that at the node below CU_SR_R35 and meet the criteria of PROPERTY GROUP = FORECASTGROUP. 

In the WHERE statement I then reference this Variable....

However,  in UJKT it does not recognise the hierarchy as an attribute.  Is my syntax incorrect or am I am trying to achieve the impossible?

The attached file shows the results from UJKT and the highlighted changes.

Any help/guidance is much appreciated.

Thanks

[former_member186338 · Accepted solution]

It's not a good idea to provide script text only as a screenshot I am unable to copy paste!

Second - you can't use PARENTHx in SELECT "I am trying to achieve the impossible"

Third - you can't use SELECT inside FOR

P.S. From previous: "Then you have to create a new property (for example PAR) with the same text as in PARENTH1."

[former_member186338 · Accepted solution]

Without adding properties I can provide only slow solution:

*SELECT(%MODELRANGE%,[ID],PRODUCT,"[CALC]='N' AND [GROUP]='MODELRANGE'")

*SELECT(%SALES_REGION%,[ID],CUSTOMER,"[CALC]='Y' AND [GROUP]='SALES REGION'")

*SELECT(%PLANNING_GROUPS%,[ID],CUSTOMER,"[CALC]='Y' AND [GROUP]='FORECASTGROUP'")

*XDIM_MEMBERSET CUSTOMER=%PLANNING_GROUPS%

*XDIM_MEMBERSET CATEGORY = BUD_INV_WK

*XDIM_MEMBERSET TIME = 2016.TOTAL_I

*XDIM_MEMBERSET UV_ACCT = UA_015, UA_017, UA_019

*XDIM_MEMBERSET PRODUCT = %MODEL_RANGE%

*WHEN_REF_DATA = MASTER_DATA //will slow down the script!!!

*WHEN CUSTOMER.PARENTH2 //here you can use PARENTx property

*FOR %PARENTCUSTOMER% = %SALES_REGION%

*IS %PARENTCUSTOMER% //direct child of %PARENTCUSTOMER%

*REC(EXPRESSION=[CUSTOMER].[%PARENTCUSTOMER%_INP])

*NEXT

*ENDWHEN

Vadim

P.S. Added scope for CUSTOMER!

[former_member313059 · Accepted solution]

Thank you for all your help with this, our solution works well and I wanted to share an update with you.

As I couldn't easily identify the Descendents through the syntax and wasn't too keen on adding more Properties, the final solution was not elegant but does the job.

First I run the logic to copy all data to Base Members.

Followed by a small script that then clears the data from Members that are not the descendants based on a GROUP property selection.

[former_member186338 · Accepted solution]

Sorry but not clear....

[Former Member]

Hi Jo,

Maybe you should consider using just one member to represent all the children per parent.

why populate unnecessary data?

Andy

[former_member186338]

First - the logic is not clear! Please read:

Provide a data sample and the desired result of calculation!

Second - the logic in the attachment is incorrect, what do you mean by this line:

*DIM CUSTOMER

WHAT=%SEL_CUST1%;

WHERE=%SEL_CUST2%

Vadim

P.S. Please show what you will have in the variables: %SEL_CUST1% and %SEL_CUST2%