As I get more and more request for the multiple select file I have written out below the steps to take (thus I will not send the file anymore, you can follow the steps yourself ). Also I updated the source file to use the push button instead of a second source button as this is easier to understand. I created this initially for a Web Intelligence prompt, but of course you can use it for any prompt.
6 Steps to create a multiple select:
1. create spreadsheet
- place in cells A5 to A14 your labels (eg. label 1, label 2 etc)
- in range B5 to B14 you create the parameter labels as they need to be send through (eg. “label 1″, “label 2″ etc)
- place in C5 to C14 a 0 (zero), these are the ‘old selection values’
- place in D5 to D14 an incremental number (10 labels = 1 to 10)
- place in D18 a reference to D2 “=D2″ (one of the destination cells of the selector), using this formula reference a delay is created in the overall calculation. When instead the direct input cell is used in the cell range E5:E14 no more then 4 items can be selected before re-initialisation. Using this delay formula enables selection of all items.
- place in E19 the hard coded value 1 (one), this is a source value for the “new value”
- place in E5 the “new value” formula “=IF($D5=$D$18,$E$19,$C5)” and drag this formula through to E14
- place in G5 the formula “=IF($E5=1,B5,”")” and drag through to G14. This is the first part of building the WebI prompt with multiple values.
- place in H5 the formula “=IF($E5=1,C5,”")” and drag through to H14. This is used in the alert of the selector (enables you to see which labels are selected).
- Place in J5 the formula “=IF(G5=”",”",G5)” and in J6 the formula “=IF(G6=”",IF(J5=”",”",J5),IF(J5=”",G6,J5&”,”&G6))” drag this formula through to J14. This is the second part of building the webi prompt. Cell J14 is the actual prompt send to WebI.
- place in L5 to L14 a 0 (zero), these are used by the push button to re-initialise to the last selection.
- place in cell G2 the formula “=”Reset to only “&B2″ (label for push button)
2. place a selector, eg.listbox with the following properties:
- Labels: A5:A14
- Insertion type: row
- Source data: B5:E14
- Destination: B2:E2
- Selected Item: Dynamic
- enable alerts
- alert values: H5:H14
- by value
- select “low values are good”
- deselect “enable auto colors”, remove 1 color and enter the value 0.5, the range minimum to 0.5 will be with a white background, range 0.5 to maximum will have a red background.
3. place a label which shows the concatenated prompt value
- link to cell J14
4. place a source data button with the following properties:
- insertion type: column
- source data: E5:E14
- destination: C5:C14
5. place a push button with the following properties:
- label: G2
- source data: L5:L14
- destination: C5:C14
6. preview your dashboard.
That’s it. Easy is it not?