nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Oracle Cloud Fusion Multi Select Parameters Errors Out When two or more items are selected

drudd

I'm using Oracle Cloud Fusion to build a data model to feed a report.

I'm trying to create a multi-select parameter. When making the parameter on my data model choose the option selecting all will return NULL. I do this because when I select return all values every option shows up on my report parameters making it unreadable.

So I'm trying to make it so that if null is provided by selecting 'All' it will return all results. Selecting all works fine.

If I select one parameter option the query below works fine.

If I select two or more parameters it always returns ORA-00920: invalid relational operator

SELECT DISTINCT    
G.CERTIFICATION_NAMEFROM     
GRC_ACN_CERTIFICATION_VIEW G
WHERE G.CERTIFICATION_NAME IN (    
CASE        
WHEN :cert_name_p IS NULL THEN G.CERTIFICATION_NAME        
WHEN :cert_name_p IS NOT NULL THEN :cert_name_p    
END )

I have tried to display the results using

SELECT:cert_name_pFROM DUAL

This also errors the same way when I select more than 1.

No matter what I do I can't get it to display or filter two parameter selections.

Find more posts tagged with

BI Publisher Data Models

Customizing Layout

Accepted answers

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/comment/64031#Comment_64031

Can you please try the following?

SELECT DISTINCT    

G.CERTIFICATION_NAMEFROM     

GRC_ACN_CERTIFICATION_VIEW G

WHERE 

1=1 AND

(

G.CERTIFICATION_NAME IN (:cert_name_p)

OR LEAST(:cert_name_p) IS NULL

)

It is now working for me.

Cheers,

All comments

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/24649/oracle-cloud-fusion-multi-select-parameters-errors-out-when-two-or-more-items-are-selected

Hi @User_9ZFXH,

Can you please try the following to see if it is working for you?

SELECT DISTINCT    

G.CERTIFICATION_NAMEFROM     

GRC_ACN_CERTIFICATION_VIEW G

WHERE 

1=1 AND 

G.CERTIFICATION_NAME IN (    

(:cert_name_p)

OR LEAST(:cert_name_p) IS NULL

)

Hope this help.

Cheers,

drudd

https://community.oracle.com/products/oracleanalytics/discussion/comment/64030#Comment_64030

Thank you for responding.

I initially got a missing from error. I moved from to a new line.

I then got an ORA-00907: missing right parenthesis error, but the parenthesis look fine. Not sure what needs changing.

copilot recommended this, but its not the same and results in the same error when I select two options.

Nathan CCC

Try this pattern for multiple value parameters

if data set is physical sql data source ApplicationDB_ FSCM/HCM/CRM

select all … where 1=1 and (coalesce(null,:p_batch_name) is null or glb.name in (:p_batch_name))

if data set is logical sql data source Oracle BI EE

select all … where 1=1 and ((case when ('null' in (:p_batch_name)) then 1 end = 1) or ("Journal Batch Details"."Journal Batch Name" in (:p_batch_name)))

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/comment/64031#Comment_64031

Can you please try the following?

SELECT DISTINCT    

G.CERTIFICATION_NAMEFROM     

GRC_ACN_CERTIFICATION_VIEW G

WHERE 

1=1 AND

(

G.CERTIFICATION_NAME IN (:cert_name_p)

OR LEAST(:cert_name_p) IS NULL

)

It is now working for me.

Cheers,

Bhaskar Konar

Hi @User_9ZFXH,

Here is a working data model with the above query.

Multi_Test.xdm.catalog

Hope this help.

Cheers,

drudd

https://community.oracle.com/products/oracleanalytics/discussion/comment/64033#Comment_64033

Yay this works. I'm hoping you can help me with one question that extends this situation.

Since 'All' comes through as null when I put the parameters on my layout it shows up blank, but I get all results. I want my parameter to read 'All' in my layout

Nathan CCC

NOTE null is 'All' only is you have a lov and you tell it do do so

Yes you can do that either in your data model or in each layout using a condition (if).

In data model for example by adding a column to your data model with a calculation

for example if physical sql where parameter is text (if number or date then cast to character to_char)

select all … , (case when (coalesce(null,:p) is null) then 'All' else :p end) as s_p from …

drudd

https://community.oracle.com/products/oracleanalytics/discussion/comment/64037#Comment_64037

How would I add an if statement to my layout?

Nathan CCC

If your layout is type RTF ? then in your "Template builder for word" add in menu "BI Publisher" after you have upload your sample.xml use the button "Conditional Format" which will add C EC for the start ad end of an if.

drudd

https://community.oracle.com/products/oracleanalytics/discussion/comment/64039#Comment_64039

Thank you!