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