If no value is passed in Multi Select Parameter, instead of showing no result the query is returning all rows of table

[inform2jayant]

Hi,

I'm using Multi-Select Parameter in my report and using this syntax "$X{IN, dummy, PARAM_MULTI_PROJ_ID}" in the query. 

QUERY 1:

Now this works fine if I'm put some value for this parameter and I run the report. But when I do not give any value in this parameter then instead of showing no results it simply selects all the rows of that table and provide the output which is not desired as per my requirement. Below is the query where i'm using this parameter. Please let me know if my implementation is in correct somewhere:

SELECT tab.id  AS id 

FROM my_table tab

WHERE 

$X{IN, tab.ID, PARAM_MULTI_PROJ_ID}

 

QUERY 2:

Also, Im using a separate queyr where I'm checking whether the Multi-Select parameter is null or not. Please check the below query and let me know if it is correct:

SELECT

CASE WHEN

$X{IN, 0, PARAM_MULTI_PROJ_ID}

  THEN 0

  ELSE 1

  END as filter_check

FROM DUAL

 

Thanks

[mlopez_1]

You can put this in your where statement of your query:

(

  $P{PARAM_MULTI_PROJ_ID} = '[]' AND tab.ID = -1

  OR

  $X{IN, tab.ID, PARAM_MULTI_PROJ_ID}

)

Where tab.ID = -1 is a value that doesn't exit.

Mariano

[inform2jayant]

Hi Lopez,

The peace of code that you have shared gives an error for this line " $P{PARAM_MULTI_PROJ_ID} = '[]' " as INVALID COLUMN TYPE. Could please suggest some alternate way?

 

Thanks

Jayant