pass in multiple comma separated values into the parameter

0

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 labelsSingle Mailing Labels:

Single Service Sheet

 

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}+"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"+$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}+"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"+$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. 
 
 
rahman.reazur's picture
Joined: Jul 6 2016 - 12:03pm
Last seen: 2 years 9 months ago

1 Answer:

0

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

Where...

params: {
            "ArrayName": ["Value1", "Value2"]
        },
dpetzold's picture
Joined: May 12 2014 - 10:47am
Last seen: 1 year 7 months ago
Feedback