How to handle 'All' parameters in Oracle BI EE query

Salma Ashraf

I used this method but it didn't work.

Sumanth V -Oracle

@Salma Ashraf - Does your data have null values as well for Person_number? If yes, that could be the issue, as Oracle BI EE connection doesn't handle NULL values. Please refer below thread where they have suggested how the null values should be handled.

https://community.oracle.com/products/oracleanalytics/discussion/16622/bi-publisher-selecting-all-in-parameter-does-not-return-any-data

Abhilash K - Deloitte USI

Hi @Salma Ashraf,

Could you try like this instead of the SQL statement you used for Person number? Since you use NULL values as passed radio button, I doubt it would pass 'All' back to the query. It will send NULL. Try revising the statement like this and let know if it works.

AND (person_number IN (:person_number) OR LEAST(:person_number) IS NULL)

Thanks ,

Abhilash

Salma Ashraf

Hi @Abhilash K - Deloitte USI

Oracle BI EE often enforces security restrictions that might prevent using functions like NVL or LEAST directly in the presentation layer.

also when i put this statement AND (PERSON_NUMBER IN (:person_number) OR 'All' IN (:person_number || 'All')) in my query it gives me this error when i run the query

Thanks in advance

Salma Ashraf