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

OBIEE 11.1.1.7 - Freeze columns (top or left) of a dashboard page layout

When working with the dashboard objects, noticed “Break” option is replaced with “Freeze Column” choice in column object.

The "Freeze Column" selection lets scrolling while keeping the context (like prompts, notes etc.) of the dashboard. When a column is frozen, it remains fixed at the edge (top or left) of the page layout and will not scroll off the page as you scroll content in other columns as below.

Dashboard Column - Freeze Column

Left - fixed column with scrolling

In OBIEE 11.1.1.6, the "Break" option is to add page and column breaks to the layout of a dashboard page.

                                  

OBIEE 11.1.1.7 - Methods to browse data (scrolling or page controls)

Another new feature supported in OBIEE 11.1.1.7 is to keep the rows/columns headers visible.

Users have a choice to browse data by specifying the methods below

1) Fixed headers with scrolling content :( available for Table/Pivot views) - This option to use scrolling as the method to browse data

• Keeps headers of rows and columns visible at all times
• Includes horizontal and vertical scroll bars

2) Content paging - This option to use page controls as the method to browse data 

Table view properties - Fixed headers with scrolling

The report results (table view) with Fixed column header and a scroll bar.

Final Report  with Vertical Scrolling

OBIEE 11.1.1.7.0: Adding actions to reports from Totals, Sub-totals, and Grand Totals

New Feature: Ability to invoke an action from the column and (sub-totals, totals, Grand Totals) was added

Create a simple report (summary) on regional performance as below. To view the detail report from any row (sub-totals, totals and Grand Total), edit the column properties – select action links and check Enable on Totals. 

OBIEE 11.1.1.7 - column properties

 Sample reports (summary & detail)

Summary - action link on region totals

 

Detail report - for region Americas