April 12, 2012

Search Field Input Control in Webi

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.

I’m Using

  • BusinessObjects Enterprise BI4.0 (although the logic is the same in XI3.1)
  • Webi

I’m Assuming

  • That you are familiar with Webi and Input Controls

 

The Logic

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.

  1. Create a variable that will hold our search term.
  2. Allow the user to enter a search term into this variable.
  3. Filter the report to only show rows that match the search term.

 

Create the Variable

  1. In BI Launch Pad, open your Webi report and add a new variable.
    image
  2. Name the variable Search Term.
  3. Leave the Qualification as Dimension.
  4. Enter something in the Formula box. It doesn’t matter what.
    image
  5. Click OK.
  6. To better demonstrate how this works, I’m going to add this variable to a simple block.
    image

 

Create an Input Control

  1. Create an input control based on the Search Term variable.
  2. Set the settings like this:
    image
    • Select the input field as Entry field
    • Make the label useful
    • Clear the default value
    • Leave the operator as Equal to
  3. Now, to test your input control, enter some text into the field and hit enter, or click OK. The column showing this variable will update.
    image

 

Filter the Report

  1. Create a new variable, and name it Search Matches.
  2. Leave the qualification as Dimension.
  3. 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.
    image
  4. Add this variable as a column to your block.
  5. 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.
    image

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.

  1. Add a report-level filter to only include rows where the Search Matches variable equals Yes.
    image
  2. Select the value Yes. If it isn’t in the list, type it in.
    image
  3. Click OK.

Hey Presto! You now only have rows where the search term is in the product dimension.

image

 

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"

to this:

=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.

image

 

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.

image

 

Now go forth and filter.