pass in multiple comma separated values into the parameter

[rahman.reazur]

Hi I have a drop down menu in my site. Which has three option: Single Case, Consolidated Case and Multi-Captioned. When ever the user picks single Case it has three parameter one for CASE_ID, DOC_ID one for PREVIEW. Once the person enters all the information it should print out something like this for the service sheet and mailing labels:

 

For Consolidated Case i want the same report to take multiple CASE_ID comma separated. The case number should print out the following: 2016-ACM-00001, 2016-ACM-00002 and so on... it should also print out all of the case name associated with all of the CASE_ID also print out the details on the following cases. It should also do the same for Mailing report.

 

Here is my xml file for Single Service Sheet:

 

<?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="Service Sheet Preview" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">

<property name="ireport.zoom" value="1.000000000000001"/>

<property name="ireport.x" value="0"/>

<property name="ireport.y" value="0"/>

<style name="table">

<box>

<pen lineWidth="1.0" lineColor="#000000"/>

</box>

</style>

<style name="table_TH" mode="Opaque" backcolor="#F0F8FF">

<box>

<pen lineWidth="0.5" lineColor="#000000"/>

</box>

</style>

<style name="table_CH" mode="Opaque" backcolor="#BFE1FF">

<box>

<pen lineWidth="0.5" lineColor="#000000"/>

</box>

</style>

<style name="table_TD" mode="Opaque" backcolor="#FFFFFF">

<box>

<pen lineWidth="0.5" lineColor="#000000"/>

</box>

</style>

<subDataset name="Recipients">

<parameter name="CASE_ID" class="java.lang.String"/>

<queryString>

<![CDATA[select c.c_first_name+' '+c.c_last_name +'<br>'+

  case when c.c_address_1 is null OR c.c_address_1 = '' then '' else c.c_address_1+'<br>' end+

  case when c.c_address_2 is null OR c.c_address_2 = '' then '' else c.c_address_2+'<br>' end+

  case when c.c_address_3 is null OR c.c_address_3 = '' then '' else c.c_address_3+'<br>' end+

  case when c.c_address_4 is null OR c.c_address_4 = '' then '' else c.c_address_4+'<br>' end+

  case when c.c_address_5 is null OR c.c_address_5 = '' then '' else c.c_address_5+'<br>' end+

  case when ( (c.c_city is not null and c.c_city != '') OR (st.c_abbreviation is not null) OR (c_postal_code is not null) )

 then

case when c.c_city is null  OR c.c_city = '' then '' else c.c_city+(case when st.c_abbreviation is not null then ', ' else''end) end +

case when st.c_abbreviation is null then '' else st.c_abbreviation+' ' end +

case when c_postal_code is null then '' else convert(varchar, c.c_postal_code) end +'<br>'

 else '' end as RECIPIENTS,

  '{Hard Copy - Regular Mail}' as RECIP_TYPE

from

t_contact c

join t_case_input cs on (c.id = convert(numeric, nullif(cs.c_claimant_id,'') )

OR c.id = convert(numeric, nullif(cs.c_claimant_representative_id ,''))

OR c.id = convert(numeric, nullif(cs.c_employer_id ,''))

OR c.id = convert(numeric, nullif(cs.c_employer_attorney_id,'')) )

left join t_state_type st on c.c_us_state = st.id

left join t_country_type ctry on c.c_country = ctry.id

where cs.id = $P{CASE_ID}]]>

</queryString>

<field name="RECIPIENTS" class="java.lang.String"/>

<field name="RECIP_TYPE" class="java.lang.String"/>

</subDataset>

<parameter name="CASE_ID" class="java.lang.String">

<defaultValueExpression><![CDATA[]]></defaultValueExpression>

</parameter>

<parameter name="DOC_ID" class="java.lang.String">

<defaultValueExpression><![CDATA[]]></defaultValueExpression>

</parameter>

<parameter name="PREVIEW" class="java.lang.String">

<defaultValueExpression><![CDATA[]]></defaultValueExpression>

</parameter>

<queryString>

<![CDATA[select cs.c_claimant+' v '+cs.c_employer as CASE_NAME,

     cs.c_case_number as CASE_NUMBER,

     doc.c_title as TITLE,

     case when datepart(dd, getdate()) in(01, 21, 31) then convert(varchar, datepart(dd, getdate()))+'st'

          when datepart(dd, getdate()) in(02, 22) then convert(varchar, datepart(dd, getdate()))+'nd'

          when datepart(dd, getdate()) in(03, 23) then convert(varchar, datepart(dd, getdate()))+'rd'

          else convert(varchar, datepart(dd, getdate()))+'th'

     end as DAY,

     datename(mm, getdate()) as MONTH,

     datepart(yyyy, getdate()) as YEAR,

 

ct.c_first_name+' '+ct.c_last_name +'<br>'+

  case when ct.c_address_1 is null OR ct.c_address_1 = '' then '' else ct.c_address_1+'<br>' end+

  case when ct.c_address_2 is null OR ct.c_address_2 = '' then '' else ct.c_address_2+'<br>' end+

  case when ct.c_address_3 is null OR ct.c_address_3 = '' then '' else ct.c_address_3+'<br>' end+

  case when ct.c_address_4 is null OR ct.c_address_4 = '' then '' else ct.c_address_4+'<br>' end+

  case when ct.c_address_5 is null OR ct.c_address_5 = '' then '' else ct.c_address_5+'<br>' end+

  case when ( (ct.c_city is not null and ct.c_city != '') OR (st.c_abbreviation is not null) OR (ct.c_postal_code is not null) )

 then

case when ct.c_city is null  OR ct.c_city = '' then '' else ct.c_city+(case when st.c_abbreviation is not null then ', ' else''end) end +

case when st.c_abbreviation is null then '' else st.c_abbreviation+' ' end +

case when ct.c_postal_code is null then '' else convert(varchar, ct.c_postal_code) end +'<br>'

 else '' end as RECIPIENTS,

  '{Hard Copy - Regular Mail}' as RECIP_TYPE

from t_case_input cs

     left join t_case_documents d on d.id_base = cs.id

     left join t_document doc on doc.id = d.document_bto_id

    join t_contact ct on (ct.id = convert(numeric, nullif(cs.c_claimant_id,'') )

OR ct.id = convert(numeric, nullif(cs.c_claimant_representative_id ,''))

OR ct.id = convert(numeric, nullif(cs.c_employer_id ,''))

OR ct.id = convert(numeric, nullif(cs.c_employer_attorney_id,'')) )

left join t_state_type st on ct.c_us_state = st.id

left join t_country_type ctry on ct.c_country = ctry.id

where cs.id = isnull ($P{CASE_ID}, '')

     and doc.id = isnull($P{DOC_ID},'')]]>

</queryString>

<field name="CASE_NAME" class="java.lang.String"/>

<field name="CASE_NUMBER" class="java.lang.String"/>

<field name="TITLE" class="java.lang.String"/>

<field name="DAY" class="java.lang.String"/>

<field name="MONTH" class="java.lang.String"/>

<field name="YEAR" class="java.lang.Integer"/>

<field name="RECIPIENTS" class="java.lang.String"/>

<field name="RECIP_TYPE" class="java.lang.String"/>

<variable name="RUNDATE" class="java.lang.String">

<variableExpression><![CDATA[(new SimpleDateFormat("yyyy/MM/dd")).format(new Date())]]></variableExpression>

</variable>

<group name="ReportGroup1">

<groupExpression><![CDATA[$F{CASE_NAME}]]></groupExpression>

<groupHeader>

<band height="122">

<staticText>

<reportElement x="0" y="20" width="100" height="20"/>

<textElement>

<font fontName="Times New Roman"/>

</textElement>

<text><![CDATA[Case Number:]]></text>

</staticText>

<textField isStretchWithOverflow="true" isBlankWhenNull="true">

<reportElement x="100" y="20" width="472" height="20"/>

<textElement>

<font fontName="Times New Roman" isBold="true" pdfFontName="Times-Bold"/>

</textElement>

<textFieldExpression class="java.lang.String"><![CDATA[$F{CASE_NUMBER}]]></textFieldExpression>

</textField>

<staticText>

<reportElement x="0" y="40" width="100" height="20"/>

<textElement>

<font fontName="Times New Roman"/>

</textElement>

<text><![CDATA[Document Title:]]></text>

</staticText>

<textField isStretchWithOverflow="true" isBlankWhenNull="true">

<reportElement x="100" y="40" width="472" height="20"/>

<textElement>

<font fontName="Times New Roman" isBold="true" pdfFontName="Times-Bold" isPdfEmbedded="true"/>

</textElement>

<textFieldExpression class="java.lang.String"><![CDATA[$F{TITLE}]]></textFieldExpression>

</textField>

<staticText>

<reportElement x="0" y="0" width="100" height="20"/>

<textElement>

<font fontName="Times New Roman"/>

</textElement>

<text><![CDATA[Case Name:]]></text>

</staticText>

<textField>

<reportElement x="100" y="0" width="472" height="20"/>

<textElement>

<font fontName="Times New Roman" isBold="true" pdfFontName="Times-Bold"/>

</textElement>

<textFieldExpression class="java.lang.String"><![CDATA[$F{CASE_NAME}]]></textFieldExpression>

</textField>

<textField>

<reportElement x="0" y="60" width="572" height="28"/>

<textElement>

<font fontName="Times New Roman"/>

</textElement>

<textFieldExpression class="java.lang.String"><![CDATA["I hereby certify that a copy of the above-referenced document was sent to the following this "+$F{DAY}+" day of "+$F{MONTH}+", "+$F{YEAR}+":"]]></textFieldExpression>

</textField>

<textField>

<reportElement x="0" y="88" width="572" height="34"/>

<textElement>

<font fontName="Times New Roman" size="14" isBold="true" isItalic="true" isUnderline="false"/>

</textElement>

<textFieldExpression class="java.lang.String"><![CDATA["<< Signature Block goes here. >>"]]></textFieldExpression>

</textField>

</band>

</groupHeader>

</group>

<background>

<band height="752" splitType="Stretch">

<printWhenExpression><![CDATA[$P{PREVIEW}.equals( "Yes" )]]></printWhenExpression>

<staticText>

<reportElement x="0" y="92" width="572" height="64" forecolor="#CCCCCC" backcolor="#FFFFFF"/>

<textElement textAlignment="Center">

<font size="48" isBold="true"/>

</textElement>

<text><![CDATA[PREVIEW ]]></text>

</staticText>

<staticText>

<reportElement x="0" y="220" width="572" height="64" forecolor="#CCCCCC"/>

<textElement textAlignment="Center">

<font size="48" isBold="true"/>

</textElement>

<text><![CDATA[PREVIEW ]]></text>

</staticText>

<staticText>

<reportElement x="0" y="348" width="572" height="64" forecolor="#CCCCCC"/>

<textElement textAlignment="Center">

<font size="48" isBold="true"/>

</textElement>

<text><![CDATA[PREVIEW ]]></text>

</staticText>

<staticText>

<reportElement x="0" y="476" width="572" height="64" forecolor="#CCCCCC"/>

<textElement textAlignment="Center">

<font size="48" isBold="true"/>

</textElement>

<text><![CDATA[PREVIEW ]]></text>

</staticText>

<staticText>

<reportElement x="0" y="604" width="572" height="64" forecolor="#CCCCCC"/>

<textElement textAlignment="Center">

<font size="48" isBold="true"/>

</textElement>

<text><![CDATA[PREVIEW ]]></text>

</staticText>

</band>

</background>

<title>

<band height="34" splitType="Stretch">

<staticText>

<reportElement x="0" y="0" width="572" height="27"/>

<textElement textAlignment="Center">

<font fontName="Times New Roman" size="20" isBold="true" isUnderline="false"/>

</textElement>

<text><![CDATA[sERVICE SHEET]]></text>

</staticText>

</band>

</title>

<detail>

<band height="42">

<textField isStretchWithOverflow="true">

<reportElement positionType="Float" stretchType="RelativeToTallestObject" x="0" y="0" width="284" height="20" isPrintWhenDetailOverflows="true"/>

<textElement markup="html">

<font fontName="Times New Roman" pdfFontName="Times-Roman" isPdfEmbedded="true"/>

<paragraph spacingAfter="20"/>

</textElement>

<textFieldExpression class="java.lang.String"><![CDATA[$F{RECIPIENTS}+"       "+$F{RECIP_TYPE}]]></textFieldExpression>

</textField>

</band>

</detail>

<summary>

<band height="50">

<printWhenExpression><![CDATA[new Boolean($V{REPORT_COUNT}.intValue()==0)]]></printWhenExpression>

<staticText>

