Search and Filter data in report

[Former Member]

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.

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.

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.

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.

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.

Here are some screenshots to illustrate the purpose:

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.

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

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.

Didier MAZOUE