Posting hard coded member value in target model using script logic

[former_member488614]

Hello, Experts, I have a requirement to copy data from one model to another model with in the same environment, while copying I need to pass the hard coded value like Company code as 'CC_UNASSGINED' if the source odel doesn't have any value for company code.

I am copying properties data from source to dimensions in destination model.We are in BPC NW 10.0

Any Ideas?

[former_member186338 · Accepted solution]

The correct code with RUNLOGIC_PH:

//Clear data in target for CATEGORY=$DEST_CAT$
*START_BADI RUNLOGIC_PH
QUERY=OFF 
WRITE=ON
APPSET=ENABLE //It's better to remove this line - then current environment will be used
APP=PROJECTREPORTING
LOGIC=CLEAR.LGF //will clear data in PROJECTREPORTING for the scope below
//CLEAR.LGF text:
//*WHEN CATEGORY
//*IS *
//*REC(EXPRESSION=0)
//*ENDWHEN
DIMENSION ACCCLASS=<ALL>
DIMENSION ANALYST=<ALL> 
DIMENSION COCODE=<ALL>
DIMENSION COMMAREA=<ALL>
DIMENSION EXPTYPE=<ALL>
DIMENSION PROJTYPE=<ALL>
DIMENSION RESPCC=<ALL>
DIMENSION PROJMGR=<ALL>
DIMENSION CATEGORY = $DEST_CAT$
*END_BADI

//Copy data to target based on PROJECT properties
*XDIM_MEMBERSET CATEGORY = $DEST_CAT$ 
*DESTINATION_APP = PROJECTREPORTING
*ADD_DIM ACCCLASS = PROJECT:ACCCLASS
*ADD_DIM ANALYST = PROJECT:ANALYST
*ADD_DIM COCODE = PROJECT:COCODE
*ADD_DIM COMMAREA = PROJECT:COMMAREA
*ADD_DIM EXPTYPE = PROJECT:EXPTYPE
*ADD_DIM PROJTYPE = PROJECT:PROJTYPE
*ADD_DIM RESPCC = PROJECT:RESPCC
*ADD_DIM PROJMGR = PROJECT:PROJMGR

*WHEN CATEGORY
*IS *
*REC(EXPRESSION=%VALUE%)
*ENDWHEN

Please read RUNLOGIC_PH documentation:

https://www.sap.com/documents/2015/08/de470383-547c-0010-82c7-eda71af511fa.html

[former_member186338]

With ADD_DIM with property you have to use 2 WHEN/ENDWHEN loops:

*SELECT(%WITHCOCODE%,ID,PROJECT,"[COCODE]<>''")
*SELECT(%WITHOUTCOCODE%,ID,PROJECT,"[COCODE]=''")
*XDIM_MEMBERSET CATEGORY = $DEST_CAT$

*XDIM_MEMBERSET PROJECT = %WITHCOCODE%

*DESTINATION_APP =PROJECTREPORTING
*ADD_DIM COCODE = PROJECT:COCODE //for projects with something in COCODE

*WHEN CATEGORY
*IS *
*REC(EXPRESSION=%VALUE%)
*ENDWHEN 

*XDIM_MEMBERSET PROJECT = %WITHOUTCOCODE%

*DESTINATION_APP =PROJECTREPORTING
*ADD_DIM COCODE = UN_ASSGINED //for projects with empty COCODE

*WHEN CATEGORY
*IS *
*REC(EXPRESSION=%VALUE%)
*ENDWHEN 

It's possible to use single loop if you can implement RENAME_DIM, but not sure about your case.

P.S. I also removed a lot of useless statements from your code.

[former_member488614]

Thank you Vadim, this is working perfectly.

[former_member488614]

Hi, Vadim, thank you for the logic and cleaning the code, I tried this but this time I am getting error Member "UN_ASSGINED" not exist.

Thank you.

[former_member186338]

And do you have this member in COCODE dimension - by the way member ID's are case sensitive!

