Our users had a weird requirement: They wanted to search all the columns in one action. We thought how to do it. In this post I'll describe the solution. Note that this solution is not good in terms of performance, but this is the best we've got.
- In every report (/answer/request) you develop, add a column and call it SearchColumn. Define its formula as the concatenation of all the columns in the report. You also need to cast the numeric columns to char in order to concatenate them. For example: FirstColumn || cast(NumericColumn as char) || LastColumn. To make things easier, I written a little C# program that takes the request XML and outputs this column's formula. It's a very simple program so I won't put it here (unless someone will ask for it). You can write this simple program in every language you know.
- Hide the column (Column Properties -> Column Format -> Hide).
- Create a filter based on this column. In the operator drop-down-list, choose "contains any". Then, click on Add -> Variable -> Presentation. Enter Term in the "Variable Expr" box (Term will be our variable name) and enter space in the (default) box.
- Now, we'll let the user to enter the search term. Create a new Dashboard Prompt based on any column you'll like. In the operator, choose "is equal to / is in". Control - "Edit Box". Show Default To - "Variable Expression" and enter Term. Set Variable - "Presentation Variable" and enter Term here too. Label - "Search", for example.
- All you have to do is to add the prompt in every page you want to let the user search.
Note that the search term will stay until the user will clear the prompt field. As I said, this solution is problematic for number of reason (performance, "why the other page filters too?" and maintenance).