OBIEE 11g/12C - Search Results are Limited On restricting the Output Using SQL Results (Doc ID 2181112.1)

Last updated on APRIL 13, 2017

Applies to:

Business Intelligence Suite Enterprise Edition - Version 11.1.1.9.5 and later
Information in this document applies to any platform.

Symptoms

On Oracle Business Intelligence Enterprise Edition (OBIEE) 11.1.1.9.5 and above, in a dashboard with prompts defined with choice List of values based on "SQL Results", when trying to select additional values in the list box by clicking on the “Search” option in the dashboard, it is noticed that the search results are limited to the values in the browse list.


Sample steps to replicate the scenario:

---------------------------------------

1. Create a Column Prompt with an Year column ( For example)

2. Under "Choice List Values", restrict it with an SQL such as below:

SELECT "Financial Calendar"."CAL_YEAR" FROM "Y07 - Demo Insurance" WHERE "Financial Calendar"."CAL_YEAR" BETWEEN (extract(year from current_date)-5) and (extract(year from current_date)) order by 1
Note- This would restrict the values to 2011 and 2012 wheras the column has more values

3. Save the prompt and add it to the dashboard.

4. When we click on the prompt to choose values, it shows 2011 and 2012 ( as per the above SQL logic , they are the chosen values)

5.On clicking search on the choices, "Select Values" wizard pops up.

6. Under the available values only , the text - "Search results are not limited to values in the browse list" and only 2011 and 2012.

7. Whereas, it is expected that all the distinct values should be displayed here instead of just two values.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms