OBIEE Blog

Someone asked Oracle’s support  the following question: “Why some of the prompt case sensitive, some of them are case insensitive”

You noticed that some prompts are case sensitive and some are not. User selects a multi-select prompt, with the ”BEGIN WITH” matching functionality, ie, User enters “We” (without quotes) and presses the Go button, the user receives “No Choices Available.” message. User then enters “We%” the users receives results.

On other dashboards the steps describes above return results by entering “We” OR “we” into the “BEGIN WITH” matching functionality.

By default, Oracle database is case sensitive, this can be easily verified by run a query to test it, for example:

SELEECT PROD_NAME FROM PRODUCTS WHERE PROD_NAME LIKE ‘p%’
ORDER BY PRODNAME;

This query should not return any result as the first character for product name stored in database is upper case.

However, when using BI Answers, you may find some of the prompt with condition IS EQUAL TO/LIKE is case insensitive. This happens when

1. CASE_SENSITIVE_CHARACTER_COMPARISON = OFF is set (BIEE default setting) for Oracle Database in NQConfig.ini file.

2. The table is set cachable in BIEE Physical model.

So the data is cached in BI server is case insensitive, consequently the prompts retriving data from cacheable table is Case Insesitive, and the prompts retriveing from Non-cacheable table is Case Sesitive.

You also can turn off the cache in BIEE Administrator for the table in question, this can be done by double click on the table in Physical Model, click on General tab, uncheck Cacheable option for the table in question, save it. Restart BI Server. You then will find it is case sensitive, but for those tables set cacheable, they are still case insensitive.

It is recommended to set CASE SENSITIVE_CHARACTER_COMPARISON according to database searching behavior, so SENSITIVE_CHARACTER_COMPARISON=ON is set for Oracle Database in NQConfig.ini file to enforce consistent Case Sensitive searching behavior.
REF: [ID 862731.1]

There is a NULL value in the Multi Select Prompt. When this blank value is selected with other selections from the prompt, the prompt either blanks out or the results of the report returns all values of the field instead of filtering only for the selected criteria.

Cause
The cause of the issue is to have a NULL value (it comes from the datawarehouse) as an option in the multi-select prompt because when is selected, in the textbox this symbol ” appears as the selected option and the filter does not work.

It was tested inhouse adding NULL values in the dimension and fact tables of a business model.
Solution

There are four options to resolve this issue:

1.- Validate if NULL values are relevant for the bussiness analysis (for some reason in the datawarehouse NULL values were stored). If they are not, you have to delete them in Dimensions and Fact tables.

2.- Change the physical table type in your rpd in all tables you have NULL values and use a SELECT like this:

SELECT LOGIN, RESP
FROM NQ_LOGIN_GROUP
WHERE NOT LOGIN IS NULL

Doing this, you only have NON-NULL values in your Multi-select prompt.

3.- Replace the NULL value with a new name like UNSPECIFIED in ALL tables you which are related.

4.- Log an enhancement request to review that multi-select prompts works with NULL values.

Customer decided to apply the third option, change the NULL values by a value like UNSPECIFIED. They couldn’t delete the NULL values so that was the best option for them.

Also I’ve already logged an enhancement request to review that multi-select prompts works with NULL values or display an error or warning message.

How can we get the Prompt values to be displayed when the report is exported in MS Office formats or printed in PDF using the ‘Download’ or ‘Print’ report links?

When you use the ‘Printer Friendly’ option on the Dashboard, all objects on the Dashboard get downloaded for printing including the Dashboard Prompt section.

However using the report ‘Download’ or ‘Print’ link, this does not print the Dashboard Prompt section.
Please note this is expected behavior. Using the Printer Friendly icon at the lower lefthand corner, it prints the entire dashboard. Alternatively the ‘Download’ or ‘Print’ link option is available, for the report. So this will print the Compound layout view of the report, or any specific report sections that have been made available on the Dashboard.

A workaround to have the Dashboard Prompt values displayed when you use the Report ‘Download’ or ‘Print’ link is by having the filter view as part of the report.

Oracle has now raised the Enhancement Request# 8242921 to address this matter. The enhancement request is to enhance the product so that so there is also an option to download to the various formats at the dashboard page level. Similar to the Dashboard Page ‘printer friendly’ functionality.