MultiValue Parameter Not Working in SSRS 2017 for Oracle DataSource

Question

I am creating a SSRS report with oracle data source. I am passing multi-value parameter values as a filter to the Oracle dataset. Below is the sample query,

select *from FIN.FINANCIAL_TRANSACTION F
 where TO_DATE(F.CREATE_DATETIME) BETWEEN  to_date(:startdate,'YYYY-MM-DD') and  to_date(:enddate,'YYYY-MM-DD')
AND F.SUBTYPE IN (:subtype)

This query works when i pass a single value in :subtype parameter. When I passed multiple values I am getting no output. When Multiple values are in the parameter, its passed as a comma separated string. So i tried to split the string with below query. But getting Error in report processing.

select *from FIN.FINANCIAL_TRANSACTION F where TO_DATE(F.CREATE_DATETIME) BETWEEN to_date(:startdate,'YYYY-MM-DD') and to_date(:enddate,'YYYY-MM-DD') AND F.SUBTYPE IN ( select regexp_substr(replace(:subtype,'''', '"'),'[^,]+', 1, level) as txt
from dual connect BY regexp_substr(replace(:subtype,'''', '"'), '[^,]+', 1, level) is not null)

Popeye Popeye · Accepted Answer · 2020-01-06T05:33:18

The issue in your query is REPLACE(:SUBTYPE, '''', '"'). Why you are replacing a single quote with the double quote?

Values in the Oracle is always wrapped in single quotes like this -

F.SUBTYPE IN ('val1','val2','val3',.....)

So I think this is the only issue with your query.

For multiple input parameters which are comma-separated, You can use Hierarchical query as following (Repeating your query to provide you the actual query):

SELECT
    *
FROM
    FIN.FINANCIAL_TRANSACTION F
WHERE
    TO_DATE(F.CREATE_DATETIME) BETWEEN TO_DATE(:STARTDATE, 'YYYY-MM-DD') 
      AND TO_DATE(:ENDDATE, 'YYYY-MM-DD')
    -- changes after this line (IN clause)
    AND F.SUBTYPE IN (
        SELECT DISTINCT
            TRIM(REGEXP_SUBSTR(:SUBTYPE, '[^,]+', 1, LEVEL)) VALUE
        FROM
            DUAL
        CONNECT BY
            REGEXP_SUBSTR(:SUBTYPE, '[^,]+', 1, LEVEL) IS NOT NULL
    );

This will support both single and multiple values(comma-separated).

Cheers!!

MultiValue Parameter Not Working in SSRS 2017 for Oracle DataSource

1 Answers