AnsweredAssumed Answered

Jasper Report is not passing values from prompt to parent query

Question asked by GurinderG on Dec 18, 2017
Latest reply on Dec 22, 2017 by Paul_Coccimiglio

I am creating a Jasper report and I am prompting users for an input.  The user has to select the Organization name from the available list of organizations and then the report will display the list of requests matching with the assignee's organization.

 

Independently, both of my queries are working fine and I am able to prompt the users with a list of active organizations.  After I had selected the organizations in the prompt and runs the report, I am receiving the following error and the selected values in the prompt were not passed to the original query, instead '?' was displayed for each selected value in the prompt.  Please help me understand what I am doing wrong?

 

Error Message

Error filling report

Error Message

net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query: SELECT cr.ref_num, cr.requestor_combo_name, cr.assignee_combo_name, cr.affected_end_user_combo_name, cr.description, cr.group_name, cr.open_date, cr.close_date, cr.resolve_date, cr.priority_symbol, cr.summary, cr.status_description, cr.Assignee_department_name, cr.assignee_organization_name, cr.category_symbol, cr.caused_by_change_order_chg_ref_num, cr.caused_by_change_order_status_description, cr.caused_by_change_order_open_date, cr.caused_by_change_order_close_date, cr.caused_by_change_order_summary FROM cr WHERE cr.assignee_organization_name IN (?, ?, ?, ?, ?, ?, ?) and cr.active =0 AND cr.open_date > PdmAddDays(-60) ORDER BY cr.ref_num DESC

Error Message

java.sql.SQLException: [DataDirect][OpenAccess SDK JDBC Driver][OpenAccess SDK SQL Engine]syntax error, unexpected '?' at ( ?

 

I was able to move on after updating my where clause to as following:

where cr.assignee_organization_name IN $P!{R_Select_Organization} and I am seeing the following error:

WHERE cr.assignee_organization_name IN[Application, Substitutes] and cr.active =0 AND cr.open_date > PdmAddDays(-60) ORDER BY cr.ref_num DESC

Now the issue is that where clause cannot contain '[' or ']'  how do in convert my square brackets to round brackets '(' or ')'.  I also need to add single quotes around the organization_name as several organizations has spaces in there names.  

Please help...

 

Outcomes