Oracle Communities

Oracle Analytics and AI

Child Item

Oracle Analytics Cloud - OAPublisher Parameter Issue on Snowflake Data Source

We have a data model off of a Snowflake data source that is working without issue except in one particular case: selecting all parameter values.

The Data Model uses our standard code method - (FIELD IN (:PARAMETER_NAME) OR 'All' IN (:PARAMETER_NAME))

However when we run the report and select All, nothing returns.

If we manually select the entire LOV, it works.

Any ideas?

Find more posts tagged with

Parameter

BI Publisher Data Models

OAC

Snowflake

Oracle Analytics Publisher

Accepted answers

JPorisVU

With the assistance of Tracey who worked on my SR, the following solution for this issue has been identified:

( COLUMNNAME IN (:PARAMETER) OR (CASE WHEN COALESCE(:PARAMETER, NULL) IS NULL THEN 1 END) = 1 )

All comments

RVohra

No experience with Snowflake but see if below works for you:

Add an 'All' option with a null or special value, like '@ALL @' .

Display Value	Return Value
All	@ALL @

WHERE (:PARAMETER_NAME = '@ALL @' OR FIELD IN (:PARAMETER_NAME))
This makes it explicit: when @ALL @ is selected , skip filtering.

I think this pattern is reliable, when data source doesn't gracefully handle ambiguous IN logic with 'All'.

JPorisVU

@RVohra Unfortunately, that did not work. Selecting the All option still returns no vales. Thank you for the option, though.

RVohra

Please check with Oracle Support by logging a SR, Oracle team have more knowledge base and might have seen this with other Users. Otherwise, log an Idea in Idea Lab. Hope it helps!

JPorisVU

SR has been submitted and I will share the solution here once established for future reference and user need.

JPorisVU

With the assistance of Tracey who worked on my SR, the following solution for this issue has been identified:

( COLUMNNAME IN (:PARAMETER) OR (CASE WHEN COALESCE(:PARAMETER, NULL) IS NULL THEN 1 END) = 1 )