Jasper Report is not passing values from prompt to parent query

[Gurinder Giran]

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...

 

[JAN POKORNY]

Hi GurinderG, 

Try to use syntax parameter "X{IN, ca_organization.name, name}".

[Gurinder Giran]

Thanks for the response Jan,  this did not work, I had received error message 'Syntax error, unexpected NAME at assignee_organization_name X....' when I had changed my where clause as following:

      where cr.assignee_organization_name $X{IN, ca_organization.name, name}

I had also tried the following where clauses as well:

        where cr.assignee_organization_name X{IN, ca_organization.name}

Another test:

       where cr.assignee_organization_name $X{IN, ca_organization.name}

 

neither or theses worked.

[SteveTroy]

You have to manipulate the string to get it in the right format.

 

I am using p_group here but replace that with p_organization

 

Main Report

 

parameter name="p_group" class="java.util.Collection" nestedType="java.lang.String">
<parameterDescription><![CDATA[]]></parameterDescription>
<defaultValueExpression><![CDATA[new ArrayList(Arrays.asList(new String[] {"*"}))]]></defaultValueExpression>

 

<variable name="v_group" class="java.lang.String" resetType="None">
<variableExpression><![CDATA["('"+org.apache.commons.lang.StringUtils.join($P{p_group}.toArray(),"','") + "')"]]></variableExpression>
</variable>

 

pass $V{v_group}  as p_group to the subreport as follows:

 

in subreport p_group is defined as 

 

<parameter name="p_group" class="java.lang.String" nestedType="java.lang.String">
<parameterDescription><![CDATA[]]></parameterDescription>
</parameter>

 

and the query is 

 

FROM cr
WHERE cr.active=1 AND cr.group_name IS NOT NULL AND cr.type = 'I'
AND (cr.group_name in $P!{p_group} or '*' in $P!{p_group})
ORDER BY cr.priority_symbol ASC, cr.open_date ASC

 

Look at the OOTB active incidents aging by group report as an example.

 

If you still can't get it to work send your jrxml to me to take a look at.

 

Regards!

[Paul Coccimiglio]

Gurinder........

 

Did Steve Troy's post help you?

 

If so, please mark his response as correct!