How to check that only single member is passed from DM prompt in BPC NW

[former_member186338]

When SELECTINPUT is used in DM advanced script it's possible to select multiple members for single dimension. In some cases it can be an issue: for example, the user is asked to select end month to perform calculations for the list of months starting from the first month of the year up to the selected end month. If the user will select multiple months then the calculation will be wrong and unpredictable. It's better not to execute script at all in case of multiple selection.

Correct selection:



Incorrect selection:



This check can be performed using script logic. At the end of calculations variable %SINGLE% will be empty for multiple selection and contain single member for single selection:

//%TIME_SET%=2007.04 

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

*SELECT(%IDGE%,[ID],TIME,ID>=%IDS%) //%IDG%=2007.04,2007.05,...

*SELECT(%IDLE%,[ID],TIME,ID<=%IDS%) //%TIDG%=...,2007.03,2007.04

*SELECT(%IDFIRST%,[ID],TIME,ID>=%IDLE% AND ID<=%IDS%) //%IDFIRST%=2007.04

*SELECT(%SINGLE%,[ID],TIME,ID<=%IDGE% AND ID>=%IDS% AND ID=%IDFIRST%) //%SINGLE%=2007.04

*FOR %T%=%SINGLE%

*XDIM_MEMBERSET TIME=%SINGLE% //Just for test

//Place the rest of the script here

*NEXT

//Result: *XDIM_MEMBERSET TIME=2007.04

In case of multiple members:

//%TIME_SET%=2007.08,2007.04 

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

*SELECT(%IDGE%,[ID],TIME,ID>=%IDS%) //%IDG%=2007.08,2007.09,...

*SELECT(%IDLE%,[ID],TIME,ID<=%IDS%) //%TIDG%=...,2007.03,2007.04

*SELECT(%IDFIRST%,[ID],TIME,ID>=%IDLE% AND ID<=%IDS%) //%IDFIRST%=2007.04

*SELECT(%SINGLE%,[ID],TIME,ID<=%IDGE% AND ID>=%IDS% AND ID=%IDFIRST%) //%SINGLE%=<EMPTY>

*FOR %T%=%SINGLE%

*XDIM_MEMBERSET TIME=%SINGLE% //Just for test

//Place the rest of the script here

*NEXT

//Result: no code executed

FOR/NEXT is used for conditional execution of the rest of the code.

P.S. If some dimension have few members (ex.: CATEGORY) you can use COMBOBOX prompt with fixed member list to ensure single member selection: read my blog DM PROMPT(COMBOBOX,…) behavior in BPC NW 10 and 7.5

Questions? Comments?