Passing parameters to Logic script from DTS

[Former Member]

I have problems to use a parameter in my logic file that I create using EvModifyPkg.

 PROMPT(RADIOBUTTON,%CHECKLCK%,"Select whether to check work status settings when running logic.",1,{"Yes, check for work status settings before running logic","No, do not check work status settings"},{"1","0"})
PROMPT(TEXT,%FRWEEK%,"Select a Week to Copy from",,"")
PROMPT(TEXT,%TOWEEK%,"Select a list of Weeks to Copy to",,"")
TASK(Execute formulas,USER,%USER%)
TASK(Execute formulas,APPSET,%APPSET%)
TASK(Execute formulas,APP,%APP%)
TASK(Execute formulas,LOGICFILE,"%LOGICPATH%CopyWeeks.lgf")
TASK(Execute formulas,FORMULASCRIPT,"*FUNCTION FRWEEK=%FRWEEK%")
TASK(Execute formulas,FORMULASCRIPT,"*FUNCTION TOWEEK=%TOWEEK%")
TASK(Execute formulas,RUNMODE,1)
TASK(Execute formulas,LOGICMODE,1)
TASK(Execute formulas,CHECKLCK,%CHECKLCK%)

This is my DTS package code.

In my Logic file I have the following reference to %FRWEEK%

*XDIM_MEMBERSET TIMEW=%FRWEEK%

I keep getting the error Invalid member %FRWEEK%

Is there something I'm missing in my setup?

[former_member186498 · Accepted solution]

Hi,

the only parameters that script logic receive from a package are <DIM>_SET where <DIM> is the name of a dimension.

You can pass the parameters to the task defining GLOBAL the variables and adding it in the dtsx (Package explorer/variables)

or to a stored procedures.

Best regards

Roberto

[Former Member · Accepted solution]

In documentation there is a section in Data Management EvDTSModifyPkg/Dynamically pass text to logic with following content:

You can use the EvDTSModifyPkg task to dynamically pass a text string to logic in Data Manager. For example, a user who wishes to dynamically pass a text string representing a year (which is a portion of the *XDIM_MEMBERSET instruction) could use the following steps:

1. Using the EvDTSModifyPkg task, prompt for the year, i.e., PROMPT(TEXT,%TEXT%,"select a year")

