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

Neeti Choksi-Oracle

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):

ALOK SH-Oracle

Hi Neeti,

Yes currently "All Values Passed" option is not available in OAC version 6.2.But "All Values Passed" option is mention in OACS doc.

Need to check if bug is already raised for this issue?

Meanwhile can you please check the below url .it might help.

https://docs.oracle.com/en/cloud/paas/analytics-cloud/acsmd/add-new-parameter.html#GUID-FD8752C2-D398-4F8F-BB80-7C1347021FA4

Neeti Choksi-Oracle

Hi Alok,

Thanks for the info. I was able to get the resolution to my issue with the help of the development team.

We were using Oracle BIEE connection as a source to our BIP data model. Apparently, the BIJDBC connection does not support NVL or NULL values. you need to use CASE WHEN statements to handle the null condition for parameters.

Example - 

 AND ("vw_fact_cnctvty_fee_smry_sox_1"."chain" IN (:chain) OR (CASE WHEN ('null') in (:chain) THEN 1 END =1))