BPC script logic - Using For/Next inside Lookup

[brett_hutchinson]

Hi everyone:

I'm having an issue with my script logic. We are on BPC 7.5 NW. I am trying to use a for next loop within a lookup statement to retrieve a series of health plan amounts from our headcount application.

Here is an example of how the data may look:

HEALTHPLAN1     450.00

HEALTHPLAN2     5000.00

HEALTHPLAN3     10000.000

...

...

I am hoping to be able to retrieve those different amounts using only script logic. However, what is happening is that for every health plan retrieved it's only grabbing the first amount (450.00 from example above).

My script is below. Can anyone advise on what I'm doing wrong?

CMMT_ITEM is our account dimension and FM_AREA is the entity. Others custom names are user-defined.

Thanks

Brett

*SELECT(%NYBD%,"YEAR_TOTAL","CATEGORY","[ID]='BASE_BUDGET'")

*SELECT(%CURCAT%,"ID","CATEGORY","RUN_FCST_CALCS='Y'")

*SELECT(%PLAN%,"ID","CMMT_ITEM","CMMT_ITEM_TYPE='5'")

*XDIM_MEMBERSET TIME = %NYBD%

*XDIM_MEMBERSET RPTCURRENCY = USD

*XDIM_MEMBERSET CATEGORY = ACTUAL

*XDIM_MEMBERSET DATA_SOURCE = INPUT

*XDIM_MEMBERSET FM_AREA = SC01

*XDIM_MEMBERSET FUND_CTR = BAS(SC01ORGANZTNL.STRT)

*XDIM_MEMBERSET FUND = BAS(SC01FUND.STRT)

*XDIM_MEMBERSET MEASURES = PERIODIC

*XDIM_MEMBERSET BAND = BAS(ALL_BANDS)

*XDIM_MEMBERSET HR_POSITION = BAS(ALL_POS)

*XDIM_MEMBERSET POS_TYPE = BAS(ALL_POS_TYPES)

*XDIM_MEMBERSET CMMT_ITEM = XFER_IN_DATE

*LOOKUP HEADCOUNT

*DIM DATE_DIFF: CMMT_ITEM=DATE_DIFF

*DIM DATE_DIFF: DATA_SOURCE=LOGIC

*DIM DATE_DIFF: CATEGORY=%CURCAT%

*DIM HLT_PLAN: DATA_SOURCE=BUD_INDICES

*DIM HLT_PLAN: CATEGORY=%CURCAT%

*DIM HLT_PLAN: FUND=SC01NF

*DIM HLT_PLAN: FUND_CTR=SC01NFC

*DIM HLT_PLAN: HR_POSITION=NPOS

*DIM HLT_PLAN: POS_TYPE=NPOSTYPE

*DIM HLT_PLAN: BAND=NBAND

*FOR %LOOP_PLAN%=%PLAN%

*DIM HLT_PLAN: CMMT_ITEM=%LOOP_PLAN%

*NEXT

*ENDLOOKUP

*WHEN TIME

*IS %NYBD%

*FOR %LOOP_PLAN%=%PLAN%

*REC(EXPRESSION=LOOKUP(DATE_DIFF)*LOOKUP(HLT_PLAN),DATA_SOURCE=TEMP_TRANSFER,CMMT_ITEM=%LOOP_PLAN%,CATEGORY=%CATEGORY_SET%)

*NEXT

*ENDWHEN

*COMMIT

[former_member186338 · Accepted solution]

Hi Brett,

Your syntax of the LOOKUP is incorrect. Please read help on the LOOKUP function and check examples. In general, LOOKUP is used to get one value based on the label corresponding to some fixed member or to the property of some member.

In the LOOKUP it's possible to define different labels (using FOR/NEXT) for different members and then use this labels in the LOOKUP call. But other dimensions has to be fixed! And all labels has to be different in LOOKUP definition.

//LOOKUP for different plans

...

*FOR %LOOP_PLAN%=%PLAN%

*DIM HLT_%LOOP_PLAN%: CMMT_ITEM=%LOOP_PLAN% //the label will contain prefix HLT_ and the name of plan

*NEXT

...

*FOR %L_PLAN%=%PLAN% //use different var name

*REC(EXPRESSION=LOOKUP(HLT_%L_PLAN%),DATA_SOURCE=TEMP_TRANSFER,CMMT_ITEM=%LOOP_PLAN%,CATEGORY=%CATEGORY_SET%)

*NEXT

I can't provide the full code without detailed info about dimensions of the current application and of the headcount application.

And don't use COMMIT - useless for WHEN/ENDWHEN

B.R. Vadim

P.S. And please, delete the duplicated post!

[brett_hutchinson · Accepted solution]

Thanks Vadim! That was the code I needed. I could not find a good example of this type of code anywhere though until you provided it for me. Can you provide a good link where more advanced script logic documentation is available? I have training material (BPC 420 class) and have looked on help.sap.com, scn, etc. but the examples that I've seen only provide simple examples. Maybe I'm just not seeing it. I also have a copy of the script logic white paper.

Also, good to know about when/endwhen and commit. All of the examples I have seen always use commit after the endwhen.

I did have to remove the other variable from my lookup (it is now retrieved as part of the expression), so if anyone is interested, here is the final code:

*SELECT(%NYBD%,"YEAR_TOTAL","CATEGORY","[ID]='BASE_BUDGET'")