Please also read: https://blogs.sap.com/2014/01/31/how-to-ask-questions-about-script-logic-issues/

to understand the amount of required information to get help on script logic!

[former_member186338]

May be it's not UN_ASSGNED

But: UN_ASSIGNED???

[former_member488614]

Hi Vadim, I maintained UN_ASSIGNED member in all dimensions where needed, now I don't see any errors in script logic, but when I run DM package system is not writing UN_ASSIGNED in target model and rejecting all blank rows.

Below are my system details

Also, I tested simple script to find K2 calc engine, mine is JAVASCRIPT.

Below is Script logic

*SELECT(%WITHAC%,ID,PROJECT,"[ACCCLASS]<>''")
*SELECT(%WITHOUTAC%,ID,PROJECT,"[ACCCLASS]=''")

*SELECT(%WITHPM%,ID,PROJECT,"[PROJMGR]<>'PM00000000'")
*SELECT(%WITHOUTPM%,ID,PROJECT,"[PROJMGR]='PM00000000'")

*SELECT(%WITHCC%,ID,PROJECT,"[COCODE]<>''")
*SELECT(%WITHOUTCC%,ID,PROJECT,"[COCODE]=''")

*SELECT(%WITHCA%,ID,PROJECT,"[COMMAREA]<>''")
*SELECT(%WITHOUTCA%,ID,PROJECT,"[COMMAREA]=''")

*SELECT(%WITHRC%,ID,PROJECT,"[RESPCC]<>'C00000000'")
*SELECT(%WITHOUTRC%,ID,PROJECT,"[RESPCC]='C00000000'")

*SELECT(%WITHPT%,ID,PROJECT,"[PROJTYPE]<>'PT00'")
*SELECT(%WITHOUTPT%,ID,PROJECT,"[RESPCC]='PT00'")

*SELECT(%WITHET%,ID,PROJECT,"[EXPTYPE]<>'ET00'")
*SELECT(%WITHOUTET%,ID,PROJECT,"[EXPTYPE]='ET00'")

*XDIM_MEMBERSET CATEGORY = $DEST_CAT$
*XDIM_MEMBERSET PROJECT = %WITHAC%
*XDIM_MEMBERSET PROJECT = %WITHPM%
*XDIM_MEMBERSET PROJECT = %WITHCC%
*XDIM_MEMBERSET PROJECT = %WITHCA%
*XDIM_MEMBERSET PROJECT = %WITHRC%
*XDIM_MEMBERSET PROJECT = %WITHPT%
*XDIM_MEMBERSET PROJECT = %WITHET%

*DESTINATION_APP =PROJECTREPORTING

*ADD_DIM COCODE = PROJECT:COCODE    //for projects with something in COCODE
*ADD_DIM COMMAREA = PROJECT:COMMAREA
*ADD_DIM EXPTYPE = PROJECT:EXPTYPE
*ADD_DIM PROJTYPE = PROJECT:PROJTYPE
*ADD_DIM RESPCC = PROJECT:RESPCC
*ADD_DIM PROJMGR = PROJECT:PROJMGR
*ADD_DIM ACCCLASS = PROJECT:ACCCLASS

*WHEN CATEGORY
*IS *
*REC(EXPRESSION=%VALUE%)
*ENDWHEN

*XDIM_MEMBERSET CATEGORY = $DEST_CAT$

*XDIM_MEMBERSET PROJECT = %WITHOUTAC%
*XDIM_MEMBERSET PROJECT = %WITHOUTPM%
*XDIM_MEMBERSET PROJECT = %WITHOUTCC%
*XDIM_MEMBERSET PROJECT = %WITHOUTCA%
*XDIM_MEMBERSET PROJECT = %WITHOUTRC%
*XDIM_MEMBERSET PROJECT = %WITHOUTPT%
*XDIM_MEMBERSET PROJECT = %WITHOUTET%

*DESTINATION_APP =PROJECTREPORTING

