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.