<reportElement x="0" y="30" width="572" height="20"/>

<textElement textAlignment="Center">

<font size="12"/>

</textElement>

<text><![CDATA[Your selection has returned no results]]></text>

</staticText>

</band>

</summary>

</jasperReport>

 

 

and here is my xml  for single mailing report: 

 

<?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="Mailing Labels" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">

<property name="ireport.zoom" value="1.0"/>

<property name="ireport.x" value="0"/>

<property name="ireport.y" value="0"/>

<style name="table">

<box>

<pen lineWidth="1.0" lineColor="#000000"/>

</box>

</style>

<style name="table_TH" mode="Opaque" backcolor="#F0F8FF">

<box>

<pen lineWidth="0.5" lineColor="#000000"/>

</box>

</style>

<style name="table_CH" mode="Opaque" backcolor="#BFE1FF">

<box>

<pen lineWidth="0.5" lineColor="#000000"/>

</box>

</style>

<style name="table_TD" mode="Opaque" backcolor="#FFFFFF">

<box>

<pen lineWidth="0.5" lineColor="#000000"/>

</box>

</style>

<subDataset name="Recipients">

<parameter name="CASE_ID" class="java.lang.String"/>

<queryString>

<![CDATA[select c.c_first_name+' '+c.c_last_name +'<br>'+

  case when c.c_address_1 is null OR c.c_address_1 = '' then '' else c.c_address_1+'<br>' end+

  case when c.c_address_2 is null OR c.c_address_2 = '' then '' else c.c_address_2+'<br>' end+

  case when c.c_address_3 is null OR c.c_address_3 = '' then '' else c.c_address_3+'<br>' end+

  case when c.c_address_4 is null OR c.c_address_4 = '' then '' else c.c_address_4+'<br>' end+

  case when c.c_address_5 is null OR c.c_address_5 = '' then '' else c.c_address_5+'<br>' end+

  case when ( (c.c_city is not null and c.c_city != '') OR (st.c_abbreviation is not null) OR (c_postal_code is not null) )

 then

case when c.c_city is null  OR c.c_city = '' then '' else c.c_city+(case when st.c_abbreviation is not null then ', ' else''end) end +

case when st.c_abbreviation is null then '' else st.c_abbreviation+' ' end +

case when c_postal_code is null then '' else convert(varchar, c.c_postal_code) end +'<br>'

 else '' end as RECIPIENTS,

  '{Hard Copy - Regular Mail}' as RECIP_TYPE

from

t_contact c

join t_case_input cs on (c.id = convert(numeric, nullif(cs.c_claimant_id,'') )

OR c.id = convert(numeric, nullif(cs.c_claimant_representative_id ,''))

OR c.id = convert(numeric, nullif(cs.c_employer_id ,''))

OR c.id = convert(numeric, nullif(cs.c_employer_attorney_id,'')) )

left join t_state_type st on c.c_us_state = st.id

left join t_country_type ctry on c.c_country = ctry.id

where cs.id = $P{CASE_ID}]]>

</queryString>

<field name="RECIPIENTS" class="java.lang.String"/>

<field name="RECIP_TYPE" class="java.lang.String"/>

</subDataset>

<parameter name="CASE_ID" class="java.lang.String"/>

<parameter name="DOC_ID" class="java.lang.String">

<defaultValueExpression><![CDATA[]]></defaultValueExpression>

</parameter>

<queryString>

<![CDATA[select ct.c_first_name+' '+ct.c_last_name +'<br>'+

  case when ct.c_address_1 is null OR ct.c_address_1 = '' then '' else ct.c_address_1+'<br>' end+

  case when ct.c_address_2 is null OR ct.c_address_2 = '' then '' else ct.c_address_2+'<br>' end+

  case when ct.c_address_3 is null OR ct.c_address_3 = '' then '' else ct.c_address_3+'<br>' end+

  case when ct.c_address_4 is null OR ct.c_address_4 = '' then '' else ct.c_address_4+'<br>' end+

  case when ct.c_address_5 is null OR ct.c_address_5 = '' then '' else ct.c_address_5+'<br>' end+

  case when ( (ct.c_city is not null and ct.c_city != '') OR (st.c_abbreviation is not null) OR (ct.c_postal_code is not null) )

 then

case when ct.c_city is null  OR ct.c_city = '' then '' else ct.c_city+(case when st.c_abbreviation is not null then ', ' else''end) end +

case when st.c_abbreviation is null then '' else st.c_abbreviation+' ' end +

case when ct.c_postal_code is null then '' else convert(varchar, ct.c_postal_code) end +'<br>'

 else '' end as RECIPIENTS,

  '{Hard Copy - Regular Mail}' as RECIP_TYPE

from t_case_input cs

     left join t_case_documents d on d.id_base = cs.id

     left join t_document doc on doc.id = d.document_bto_id

    join t_contact ct on (ct.id = convert(numeric, nullif(cs.c_claimant_id,'') )

OR ct.id = convert(numeric, nullif(cs.c_claimant_representative_id ,''))

OR ct.id = convert(numeric, nullif(cs.c_employer_id ,''))

OR ct.id = convert(numeric, nullif(cs.c_employer_attorney_id,'')) )

left join t_state_type st on ct.c_us_state = st.id

left join t_country_type ctry on ct.c_country = ctry.id

where cs.id = isnull($P{CASE_ID},'')

     and doc.id = isnull($P{DOC_ID},'')]]>

</queryString>

<field name="RECIPIENTS" class="java.lang.String"/>

<field name="RECIP_TYPE" class="java.lang.String"/>

<variable name="RUNDATE" class="java.lang.String">

<variableExpression><![CDATA[(new SimpleDateFormat("yyyy/MM/dd")).format(new Date())]]></variableExpression>

</variable>

<background>

<band height="752" splitType="Stretch">

<staticText>

<reportElement x="0" y="92" width="572" height="64" forecolor="#CCCCCC" backcolor="#FFFFFF"/>

<textElement textAlignment="Center">

<font size="48" isBold="true"/>

</textElement>

</staticText>

<staticText>

<reportElement x="0" y="220" width="572" height="64" forecolor="#CCCCCC"/>

<textElement textAlignment="Center">

<font size="48" isBold="true"/>

</textElement>

</staticText>

<staticText>

<reportElement x="0" y="348" width="572" height="64" forecolor="#CCCCCC"/>

<textElement textAlignment="Center">

<font size="48" isBold="true"/>

</textElement>

</staticText>

<staticText>

<reportElement x="0" y="476" width="572" height="64" forecolor="#CCCCCC"/>

<textElement textAlignment="Center">

<font size="48" isBold="true"/>

</textElement>

</staticText>

<staticText>

<reportElement x="0" y="604" width="572" height="64" forecolor="#CCCCCC"/>

<textElement textAlignment="Center">

<font size="48" isBold="true"/>

</textElement>

</staticText>

</band>

</background>

<title>

<band height="34" splitType="Stretch">

<staticText>

<reportElement x="0" y="0" width="572" height="27"/>

<textElement textAlignment="Center">

<font fontName="Times New Roman" size="20" isBold="true" isUnderline="false"/>

</textElement>

<text><![CDATA[Mailing Labels]]></text>

</staticText>

</band>

</title>

<detail>

<band height="42">

<textField isStretchWithOverflow="true">

<reportElement positionType="Float" stretchType="RelativeToTallestObject" x="0" y="0" width="284" height="20" isPrintWhenDetailOverflows="true"/>

<textElement markup="html">

<font fontName="Times New Roman" pdfFontName="Times-Roman" isPdfEmbedded="true"/>

<paragraph spacingAfter="20"/>

</textElement>

<textFieldExpression class="java.lang.String"><![CDATA[$F{RECIPIENTS}+"       "+$F{RECIP_TYPE}]]></textFieldExpression>

</textField>

</band>

</detail>

<summary>

<band height="50">

<printWhenExpression><![CDATA[new Boolean($V{REPORT_COUNT}.intValue()==0)]]></printWhenExpression>

<staticText>

<reportElement x="0" y="30" width="572" height="20"/>

<textElement textAlignment="Center">

<font size="12"/>

</textElement>

<text><![CDATA[Your selection has returned no results]]></text>

</staticText>

</band>

</summary>

</jasperReport>

 

 

I tried doing $X{} and $X(IN) that didn't work for me. 

 

 

[dpetzold]

Are you simply trying to pass in comma seperated values similar to this fiddle?

Where...

params: {

            "ArrayName": ["Value1", "Value2"]

        },