Fusion - BI Publisher - Handle Null Value Parameter Using Comma Separated Parameter and Data Source is Fusion OTBI Dashboard

(Doc ID 2104416.1)

Last updated on OCTOBER 11, 2017

Applies to:

Oracle Fusion Performance Management Cloud Service - Version 11.1.10.0.0 and later
Oracle Fusion Application Toolkit - Version 11.1.10.0.0 and later
Oracle Fusion Application Toolkit Cloud Service - Version 11.1.10.0.0 and later
Information in this document applies to any platform.

Symptoms

On : 11.1.10.0.0 version, Technology Management - Reports/Analytics

ACTUAL BEHAVIOR
---------------
Create a BI Publisher report that has comma separated text parameter and receives values from a OTBI dashboard prompt.
The report uses Oracle BI Server source so you cannot use nvl() database function to handle null with multiple values selection and parameter to be OPTIONAL.

After trying to use the following where clause:
WHERE "Performance Document"."Performance Document Period Name" in (case when :PERF_TEMPLATE = 'null' then "Performance Document"."Performance Document Period Name" else :PERF_TEMPLATE end)

 - works with single value parameter, it handles null value, but produces error when is used with multiple value parameter


If you use the following where clause works with multiple value parameter but the parameter becomes mandatory:
WHERE "Performance Document"."Performance Document Period Name" in (:PERF_TEMPLATE)


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms