Passing Multiple Values to BI Publisher Report via single Parameter

1.     Problem Description

In general BI Publisher supports multiple values to be passed as filters to the Report .But where in the case if the number of values passed from a parameter reaches more than 1000 then sql throws exception saying   ORA-01795: maximum number of expressions in a list is 1000 .

2.What happens in background

To make reports work with Multiple Select Prompts/Parameters, we need to handle that logic inside the sql dataset. Usually  this functionality can be achieved using IN operator in WHERE clause .

Example:

SELECT * FROM W_INVENTORY_PRODUCT_D

WHERE PRODUCT_NUM IN (:P_NUM)

Above query works well until P_NUM parameter holds less than 1000 literals. Whenever it reaches more than 1000 then sql throws exception like as mentioned above. Because IN operator won’t allow more than 1000 literals or hard coded values.

Let say we passed two values(Part_ABC,Part_XYZ) from prompt,then in side BI Publisher query will be generated as below.

SELECT * FROM W_INVENTORY_PRODUCT_D

WHERE PRODUCT_NUM IN (:P_NUM4118,:P_NUM4119,'X')

Bind Variables ...

     1: P_NUM4118:Part_ABC

Bind Variables ...

     2: P_NUM4119:Part_XYZ

So for each selected  value XDO engine creating Bind Variable and assigning appropriate value at run time .Hence if we select more than 1000 values from the prompt then XDO engine will create more than 1000 Bind variables where it will cause IN operator exception.

3.Resolution

To make it work for even more than 1000 values, need to alter sql dataset code. This change  is very small and of course worth.

Parameter Definition:

Create a Parameter :P_NUM

and enable

Multiple Selection

Can Select All (Null Value Passed)  options

Dataset definition:

SELECT * FROM

(SELECT PRODUCT_NUM FROM W_INVENTORY_PRODUCT_D)

WHERE

PRODUCT_NUM IN (:P_NUM) OR LEAST(:P_NUM) IS NULL

Here LEAST() is a function that returns the least value . NULL will be returned if it has null as argument.

Example

LEAST(1,3,5) returns 1

LEAST (1,null,7) returns null

LEAST(null) returns null

As per the Parameter definition NULL will be passed if ALL value selected in the prompt.

So in this case when ALL selected ,condition  LEAST(:P_NUM) IS NULL  becomes true and acts like 1=1 ,so all rows can be fetched. So that we can avoid the sql error  maximum number  1000 reached by passing null (ALL).