SSRS 2012: Cannot add multi value parameter '?' for dataset XYZ because it is not supported by the data extension

Question

Scenario: I am working with a Firebird database (somewhat similar to MySQL) connected to Microsoft SSRS 2012 via ODBC connection manager.

I have a huge stored procedure (which I am not supposed to modify at all) given by our client. The stored procedure has a parameter - Parameter X filtering Column X (integer datatype); when I pass values to Parameter X - say 1,2,3,4... individually, I am able to run the report correctly in SSRS.

However, when I try to pass a string of values - (1,2,3,4...) - i.e. (by checking) 'Allow multiple values' for this Parameter X (text datatype) in SSRS Report Parameter Properties box, I get this error -

'Cannot add multi value parameter '?' for dataset XYZ because it is not supported by the data extension. (XYZ is the main dataset for this report)

The values 1,2,3 are passed as input from another dataset (PQR).

How can I pass multiple input values (1,2,3,...) to Parameter X from this PQR dataset ?

As I have mentioned, I cannot modify the PSQL Firebird stored procedure (main dataset XYZ) at all. The procedure reads something like this for filtering Column_X:

WHERE       :Parameter_X    CONTAINING   Column_X

Now if :Parameter_X = 1 or 2 or 3 individually passed from dataset PQR, SSRS report works fine.

If :Parameter_X = (1 ,2, 3, ....) multi values passed from dataset PQR, SSRS report throws the above error.

I can modify the dataset PQR, but not the main dataset XYZ.

Any suggestions ?

Figured out: Go to the main dataset (XYZ) of the report; In the Dataset Properties, go to the Parameters tab. In the Parameter Value column for the particular parameter in question, overwrite [@ParameterX] with : =Join(Parameters!ParameterX.Value,",") My Input has to be (1 2 3 .....) for ParameterX from the dataset PQR; This will be passed as (1,2,3,....) with the Join keyword; and multi select is allowed without errors now. The datatype for ParameterX in SSRS must be Text only. This is the workaround WITHOUT modifying the stored procedure — user3812887

aspxsushil aspxsushil · Accepted Answer · 2019-02-06T16:33:01

Join(Parameters!ParameterX.Value,",") still works. In my case it was using OLE DB connection type instead of regular ORACLE in Report Manager.

It seems OLE DB could not parse Join(Parameters!ParameterX.Value,",") .

SSRS 2012: Cannot add multi value parameter '?' for dataset XYZ because it is not supported by the data extension

As I have mentioned, I cannot modify the PSQL Firebird stored procedure (main dataset XYZ) at all. The procedure reads something like this for filtering Column_X:

1 Answers