Selectors: 6 steps to Multiple Selection

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
Multiple Select 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?

Recent Related Posts
Femke Kooij is a dashboard and report designer, developer and educator specialising in SAP|BO Dashboard Design (better known as Xcelsius) and Crystal Reports. She has also a working knowledge of other SAP | BO toolings like, Web Intelligence (WebI), Universe Designer, BO Data Servcies (BODS) and the overal platform BO Enterprise. She spents a lot of her time thinking about and sharing her knowledge of Xcelsius to others through her own blog. Currently she is in the employment of Cognizant. Her blog only contains her personal views, thoughts and opinions. It is not endorsed by Cognizant nor does it constitute any official communication of Cognizant.

This entry was posted in Components, Xcelsius and tagged , , , . Bookmark the permalink.

40 Comments to Selectors: 6 steps to Multiple Selection

  1. Anshul says:

    Hi Femke

    Can you share this xlf file ?? I need this functionlaity but without Web I prompts and my data is flowing in from BEX queries via SAP BW connections

  2. Sundar says:

    Hello!! I have a question. I have a requrement for multiselection. Let me give you the example.-
    I need to select the name of a country(USA), then state(VA) then cities (Arlington, Fairfax, annandale). How do I multiselect citites? what component can I use. I tried Filter component. it’s pretty close to what I want. but it doesn’t have function to do multiselect.

    Thanks.

  3. maxbo says:

    i dont know how to fill the Pie Chart, can you send me the file plz? (xlf)

  4. Raj says:

    Hi, can anyone help me to konw “how to connect the pie chart values”?

  5. Gopinath says:

    Hi,
    will this work in Coloum chart, without showing empty space?, Kinldy send xlf file, that will really help me to lean new ideas.

    Regards,
    Gopi

  6. Gopinath says:

    Awesome stuff, I’m interested in how you did this as well. Can you send me the source file or xlf file?
    Thanks

  7. Lora says:

    Hi Femke,

    I was so pleased to have been able to reproduce this feature in my dashboard up until passing the concatenated values back to QAAWS query. It works fine for when I select one value, but when multiple values are selected and concatenated, nothing is returned.

    I made sure that I’m passing the way QAAWS filter expects. ValueA;ValueB;ValueC without quotes.

    I wonder if I can separate the chosen values into separate cells and select the range as my input? I only have 10 values that can be chosen.

    Your input is appreciated.

  8. Inge says:

    Thanks Femke,
    You saved the day for me :D
    I do have a question, I’ve quickly read through the questions and replys so if i missed this question sorry.
    Would it be possible to start with all selected and a button that ‘Resets to all’ ??
    But super this, thank you

  9. Ram says:

    I am looking for some logic of this kind using Xcelsius. Could you please send me the source xlf file.

  10. jim says:

    Hi,
    congratulation for your website.
    I’m a newbie with xcelsius and thanks to your website I found often ideas to develop.

    Unfortunately,
    I try the listbox with multiselection but I did not succeed.
    Each times, I get only the monoselection :-(

    Could you help me maybe send me the original file.
    Thanks for your help.

  11. AlexanderV says:

    … very good solution, thank you for the explanation and for publishing a sample source code.

  12. kotte says:

    HI,

    Is it possible below change in the current graph.
    Now adding more countries to the list is happening as it should, but removing them should happen the same way i.e. when you have multiple countries selected, clicking on one of the existing countries should remove that one country from the selection and not focus graph solely on that country.

    E.g. when you have on list in the listbox
    Nethralands, Belgium and Germany, clicking Germany, would update the list so that there are only Netherlands and Belgium on the graph.

    Can you please let me know how to achieve?

  13. kotte says:

    HI,
    Thanks for easy and great example.

    I have followed the above 6 steps for multiple selections and its working fine. But i want implement this logic in below situation.

    Jus I have country names and Headcount data and we have one pie chart, pie chart shows the all countries headcount. So I need one combo box(or List Box)on the above pie chart and I need to select multiple countries from combo box then the pie chart will show the selected countries Headcount only.

    Now I’m able to select the multiple countries with your steps.
    Please let me know how to reflect the headcount data into pie chat according to multiple country selection.

    • Femke says:

      Hi,

      You can use cell J14 in your prompt query and the query returns only the selected countries with their headcounts. You connect the pie to the returned data set.

      If you do not have the option of triggering a query, you need to alter formulas in column J to have only the selected labels name of that row (so without the previous row value). You can use then this individual value to use in match and index formulas to built up you pie data set.

      • kotte says:

        HI Femke,
        Thanks for your help.

        Can you tell me the alter J column formula to show the headcount for selected multiple countries in the pie chart. i have tried but it’s not giving proper results.

        I have source data (country names A30:A40 and Headcount B30:B40, remain above 6 steps have same cells used.

        Please let me know the exactly J column formula and Pie chart properties.

        Thanks for your support.

        • kotte says:

          HI,

          can you please help me.

          • Femke says:

            HI Kotte,

            I have changed the swf above to what I think you want.

            I have changed the file a bit, as the example was created for WebI prompt and included the nescessary “” tags as well (BTW, the tags are not needed anymore in WebI 3.0).
            Hopefully you can use this one.

            download xlf

          • kotte says:

            HI Femke,

            Wowww… yes exactly i need this way. I can’t believe if it will work. But here you have attached SWF file, i can’t see “.XLF” file here or any pie chart properties or any steps also.

            Can you please send me the .xlf file or related steps

          • Femke says:

            oh, indeed wrong extension used. I changed the link. BTW version is 2008 SP4.

          • kotte says:

            HI,

            Still i can’t see “.XLF” file. Your attachment has one zip file and it has two files (document.xml and xldoc).

            I’m very exciting to see the logic, please send me the correct “.XLF” file or at least steps.

          • Femke says:

            It is no real zip file. it is the xlf itself you downloaded So save the file as multipleselect_pie.xlf and open it in Xcelsius. The document.xml and xldoc are the building blocks of an xlf file. All Xcelsius files are really just a zip archives. Right click in Windows explorer on any xlf and then open the file with winzip or 7zip and you’ll open the archive. Be carefull though, even if you can make some alterations, you cannot extract and repack the file again. The xlf will be lost then.

          • kotte says:

            HI,

            Multiple selections are working very excellently. What a great logic.
            Thanks for sharing this logic and your help.

  14. gary says:

    is there a place I can grab this file? I can’t seem to get it working from following the tutorial..thanks! would be great if I can get it to work :) )

  15. gary says:

    Questions…

    3. place a label which shows the concatenated prompt value

    * link to cell L14 — why L14? Should this be J14??

    4. place a source data button with the following properties:
    –what is a source data button? I don’t see this in my Tree, is there a different name?

    • Femke says:

      Hi Gary,

      Answer 3: You are right, thanks. I have updated the post.

      Answer 4: in the tree folder “Other” you can find source component. However I do not know if this is available in every Xcelsius version. You need at least Xcelsius 2008, that I know for sure, if it is available in present and/or engage (server) I do not know. I use Enterprise version (departemental).

  16. Fred says:

    Is there a way to pass these values to be used as a filter for another selector (i.e. Filtered Rows) to be able to pull in those value that match the ones selected from the first?

    • Femke says:

      Hi Fred,

      You mean select multiple values from selector 1, then these values are the source for selector 2? With which you can make another selection? For example, select multiple Business Units and thus fill selector 2 with all the departements from those units?

      The best way would be to trigger the query for the departments after you have made the multile selection with the prompt value as the parameter.

      If you cannot trigger a query, you need to setup a list of all possible BU/department combinations (column AA=BU, AB=Department) in the next column (AC) you place a condition where you show the department’s name if you have matched the value of the business unit to the multiple selection list (column G).
      EG: if(vlookup(AA1;$G5:G$14;0)=AA1;AB1;”")
      I know the formula looks strange, however if the BU is found then the department is shown otherwise the output is #N/A and this will be rendered as NULL bij xcelsius.
      Connect the labels of the 2nd selector to column AC.

      Hope this helps;

  17. Fred says:

    Is there a way to deselect an entry after selecting?

    • Femke says:

      No, not without deselecing all selections through the push button. You might try to work something out by combining the push button with the history component as you need to remember the order of selection with the values the push button is placing as a reset.

  18. egat says:

    Can you please send me the source file, too? Would be amazing!! Thanks!

  19. Denis says:

    Hi Femke,

    This is what i was struggling with. Is it possible that you send me the original xlf file?

    Many thanks in advance

  20. Kalyan Verma says:

    Femke,

    Looks like an interesting approach. I followed the steps exactly, however was unable to re-create the magic. I think you missed or mistypes few cell ranges. Can you recheck your instructions.

    Regards,
    Kalyan Verma

    • Femke says:

      Hi Kaylan,

      Thanks for your comment. I checked the steps and found indeed a discrepancy with the working. I forgot to mention the insertion type of the selector, which is row and the selector did not show which items were selected, the prompt was built correctly though.

      I have corrected this now. (alert values are column H instead of J and insertion type is row instead of default position).

      Could you please let me know if the magic as you call it works?

      • Jamie says:

        I just ran through your instructions and found no problems with it.
        I also used a filtered label to insert a specific set into columns N5 to N14 and then used a reference for labels A5 to A14. Worked great.

        I too wish you could deselect. …. :(

  21. Joe says:

    I’m interested in how you did this as well. Can you send me the source file? Thanks

  22. Jack Nijssen says:

    i would be interested in the multi selection file, could you send me the xlf file, thanks

    Jack

  23. Kevin says:

    Can you send me the source file, I’ve been looking for something with this functionality. Thanks.

  24. kotte says:

    HI,

    Is it possible below change in the current graph.
    Now adding more countries to the list is happening as it should, but removing them should happen the same way i.e. when you have multiple countries selected, clicking on one of the existing countries should remove that one country from the selection and not focus graph solely on that country.

    E.g. when you have on list in the listbox
    Nethralands, Belgium and Germany, clicking Germany, would update the list so that there are only Netherlands and Belgium on the graph.

    Can you please let me know how to achieve?

    please help me.

  25. kotte says:

    Hi,
    I have removed the “Source Data 2” and toggle button destination mapping has changed from “Sheet1!$H$20” to “Sheet1!$F$20”.
    Then I can add or remove the countries from List box by selecting the Toggle button. Every time I have to select the toggle button for adding or removing.
    For this to really be successful solution, I would need to be able to add and remove the countries by simply clicking the country on the list box, Can it be done?

  26. Femke says:

    sorry that is for now not possible.