*SELECT(%CURCAT%,"ID","CATEGORY","RUN_FCST_CALCS='Y'")

*SELECT(%PLAN%,"ID","CMMT_ITEM","CMMT_ITEM_TYPE='5'")

*XDIM_MEMBERSET TIME = %NYBD%

*XDIM_MEMBERSET RPTCURRENCY = USD

*XDIM_MEMBERSET CATEGORY = ACTUAL

*XDIM_MEMBERSET DATA_SOURCE = INPUT

*XDIM_MEMBERSET FM_AREA = SC01

*XDIM_MEMBERSET FUND_CTR = BAS(SC01ORGANZTNL.STRT)

*XDIM_MEMBERSET FUND = BAS(SC01FUND.STRT)

*XDIM_MEMBERSET MEASURES = PERIODIC

*XDIM_MEMBERSET BAND = BAS(ALL_BANDS)

*XDIM_MEMBERSET HR_POSITION = BAS(ALL_POS)

*XDIM_MEMBERSET POS_TYPE = BAS(ALL_POS_TYPES)

*XDIM_MEMBERSET CMMT_ITEM = XFER_OUT_DATE

*LOOKUP HEADCOUNT

*DIM DATA_SOURCE=BUD_INDICES

*DIM CATEGORY=%CURCAT%

*DIM FUND=SC01NF

*DIM FUND_CTR=SC01NFC

*DIM HR_POSITION=NPOS

*DIM POS_TYPE=NPOSTYPE

*DIM BAND=NBAND

*FOR %LOOP_PLAN%=%PLAN%

*DIM HLT_%LOOP_PLAN%: CMMT_ITEM=%LOOP_PLAN%

*NEXT

*ENDLOOKUP

*WHEN TIME

*IS %NYBD%

*FOR %L_PLAN%=%PLAN%

*REC(EXPRESSION=([CATEGORY].[%CURCAT%],[CMMT_ITEM].[DATE_DIFF],[DATA_SOURCE].[LOGIC])*LOOKUP(HLT_%L_PLAN%),DATA_SOURCE=TEMP_TRANSFER,CMMT_ITEM=%L_PLAN%,CATEGORY=%CATEGORY_SET%)

*NEXT

*ENDWHEN

Thanks again.

Brett

[former_member186338 · Accepted solution]

Hi Brett,

Unfortunately I can't provide you some better script logic documents then you have already mention. Use search in this forum, or if you have some ABAP skills, try to debug in ABAP script logic execution... can be very interesting

B.R. Vadim

[brett_hutchinson · Accepted solution]

Yes. I am much more comfortable with ABAP than I am with script logic for the time being. I think I found some documentation on how to get from UJKT into the regular ABAP debugger, so I will give that a shot.

Thanks again.

Brett

[former_member186338 · Accepted solution]

Hi Brett,

One additional thing to mention:

After I looked on your final code I understood that you are doing LOOKUP to the same cube where you are executing script. In this case it's better not to use LOOKUP at all, but to use long tuple expression:

*FOR %L_PLAN%=%PLAN%

*REC(EXPRESSION=([CATEGORY].[%CURCAT%],[CMMT_ITEM].[DATE_DIFF],[DATA_SOURCE].[LOGIC])*([DATA_SOURCE].[BUD_INDICES],[CATEGORY].[%CURCAT%],[FUND].[SC01NF],[FUND_CTR].[SC01NFC],[HR_POSITION].[NPOS],[POS_TYPE].[NPOSTYPE],[BAND].[NBAND],[CMMT_ITEM].[%L_PLAN%]),DATA_SOURCE=TEMP_TRANSFER,CMMT_ITEM=%L_PLAN%,CATEGORY=%CATEGORY_SET%)

*NEXT

LOOKUP is required to get data from another cube, for the same cube it's better to use tuple.

The only exception is when you want to select member in the REC expression by the property of another member. In this case LOOKUP is the only solution to my mind.

Example:

Members of ACCOUNT dimension are: A,B,C,D,S1,S2

You want to perform the following calculations:

S1=A*C

S2=B*D

You can define a property like MULT in the ACCOUNT dimension and fill it:

For A - C

For B - D

You can also define another property like TARGET and fill it:

For A - S1

For B - S2

Then you can define LOOKUP

*LOOKUP SOMECURRENTAPP

*DIM M:ACCOUNT=ACCOUNT.MULT

*ENDLOOKUP

*SELECT(%ACC%,"[ID]",ACCOUNT,"[MULT]<>''") //select accounts with non empty MULT property

*XDIM_MEMBERSET ACCOUNT=%ACC% //A and B will be scoped

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=%VALUE%*LOOKUP(M),ACCOUNT={ACCOUNT].TARGET)

*ENDWHEN

B.R. Vadim

Message was edited by: Vadim Kalinin - Example added!

[brett_hutchinson · Accepted solution]

Thanks Vadim. I made the change and re-tested and the output is the same. I appreciate all of the helpful ideas and tips.

Brett

[former_member186338 · Accepted solution]

Hi Brett,

You can simply investigate the code of UJK_SCRIPT_LOGIC_EXECUTE and below to understand the script logic parsing and execution methods.

B.R. Vadim

[Former Member]

Hello Bret

Do you by any chance have the practice 420, 440 Planning and Consolidation BW Excel files for practice?

I just need the excel data files.

I appreciate your help.