Cannot add multi value query parameter '?' for dataset 'DataSet_Justificatif' because it is not supp

[Nadege]

I use AS400 like datasource

I want to create a report with 3 filter : year (unique choice ), Mois (multiple choise with all 12 month as default value), City (multiple choice with all city store in my coloun city).

I create the 3 parameters and on my dataset, and i specify the parmeters by a char "?"

my data set is : 

---------------------------------

with cedant as (
select client_number,service_agreement_number, SELLING_GROUP
from dwe_service_agreement
where selling_group in (?) --parameter selling group
and sag_on_live = 'Y')

, nb as(
select periode as "Période",
SELLING_GROUP as "Groupe cédant"

from DWE_WRK_REFI_PREC a
inner join cedant on client_number = num_adh
and service_agreement_number = num_cpt

where year(periode) = ? and month(periode) in(?) -- parameters year, month
)
select *
from nb

------------------------

when i execute my report, i have the below issue: 

An error has occurred during report processing. (rsProcessingAborted)
Cannot add multi value query parameter '?' for dataset 'DataSet_Justificatif' because it is not supported by the data extension. (rsErrorAddingMultiValueQueryParameter)

[v-karpurapud]

Hi @Nadege 

We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank You.

[v-karpurapud]

Hi @Nadege 

I would also like to thank you @Mauro89   for your active participation and for sharing solutions within the community forum. 

I hope the information provided helps resolve your issue. If you have any further questions or need additional assistance, please feel free to contact us. We are always here to help.

Best regards,
Community Support Team

[Mauro89 · Accepted solution]

Hi @Nadege!

This error occurs because AS400 (IBM i/DB2) doesn't support multi-value parameters with the ? placeholder syntax in the way Power BI Report Builder expects.

Solution: Use the IN clause with dynamic SQL

Instead of using ? for multi-value parameters, you need to construct the parameter list as a comma-separated string. Here's how:

1. Change your parameter to allow multiple values and set it to return a comma-separated string

2. Modify your query to use the parameter directly in the SQL:

with cedant as ( 
    select client_number, service_agreement_number, SELLING_GROUP 
    from dwe_service_agreement 
    where selling_group in (@SellingGroup)  -- Use @ParameterName
    and sag_on_live = 'Y'
), 
nb as (
    select periode as "Période", SELLING_GROUP as "Groupe cédant"
    from DWE_WRK_REFI_PREC a 
    inner join cedant on client_number = num_adh and service_agreement_number = num_cpt
    where year(periode) = @Year 
    and month(periode) in (@Months)  -- Multi-value parameter
) 
select * from nb

1. Configure the multi-value parameters (@Months, @SellingGroup) in Report Builder to use JOIN with comma separator in the parameter properties.

If this still doesn't work with AS400, you may need to use a stored procedure or build the SQL dynamically using expressions.

Best regards!

PS: If you find this post helpful consider leaving kudos or mark it as solution