OBIEE/BI Publisher List of Values/NULL entries problem
Before I go into my question, below is the version of OBIEE I am currently using
OBIEE Version: Oracle Business Intelligence 11.1.1.7
My problem is using a list of values within BI Publisher. I am able to create the list of values and the values to a parameter. I have two different lists of values that are DEPARTMENT and ROLES. Each of these lists of values are “Multiple Selection” and also they “Can select all”. If they do Select All, then a NULL value is passed instead of All Values Passed.
The logic for my Where clause in this BI Publisher report is:
SELECT ….
FROM ….
WHERE (Department in (:dept_ID) OR least (:dept_id) is NULL;
If you run this query without any values, the statement makes sure you get ALL the values in that list of values. Even picking one dept_ID will return you the correct information. My problem arises when I pick multiple dept_IDs. When I run it with multiple dept_ID through the database and physical SQL, the data comes out properly. But when I put that SQL into BI Publisher it seems that the integration between the server and the database is not working properly.
The error I get when I try to run it with 2 or more dept_IDs is: <txt>oracle.xdo.dataengine.datasource.plugin.DataAccessException: java.sql.SQLException: Missing IN or OUT parameter at index:: 9
I have a feeling it has something to do with the second part of the query with Least.
In summation, I am trying to use a list of values to run a BI Publisher report, and it seems to fail when I enter more than 1 parameter.
- This statement will return all the departments
WHERE (Department in (‘’) OR least (‘’) is NULL;
02. This statement will return the specific dept_id entered by the user
WHERE (Department in (’IT’) OR least (‘IT’) is NULL;
03. Lastly, this statement fails with an error when I am using 2 or more values
WHERE (Department in (‘IT’, ‘BUSINESS’) OR least (‘IT’, ‘BUSINESS’) is NULL;
Again these statements work properly when running the physical SQL in the database. I have looked around the internet trying to find other information to solve this problem, but I have not found the right solution yet. Please take a look at my problem and let me know if you have any solutions for it.