Skip to Content

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.

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. Vadim Kalinin

    Hi Noel,

    Very good trick! Some corrections have to be done with DM variable definition ($B_YR$?), but the idea that $$ vars are replaced before %% vars is OK. In case of Year or Month selection it’s better to use COMBOBOX with a hardcoded list of values instead of TEXT prompt.

    Vadim

    (0) 
  2. Patrick Eklund

    Hi Noel!

    This would have been really good if I only could get it to work!
    It seems like in BPC 10 NW  the SELECT statement dont like having more than 1 variable , at least for me.

    I get an exception “invalid select” when having more that one variable in the Select statement. I have tried in UJKT too same there.

    (0) 
        1. Vadim Kalinin

          Hi Patric, check notes…

          And perform a simple test in UJKT:

          In the Data Region put:

          TIME=2012.01,2012,02

          In the script area:

          *SELECT(%TT%,”[ID]”,TIME,”[ID]=%TIME_SET%”)

          *XDIM_MEMBERSET TIME=%TT%

          Press Execute Simulate and check result.

          Vadim

          (0) 
          1. Patrick Eklund

            Hi Vadim!

            Thanks for your reply.
            I have tested your code with some slight chages for my enviroment, see the code below:

            *SELECT(%TT%,”[ID]”,TID,”[ID]=%TID_SET%”)

            *XDIM_MEMBERSET TID=%TT%

            With
            TID=2012.01,2012.02
            in Data region gice me this error :

            LGX:


            *XDIM_MEMBERSET TID=

            LOG

            UJK_VALIDATION_EXCEPTION:Invalid “select” statement

            But if I give only one value in the data region like 2012.01 then it works fine.

            I´ll search for notes too,

            BR
            Patrick

            (0) 
  3. Vadim Kalinin

    Hi Noel,

    The solution works fine with BPC 10 (SP09 minimum) but I was unable to make it work with BPC 7.5 (with the latest SP17 for 750). The note to allow multiple member selection in “where” part of SELECT was installed in the SP14 and it’s working. But looks like the second replace of %XXX%YYY%% is not here. I am talking about the code like this (correctly working in BPC 10, not working in 7.5):

    *SELECT(%Y%,”[YEAR]”,TIME,”[ID]=$PERIOD$) //$PERIOD$ – single TIME member like PERIOD=2014.Q4

    *SELECT(%MVZSET2014%, “[ID]”, MVZ, “[SPLIT]<>’TSH’ AND [ID]<>’10100′ AND [CALC]=’N'”)

    *SELECT(%MVZSET2015%, “[ID]”, MVZ, “[SPLIT]<>’TSH’ AND [CALC]=’N'”)

    *SELECT(%MVZSET2016%, “[ID]”, MVZ, “[SPLIT]<>’TSH’ AND [CALC]=’N'”)

    //…

    *SELECT(%MVZFINAL%, “[ID]”, MVZ, “[ID]=%MVZSET%Y%%”)

    *XDIM_MEMBERSET MVZ=%MVZFINAL% //Empty result

    *SELECT(%MVZFINAL%, “[ID]”, MVZ, “[ID]=10102,10103,10104”)

    *XDIM_MEMBERSET MVZ=%MVZFINAL% //Correct result: *XDIM_MEMBERSET MVZ=10102,10103,10104

    Any ideas?

    B.R. Vadim

    (0) 

Leave a Reply