Building a Cleaner “Search” Function into a TIBCO Spotfire Dashboard
There are plenty of occasions for which the end-user of an interactive Spotfire dashboard may want to search a table or limit the data presented via user-entry search boxes like those that appear as filters on a website. Spotfire provides a version of this capability in the form of a default searchable List Box Filter.
While this is one solution, it may, however, take up a bit more space in a text area than the author would want, it may not give the ability to limit a visualization or data table by ‘fuzzy’ logic, or an approximation of a cell value. The solution that Syntelli uses will meet these needs by using the ~= function, or the Spotfire equivalent of a ‘Like’ function, by removing the results window below the search field. Your end result will look something like the image below. The only caution to this approach is that while it does employ the fuzzy logic of the ~= function in Spotfire, it remains case sensitive, so be sure to remind your report consuming audience of this.
Although there are a few steps involved, the process is fairly simple if the author is familiar with the concepts of document properties, creating calculated columns and data limiting.
The first step is to create a blank text area. This is our ‘canvas’ on which we will create our fields for the report users to enter information which will limit all related visualizations. This step is as simple as selecting the new text area button:
Next we’ll create an entry field document property with the following steps: within the text area, select the input field property as pictured below:
This will bring up the Property Control window:
At this point, we’ll need to create a new property using the “New” button pictured above. The reason for this is to serve as a placeholder for our new search function. As the author, you may name the property however you would like, but it is best if the property name corresponds to the purpose of the document property, such as “SearchFieldProperty.” Once you have created your new property, this portion is complete. You will now notice that your new property will appear in the Insert Calculated Column window as pictured below:
This means that you will be able to refer to this property, and the values entered into your newly created search field, when creating calculated columns.
This takes us to our next phase, which involves doing just that – creating a calculated ‘Flag’ column which will essentially flag specified cells of data containing the user-entered phrase or word. We will start by opening the Insert Calculated Column window pictured above. At this point, we will use a case statement to apply the flag. As an example, I have pasted a case statement using our recently created document property.
What this is essentially saying, is that when the cell in the specified column contains a value like the one entered in the text area, flag the row as “Y” else “N.” This could also be done using 1 and 0 in place of “Y” and “N” depending on your application. This creates a dynamic column within the data that will change based on user entry.
This brings us to our final step, which is limiting the desired visualization(s) to only data rows where our newly created flag is either “Y” or 1. To do this, we’ll need to limit the visualization(s) to only data rows where our Flag column, in this case [SearchFlag], is equal to “Y.” This is as simple as entering a data limiting statement in our visualization(s) similar to the one below:
We have now effectively given the user the ability to dynamically text-filter any specified visualizations in real-time with a much cleaner appearance than the one available by default. You also have the ability within the text area to enter any description you want above the search field. Just remember, although the underlying logic is a ‘fuzzy’ match, it is case sensitive. One way to avoid this potential complication is to create calculated version of your target column which is either upper([your column]) or lower([your column]). This will make all values within your specified text column upper-case or lower-case. You would then target this column when creating your calculated flag column. Although this may sound like extra steps, the process only takes a few minutes after the first run-through and will allow for very clean entry-field limiting and searching within the analysis file.
You can also create multiple flag columns and allow the user to enter multiple search criteria and link them to visualizations with a combined data limiting statement like the one below: