BIRT report parameter not accepting mutiple values seperated by ','

Question

Front end Report Link:

PharmacyItemDispnse_ByOffice.rptdesign&SDATE="+sdate+"&EDATE="+edate+"&OID="+oid+"&MEDID="+medy;

my values passed are like this from frontend side

Front end report Link:

PharmacyItemDispnse_ByOffice.rptdesign&SDATE=2024-01-08&EDATE=2024-01-08&OID=7&MEDID=253078,253077,253076

the issue is BIRT is not acepting MEDID report parameter.MEDID is set as String in BIRT I made sure the sql query is containg 'IN' But the error i am getting is

Error:

 Cannot convert the parameter value 253076,253077,253078 at index 1 from the value type of class java.lang.String to the ODA type of 4. For input string: "253076,253077,253078" 

sql query added in Birt report Data set:

SELECT  
    ite.ITEM_PDT_ID,    
    ite.ITEM_NAME , 
    im.ITEM_CODE ,  
    ite.item_unit, 
    inv.OFFICE_ID,  
    od.OFFICE_NAME, 
    sum(ite.item_qtysold) total_dispensed 
    FROM
    ABC.invoice_item ite 
    INNER JOIN ECLINIC_KNG.invoice inv 
    ON ite.item_invoice_id = inv.invoice_id 
    LEFT JOIN ECLINIC_KNG.OFFICE_DETAILS od ON inv.OFFICE_ID = od.OFFICE_ID  
    LEFT  JOIN ECLINIC_KNG.ITEM_MASTER im ON ite.ITEM_PDT_ID =im.ITEM_ID 
    WHERE   inv.INV_STATUS = 'Closed' AND 
    inv.INV_CANCEL_STATUS = 'N' AND
    inv.office_id = ? AND 
    inv.invoice_date BETWEEN ? AND ?
    AND ite.ITEM_PDT_ID IN (?)
    GROUP BY ite.ITEM_PDT_ID,
    ite.ITEM_NAME,
    im.ITEM_CODE,
    ite.item_unit,
    inv.OFFICE_ID,
    od.OFFICE_NAME

Answer 1

AFAIK the construct IN ? is generally not supported by JDBC. Or to be more precise, it is not going to work as one might expect. A bind variable must always be a scalar value.