Select Statement Issue in Logic Script

[Former Member]

Hello Experts,

I would like to use logic script to copy data for some months from one year to another.

what I did is to input year(Version 2) or year.month(Version 3) from Time dimesion property YEAR(Version 2) or ID(Version 3) to get the Year code and then loop for the months which hardcoded as below.

However, only version 1 is working, but version 2 and version 3 got the same error when I ran from the package  RUN_LOGIC:Invalid "select" statement.

I am sure that the statement like *SELECT(%TARGET_YR%, "[YEAR]", TIME, "[ID] = '$TARGET_YEAR$'") will not pass the validation in Logic Script, but it does work when you run the package.

Could someone help on it? Thank you very much for your kindly help in Advance

Wei

Logic Script Code:

//This LG is to copy data from A YEAR to ANOTHER

//Version 1 working well

//*SELECT(%SOURCE_YR%, "[YEAR]", TIME, "[ID] = 2006.01")

//*SELECT(%TARGET_YR%, "[YEAR]", TIME, "[ID] = 2009.01")

//Version 2 Not working, Error: RUN_LOGIC:Invalid "select" statement

//*SELECT(%SOURCE_YR%, "[YEAR]", TIME, "[YEAR] = '$SOURCE_YEAR$'")  

//*SELECT(%TARGET_YR%, "[YEAR]", TIME, "[YEAR] = '$TARGET_YEAR$'")

//Version 3 Not Working, Error: RUN_LOGIC:Invalid "select" statement

*SELECT(%SOURCE_YR%, "[YEAR]", TIME, "[ID] = '$SOURCE_YEAR$'")

*SELECT(%TARGET_YR%, "[YEAR]", TIME, "[ID] = '$TARGET_YEAR$'")

*XDIM_MEMBERSET PRODUCT = ProductA

*XDIM_MEMBERSET ENTITY = CSO

*WHEN ACCOUNT

  *IS *

    *FOR %MONTHNUM% = .01, .02, .03

  *REC(EXPRESSION = ([TIME].[TMVL(0,%SOURCE_YR%%MONTHNUM%)]),TIME = TMVL(0, %TARGET_YR%%MONTHNUM%),PRODUCT = %PRODUCT_SET%)

    *NEXT

*ENDWHEN

*COMMIT

Data Manager Code:

PROMPT(SELECTINPUT,%SELECTION%,,"Select Target Product","PRODUCT")

PROMPT(TEXT, %SOURCE_YEAR%, "Enter the Source Year, e.g. 2006" , ,) //For Version 2

PROMPT(TEXT, %TARGET_YEAR%, "Enter the Target Year, e.g. 2007" , ,) //For Version 2

PROMPT(TEXT, %SOURCE_YEAR%, "Enter the Source Year.month, e.g. 2006.01" , ,) //For Version 3

PROMPT(TEXT, %TARGET_YEAR%, "Enter the Target Year.month, e.g. 2007.01" , ,) //For Version 3

INFO(%EQU%,=)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SUSER,%USER%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPPSET,%APPSET%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,%APP%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SELECTION,%SELECTION%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,DATA_REPLICATION.LGF)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,REPLACEPARAM, SOURCE_YEAR%EQU%%SOURCE_YEAR%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,REPLACEPARAM, TARGET_YEAR%EQU%%TARGET_YEAR%)

[former_member186338 · Accepted solution]

Not:

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,REPLACEPARAM, SOURCE_YEAR%EQU%%SOURCE_YEAR%)

But

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,MEMBERSELECTION, SOURCE_YEAR%EQU%%SOURCE_YEAR%)

SELECTINPUT Prompt() Command - SAP BusinessObjects Planning and Consolidation - SAP Library

Vadim

[former_member186338 · Accepted solution]

By the way, the code in WHEN/ENDWHEN is strange... please explain what you want to achieve:

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Thank you very much for your kindly help.

However, it is still not working.

PROMPT(SELECTINPUT,%SELECTION%,,"Select Target Product","PRODUCT")

PROMPT(TEXT, %SOURCE_YEAR%, "Enter the Source Year, e.g. 2006.01" , ,)

PROMPT(TEXT, %TARGET_YEAR%, "Enter the Target Year, e.g. 2007.01" , ,)

INFO(%EQU%,=)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SUSER,%USER%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPPSET,%APPSET%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,%APP%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SELECTION,%SELECTION%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,DATA_REPLICATION.LGF)

'TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,REPLACEPARAM, SOURCE_YEAR%EQU%%SOURCE_YEAR%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,MEMBERSELECTION, SOURCE_YEAR%EQU%%SOURCE_YEAR%)

'TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,REPLACEPARAM, TARGET_YEAR%EQU%%TARGET_YEAR%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,MEMBERSELECTION, TARGET_YEAR%EQU%%TARGET_YEAR%)

Please see the error below. The error is the same as before.

In the arcticle you sent to me earlier, it uses REPLACEPARAM and MEMBERSELECTION both, so I am still a bit confused here.

TASK(/CPMB/ALLOCATION_LOGIC,REPLACEPARAM,ASARENT%EQU%%ASARENT%%TAB%SOURCE%EQU%%SOURCEENTITY%%TAB

%TARGET%EQU%%TARGETENTITY%)

