Sunday, December 6, 2015

Passing multiple values to BI Publisher report using parameter prompt

Objective is to fix the problem when selecting multiple values from a parameter then SQL throws exception: ORA-00909: invalid number of arguments

Create data set and pass the parameter as shown

In general BI Publisher supports all, single, multiple values to be passed as filters to the Report. But to handle show multiple (all) values including null  this functionality can be achieved using the condition as shown.

select STATE_CODE, STATE_NAME
from
WC_STATE_D
where
COUNTRY='US'
and (STATE_CODE IN (:states) or 'All' IN (:states || 'All'))
order by
STATE_NAME ASC
  
Create a parameter and select list of values defined for this parameter

A menu type parameter "states" presents state LOVs to the user from which to select a value or values to pass to the data source. Define the list of values as "States List" using SQL query type.

Parameter - menu type

Note: Choose any parameter option NULL Value Passed or All Values Passed.To allow multiple entries from the list, selected "Multiple Selection" option.

 If the parameter values are not dependent on any other parameters, uncheck 'Refresh other parameters on change" option.

Click View Data and select All from the list menu.

The following figure shows how the states menu type parameter will display list of state(s) in the report when multiple selection is enabled:

All states

Selected state

I hope the above solution is helpful to avoid the sql error ORA-00909: invalid number of arguments

Monday, November 16, 2015

BI Publisher: How to customize the Prompt – Parameter Labels font-size


I have had request to customize the font-size of BI publisher prompt-parameter labels, please follow the steps below.

1) Locate the file “blafplus-rich.css” from the path below.

\OBIEE\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\bipublisher_11.1.1\to5gma\war\xdo\styles

2) In the above .css file, you would notice the class .prompt {} as shown; the default font-size is 11.

.prompt {font-family:Tahoma,sans-serif;font-size:11px;font-weight:normal;color:#704A00;vertical-align: baseline;}


3) Set the required font-size, save as this file blafplus-rich.css in the same folder. Be sure to backup the css file.

Below are the screenshots of prompt labels before and after updating font-size.



Monday, November 25, 2013

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