Search Filter in Xcelsius

Xcelsius popularity as a platform for building BI applications increases, and the demand for streamlined functionality, such as search, is growing as well. Unfortunately, there is still no “search” component or functionality built into Xcelsius yet. However, using your excel model and leveraging filtered rows functionality  you can build search functionality into your Xcelsius driven BI application or dashboard. In a prior post i wrote about creating a google like search by leveraging database connections to retrieve new data as users search large result sets. In this example, i created a search filter that DOES NOT require a data refresh and filters the results within the data stored in the xlf/swf. This is suitable for smaller datasets, where the record count is still large enough to merit a search, but not so large that it requires a round trip to the database. In the example below, type a search term from the state, city, store name or name of manager column and hot enter ot click search to see the grid filter appropriately.

You can find the XLF for download here. In a nut shell, the way this works is as follow:
the input text box is linked to a cell in the model. A formula is used to set the search term to lower case. Then, the FIND function is used to check if the search term is contained in each cell of the dataset. If it does, a label is set to a value, otherwise is set to blank. These labels are finally used to filter the rows to the ones that matched the user search term. Enjoy.

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

14 Responses to Search Filter in Xcelsius

  1. Josh Tapley says:

    Ron,

    Very nice! This topic came up recently on one of the LinkedIn groups, so I’m interested to see how you put it together on the back-end.

    When I donwload the file though, I don’t see the XLF in the zip.

    Thanks,
    Josh

    • Ron Keler says:

      Thanks Josh! Yes, almost every BI app i worked on in the past couple of years needed something like this.. Search is so mainstream. The link on the post is to the .xlf file, not a .zip file. I think some browser may try to automatically add a .zip extension to the download, which can cause problems when you try to download it. Here’s the complete url to the xlf: http://bihappyblog.com/samples/search.xlf
      let me know if you are still having issues with it.. – Ron

  2. Denilson says:

    Thank you Ron,
    Very nice topic…
    I’ve been looking for this solution…
    The link is not downloading a correct file.
    thank yu

  3. Bruno says:

    Ron,

    Very cool!
    When I donwload the file though, I don’t see the XLF in the zip.
    Could you send it to my email please

    Thanks,
    Bruno

  4. Sjoerd van Middelkoop says:

    Ron,

    I have been looking everywhere for this solution, thanks!

    -SJoerd van Middelkoop

  5. ismail says:

    Hi
    Nice solution. I have been looking for this. I am not able to open the xlf file, as I may have a different fix pack. Can you please let me know the formula that is used, and the formula/code behind the search and clear buttons. BTW, the search/clear buttons, is it push button component?

    • Ron Keler says:

      Thanks Mohammed. This is an Xcelsius 2008 SP4 file. Search and Clear are push buttons. It’s a bit hard to explain how this model works, much easier with the XLF. The gist of it is a series of formulas to search for the user search term across the data using excel formulas like lower() and find() and then applying filtered rows on the relevant rows only.

      • ismail says:

        Thanks for getting back to me on this. I have Xcelsius Engage 5.3.5.0. Yes, that’s true, it will be bit hard to explain. One of our live application is using this version (SP3), and some of the other team members are on the same version. I will try to look for SP4. If I upgrade, the other team members also have to upgrade. I am not sure if I will be able to open my existing xlf files if I upgrade to SP4. Any clues on this?

  6. Flo says:

    How is the data moved to the orange area?
    I dont know how this should work, but it does?
    Can anybody help me?

    Thankin advance

  7. Ritesh says:

    Very Nice search functionality.

    I have 2 questions.

    1. By default, it shows up the whole scorecard, is there any way to hide it when the dashboard opens up, like Google, nothing is there is the beginning and then result set shows up.

    2. Is there any way to show up or down trend only? Like 2 more buttons, when you push you only see stores which are either up or down.

    Thanks again for thinking outside the box.

    • Ron Keler says:

      Thank you Ritesh, sure, you can use dynamic visibility and have the cell used for it be blank on initialization and then populate it with the desired value when the user inputs value and you detects that in the input bound cell. Yes, also on the trend, you can use the filtered rows functionality to show only up or down trends based on a value that will be designed as part of the data set for filtering.

Leave a Reply

Your email address will not be published. Required fields are marked *