Skip to Content
Author's profile photo Didier MAZOUE

Search and Filter data in report

Web Intelligence offers capabilities to search data in a report, especially in a table.

To use this feature, you need to enter the text to search in the bottom hand side toolbar of the report and then select the parameter “Match case” or “Ignore case”. You also have the ability to highlight all occurrences in the document.

Search and Filter - 1.png

This is very useful but it cannot be used for print or export. The highlight capabilities is not very visible and moreover we cannot use the “Match whole word” parameter. Last we cannot filter the data based on the search results.

Search and filter

The idea is to use input controls to allow an end user more powerful search capabilities with possibility to filter the data based on search results.

To do so I first need to create 3 variables:

  • Match case: =0
  • Match whole word: =0
  • Apply filter: =0

Then I created one input control for each new variable. Then I used a slider with a value varying from 0 to 1. s:

  • Match case = 1: the search must match the case
  • Match whole word = 1: the search must match a whole word
  • Apply filter = 1: the search results are used to filter the data.

Search and Filter - 2.png

Now I need to create another variable (string data type) used to enter the search criteria:

  • Search: =””

And I created an input control based on this new variable.

Search and Filter - 4.png

Search and Filter - 3.png

Now I need to create a variable for each dimension, detail or measure I want to be used for searching in the report

I give you one example, and you just need to copy the formula and replace the object name by another object.

  • City filter: =If ([Search] <> “”) Then
    If ([Whole Word] = 1 And [Match case] = 1 And [City] = [Search]) Then “OK”
    Else If ([Whole Word] = 1 And [Match case] = 0 And Upper([City]) = Upper([Search])) Then “OK”
    Else If ([Whole Word] = 0 And [Match case] = 1 And Pos([City];[Search]) > 0) Then “OK”
    Else If ([Whole Word] = 0 And [Match case] = 0 And Pos(Upper([City]);Upper([Search])) > 0) Then “OK”

In the formula I need to ensure that at least some search criteria have been entered (If ([Search] <> “”)) before doing the test.

My report is looking like this before using the search capabilities.

Search and Filter - 5.png

Now to show the search results in the report I defined a formatting rule for each variable used for the search. The background and foreground colors are changed if the search matches an occurrence of the variable.

Search and Filter - 6.png

Here are some screenshots to illustrate the purpose:

Search and Filter - 7.png

Search and Filter - 8.png

Search and Filter - 9.png

Search and Filter - 10.png

Search and Filter - 11.png

Now, in order to filter the data, I need to create a new variable to test if the data needs to be filtered:

  • If Filter applied? =If [Apply filter] = 0 Or [Search] = “” Then “OK”
    Else If ([Apply filter] = 1 And ([Calendar Year filter] = “OK”
    Or [Category filter] = “OK”
    Or [City filter] = “OK”
    Or [Country filter] = “OK”
    Or [Month filter] = “OK”
    Or [Product filter] = “OK”
    Or [State-Province filter] = “OK”
    Or [Subcategory filter] = “OK”)) Then “OK”

The first condition in the formula is to ensure that’s no filter is applied when no search has been entered or when the apply filter input control has not been set to 1.

Last I need to create a filter in the report including all sections and tables.

Search and Filter - 12.png

Tio illustrate the purpose I will use the same search criteria (“or”) I used in a previous screenshot but now WITH filter applied.

Search and Filter - 14.png

Search and Filter - 15.png

In conclusion, these search capabilities offer more powerful and flexible capabilities (export, print) and allow the user to filter the data based on search results.

We could imagnine adding more serach capabilities with additoonal operators such as: >, < >=, <=, <>, etc.

You can download the document I used to illustrate my purpose.


Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Ruby Sriwastwa
      Ruby Sriwastwa

      Hi. Thanks for this article. I have been struggling trying to place such a global filter in WebI.

      However, even after following all steps, I don't see my filtered data. Not even highlighted after
      I try search. I dont know how to proceed.