*ADD_DIM COCODE = CC_UNASSGINED    //for projects with empty COCODE
*ADD_DIM COMMAREA = CA_UNASSIGNED
*ADD_DIM EXPTYPE = ET_UNASSIGNED
*ADD_DIM PROJTYPE = PT_UNASSIGNED
*ADD_DIM RESPCC = RC_UNASSIGNED
*ADD_DIM PROJMGR = PM_UNASSIGNED
*ADD_DIM ACCCLASS = AC_UNASSIGNED

*WHEN CATEGORY
*IS *
*REC(EXPRESSION=%VALUE%)
*ENDWHEN

Below is UJKT log

LGX: *XDIM_MEMBERSET CATEGORY = $DEST_CAT$

*XDIM_MEMBERSET PROJECT = %WITHAC%

*XDIM_MEMBERSET PROJECT = %WITHPM%

*XDIM_MEMBERSET PROJECT = %WITHCC%

*XDIM_MEMBERSET PROJECT = %WITHCA%

*XDIM_MEMBERSET PROJECT = %WITHRC%

*XDIM_MEMBERSET PROJECT = %WITHPT%

*XDIM_MEMBERSET PROJECT = %WITHET%

*DESTINATION_APP =PROJECTREPORTING

*ADD_DIM COCODE = PROJECT:COCODE

*ADD_DIM COMMAREA = PROJECT:COMMAREA

*ADD_DIM EXPTYPE = PROJECT:EXPTYPE

*ADD_DIM PROJTYPE = PROJECT:PROJTYPE

*ADD_DIM RESPCC = PROJECT:RESPCC

*ADD_DIM PROJMGR = PROJECT:PROJMGR

*ADD_DIM ACCCLASS = PROJECT:ACCCLASS

*WHEN CATEGORY *IS *

*REC(EXPRESSION=%VALUE%)

*ENDWHEN

*XDIM_MEMBERSET CATEGORY = $DEST_CAT$

*XDIM_MEMBERSET PROJECT = %WITHOUTAC%

*XDIM_MEMBERSET PROJECT = %WITHOUTPM%

*XDIM_MEMBERSET PROJECT = %WITHOUTCC%

*XDIM_MEMBERSET PROJECT = %WITHOUTCA%

*XDIM_MEMBERSET PROJECT = %WITHOUTRC%

*XDIM_MEMBERSET PROJECT = %WITHOUTPT%

*XDIM_MEMBERSET PROJECT = %WITHOUTET%

*DESTINATION_APP =PROJECTREPORTING

*ADD_DIM COCODE = CC_UNASSGINED

*ADD_DIM COMMAREA = CA_UNASSIGNED

*ADD_DIM EXPTYPE = ET_UNASSIGNED

*ADD_DIM PROJTYPE = PT_UNASSIGNED

*ADD_DIM RESPCC = RC_UNASSIGNED

*ADD_DIM PROJMGR = PM_UNASSIGNED

*ADD_DIM ACCCLASS = AC_UNASSIGNED

*WHEN CATEGORY *IS *

*REC(EXPRESSION=%VALUE%)

*ENDWHEN

------------------------------------------------------------------------------------------------------------------------------------- LOG: FILE:\ROOT\WEBFOLDERS\ENABLE \ADMINAPP\PROJECTINPUT\TEST.LGF

USER:90001200

APPSET:ENABLE

APPLICATION:PROJECTINPUT

[INFO] GET_DIM_LIST(): I_APPL_ID="PROJECTINPUT", #dimensions=7 CATEGORY,COSTELEMENT,DATASRC,MEASURES,PO,PROJECT,TIME #dim_memberset=2

CATEGORY:$DEST_CAT$,1 in total.

PROJECT:A000001,A002947,A003053,A003102,A003113,...51047 in total.

REC :%VALUE%

CALCULATION BEGIN:

QUERY PROCESSING DATA

UJO_READ:Members invalid On Dimension(CATEGORY)

And Below is DM package log

Please let me know if you need any other Information.

Thank you.

[former_member186338]

