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

Last updated on NOVEMBER 29, 2021

Applies to:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.