Technical Articles
How to check that only single member is passed from DM prompt in BPC NW
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.04In 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 executedFOR/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?
Hi Vadim,
Thank you for the blog. Its very useful.
I have similar requirement but I need to select multiple members. Basically, my requirement is retrieve the project duration. PROJECT is my dimension and its has properties START_DATE and END_DATE. Based on these properties I have calculate as below:
// Collect Hierarchy or multiple projects selected from data manager package selection from Project dimension
//%PROJECT_SET% = PROJECT1, PROJECT2
*SELECT(%GETPROJ%,ID,PROJECT,ID =%PROJECT_SET%
// Retrieving State & End dates from properties of Project dimension with format as TIMEID (YYYYMM00)
*SELECT(%STARTDT%,START_DATE,PROJECT,ID =%PROJECT_SET%) //<-- I am getting Error for multiple project selection
*SELECT(%ENDDT%,END_DATE,PROJECT,ID =%PROJECT_SET%) //<-- I am getting Error for multiple project selection
// Collect duration months from State and End dates
*SELECT(%ALLMONTHS%,[ID],TIME,TIMEID>=%STARTDT% AND TIMEID<=%ENDDT% AND LEVEL = MONTH)
*XDIM_MEMBERSET PROJECT =%PROJECT_SET%
*XDIM_MEMBERSET DATASOURCE = DS_00
*XDIM_MEMBERSET VERSION = PLAN
*XDIM_MEMBERSET TIME = %ALLMONTHS%
*WHEN ACCOUNT
*IS S1001
*REC(EXPRESSION=%VALUE%=1) // some simple logic
*ENDWHEN
*COMMIT
I am getting error at above SELECT statements for retriving Start and dates from Mutiple Project selection. Could you please help? If I am selecting only one project in %PROJECT_SET% then its work but not working got multiple project selection.
*SELECT(%STARTDT%,START_DATE,PROJECT,ID =%PROJECT_SET%) //<-- I am getting Error for multiple project selection
*SELECT(%ENDDT%,END_DATE,PROJECT,ID =%PROJECT_SET%) //<-- I am getting Error for multiple project selection
Sorry, but I do not understand how your post is related to this particular blog!
It's better to ask a question and post detailed info about your requirements...
To my mind there is something wrong with your expected logic - you can't scope dynamically in the single when/endwhen loop. Ask a question and I will help you.