Absolutely strange code!

*XDIM_MEMBERSET PROJECT = %WITHOUTAC%
*XDIM_MEMBERSET PROJECT = %WITHOUTPM%
*XDIM_MEMBERSET PROJECT = %WITHOUTCC%
*XDIM_MEMBERSET PROJECT = %WITHOUTCA%
*XDIM_MEMBERSET PROJECT = %WITHOUTRC%
*XDIM_MEMBERSET PROJECT = %WITHOUTPT%
*XDIM_MEMBERSET PROJECT = %WITHOUTET%

Only last statement will be effective....

[former_member488614]

Sorry, Vadim, I am very new to Script logic, so as you see in the code I need to check all 7 properties and load in to reporting cube.In that case, do I need to split this code and write separately for each property?

Thank you for your help on this.

[former_member186338]

If you have a lot of properties then there is no simple solution! May be it's better to fix your master data - fill all properties correctly including all XX_UNASSGINED

[former_member488614]

Yes, I am fixing the master data now and will try to run copy data logic to reporting once that is done, this way no need to check blanks and other junk master data values in code.

[former_member186338]

Yes, it's the correct solution!

Checking for blanks is useful when you have only one property - then you can use code like I posted. But for multible properties it's hard to test all possible combinations in the script logic.

Other alternative is to create a custom logic badi!

[former_member488614]

Hi Vadim, I fixed all master data in input model, now copy to reporting model is working fine, but now the problem is

Issue1:- Since we are loading Input model properties as dimensions in Reporting model if any changes in input property the system writing it as a new record in reporting.

Ex: -Attached document with a scenario for the property COMMAREA yesterday it was ARKOMA, to day business changed it to ADMIN since it is a property in input model it overwrites while loading, but it is DIM in reporting model, it is adding up and $ value is doubled.

Is it possible to clear reporting model data before writing? this way it will update only latest information.

Issue2:- I have a requirement to split January month data in year4 and year5 and allocate equally to all the months in the year-This is for Budget and Forecast, users enter for year4 and year5 only in January month, this data will save in Input model, this amount I need to divide by 12 and allocate equally to all the months of the year while writing into reporting model.

Ex:-For Budget 2018 category user enters data in all the months from 2018 to 2020, for year4 which is 2021 they enter only in 01.2021, same for year5 01.2022. I maintained Year4 and Year5 as a property to Category dimension and maintained year info in that.

The below code is working fine while selecting only one category at a time in DM package, but if I select Budget2018 and Forecast2017 categories at a time it does nothing because year4 and year5 are different for these categories.

Is there a way to select different year4 and year5 in the variable for different categories?

*SELECT(%YEAR4%,YEAR4,CATEGORY,[ID]=$DEST_CAT$)
*SELECT(%4YEAR_MNTH%,ID,TIME,[ID]>=%YEAR4%.01 AND [ID]<=%YEAR4%.12)

*SELECT(%YEAR5%,YEAR5,CATEGORY,[ID]=$DEST_CAT$)
*SELECT(%5YEAR_MNTH%,ID,TIME,[ID]>=%YEAR5%.01 AND [ID]<=%YEAR5%.12)

*XDIM_MEMBERSET CATEGORY = $DEST_CAT$

*DESTINATION_APP =PROJECTREPORTING

*ADD_DIM COCODE = PROJECT:COCODE
*ADD_DIM COMMAREA = PROJECT:COMMAREA
*ADD_DIM EXPTYPE = PROJECT:EXPTYPE
*ADD_DIM PROJTYPE = PROJECT:PROJTYPE
*ADD_DIM RESPCC = PROJECT:RESPCC
*ADD_DIM PROJMGR = PROJECT:PROJMGR
*ADD_DIM ACCCLASS = PROJECT:ACCCLASS

*WHEN CATEGORY
*IS *
*REC(EXPRESSION=%VALUE%)
*ENDWHEN

