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.

BI Publisher - Selecting 'All' in Parameter does not return any data

Neeti Choksi-Oracle

Oct 5, 2021

Summary:

We have created a BIP model parameter in such a way that when all values are selected, Null will be passed. We are handling this condition in the data model query with COALESCE or NVL or IS NULL functions, but none of them is returning any results.

Content (required):

Hi Team,

We have created a BIP model parameter in such a way that when all values are selected, Null will be passed (I believe there was another option of 'All will be passed' but somehow it's not available in OAC). We are handling this condition in the data model query with COALESCE or NVL or IS NULL functions, but none of them is returning any results. When we explicitly select all the values in the parameter (without selecting 'All') then it's returning the results correctly. Tried this with multiple Oracle and non-Oracle data sources. Can anyone help here, please?

Version (include the version you are using, if applicable):

6.2

Code Snippet (add any code snippets that support your topic, if applicable):

---

View Post

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