Tip : Multi Select Query Integer Parameter in Japser iReport/Studio

Tip :  Multi Select Query Integer Parameter 
Update 1 : 16th March 2014

We can use java.util.Collection for Integers in multiselect parameter for this we need to add below code explained in thread from community

<parameter name="paramAbc_Id" class="java.util.Collection" nestedType="java.lang.Integer">             <defaultValueExpression><![CDATA[ ]]></defaultValueExpression> </parameter>  

reference Jasper report schema http://jasperreports.sourceforge.net/schema.reference.html#parameter

http://community.jaspersoft.com/questions/543981/send-integer-list-values-parameter

Generally we take string values for multi select input controls in jasper iReport/studio and we take java.util.Collection for the input parameter.

Jasper iReport doesn't allow you take Integer values for multi select using java.util.Collection for parameter.

i.e, we should give java.lang.Integer for parameter though we are taking it for multi select input control.

( A multi select is a collection of values , could be integers or could be strings).

From 4.7 and greater versions of Jasper iReport multi select input control allows java.lang.Integer in place of java.lang.Collection for parameter.


Example :  Correct Way :

Main Query : 
SELECT * from customer where $X{IN,customer_id,param_customerID}

Parameter : 
Name : param_customerID
Class : java.lang.Integer

Parameter Query in the repository : 
Type : Multi Select Query
Query :
SELECT DISTINCT
        customer_id FROM customer
ORDER BY customer_id


Example : Wrong Way :
Main Query : 
SELECT * from customer where $X{IN,CAST(customer_id AS char),param_customerID}

Parameter : 
Name : param_customerID
Class : java.lang.Collection

Parameter Query in the repository : 
Type : Multi Select Query
Query :
SELECT DISTINCT
        CAST(customer_id AS char)  FROM customer
ORDER BY customer_id

:-)

Example JRXML
1) Copy below code in a report JRXML completely.
2) Create parameter in repository and test it.


<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="report1" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="6a7ac7e7-2d64-447b-9aba-54346ff9b8b4">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <parameter name="param_customerID" class="java.lang.Integer"/>
    <queryString>
        <![CDATA[SELECT * from customer where $X{IN,customer_id,param_customerID}]]>
    </queryString>
    <field name="customer_id" class="java.lang.Integer"/>
    <field name="account_num" class="java.lang.Long"/>
    <field name="lname" class="java.lang.String"/>
    <field name="fname" class="java.lang.String"/>
    <field name="mi" class="java.lang.String"/>
    <field name="address1" class="java.lang.String"/>
    <field name="address2" class="java.lang.String"/>
    <field name="address3" class="java.lang.String"/>
    <field name="address4" class="java.lang.String"/>
    <field name="city" class="java.lang.String"/>
    <field name="state_province" class="java.lang.String"/>
    <field name="postal_code" class="java.lang.String"/>
    <field name="country" class="java.lang.String"/>
    <field name="customer_region_id" class="java.lang.Integer"/>
    <field name="phone1" class="java.lang.String"/>
    <field name="phone2" class="java.lang.String"/>
    <field name="birthdate" class="java.sql.Date"/>
    <field name="marital_status" class="java.lang.String"/>
    <field name="yearly_income" class="java.lang.String"/>
    <field name="gender" class="java.lang.String"/>
    <field name="total_children" class="java.lang.Integer"/>
    <field name="num_children_at_home" class="java.lang.Integer"/>
    <field name="education" class="java.lang.String"/>
    <field name="date_accnt_opened" class="java.sql.Date"/>
    <field name="member_card" class="java.lang.String"/>
    <field name="occupation" class="java.lang.String"/>
    <field name="houseowner" class="java.lang.String"/>
    <field name="num_cars_owned" class="java.lang.Integer"/>
    <field name="fullname" class="java.lang.String"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch"/>
    </title>
    <columnHeader>
        <band height="20" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="138" height="20" uuid="10e1bad7-7151-4d49-8001-141486634050"/>
                <text><![CDATA[customer_id]]></text>
            </staticText>
            <staticText>
                <reportElement x="138" y="0" width="138" height="20" uuid="8173c779-65a8-402d-86a6-6ae03478765d"/>
                <text><![CDATA[account_num]]></text>
            </staticText>
            <staticText>
                <reportElement x="276" y="0" width="138" height="20" uuid="e4d4715e-30c1-430a-a9c3-3968ffc7a9de"/>
                <text><![CDATA[lname]]></text>
            </staticText>
            <staticText>
                <reportElement x="414" y="0" width="138" height="20" uuid="e7e42982-dc47-4888-b4ca-7a9418281f1f"/>
                <text><![CDATA[fname]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="21" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="138" height="20" uuid="764a2921-621e-4940-8fda-e01805793bd4"/>
                <textFieldExpression><![CDATA[$F{customer_id}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="138" y="0" width="138" height="20" uuid="a7928dcb-a9b4-4291-b038-8c4bb422a446"/>
                <textFieldExpression><![CDATA[$F{account_num}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="276" y="0" width="138" height="20" uuid="308acd96-bbe5-4052-94d0-286b3864ee96"/>
                <textFieldExpression><![CDATA[$F{lname}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="414" y="0" width="138" height="20" uuid="84f8c767-812a-4ef1-8e72-65cfed6f2cac"/>
                <textFieldExpression><![CDATA[$F{fname}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>



Sample output image : 

References : 

https://community.jaspersoft.com/questions/800205/multi-select-47-seems-broken

https://community.jaspersoft.com/questions/823425/passing-values-multi-select-query-input-control-jasperserver