Using property to define dimension member in *REC

[cecilia_petersson2]

Hi,

We’re on EHP7 FOR SAP ERP 6.0, JavaScript engine.

I’d like to loop on dimension Brand and copy records for a certain account to all members of dimension Material where property Brand equals the Brand currently being in the loop.

My original record that I’d like to copy:

CATEGORY     ENTITY      ACCOUNT                       TIME           RPTCURRENCY   BRAND     MATERIAL     SEGMENT       SignData

PLAN                CO.1100    AC.S.MKTGROWTH        2014.INP    LC                          BR.XYZ     MA.NONE       SG.NONE        0,05

Material MA.ABC has Brand property BR.XYZ.

My script:

*SELECT(%INP_BRANDS%,"[ID]","BRAND","[CALC] = 'N' AND [ID] <> 'BR.NONE'")    // Brands where input is done per brand

*XDIM_MEMBERSET ACCOUNT = AC.S.MKTGROWTH

*XDIM_MEMBERSET CATEGORY = PLAN

*XDIM_MEMBERSET ENTITY = CO.1100

*XDIM_MEMBERSET SEGMENT = SG.NONE

*XDIM_MEMBERSET TIME = <ALL>

*FOR %MY_BRAND% = %INP_BRANDS%

*WHEN_REF_DATA = MASTER_DATA

*WHEN MATERIAL.BRAND

*IS %MY_BRAND%

*REC(EXPRESSION = ([BRAND].[%MY_BRAND%]))  // Copy brand value to its materials

*ENDWHEN

*NEXT

*COMMIT

My intention was to create a record almost identical to my original record, the only difference being that Material=MA.ABC. However, no records were created. There are no records on account AC.S.MKTGROWTH where Material.Brand = BR.XYZ, but my understanding was that *WHEN_REF_DATA = MASTER_DATA would make the script create records for all materials  regardless if records already existed or not.

My log looks as per below, so at least it seems the original record has been identified:

#dim_memberset=4

ACCOUNT:AC.S.MKTGROWTH,1 in total.

CATEGORY:PLAN,1 in total.

ENTITY:CO.1100,1 in total.

SEGMENT:SG.NONE,1 in total.

[INFO] GET_DIM_LIST(): I_APPL_ID="Planning", #dimensions=16

ACCOUNT,AUDITTRAIL,BRAND,CATEGORY,COST_CENTRE,CUSTOMER,ENTITY,FUNCTIONS,INTERCO,MATERIAL,MEASURES,PROJECT,RPTCURRENCY,SEGMENT,TIME,TRX_CURR

#dim_memberset=4

ACCOUNT:AC.S.MKTGROWTH,1 in total.

CATEGORY:PLAN,1 in total.

ENTITY:CO.1100,1 in total.

SEGMENT:SG.NONE,1 in total.

REC :([BRAND].[BR.XYZ])

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 1.00 ms. 0  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY TIME : 0.00 ms. 0  RECORDS QUERIED OUT.

QUERY TIME : 1.00 ms. 0  RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :43547.00 ms.

NO RECORDS GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 0  RECORDS ARE GENERATED.

[INFO] GET_DIM_LIST(): I_APPL_ID="Planning", #dimensions=16

ACCOUNT,AUDITTRAIL,BRAND,CATEGORY,COST_CENTRE,CUSTOMER,ENTITY,FUNCTIONS,INTERCO,MATERIAL,MEASURES,PROJECT,RPTCURRENCY,SEGMENT,TIME,TRX_CURR

#dim_memberset=4

ACCOUNT:AC.S.MKTGROWTH,1 in total.

CATEGORY:PLAN,1 in total.

ENTITY:CO.1100,1 in total.

SEGMENT:SG.NONE,1 in total.

Any ideas how I can change my script to make it work?

/Cecilia

[former_member186338 · Accepted solution]

Ups, I've read you message once again and a bit lost with the requirements:

Please, confirm that I correctly understand you!

You have some data on MATERIAL: MA.NONE for each brand

And you want to copy this value to all MATERIAL members having same brand?

This is theoretically possible using script logic (but very slow - FOR/NEXT and *WHEN_REF_DATA = MASTER_DATA), because:

*SELECT(%INP_BRANDS%,"[ID]","BRAND","[CALC] = 'N' AND [ID] <> 'BR.NONE'")

// Brands where input is done per brand

*XDIM_MEMBERSET ACCOUNT = AC.S.MKTGROWTH

*XDIM_MEMBERSET CATEGORY = PLAN

*XDIM_MEMBERSET ENTITY = CO.1100

*XDIM_MEMBERSET SEGMENT = SG.NONE

*XDIM_MEMBERSET TIME = <ALL> //??? Huge scope!!!

*XDIM_MEMBERSET MATERIAL<>MA.NONE

*FOR %MY_BRAND% = %INP_BRANDS% //Slow!!!

*XDIM_MEMBERSET BRAND=%MY_BRAND%

*WHEN_REF_DATA = MASTER_DATA //Slow!!!! Affect ALL dimensions - including TIME

*WHEN MATERIAL.BRAND //All materials will be looped except MA.NONE

*IS %MY_BRAND%

  *REC(EXPRESSION = [MATERIAL].[MA.NONE]) // Copy brand value to its materials

*ENDWHEN

*NEXT

//NEVER USE COMMIT WITH WHEN/ENDWHEN!!!!!!

I recommend writing CUSTOM LOGIC badi for this calculation!

B.R. Vadim

[cecilia_petersson2 · Accepted solution]

Hi Vadim,

Thanks for your quick reply! Yes, you understood my intention correctly, and I also realised my test script took hours to run on my still rather limited number of dimensions, members and data. So I'll take your advise and use badi.

/Cecilia

[former_member186338 · Accepted solution]

And it will be relatively simple badi!

Vadim

[former_member186338]

Hi Cecilia,

In order to get Member using property of another member in REC you have to use LOOKUP:

*LOOKUP SameModel

*DIM BR:BRAND=MATERIAL.BRAND

*ENDLOOKUP

*REC(EXPRESSION=LOOKUP(BR)...

Lookup will return brand of current material.

Vadim