Business scenario:
The end user needs a dialog box to capture the current year and period after which script logic needs to execute a function to perform an allocation where a captured annual value must be split over the remaining open periods of the year.
Problem:
The method of passing selections between DM and Script logic is somewhat confusing between:
1. Single values
2. Multiple values (sets)
3. Standard %SELECTION% values
4. Custom selection values
and how these can be used in the script logic for various functions, some of which accept multiple values (e.g. *XDIM_MEMBERSET) and some of which can only accept a single value (e.g. *BAS()).
Solution description:
1. The first problem is to obtain the user input and assign it to a variable value that you can use in your script logic. Create a new package under the DM in the EPM Add-in for Excel. The simplest method is to use a text input field like this: PROMPT(TEXT, %TXT_YR%, "Periodic phasing for year?", , ). Use another prompt to obtain the period PROMPT(TEXT, %TXT_PER%, "First open period?", , ).
2. In the "Modify Script-->Advanced" add the INFO lines to allow you to assign the dialog box variable names to the script logic variable names. Ensure you have changed the ".LGF" file to point to your logic file and add the REPLACEPARAM TASK line to make the assignment.
INFO(%EQU%,=)
INFO(%TAB%,;)
TASK(/CPMB/ICDATA_LOGIC,LOGICFILENAME,EXAMPLE.LGF)
TASK(/CPMB/ICDATA_LOGIC,REPLACEPARAM,IN_YR%EQU%%TXT_YR%%TAB%IN_PER%EQU%%TXT_PER%)
Notice that the script logic variable name used in this last statement is not declared anywhere else and does not have the encompasing %'s. It is your responsibility to ensure you use the exact variable name in your script logic file for the values to be transferred. The line is interpreted as:
IN_YR = TXT_YR
IN_PER = TXT_PER
You are free to use the same variable name in your script logic file as the package but for clarity sake and debugging I prefer to make them differ.
3. Logon to the web client and open your script logic file. In this case EXAMPLE.LGF. Setting the scope of the script logic is the next step. The script logic file replaces any occurrence of your variable, being a TEXT variable, with the exact string passed from the package using the syntax $<variable name>$. This can be applied to dimension member ID's, variable names or elsewhere in your script. We specified our variable names in the package as follows:
IN_YR and IN_PER respectively for YEAR and PERIOD entered by the user. So the following is possible:
If hard-coding your scope would have looked like this:
*XDIM_MEMBERSET ZEXPL_TIME = 2014.06
Then you can replace any part of this text with one or more of your variables like a find-and-replace function:
*XDIM_MEMBERSET ZEXPL_TIME = $IN_YR$.06
This will cause the year of your scope to be dynamic and follow the input of the user while the period remains fixed to "06",
*XDIM_MEMBERSET ZEXPL_TIME = 2014.$IN_PER$
This will cause the period to be dynamic depending on the user input but fixed to year 2014 and
*XDIM_MEMBERSET ZEXPL_TIME = $IN_YR$.$IN_PER$
Here both the year and period of the member ID is dynamic depending on what the user entered.
The concept can be extended to use the same user input text variable apply to multiple dimensions because the variable is not linked to any particular dimension. It is simply used "find-and-replace" in your script.
Here another dimension which uses the year as the last part of its member ID is replace by the dynamic selection. If it was hard-coded it would look like this:
*XDIM_MEMBERSET BASE_YR = BASEYR_2014
but using the variable it is changed to:
*XDIM_MEMBERSET BASE_YR = BASEYR_$IN_YR$
If you had yet another dimension for which you wanted the base members of the user input and found errors when trying to use BAS($IN_YR$) there is a good chance that your variable is not a text variable but a selection variable which can technically hold more than one user selected value and the BAS() statement is unable to interpret this.
4. Scoping done. Now setup the open periods. We obtained the current open period from the user with two text variables for YEAR and PERIOD but how do we determine a selection of open periods to use in our function?
*SELECT(%TIME01%, [ID], ZEXPL_TIME, "[ID] = $B_YR$.01, $B_YR$.02, $B_YR$.03, ...,$B_YR$.11,$B_YR$.12")
*SELECT(%TIME02%, [ID], ZEXPL_TIME, "[ID] = $B_YR$.02, $B_YR$.03, $B_YR$.04,...,$B_YR$.11,$B_YR$.12")
*SELECT(%TIME03%, [ID], ZEXPL_TIME, "[ID] = $B_YR$.03, $B_YR$.04,$B_YR$.05,...,$B_YR$.11,$B_YR$.12")
*SELECT(%TIME04%, [ID], ZEXPL_TIME, "[ID] = $B_YR$.04,$B_YR$.05,$B_YR$.06,...,$B_YR$.11,$B_YR$.12")
*SELECT(%TIME05%, [ID], ZEXPL_TIME, "[ID] = $B_YR$.05,$B_YR$.06,$B_YR$.07,...$B_YR$.11,$B_YR$.12")
*SELECT(%TIME06%, [ID], ZEXPL_TIME, "[ID] = $B_YR$.06,$B_YR$.07,$B_YR$.08,$B_YR$.09,$B_YR$.10,$B_YR$.11,$B_YR$.12")
*SELECT(%TIME07%, [ID], ZEXPL_TIME, "[ID] = $B_YR$.07,$B_YR$.08,$B_YR$.09,$B_YR$.10,$B_YR$.11,$B_YR$.12")
*SELECT(%TIME08%, [ID], ZEXPL_TIME, "[ID] = $B_YR$.08,$B_YR$.09,$B_YR$.10,$B_YR$.11,$B_YR$.12")
*SELECT(%TIME09%, [ID], ZEXPL_TIME, "[ID] = $B_YR$.09,$B_YR$.10,$B_YR$.11,$B_YR$.12")
*SELECT(%TIME10%, [ID], ZEXPL_TIME, "[ID] = $B_YR$.10,$B_YR$.11,$B_YR$.12")
*SELECT(%TIME11%, [ID], ZEXPL_TIME, "[ID] = $B_YR$.11,$B_YR$.12")
*SELECT(%TIME12%, [ID], ZEXPL_TIME, "[ID] = $B_YR$.12")
*SELECT(%TIME00%, [ID], ZEXPL_TIME, "[ID] = %TIME$IN_PER$%")
If you look at this script you see that we setup 12+1 select statements and use the "find-and-replace" concept to select the correct one. The first 12 select statements simply replace the YEAR with the user input and then select the remaining open periods into any SELECT variable which must end in the period number. The trick comes in the last SELECT statement which moves the correct set into SELECT variable %TIME00% by replacing the period part of the prior SELECT variable names with the period entered by the user.
If hard-coded for period 06 the last statement might have read:
*SELECT(%TIME00%, [ID], ZEXPL_TIME, "[ID] = %TIME06%")
This statement would simply move the selection set from %TIME06% to %TIME00% or effectively done: %TIME00% = %TIME06%. However by adding the $IN_PER$ to replace the last two characters the user input effectively also selects all remaining open periods for the current year also dynamically selected by the user.
5. The next step is to execute the allocation logic. All the hard work is already done so the allocation logic is simple:
*RUNALLOCATION
*FACTOR = USING/TOTAL
*DIM ZEXPL_TIME WHAT = $IN_YR$; WHERE = %TIME00%; USING = %TIME00%
*DIM ... <any other dimension movement you require>
*ENDALLOCATION
The effect is that the annual total stored in $IN_YR$ from any other source dimension member values is allocated only accross remaining open periods for the year selected by the user.
6. Additional requirements.
Some minor additional logic is required to calculate the difference between a prior allocated value and a current total value so that only the difference is allocated when the logic is run for a second time.
7. Other issues.
I have left the issue of using a dimension member selection dialog box for another discussion i.e. using text variables means a user can enter an incorrect value and is not presented with a selection box of current system values.
