on 07-25-2018 4:32 PM
Hi guys,
I am trying to set up a DEFAULT_FORMULAS Data Manager Package where I would like to define two variables for each of two dimensions. One variable should select a source member, the other one the target member of a copy process that gets executed through a LOGICFILE, referenced in the advanced script of the DMP.(I am not using the /CPMB/COPY - DMP since further logic should apply)
Question 1: Is it possible to define a Select from and Select To Variable for the same dimension within the context of a DEFAULT_FORMULAS DMP?
Question 2: Is it possible to do so for another dimension as well? (Within the same DMP)
Question 3: Would you please share some syntax? That would be of big help!
With kind Regards
Claus
Standard approach:
Use SELECTINPUT into some variable for some specific dimension:
PROMPT(SELECTINPUT,%SOME_VAR%,,"Select dimension member:","%XXXXX_DIM%")
Then pass this variable to $VAR$ to be used in the script:
INFO(%EQU%,=)
INFO(%TAB%,;)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,TAB,%TAB%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,EQU,%EQU%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,MEMBERSELECTION,VAR%EQU%%SOME_VAR%)
Then in the script you can use $VAR$ variable containing members selected by user in SELECTINPUT prompt
P.S. Please read help:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks a lot Vadim,
however, for whatever reason (or I should better say 'mistake' I am not aware of) your approach won't work out for me.
The Advanced Script of my DEFAULT_FORMULAS Data Manager Package is as follows:
PROMPT(SELECTINPUT,%VWR_KATEGORIE_FROM%, , "Bitte wählen Sie die Quell Kategorie","VWR_KATEGORIE")
PROMPT(SELECTINPUT,%VWR_KATEGORIE_TO%, , "Bitte wählen Sie die Ziel Kategorie","VWR_KATEGORIE")
PROMPT(SELECTINPUT,%VWR_PLANUNGSZYKLUS_FROM%, , "Bitte wählen Sie den Quell Planungszyklus","VWR_PLANUNGSZYKL")
PROMPT(SELECTINPUT,%VWR_PLANUNGSZYKL_TO%, , "Bitte wählen Sie den Ziel Planungszyklus","VWR_PLANUNGSZYKL")
INFO(%EQU%,=)
INFO(%TAB%,;)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,TAB,%TAB%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,EQU,%EQU%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,MEMBERSELECTION,$VWR_KATEGORIE_FROM$%EQU%%VWR_KATEGORIE_FROM%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,MEMBERSELECTION,$VWR_KATEGORIE_TO$%EQU%%VWR_KATEGORIE_TO%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,MEMBERSELECTION,$VWR_PLANUNGSZYKL_FROM$%EQU%%VWR_PLANUNGSZYKL_FROM%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,MEMBERSELECTION,$VWR_PLANUNGSZYKL_TO$%EQU%%VWR_PLANUNGSZYKL_TO%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,BASIS_FINANZPLAN_VS.LGF)
In accordance to the advanced script, the Logic File now reads as follows:
*SELECT (%YEAR%, [YEAR], VWR_KATEGORIE, [ID] = IST)
*SELECT (%AKT_MONAT%, [AKT_MONAT], VWR_KATEGORIE, [ID] = $VWR_KATEGORIE_TO$)
*XDIM_MEMBERSET VWR_KONTO = <ALL>
*XDIM_MEMBERSET VWR_FUNKTIONSBER = <ALL>
*XDIM_MEMBERSET VWR_KATEGORIE = $VWR_KATEGORIE_FROM$
*XDIM_MEMBERSET TIME = BAS(%YEAR%.TOTAL)
*XDIM_MEMBERSET VWR_DATENQUELLE = <ALL>
*XDIM_MEMBERSET VWR_PLANUNGSZYKL = $VWR_PLANUNGSZYKL_FROM$
*XDIM_MEMBERSET VWR_BEWEGUNGSART = <ALL>
*XDIM_MEMBERSET VWR_WAEHRUNG = EUR
*WHEN TIME
*IS > %YEAR%.%AKT_MONAT%
*REC(FACTOR = 1, VWR_KATEGORIE = $VWR_KATEGORIE_TO$, VWR_PLANUNGSZYKL = $VWR_PLANUNGSZYKL_TO$)
*ENDWHEN
*COMMIT
In the TASK section of the Advanced Script of the Data Manager Package I referred the variables in $-signs. Both, with or without $-signs won't work...
The error I am getting in both circumstances reads as follows:
CPMB/MODIFY completed in 0 seconds
/CPMB/DEFAULT_FORMULAS_LOGIC completed in 0 seconds
/CPMB/CLEAR completed in 0 seconds
[Selection]
--------------------------------------------------------------
VWR_KATEGORIE_FROM = /ENVIRONMENT_VWR/VWR_Finanz/PRIVATEPUBLICATIONS/DREESBACH/TempFiles/FROM.TMP@@@SAVE@@@@@@EXPAND@@@|DIMENSION:VWR_KATEGORIE|VS.03_09
VWR_KATEGORIE_TO = /ENVIRONMENT_VWR/VWR_Finanz/PRIVATEPUBLICATIONS/DREESBACH/TempFiles/FROM.TMP@@@SAVE@@@@@@EXPAND@@@|DIMENSION:VWR_KATEGORIE|VS.04_08
VWR_PLANUNGSZYKLUS_FROM = /ENVIRONMENT_VWR/VWR_Finanz/PRIVATEPUBLICATIONS/DREESBACH/TempFiles/FROM.TMP@@@SAVE@@@@@@EXPAND@@@|DIMENSION:VWR_PLANUNGSZYKL|PZ.2018
VWR_PLANUNGSZYKL_TO = /ENVIRONMENT_VWR/VWR_Finanz/PRIVATEPUBLICATIONS/DREESBACH/TempFiles/FROM.TMP@@@SAVE@@@@@@EXPAND@@@|DIMENSION:VWR_PLANUNGSZYKL|PZ.2018
[Message]
--------------------------------------------------------------
Error found in system
model: VWR_Finanz. Package status: ERROR
Would you have an idea why it's not working?
Cheers
Claus
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Because you are using incorrect syntax!
Not:
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,MEMBERSELECTION,$VWR_PLANUNGSZYKL_FROM$%EQU%%VWR_PLANUNGSZYKL_FROM%)
But:
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,MEMBERSELECTION,VWR_PLANUNGSZYKL_FROM%EQU%%VWR_PLANUNGSZYKL_FROM%)
No $$ in the DM advanced script!
$$ to be used in script logic!
Hi Vadim,
I am really sorry, but I have to come to you another time.
As you can see, I have changed my code accordingly and in fact, the UJKT Logic Checker runs successfully.
However, running the logic through the DM, the error persists:
/CPMB/MODIFY completed in 0 seconds
/CPMB/DEFAULT_FORMULAS_LOGIC completed in 0 seconds
/CPMB/CLEAR completed in 0 seconds
[Selection]
--------------------------------------------------------------
VWR_KATEGORIE_FROM = /ENVIRONMENT_VWR/VWR_Finanz/PRIVATEPUBLICATIONS/DREESBACH/TempFiles/FROM.TMP@@@SAVE@@@@@@EXPAND@@@|DIMENSION:VWR_KATEGORIE|VS.01_11
VWR_KATEGORIE_TO = /ENVIRONMENT_VWR/VWR_Finanz/PRIVATEPUBLICATIONS/DREESBACH/TempFiles/FROM.TMP@@@SAVE@@@@@@EXPAND@@@|DIMENSION:VWR_KATEGORIE|VS.02_10
VWR_PLANUNGSZYKLUS_FROM = /ENVIRONMENT_VWR/VWR_Finanz/PRIVATEPUBLICATIONS/DREESBACH/TempFiles/FROM.TMP@@@SAVE@@@@@@EXPAND@@@|DIMENSION:VWR_PLANUNGSZYKL|PZ.2018
VWR_PLANUNGSZYKL_TO = /ENVIRONMENT_VWR/VWR_Finanz/PRIVATEPUBLICATIONS/DREESBACH/TempFiles/FROM.TMP@@@SAVE@@@@@@EXPAND@@@|DIMENSION:VWR_PLANUNGSZYKL|PZ.2018
[Message]
--------------------------------------------------------------
Error found in system
model: VWR_Finanz. Package status: ERROR
Advanced Script Data Manager Package:
PROMPT(SELECTINPUT,%VWR_KATEGORIE_FROM%, , "Bitte wählen Sie die Quell Kategorie","VWR_KATEGORIE")
PROMPT(SELECTINPUT,%VWR_KATEGORIE_TO%, , "Bitte wählen Sie die Ziel Kategorie","VWR_KATEGORIE")
PROMPT(SELECTINPUT,%VWR_PLANUNGSZYKLUS_FROM%, , "Bitte wählen Sie den Quell Planungszyklus","VWR_PLANUNGSZYKL")
PROMPT(SELECTINPUT,%VWR_PLANUNGSZYKL_TO%, , "Bitte wählen Sie den Ziel Planungszyklus","VWR_PLANUNGSZYKL")
INFO(%EQU%,=)
INFO(%TAB%,;)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,TAB,%TAB%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,EQU,%EQU%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,MEMBERSELECTION,VWR_KATEGORIE_FROM%EQU%%VWR_KATEGORIE_FROM%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,MEMBERSELECTION,VWR_KATEGORIE_TO%EQU%%VWR_KATEGORIE_TO%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,MEMBERSELECTION,VWR_PLANUNGSZYKL_FROM%EQU%%VWR_PLANUNGSZYKL_FROM%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,MEMBERSELECTION,VWR_PLANUNGSZYKL_TO%EQU%%VWR_PLANUNGSZYKL_TO%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,BASIS_FINANZPLAN_VS.LGF)
Logic Script:
*SELECT (%YEAR%, [YEAR], VWR_KATEGORIE, [ID] = IST)
*SELECT (%AKT_MONAT%, [AKT_MONAT], VWR_KATEGORIE, [ID] = $$VWR_KATEGORIE_TO$$)
*XDIM_MEMBERSET VWR_KONTO = <ALL>
*XDIM_MEMBERSET VWR_FUNKTIONSBER = BAS(ALLE_FB),FB.NZ
*XDIM_MEMBERSET VWR_KATEGORIE = $$VWR_KATEGORIE_FROM$$
*XDIM_MEMBERSET TIME = BAS(%YEAR%.TOTAL)
*XDIM_MEMBERSET VWR_DATENQUELLE = <ALL>
*XDIM_MEMBERSET VWR_PLANUNGSZYKL = $$VWR_PLANUNGSZYKL_FROM$$
*XDIM_MEMBERSET VWR_BEWEGUNGSART = <ALL>
*XDIM_MEMBERSET VWR_WAEHRUNG = EUR
*WHEN TIME
*IS = %YEAR%.%AKT_MONAT%
*REC(FACTOR = 0, VWR_KATEGORIE = $$VWR_KATEGORIE_TO$$, VWR_PLANUNGSZYKL = $$VWR_PLANUNGSZYKL_TO$$)
*ELSE
*REC(FACTOR = 1, VWR_KATEGORIE = $$VWR_KATEGORIE_TO$$, VWR_PLANUNGSZYKL = $$VWR_PLANUNGSZYKL_TO$$)
*ENDWHEN
*COMMIT
Sure I must be missing something, but I've got no clue what it could be. What irritates me though, is that the UJKT logic checker runs through...
Cheers
Claus
Looks like you decided not to read help (the link was provided in my post)!
You have to use single MEMBERSELECTION statement with different variables separated by %TAB% - see example in help.
P.S. And where did you find this strange syntax $$VWR_KATEGORIE_TO$$????
Has to be $VWR_KATEGORIE_TO$
Thanks for your hint, Vadim!
However, unfortunately I still cannot convince the Data Manager Package to run through successfully.
The Advanced script of the DMP now reads as follows:
PROMPT(SELECTINPUT,%VWR_KATEGORIE_FROM%, , "Bitte wählen Sie die Quell Kategorie","VWR_KATEGORIE")
PROMPT(SELECTINPUT,%VWR_KATEGORIE_TO%, , "Bitte wählen Sie die Ziel Kategorie","VWR_KATEGORIE")
PROMPT(SELECTINPUT,%VWR_PLANUNGSZYKL_FROM%, , "Bitte wählen Sie den Quell Planungszyklus","VWR_PLANUNGSZYKL")
PROMPT(SELECTINPUT,%VWR_PLANUNGSZYKL_TO%, , "Bitte wählen Sie den Ziel Planungszyklus","VWR_PLANUNGSZYKL")
INFO(%EQU%,=)
INFO(%TAB%,;)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,TAB,%TAB%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,EQU,%EQU%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,MEMBERSELECTION,KATF%EQU%%VWR_KATEGORIE_FROM%%TAB%KATT%EQU%%VWR_KATEGORIE_TO%%TAB%ZYKF%EQU%%VWR_PLANUNGSZYKL_FROM%%TAB%ZYKT%EQU%%VWR_PLANUNGSZYKL_TO%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,BASIS_FINANZPLAN_VS.LGF)
Accordingly, I have changed the script logic:
(mainly getting rid of the $$ and using the simplified Variables $KATF$, $KATT$, $ZYKF$, and $ZYKT$)
*SELECT (%YEAR%, [YEAR], VWR_KATEGORIE, [ID] = IST)
*SELECT (%AKT_MONAT%, [AKT_MONAT], VWR_KATEGORIE, [ID] = $KATT$)
*XDIM_MEMBERSET VWR_KONTO = <ALL>
*XDIM_MEMBERSET VWR_FUNKTIONSBER = BAS(ALLE_FB),FB.NZ
*XDIM_MEMBERSET VWR_KATEGORIE = $KATF$
*XDIM_MEMBERSET TIME = BAS(%YEAR%.TOTAL)
*XDIM_MEMBERSET VWR_DATENQUELLE = <ALL>
*XDIM_MEMBERSET VWR_PLANUNGSZYKL = $ZYKF$
*XDIM_MEMBERSET VWR_BEWEGUNGSART = <ALL>
*XDIM_MEMBERSET VWR_WAEHRUNG = EUR
*WHEN TIME
*IS = %YEAR%.%AKT_MONAT%
*REC(FACTOR = 0, VWR_KATEGORIE = $KATT$, VWR_PLANUNGSZYKL = $ZYKT$)
*ELSE
*REC(FACTOR = 1, VWR_KATEGORIE = $KATT$, VWR_PLANUNGSZYKL = $ZYKT$)
*ENDWHEN
*COMMIT
What I have noticed though is that in the EPM help, the example refers to CPMB/ALLOCATION_LOGIC whereas I need to run the CPMB/DEFAULT_FORMULAS_LOGIC. Could this be the issue?
Cheers
Claus
Yep - that was it, Vadim.
At the end the occam's razor principle applied to the final fix.
In the task statements, I forgot the
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SUSER,%USER%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPPSET,%APPSET%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,%APP%)
🙂
Thanks heaps for your valid input, Vadim. Without your contributions BPC would not have a chance... 😉
Cheers
Claus
Hi Vadim,
thanks again for your attention...
Please find my code below...
*SELECT (%YEAR%, [YEAR], VWR_KATEGORIE, [ID] = IST)
*SELECT (%AKT_MONAT%, [AKT_MONAT], VWR_KATEGORIE, [ID] = %VWR_KATEGORIE_TO_SET%)
*XDIM_MEMBERSET VWR_KONTO = <ALL>
*XDIM_MEMBERSET VWR_FUNKTIONSBER = <ALL>
*XDIM_MEMBERSET VWR_KATEGORIE = %VWR_KATEGORIE_FROM_SET%
*XDIM_MEMBERSET TIME = BAS(%YEAR%.TOTAL)
*XDIM_MEMBERSET VWR_DATENQUELLE = <ALL>
*XDIM_MEMBERSET VWR_PLANUNGSZYKL = %VWR_PLANUNGSZYKL_FROM_SET%
*XDIM_MEMBERSET VWR_BEWEGUNGSART = <ALL>
*XDIM_MEMBERSET VWR_WAEHRUNG = EUR
*WHEN TIME
*IS <> %YEAR%.%AKT_MONAT%
*REC(FACTOR = 1, VWR_KATEGORIE = %VWR_KATEGORIE_TO_SET%, VWR_PLANUNGSZYKL = %VWR_PLANUNGSZYKL_TO_SET%)
*ENDWHEN
*COMMIT
In a nutshell, the code should copy everything that is not the actual Month (%YEAR%.%AKT_MONAT%) from a source Forecast Version (%VWR_KATEGORIE_FROM_SET%) into a target Forecast Version (%VWR_KATEGORIE_TO_SET). Additionally the Planning Cycle (e.g. "PZ.2018") should be populated as well and in dependence of the user selection (%VWR_PLANUNGSZYKL_FROM_SET% and %VWR_PLANUNGSZYKL_TO_SET%).The problem I am facing is that I don't know how to declare the variables in the advanced script of my DEFAULT_FORMULAS Data Manager Package...
Kind Regards
Claus
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Question 1: Yes
Question 2: Yes
Question 3: Please show your script file to understand what you want to achieve!
You can pass selection using MEMBERSELECTION to some $VAR$
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
6 | |
5 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.