*WHEN TIME
*IS %4YEAR_MNTH%
*FOR %MONTH%=01,02,03,04,05,06,07,08,09,10,11,12
*REC(EXPRESSION=%VALUE%/12,CATEGORY=$DEST_CAT$,TIME="%YEAR4%.%MONTH%")
*NEXT
*ENDWHEN

*WHEN TIME
*IS %5YEAR_MNTH%
*FOR %MONTH%=01,02,03,04,05,06,07,08,09,10,11,12
*REC(EXPRESSION=%VALUE%/12,CATEGORY=$DEST_CAT$,TIME="%YEAR5%.%MONTH%")
*NEXT
*ENDWHEN


before-change.jpgafter-change.jpg

Please let me know if you are not clear on this.

[former_member186338]

Issue 1 is solved by implementing badi RUNLOGIC_PH and running clear script in the target model using this badi.

For issue 2 - please open a new question!

[former_member488614]

Hi Vadim-I am working with our BASIS team to implement above said badi, in the mean time I am just curious on the below code, can't we clear target model data using this code in input model?

 *XDIM_MEMBERSET CATEGORY = $DEST_CAT$
 *DESTINATION_APP =PROJECTREPORTING
 *WHEN CATEGORY *IS ACTUAL
 *REC(EXPRESSION=0)
 *ENDWHEN

[former_member186338]

No you can't. You have to run script in the target model.

[former_member488614]

Hello, Vadim-Basis team successfully imported RUNLOGIC_PH badi in BW, now how to use this for clearing target model data before loading?

Do I need to create DEFAULT.LGF in target model and call this badi like below?

*START_BADI

RUNLOGIC_PH

*END_BADI

If yes, will this trigger when I run my copy data to target DM package in source model?

Also I have another requirement to load data to target model when users enter any data in input sheets in the source model.

that should trigger my script logic calculation every time a record is saved in source Model. That should only update the new values.In case if any changed record that should clear old record and write only changed record.

for this, I believe I need to create a default logic in the source model and include my above script logic.Can we use RUNLOGIC_PH badi for this purpose too?

Thanks for helping me on this.

[former_member186338]

Please read the documentation about RUNLOGIC_PH - nothing to do with DEFAULT.LGF!!!

In the target model you create script like CLEAR.LGF:

*WHEN CATEGORY
*IS *
*REC(EXPRESSION=0)
*ENDWHEN

And you call this script from RUNLOGIC_PH badi in the main script setting the scope to be cleared.

[former_member186338]

P.S. - other requirements - new question please!

[former_member488614]

Hi Vadim-I created CLEAR.LGF in target model and that logic I am calling in my main script which is in my source model like below, I put this in the beginning of my main script.

*START_BADI RUNLOGIC_PH
QUERY=OFF 
WRITE=ON
APPSET=ENABLE 
APP=PROJECTREPORTING
*XDIM_MEMBERSET CATEGORY = $DEST_CAT$ 
*DESTINATION_APP = PROJECTREPORTING
DIMENSION ACCCLASS=*
DIMENSION ANALYST=* 
DIMENSION COCODE=* 
DIMENSION EXPTYPE=* 
DIMENSION PROJTYPE=* 
DIMENSION RESPCC=* 
DIMENSION PROJMGR=* 
DIMENSION COMMAREA=* 
*ADD_DIM COCODE = PROJECT:COCODE
*ADD_DIM COMMAREA = PROJECT:COMMAREA
*ADD_DIM EXPTYPE = PROJECT:EXPTYPE
*ADD_DIM PROJTYPE = PROJECT:PROJTYPE
*ADD_DIM RESPCC = PROJECT:RESPCC
*ADD_DIM PROJMGR = PROJECT:PROJMGR
*ADD_DIM ACCCLASS = PROJECT:ACCCLASS
*ADD_DIM ANALYST = PROJECT:ANALYST
LOGIC=CLEAR.LGF
*END_BADI 

then my main script is staring from select statements as poseted in earlier comments.
Is this the way to clear or do I need to do any cleanup?
I tested this and working fine,below is my DM package log.
 
