Cannot add multi value query parameter '?' for dataset

[isuru]

This is my query.i want branch id as parameter and select multiple values but error occurs.ODBC data source used.

select * from dwh.FACT_ECS_PROD_SOLD_NEW_AGGR where AS_AT_DATE='28-AUG-24' AND branch_id in (?);

i use below query as available values.

select distinct BRANCH_ID from dwh.FACT_ECS_PROD_SOLD_NEW_AGGR where AS_AT_DATE='28-AUG-24'; 

[v-jtian-msft]

Hi,@isuru .I am glad to help you.
According to your description, you encountered an unsupported function error when you set the multi-value query parameter using ODBC data source, here is my test
This is the test data I used (I used a local EXCEL file as the ODBC data source)

SELECT * FROM [TestTable$]

I have successfully connected to the data source as ODBC and can display the entire excel data without setting any multi-value parameter.
But after creating the multivalue parameter, I am facing the following problem:
I created the report parameter first, and then directly queried the multivalue parameter in “Query” by (@Bus_ID).
The error is as follows.

Cannot add multi value query parameter '@Bus_ID' for dataset 'DataSet1' because it is not supported by the data extension. -------------------------- -- An error has occurred during report processing. ---------------------------- An error occurred during local report processing.
I then tried several other common multi-value parameter lookups, but unfortunately they all failed as well
like this.

= “SELECT * FROM TableName WHERE FieldName IN (”” & JOIN(Parameters!ParameterName.Value, ‘, ’) & ”)”

This means that ODBC type data sources (excel type) do not support multi-value parameter queries
For the remaining two types of ODBC data sources, which I can't reproduce due to my work environment, I think you can try dumping the data source into MS Access DataBase to see if it supports multi-value parameter queries.

In fact,using Microsoft SQL Server as the data source for paging reports can solve many problems.
ODBC data source as an external data source, generally will be connected to various other types of databases, which may lead to some functionality is missing, so if possible, using Microsoft SQL Server data source will be more convenient and efficient, to avoid many functional limitations.
In my test, the data in SQL Server data source does not have any problem after setting the multi-value query parameter.
Like this:

The multi-valued parameter query effect can be successfully implemented by means of @parameters.

Pagination reports are displayed normally.
Here are the official documents and video links on the subject, I hope you find them helpful:
URL:
Solved ! Multi-value Parameter in ODBC Power BI Paginated Reports  (2 of 3)
Add a multi-value parameter to a paginated report - Microsoft Report Builder & Power BI Report Build...
Parameters collection references in a paginated report - Microsoft Report Builder & Power BI Report ...

 I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.