TASK(/CPMB/ALLOCATION_LOGIC,MEMBERSELECTION,SOURCE%EQU%%SOURCEENTITY%%TAB %TARGET%EQU%%TARGETENTITY%)

by the way, the logic in the WHEN and ENDWHEN is just to use copy data from a year for only Jan, Feb an Mar to another Year, it is just a practice.

Please kindly help again.

Thank you very much.

Regards,

Wei

[former_member186338 · Accepted solution]

First: For SELECTINPUT result you have to use MEMBERSELECTION! And you can see that variables are correctly assigned. In the script the correct line has to be:

*SELECT(%SOURCE_YR%, "[YEAR]", TIME, "[ID] = $SOURCE_YEAR$")

[ID] - not [YEAR]

Vadim

[former_member186338 · Accepted solution]

P.S.  REPLACEPARAM is used for simple text variables coming from PROMPT(TEXT..., PROMPT(COMBOBOX... etc...

SELECTINPUT generates the long string like you have in SELECTION. In order to get member ID's from this string MEMBERSELECTION is used.

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Thank you very much again.

Per my understanding,  the difference between MEMEBERSELECTION and REPLACEPARAM is:

if you want to parse a string to a Dimension ID, then you use Selectinput with  MEMEBERSELECTION, such as 2006.01 to Time Dim Year Month ID

if you want to parese a string to a Dimension Property, then you use TEXT with REPLACEPARAM, such as 2006 to Time Dime Year Property

is that right?

So I changed the code to as below in Red:

***********************************************************************************************************

PROMPT(SELECTINPUT,%SELECTION%,,"Select Target Product","PRODUCT")

'PROMPT(TEXT, %SOURCE_YEAR%, "Enter the Source Year, e.g. 2006.01" , ,)

'PROMPT(TEXT, %TARGET_YEAR%, "Enter the Target Year, e.g. 2007.01" , ,)

PROMPT(SELECTINPUT, %SOURCE_YM%, , "Select the Source Year Month, e.g. 2006.01" , "TIME")

PROMPT(SELECTINPUT, %TARGET_YM%, , "Select the Target Year Month, e.g. 2007.01" , "TIME")

INFO(%EQU%,=)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SUSER,%USER%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPPSET,%APPSET%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,%APP%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SELECTION,%SELECTION%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,DATA_REPLICATION.LGF)

'TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,REPLACEPARAM, SOURCE_YEAR%EQU%%SOURCE_YEAR%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,MEMBERSELECTION, SOURCE_YM%EQU%%SOURCE_YM%)

'TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,REPLACEPARAM, TARGET_YEAR%EQU%%TARGET_YEAR%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,MEMBERSELECTION, TARGET_YM%EQU%%TARGET_YM%)

***********************************************************************************************************

In LGF:

***********************************************************************************************************

*SELECT(%SOURCE_YRS%, "[YEAR]", TIME, "[ID] = '$SOURCE_YM$'")

*SELECT(%TARGET_YRS%, "[YEAR]", TIME, "[ID] = '$TARGET_YM$'")

*XDIM_MEMBERSET PRODUCT = ProductA

*XDIM_MEMBERSET ENTITY = CSO

*WHEN ACCOUNT

*IS *

*FOR %SOURCE_YR% = %SOURCE_YRS%

*FOR %TARGET_YR% = %TARGET_YRS%

*FOR %MONTHNUM% = .01, .02, .03

*REC(EXPRESSION = ([TIME].[TMVL(0,%SOURCE_YR%%MONTHNUM%)]),TIME = TMVL(0, %TARGET_YR%%MONTHNUM%),PRODUCT = %PRODUCT_SET%)

*NEXT

*NEXT

*NEXT

*ENDWHEN

*COMMIT

***********************************************************************************************************

However, the error is still the same as below, Please help again. Thank you very much again!

Regards,

Wei

[former_member186338 · Accepted solution]

Some lines are missing in the DM advanced script! The correct will be:

PROMPT(SELECTINPUT,%SELECTION%,,"Select Target Product","PRODUCT")

PROMPT(SELECTINPUT, %SOURCE_YM%, , "Select the Source Year Month, e.g. 2006.01" , "TIME")

PROMPT(SELECTINPUT, %TARGET_YM%, , "Select the Target Year Month, e.g. 2007.01" , "TIME")

INFO(%EQU%,=)

INFO(%TAB%,;)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,EQU,%EQU%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,TAB,%TAB%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SUSER,%USER%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPPSET,%APPSET%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,%APP%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SELECTION,%SELECTION%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,DATA_REPLICATION.LGF)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,MEMBERSELECTION, SOURCE_YM%EQU%%SOURCE_YM%%TAB%TARGET_YM%EQU%%TARGET_YM%)

Vadim

[former_member186338 · Accepted solution]

By the way, you script logic is strange, can you explain in words what do you want to achieve?

[Former Member · Accepted solution]

Hi Vadim,

Thank you very  much for your patience again. It is working.

So the variables have to be written into the same TASK Statement with MEMEBERSELECTION or REPLACEPARMA seperated by TAB(;) when using SELECTINPUT.

Thank you very much again!

Regards,

Wei Kong

[former_member186338 · Accepted solution]

Your error was missing lines:

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,EQU,%EQU%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,TAB,%TAB%)