Tag Archives: prompts

Values In OBIEE 10g Multiselect Prompt Are Not Sorting in Alpha-Numeric Order In Large LOVs

Symptoms
When running an Oracle Business Intelligence Enterprise Edition (OBIEE) Dashboard with multi-select prompts that contain many values, the values in the list are not consistently alpha-numerically sorted.

The initial retrieval in the multi-select dialog box shows the values alpha-numerically sorted.  When the next set of values are retrieved, the values lose their sort order and are no longer alpha-numerically sorted.
Cause
This issue is described in the following known bugs:

Bug 8995183 – SORTING IN MULTISELECT PROMPT NOT WORKING AS EXPECTED
Bug 9787610, MULTISELECT PROMPT NOT SORTING PROPERLY WHEN CONTAINS MORE THAN 256 ITEMS
Solution
This issue is fixed in OBIEE 10.1.3.4.2.
If you are encountering this issue, you are urged to upgrade to version 10.1.3.4.2 to obtain the fix
for this bug and many other bugs.
For information on upgrading to this version, please see Note 1357872.1 How To Upgrade to
Oracle Business Intelligence Enterprise Edition (OBIEE) 10.1.3.4.2

If you are currently unable to upgrade to OBIEE 10.1.3.4.2, and require the fix in OBIEE 10.1.3.4.1, please do the following:
1.    Open a Service Request (SR) with Oracle Support and request the password for
Patch 9787610.

2.    Follow instructions for installing the patch as described in patch readme.txt file.
Oracle Support recommends that you first test this patch on a development or test server.
Once you are satisfied that the patch corrects the issue, you may promote the patch to
your production environment.

NOTE: If you are using any web server other than IIS, you will need to recreate and redeploy the
Analytics.war file after installing the patch.

For detailed instructions on how to do this in an OC4J environment, please see:

Note 1075754.1 Create and Deploy A New BIEE 10.1.3.4/10.1.3.4.1 ANALYTICS.WAR File.

References
BUG:8995183 – SORTING IN MULTISELECT PROMPT NOT WORKING AS EXPECTED
BUG:9787610 – MULTISELECT PROMPT NOT SORTING PROPERLY WHEN CONTAINS MORE THAN 256 ITEMS
NOTE:1075754.1 – How To Create And Deploy A New OBIEE 10.1.3.4.X ‘analytics.war’ File (for OC4J)
PATCH:9787610 – MULTISELECT PROMPT NOT SORTING PROPERLY WHEN CONTAINS MORE THAN 256 ITEMS

Applies to:
Business Intelligence Suite Enterprise Edition – Version: 10.1.3.4.1 [1900] to 10.1.3.4.1 [1900] – Release: to 10g
Business Intelligence Server Enterprise Edition – Version: 10.1.3.4.1 [1900] to 10.1.3.4.1 [1900]   [Release: 10g to 10g]
Business Intelligence Answers Option – Version: 10.1.3.4.1 [1900] to 10.1.3.4.1 [1900]   [Release: 10g to 10g]
Information in this document applies to any platform.
This knowledge document is a replacement for 952833.1 which has been deleted.

Oracle’s answer to OBIEE’s case sensitivity

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]

NULL value in multi-select prompt

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.

display prompt values in Excel

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.