2. Pass the returned %TEXT% to the FormulaScript of the RunLogic task as follows: TASK(RUNLOGIC,FORMULASCRIPT,"*FUNCTION MYYEAR=%TEXT%u201D)

3. In the Data Manager logic, use the dynamically created function as follows: *XDIM_MEMBERSET TIME=MYYEAR.INPUT.

The logic name in the RunLogic task must be specified with the .LGF extension to enforce its validation at run time.

However I have also tested the variable FRWEEK.INPUT but that is also an invalid member.

Edited by: Clas Uddenäs on Nov 24, 2011 3:30 PM

[former_member186498 · Accepted solution]

Hi,

can you put the link of the documentation that you are looking?

Best regards

Roberto

Edited by: Roberto Vidotti on Nov 24, 2011 3:41 PM

[JohnL · Accepted solution]

Hi Clas,

Try placing the FORMULASCRIPT tasks before the LOGICFILE task:

i.e.

TASK(Execute formulas,FORMULASCRIPT,"*FUNCTION FRWEEK=%FRWEEK%")

TASK(Execute formulas,FORMULASCRIPT,"*FUNCTION TOWEEK=%TOWEEK%")

TASK(Execute formulas,LOGICFILE,"%LOGICPATH%CopyWeeks.lgf")

Can you also turn DEBUG mode on.

Remove the single quote before the DEBUG(ON) command.

'DEBUG(ON)

Paste the contents of the log here which should show all the variables.

Thanks,

John

[Former Member · Accepted solution]

Hi,

http://<APPSERVER>/OSOFT/Help/DataManager/helpstart.htm

Then open 'Custom DTS tasks/EvDTSModifyPkg/Dynamically pass text to logic'

PS. We are running SAP BPC 7.0.117

Clas

[Former Member · Accepted solution]

Hi John,

I receive the same error if I have FORMULASCRIPT before or after. I started with it before (makes more sence) but tried then after as well.

Please se DEBUG info below.

11-24-2011 15:55:06 - Debug turned ON

TASK(Execute formulas, USER, TAB\clas.uddenaes)

TASK(Execute formulas, APPSET, Clas_Dev)

TASK(Execute formulas, APP, WSALES)

TASK(Execute formulas, FORMULASCRIPT, *FUNCTION FRWEEK=2009.w3)

TASK(Execute formulas, FORMULASCRIPT, *FUNCTION TOWEEK=2009.w4,2009.w5)

TASK(Execute formulas, LOGICFILE, C:\BPC\Data\WebFolders\Clas_Dev\Adminapp\WSALES\CopyWeeks.lgf)

TASK(Execute formulas, RUNMODE, 1)

TASK(Execute formulas, LOGICMODE, 1)

TASK(Execute formulas, CHECKLCK, 1)

INFO(%CHECKLCK%, 1)

INFO(%FRWEEK%, 2009.w3)

INFO(%TOWEEK%, 2009.w4,2009.w5)

INFO(%APPSET%, Clas_Dev)

INFO(%APP%, WSALES)

INFO(%DEFAULT_MEASURE%, PERIODIC)

INFO(%MEASURES%, YTD,Periodic,MTD,QTD,R12)

INFO(%OLAPSERVER%, TABTBGSRV026\BPC7)

INFO(%SQLSERVER%, TABTBGSRV026\BPC7)

INFO(%APPPATH%, C:\BPC\Data\WebFolders\Clas_Dev\WSALES\)

INFO(%DATAPATH%, C:\BPC\Data\WebFolders\Clas_Dev\WSALES\DataManager\DataFiles\)

INFO(%DATAROOTPATH%, C:\BPC\Data\WebFolders\)

INFO(%SELECTIONPATH%, C:\BPC\Data\WebFolders\Clas_Dev\WSALES\DataManager\SelectionFiles\)

INFO(%CONVERSIONPATH%, C:\BPC\Data\WebFolders\Clas_Dev\WSALES\DataManager\ConversionFiles\)

INFO(%TEMPPATH%, C:\BPC\Data\WebFolders\Clas_Dev\WSALES\PrivatePublications\clas.uddenaes\TempFiles\)

INFO(%LOGICPATH%, C:\BPC\Data\WebFolders\Clas_Dev\Adminapp\WSALES\)

INFO(%TRANSFORMATIONPATH%, C:\BPC\Data\WebFolders\Clas_Dev\WSALES\DataManager\TransformationFiles\)

INFO(%DIMS%, [Account_WSALE],[Category],[Channels_WH],[DataSrc],[Entity],[Operating],[RptCurrency],[TimeW])

INFO(%FACTDIMS%, [Account_WSALE],[Category],[Channels_WH],[DataSrc],[Entity],[Operating],[RptCurrency],[TIMEID])

INFO(%CATEGORY_DIM%, [Category])

INFO(%TIME_DIM%, [TimeW])

INFO(%ENTITY_DIM%, [Entity])

INFO(%ACCOUNT_DIM%, [Account_WSALE])

INFO(%CURRENCY_DIM%, [RptCurrency])

INFO(%APP_LIST%, CR,Disputes,ENCL,ENCL22,Finance,ICMatching,InternalControl,ITT,MANEXC,Ownership,PROPERTY,Rate,Rate_PR,RateW,RateYear,RawMaterial,Risks,Treasury,TRIO,WORKFLOW,WSALES)

INFO(DATAMGRGLOBALBPU, )

INFO(DATAMGRGLOBALCLIENTMACHINEID, TABSTRCTX003)

INFO(DATAMGRGLOBALERROR, )

INFO(DATAMGRGLOBALPACKAGEINFOR, )

INFO(DATAMGRGLOBALPACKAGENAME, C:\BPC\Data\WebFolders\Clas_Dev\WSALES\DataManager\PackageFiles\SYSTEM FILES/CopyWeeks.dtsx)

INFO(DATAMGRGLOBALSEQ, 7)

INFO(DATAMGRGLOBALSITEID, )

INFO(MODIFYSCRIPT, DEBUG(ON)<BR>PROMPT(RADIOBUTTON,1,"Select whether to check work status settings when running logic.",1,{"Yes, check for work status settings before running logic","No, do not check work status settings"},{"1","0"})<BR>'PROMPT(COPYMOVEINPUT,2009.w3,2009.w4,2009.w5,"Please select From and To weeks",[TimeW])<BR>PROMPT(TEXT,2009.w3,"Select a Week to Copy from",,"")<BR>PROMPT(TEXT,2009.w4,2009.w5,"Select a list of Weeks to Copy to",,"")<BR>'PROMPT(SELECTINPUT,,,"Please select category, entity and time for restatement",[Category][Entity][TimeW])<BR>TASK(Execute formulas,USER,TAB\clas.uddenaes)<BR>TASK(Execute formulas,APPSET,Clas_Dev)<BR>TASK(Execute formulas,APP,WSALES)<BR>'TASK(Execute formulas,SELECTION,2009.w3)<BR>TASK(Execute formulas,FORMULASCRIPT,"FUNCTION FRWEEK=2009.w3")<BR>TASK(Execute formulas,FORMULASCRIPT,"FUNCTION TOWEEK=2009.w4,2009.w5")<BR>TASK(Execute formulas,LOGICFILE,"C:\BPC\Data\WebFolders\Clas_Dev\Adminapp\WSALES\CopyWeeks.lgf")<BR><BR>TASK(Execute formulas,RUNMODE,1)<BR>TASK(Execute formulas,LOGICMODE,1)<BR>TASK(Execute formulas,CHECKLCK,1)

Clas

[JohnL · Accepted solution]

Can you post your whole logic file?

CopyWeeks.lgf

Do you have *INCLUDE statements within it?

Thanks,

John

[Former Member · Accepted solution]

Hi John,

Here is my CopyWeeks.lgf content:

*MEMBERSET(%SEAL%,"DESCENDANTS([SEAL],999,LEAVES)")
*MEMBERSET(%WHEEL%,"DESCENDANTS([WHEEL],999,LEAVES)")
*MEMBERSET(%TIP%,"DESCENDANTS([EIP],999,LEAVES)")

*XDIM_MEMBERSET OPERATING=%SEAL%,%WHEEL%,%TIP%
*XDIM_MEMBERSET TIMEW=%FRWEEK%

*WHEN *
*IS *
*FOR %TM%=%TOWEEK%
*REC(TIMEW=%TM%)
*NEXT
*ENDWHEN
*COMMIT

Clas

[JohnL · Accepted solution]

Hi Clas,

I believe this is a limitation with the TASK(Execute formulas,FORMULASCRIPT ) function where that you can only pass 1 variable from a prompt to a logic script.

I tested many variations and I wasn't able to get this working.

However I did come up with a viable and even better workaround:

In your DTSX MODIFYSCRIPT variable remove the PROMPTS and FORMULASCRIPT and add the following line:

PROMPT(COPYMOVE,%SELECTION%,TOSELECTION%,"Enter your selection",Time)
TASK(Execute formulas,FORMULASCRIPT,"*FUNCTION TOWEEK=%TIME_TO_SET%")

Then in your logic script change it to the following:

*XDIM_MEMBERSET TIME=%TIME_SET%

*WHEN TIME
*IS *

*FOR %TM%=TOWEEK

*REC(FACTOR=1,TIME=%TM%)

*NEXT

*ENDWHEN

*COMMIT

The %TIME_TO_SET% variable isn't recognized in script logic but you can pass it as a variable within the FORMULASCRIPT task.

Give that a try,

John

[Former Member · Accepted solution]

Hi John,

Thank's a lot that solved it.

I made some minor changes since my time dimension is TIMEW I had to use %TIMEW_TO_SET%. I also used COPYMOVEINPUT since I only want basemembers (in my case weeks) to be selected.

Best regards,

Clas

PS. Do you know where can I find more documentation regarding EvDTSModifyPkg functions, variables, etc.

[JohnL · Accepted solution]

Try the [SAP BPC 7.0 SP00 M Data Manager Guide|https://websmp209.sap-ag.de/~sapdownload/011000358700001123522008E/SAP_BPC_70SP00M_DataMgrGde.pdf]

or the online help:

[MODIFYSCRIPT Package Variable Task Usage |http://help.sap.com/saphelp_bpc75/helpdata/en/92/5b94afe1fb4b9fb612b85ef22ca326/frameset.htm]

Thanks,

John