Case Insensitive Search using OBIEE 11g dashboard prompt - Text Field

Objective: Users should be able to search by entering the text (in all UPPER/lower case) and
view results.

To achieve this functionality we need to create a dashboard prompt, analysis and apply the filters using Case Conversion functions (Lower, Upper) as below.

1) Create a new dashabord prompt and select column prompt from the list.

• Select Text Field for User Input
• Select the type of variable as Presentation Variable, enter a name for the variable as "company_name".
• Save the prompt.

Dashboard Prompt - Text Field

2) Create an analysis and add coulmns to the criteria

• Edit the column filter and from the “Add More Options” list, select Presentation Variable. In the variable expresssion, enter the presentation variable name as company_name.
• Using the Advanced SQL Filter, you can convert this filter to SQL and edit the filter to apply LOWER or UPPER functions. 
• Apply condition below, for a column where data in the column contains all of the values in the filter.

             UPPER("Offices"."D4  Company") like UPPER('%@{company_name}{%}%')
     or
             UPPER("Offices"."D4  Company") like UPPER('%@{company_name}%')  - display no results message in the dashboard.          

• Similarly, for a column where the data in the column begins with the value in the filter, apply the condition below.

            UPPER("Offices"."D4  Company") like UPPER('@{company_name}%')

Report Filter

Filter SQL

3) Finally when a user searches a company regardless of the case (lower/upper), content of the presentation variable and SQL filter applied in the analysis makes the comparison and provide the results.

Lower case search

Upper case Search