question

TuYuHsuan-4881 avatar image
0 Votes"
TuYuHsuan-4881 asked Joyzhao-MSFT commented

SSRS Error: Cannot add multi value query parameter '@CountryId' for dataset 'ds_GetPractice' because it is not supported by the data extension. (rsErrorAddingMultiValueQueryParameter)

Hi,

I am using ODBC to connect PostgreSQL in SSRS.
and face issue
Cannot add multi value query parameter '@CountryId' for dataset 'ds_GetPractice' because it is not supported by the data extension. (rsErrorAddingMultiValueQueryParameter)

I have two dataset
the first dataset (DS1) will return two value CountryId and CountryName,
the second dataset (DS2) will use the first dataset's CountryId as parameter, this allow multiple values.

this is my DS2 query:
select PracticeId , PracticeName
from PracticeDim
where CountryId in (select cast(regexp_split_to_table(?,',') as int))

I post this query to pg_admin and this is workable as below,
select PracticeId , PracticeName
from PracticeDim
where CountryId in (select cast(regexp_split_to_table('1,2,3,4,5,6',',') as int))

Please help

sql-server-reporting-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @TuYuHsuan-4881 ,
Has your issue been resolved?
If you have any questions,please feel free to let me know.
If the answer is helpful, please click "Accept Answer" and upvote it.
Best Regards,
Joy

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered TuYuHsuan-4881 commented

multi value query parameter .... select cast(regexp_split_to_table('1,2,3,4,5,6',',') as int))

That may works directly as a PostgreSQL query with one fix string.
But if you have a multi-value parameter of type numeric in SSRS report, it sends the selected values as a comma separated list, not as one string.
So no need to use a split function, use directly in parameter for the IN compare, like

 ... where CountryId in (@yourParameter)





· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@OlafHelper-2800

ODBC does not support using @Parameter, This cause another error
ERROR: operator does not exist: @ character varying: Error while executing the query.

That's why I use "?"

0 Votes 0 ·

Right, ODBC don't support named parameter, only the question mark as placeholder for the parameter; so use that one in the same way >=

  ... where CountryId in (?)
0 Votes 0 ·

This does not work, it show the same error.

Cannot add multi value query parameter '@CountryId' for dataset 'ds_GetPractice' because it is not supported by the data extension. (rsErrorAddingMultiValueQueryParameter)

0 Votes 0 ·
Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered Joyzhao-MSFT edited

Hi @TuYuHsuan-4881
Try the following query for DS2:

 select PracticeId , PracticeName
 from PracticeDim
 where PracticeDim.CountryId in (@CountryId)

In the Report Data pane, expand the Parameters node, right-click the report parameter that was automatically created for the dataset parameter, and then click Parameter Properties.

In the General tab, select Allow multiple values to allow a user to select more than one value for the parameter.

(Optionally) In the Available values tab, specify a list of available values to display to the user.

For more information,please refer to: To add a multi-value parameter
Best Regards,
Joy


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Joyzhao-MSFT
ODBC does not support using @Parameter, This cause another error
ERROR: operator does not exist: @ character varying: Error while executing the query.

That's why I use "?"

0 Votes 0 ·

Hi @TuYuHsuan-4881 ,

 select PracticeId , PracticeName
  from PracticeDim
  where PracticeDim.CountryId in ("& Join(Parameters!CountryId.Value,",") &")

You could refer to: How to pass multi value parameters in SSRS for the Oracle Database using OLEDB Connection.
Regards,
Joy


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


0 Votes 0 ·