Clarity Service Management

Expand all | Collapse all

Jasper Report is not passing values from prompt to parent query

Jump to Best Answer
  • 1.  Jasper Report is not passing values from prompt to parent query

    Posted 12-18-2017 05:51 PM
      |   view attached

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

     



  • 2.  Re: Jasper Report is not passing values from prompt to parent query

    Posted 12-19-2017 05:35 AM

    Hi GurinderG

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



  • 3.  Re: Jasper Report is not passing values from prompt to parent query

    Posted 12-19-2017 10:32 AM

    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.



  • 4.  Re: Jasper Report is not passing values from prompt to parent query
    Best Answer

    Posted 12-19-2017 01:46 PM

    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!



  • 5.  Re: Jasper Report is not passing values from prompt to parent query

    Posted 12-22-2017 01:16 PM

    Gurinder........

     

    Did Steve Troy's post help you?

     

    If so, please mark his response as correct!