LOG BEGIN TIME:2017-08-03 11:13:06
FILE:\ROOT\WEBFOLDERS\ENABLE\ADMINAPP\PROJECTINPUT\REPORTING_MODEL_CALC.LGF
USER:90001200
APPSET:ENABLE
APPLICATION:PROJECTINPUT
[WARNING!] NO MEMBER SPECIFIED FOR DIMENSION:COSTELEMENT WILL QUERY ON ALL BASE MEMBERS.
[WARNING!] NO MEMBER SPECIFIED FOR DIMENSION:DATASRC WILL QUERY ON ALL BASE MEMBERS.
[WARNING!] NO MEMBER SPECIFIED FOR DIMENSION:PO WILL QUERY ON ALL BASE MEMBERS.
[WARNING!] NO MEMBER SPECIFIED FOR DIMENSION:PROJECT WILL QUERY ON ALL BASE MEMBERS.
[WARNING!] NO MEMBER SPECIFIED FOR DIMENSION:TIME WILL QUERY ON ALL BASE MEMBERS.
EXECUTION BADI:RUNLOGIC_PH
QUERY: OFF
WRITE: ON
LOG BEGIN TIME:2017-08-03 11:13:05
FILE:\ROOT\WEBFOLDERS\ENABLE\ADMINAPP\PROJECTINPUT\REPORTING_MODEL_CALC.LGF
USER:90001200
APPSET:ENABLE
APPLICATION:PROJECTREPORTING
[INFO] GET_DIM_LIST(): I_APPL_ID="PROJECTREPORTING", #dimensions=15
ACCCLASS,ANALYST,CATEGORY,COCODE,COMMAREA,COSTELEMENT,DATASRC,EXPTYPE,MEASURES,PO,PROJECT,PROJMGR,PROJTYPE,RESPCC,TIME


#dim_memberset=9
CATEGORY:BUD2018,1 in total.
ACCCLASS:*,1 in total.
ANALYST:*,1 in total.
COCODE:*,1 in total.
EXPTYPE:*,1 in total.
PROJTYPE:*,1 in total.
RESPCC:*,1 in total.
PROJMGR:*,1 in total.
COMMAREA:*,1 in total.

[former_member186338]

Sorry, looks like you don't want to read documentation!

And where did you seen this syntax????????????????

DIMENSION ACCCLASS=*

[former_member488614]

Hi Vadim, I have an input model with some dimensions and many properties for one of the dimension, for the reporting purpose we are moving these properties data into reporting model, all these properties we have created as dimensions in the reporting model.

Ex:- I have a dimension 'Project' in input model and 'Company code' as a property to that, in the reporting model my dimensions are 'Project' and 'Company code', I am copying data from input model to Reporting model and loading 'Company code' property data to Company code dimension in reporting model, but we have some blank values coming for 'Company code', since it is a dimension in reporting model my data copy is failing, I would like to pass hard coded value 'CC_UNASSGINED' when there is a blank value for 'Company code' property coming from Input model.

*SELECT(%CC%,COCODE,PROJECT,"[COCODE]=''")

*XDIM_MEMBERSET CATEGORY = $DEST_CAT$

*DESTINATION_APP =PROJECTREPORTING

*ADD_DIM COCODE = PROJECT:COCODE

*WHEN CATEGORY

*IS $DEST_CAT$

*WHEN PROJECT.COCODE

*IS %CC%

*REC(EXPRESSION=%VALUE%,CATEGORY=$DEST_CAT$,COCODE='UN_ASSGINED')

*ENDWHEN

The error I am getting as Unknown Dimension Name in Keyword:"COCODE:

Looks like the error at line *REC( and it is not recognizing COCODE Dimension in reporting model, even I tried with PROJECT:COCODE and PROJECT.COCODE but same error.

Hope this is clear, thank you for your help.

[former_member186338]

Sorry but absolutely not clear what do you want to achieve! Please show detailed examples.