Skip to Content

Having read in quite a few discussions stating ‘filtering data from other components other than filters’, this document provides a ‘quick learn way’ for beginner’s on ‘Filtering data using Combo box & List box’. Follow the easy steps below.

Let this be the data, which has Continent->Country->State->Region.

Refer the images right below each step for better understanding.

1_Data.PNG

2_Cell Mapping.PNG

Combo Box1 :

3_Combo1 General tab.PNG

4_Combo1 Behaviour Tab.PNG

Combo Box 2 :

5_Combo2 General Tab.PNG

6_Combo2 Behaviour Tab.PNG

Combo Box 3 :

7_Combo3 General Tab.PNG

8_Combo3 Behaviour Tab.PNG

Combo Box 4 :

9_Combo4 General Tab.PNG

10_Combo4 Behaviour Tab.PNG

Output:

11_Output.PNG

12_Output1.PNG

But you can do the same with other selectors like List box. This method is used in many scenarios where Drill downs and other complicated functionality needs to be done.
On a further note, use of combo box instead of look-ups functions and few other complicated excel functions is advisable for better performance of dashboard (these combo boxes usually are hidden behind any component, when is used for the functionality).

The sample file has been attached with the extension “.txt”, which can be used by renaming the extension “.xlf”.

To report this post you need to login first.

47 Comments

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

    1. Sara G Iyer Post author

      Hi Pramod,

      As you see here it is a 3 level hierarchy here.
      Continent -> Country -> State -> District
      Sources of the last 3 selections are dependent on the selection made in the L1  (continent Combo box)

      So in a hierarchy like this, it cant be done.

      Suppose you have all the values hard-coded and you r making selection in the State level , then yes , the respective country and the respective continent will appear in the first two combo boxes and the last  (district selection)  ll show you the all the districts you have under that state. (this is the typical work of a any selector in xcelsius).
      Feel free to ask if you have any more doubts on this.

      Regards,

      Sara

      (0) 
  1. Vijai Muniraj

    Hi Sara,

    This is really good, and my doubt is that we have a component name called Filter in dashboard which alone does the same functionality(what u have done here) in simple way. Now my question is how can u differentiate this one with tat Filter Component. Please correct me if i am wrong. Thanks

    (0) 
    1. pramod k

      I guess  you can only go from left to right in filter and by following this way you can select any of the level  and have the   other combo boxes  get   selected  with  corresponding  values .

      and the last final  combo box can be used for  all the  different  type of insertions .

      (0) 
    2. Sara G Iyer Post author

      Hi Vijay ,

      As pramod k said filter have limited functionality, and such cascade combos can be used with flexibility also that these(hidden combo boxes) can be used in many other ways in a dashboard, as I ve mentioned in the beginning of the blog post, it also curtails the use of complicated formulas which actually kill the performance of the dashboard.

      The more you use dashboards the more you ll know the role of combo boxes 🙂

      Thanks for the explanation pramod k . Nicely explained.

      (0) 
  2. Nirav Gandhi

    Sara Good Doc but one more thing about this Doc ,

    Vlookup is no supported in Mobile Dashboard so when u need to filter data , use this concept.

    (0) 
    1. Sara G Iyer Post author

      Thanks Nirav

      I guess Vlookup does work in Mobile dashboards.
      But as more number of lookups can make the excel sheet heavy, which eventually will make the dashboard prone to Crashes, so to avoid those this technique can be used.
      Its a best practice to use this.

      Regards,

      Sara

      (0) 
    1. Sara G Iyer Post author

      Thanks Shweta p

      That is how the Cascade combo box would work when the hierarchy is there. I have shown it in a hierarchy here, but it can be used in many other ways otherwise, needless to mention it does make the work lot easier, and is the most used technique in developing dashboards. 
      And as far as the coloring is concerned, its done here is just for the explanation. 🙂

      Regards,

      Sara

      (0) 
    1. Sara G Iyer Post author

      Hi Lisa ,

      Glad to know you found it helpful.

      About the chart integration I would just say, you can format the results (sanitising the raw data)  before mapping it to charts.

      Suppose you have the daily trend chart, instead of showing 10 Sep  11 Sep , you can instead have !0 Sep, 11 , 12 and so on till end of the month.

      Go through the Best practices to build a qualitative dashboard.

      http://wiki.scn.sap.com/wiki/display/BOBJ/Dashboards+(Xcelsius)+General+Best+Practices

      http://scn.sap.com/docs/DOC-10475

      Thanks,

      Sara

      (0) 
  3. Deepak Gupta

    Hi Sara,

    Very well explained. I am new to BO. I have one more query on this. Suppose I have a key figure lets say Sales Value against each City then how shall I get aggregated value of Sales at each level of State, country and continent.

    Thanks in Advance

    Deepak

    (0) 
    1. Sara G Iyer Post author

      Hi Deepak,

      Apologies for the delayed response. For your requirement there is aformula called “sumif ” with that you can get the sales in each country ,and continent.

      Thanks,

      Sara

      (0) 
  4. Li Li

    Hi, Sara,

    I am new to the dashboard. Thanks for the info.  In my dashboard, I would like to add “all” into the option.  For example,  in combo 2, I need to have “All” to present all countries in the selected continent; in combo 3, I need to have “All” to present all states in the selected country; etc.  Any suggestion on how to do it?

    Thanks!,

    Li

    (0) 
  5. Anurag Gupta

    Hi Sara,

    It’s very useful, in my requirement, user wants multiple selections of LOVs at any level with cascading filtering. Is it possible? If son please share some details.

    Thanks,

    Anurag

    (0) 
      1. Anurag Gupta

        Hi Sara,


        Thanks for you reply. I have gone through few of the potential solutions you have recommended around these cascading filtering and multiple selections of LOVs requirements.


        Specific to my requirement, I have to create 4 level of cascading filters (Dashboard 4.1 SP5). As a example Country, Sate, County and City. The selection of Countries (multiple selection) should filter the states of the selected Countries and subsequent selections of multiple states, countries and then cities. Please advice how these cascading filtering can be created in Dashboard. Fyi, I have found Query Pomprt filter works however it is not compatible with mobile.


        Thanks,

        Anurag

        (0) 
        1. Sara G Iyer Post author

          Hi Anurag ,

          you will have to follow the same procedure as stated above. Just have a query which has all the four levels and result of that query should be in the same way as it appears in the 1 st screenshot as in this blog.

          And follow the procedures as stated here.

          Regards,

          Sara

          (0) 
          1. Anurag Gupta

            Hi Sara,

            I understand it is for only single selection. Will this support multiple selections of values in any level as well?

            Thanks,

            Anurag

            (0) 

Leave a Reply