Input controls are great. But one lacking operator is ‘Like’. For example, if I have a report with many employee names and I want to search for someone, I can’t really do that. I need to either type their full name, or select a name from a list. For this trick, I’ll show how to allow a user to ‘search’ for a term and return all results that contain that term.
- BusinessObjects Enterprise BI4.0 (although the logic is the same in XI3.1)
- That you are familiar with Webi and Input Controls
There’s an interesting thing about Input Controls. Normally you would use them to select a value from a list of values for a dimension, and that dimension would be based on data from your data source. But as you may have seen in my post on dynamic measures, an input control can actually be used to control the value of a variable.
So, there are three parts to this.
- Create a variable that will hold our search term.
- Allow the user to enter a search term into this variable.
- Filter the report to only show rows that match the search term.
Create the Variable
- In BI Launch Pad, open your Webi report and add a new variable.
- Name the variable Search Term.
- Leave the Qualification as Dimension.
- Enter something in the Formula box. It doesn’t matter what.
- Click OK.
- To better demonstrate how this works, I’m going to add this variable to a simple block.
Create an Input Control
- Select the input field as Entry field
- Make the label useful
- Clear the default value
- Leave the operator as Equal to
Filter the Report
- Create a new variable, and name it Search Matches.
- Leave the qualification as Dimension.
- Enter a formula that will indicate if a row of data should be considered a ‘match’ or not. You’ll probably want to use the POS function if you’re ‘searching’ for text.
- Add this variable as a column to your block.
- Go back to your input control and type in a word that should provide a match. I’ve typed in Hitch since my first product contains that word.
Making sense? The formula in the Search Matches variable checks if the search term (“Hitch”) is in the product name. For the first row of data, it is! For the other rows, it isn’t.
You can probably guess the rest, right?
I’ll carry on regardless.
- Add a report-level filter to only include rows where the Search Matches variable equals Yes.
- Select the value Yes. If it isn’t in the list, type it in.
- Click OK.
Hey Presto! You now only have rows where the search term is in the product dimension.
That’s good, but it’s not great. Firstly, this is case sensitive. So I’ll change the definition of the Search Matches variable from this:
=If Pos([Product]; [Search Term]) > 0 Then "Yes" Else "No"
=If Pos(Upper([Product]); Upper([Search Term])) > 0 Then "Yes" Else "No"
But not all bikes have the word ‘bike’ in the product name; I’d also like to ‘search’ some other dimensions. So I’ll change it to this:
=If Pos(Upper([Category]); Upper([Search Term])) > 0
Or Pos(Upper([Sub-Category]); Upper([Search Term])) > 0
Or Pos(Upper([Product]); Upper([Search Term])) > 0
Then "Yes" Else "No"
Now, if my search term is in the category, sub-category, or product dimensions, the row will be included in my results.
That’s it! All the power is in the Search Matches formula. You might want something to be in the category name and the product name, or some other complex logic.
Of course the search doesn’t need to apply to a block. Apply the same logic to any report to filter the data. Here I have my results for ‘bike’ related products, for what it’s worth.
Now go forth and filter.