Jasper parameters

0

I created a new report with 3 parameters (Nome, Sexo and CPF), however when generating it I just put Nome and Sexo and my report generates it in white!
It only generates if I fill in the 3 parameters!
Can someone help me please? I need to fill only the parameters I want, since the 3 are not mandatory!

Emphasizing that the database is Sql Server, then the query syntax must be accepted in this SGDB.

 <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="Leaf_Grey" pageWidth="595" pageHeight="842" whenNoDataType="BlankPage" columnWidth="535" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="a1f690a3-5977-4467-85f8-6b0bcd5b7210">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="MPWJ_ARH"/>
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <style name="Title" fontName="Arial" fontSize="26" isBold="true"/>
    <style name="SubTitle" forecolor="#666666" fontName="Arial" fontSize="18"/>
    <style name="Column header" forecolor="#666666" fontName="Arial" fontSize="12" isBold="true"/>
    <style name="Detail" fontName="Arial" fontSize="12"/>
    <subDataset name="Empty Dataset1" uuid="b150cafa-6752-44fe-8966-6a154ef3ca7b"/>
    <parameter name="CPF" class="java.lang.String"/>
    <parameter name="Nome" class="java.lang.String"/>
    <parameter name="Sexo" class="java.lang.String"/>
    <queryString>
        <![CDATA[SELECT TOP 10
PES.PES_COD,
PES_NOME,
PFI.PFI_CPF,
PFI.PFI_SEXO
FROM GER_PESSOA PES
LEFT JOIN GER_PESSOA_FISICA PFI ON PES.PES_COD = PFI.PES_COD
WHERE   PES.PES_NOME =  $P{Nome}
AND
 PFI.PFI_SEXO =  $P{Sexo}  
 AND PFI.PFI_CPF =  $P{CPF}]]>
    </queryString>
    <field name="PES_COD" class="java.lang.Integer">
        <property name="com.jaspersoft.studio.field.label" value="PES_COD"/>
    </field>
    <field name="PES_NOME" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.label" value="PES_NOME"/>
    </field>
    <field name="PFI_CPF" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.label" value="PFI_CPF"/>
    </field>
    <field name="PFI_SEXO" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.label" value="PFI_SEXO"/>
    </field>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="81" splitType="Stretch">
            <staticText>
                <reportElement style="Title" x="0" y="13" width="263" height="33" uuid="6f9c1004-6140-4588-9c4f-d75824835765"/>
                <textElement verticalAlignment="Middle"/>
                <text><![CDATA[Leaf Gray Title]]></text>
            </staticText>
            <staticText>
                <reportElement style="SubTitle" x="117" y="46" width="157" height="22" uuid="8e16dabd-2e54-471b-9652-83e1d55379ec"/>
                <text><![CDATA[Leaf Gray SubTitle]]></text>
            </staticText>
        </band>
    </title>
    <pageHeader>
        <band splitType="Stretch"/>
    </pageHeader>
    <detail>
        <band height="33" splitType="Stretch">
            <property name="com.jaspersoft.studio.unit.height" value="px"/>
            <textField>
                <reportElement x="0" y="0" width="40" height="30" uuid="9a2e2921-c5c7-4581-b69e-aa706a70d99f"/>
                <textFieldExpression><![CDATA[$F{PES_COD}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="40" y="0" width="290" height="30" uuid="b30f07a0-2845-4024-8f1b-1fb1e05c65f2"/>
                <textFieldExpression><![CDATA[$F{PES_NOME}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="330" y="0" width="160" height="30" uuid="fb031782-9e22-4e15-bd76-0e874cafb684"/>
                <textFieldExpression><![CDATA[$F{PFI_CPF}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="493" y="0" width="60" height="30" uuid="27264cdf-6fda-4aca-a415-85a33f598ac1"/>
                <textFieldExpression><![CDATA[$F{PFI_SEXO}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <pageFooter>
        <band height="20" splitType="Stretch">
            <textField>
                <reportElement style="Column header" x="433" y="0" width="80" height="20" uuid="deb23d85-47bb-4de6-978e-113ff117188c"/>
                <textElement textAlignment="Right">
                    <font size="10" isBold="false"/>
                </textElement>
                <textFieldExpression><![CDATA["Page "+$V{PAGE_NUMBER}+" of"]]></textFieldExpression>
            </textField>
            <textField evaluationTime="Report">
                <reportElement style="Column header" x="513" y="0" width="40" height="20" uuid="c6ce76ba-6e11-41e2-9415-72dc2eff001b"/>
                <textElement>
                    <font size="10" isBold="false"/>
                </textElement>
                <textFieldExpression><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression>
            </textField>
            <textField pattern="EEEEE dd MMMMM yyyy">
                <reportElement style="Column header" x="0" y="0" width="197" height="20" uuid="5a79d2a0-bcde-4ad5-adf0-c4eb2066f6dd"/>
                <textElement>
                    <font size="10" isBold="false"/>
                </textElement>
                <textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>
            </textField>
        </band>
    </pageFooter>
    <summary>
        <band splitType="Stretch"/>
    </summary>
</jasperReport>
Marcelo.Costta's picture
Joined: Oct 20 2015 - 5:05am
Last seen: 1 month 1 week ago

3 Answers:

1

Change your query from :

WHERE   PES.PES_NOME =  $P{Nome}

to:

WHERE   $X{IN,PES.PES_NOME,Nome}

do the same for other parameters where input is not required

Or just simply change AND to OR

WHERE   
(PES.PES_NOME =  $P{Nome}
or
 PFI.PFI_SEXO =  $P{Sexo}  
or  PFI.PFI_CPF =  $P{CPF})
bigalex's picture
58
Joined: Apr 12 2019 - 9:15am
Last seen: 3 days 19 hours ago
0

WHERE   $X{IN,PES.PES_NOME,Nome}
The above command returned the following error:
net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRRuntimeException: Invalid type java.lang.String for parameter Name used in an IN clause; the value must be an array or a collection ...

Emphasizing that the database is Sql Server, then the query syntax must be accepted in this SGDB.

Working with OR would work, but I put a simple example. I have reports in another generator with more than 20 parameters, to modify all the reports and using OR, it gets complicated, in addition to worsening the performance, since the number of parameters at least will double!

Marcelo.Costta's picture
Joined: Oct 20 2015 - 5:05am
Last seen: 1 month 1 week ago
0

An alternative to bigalex solution would be to use COALESCE() in the where clause assuming the parameters are retuning NULL when not populated.

 

SELECT TOP 10
       PES.PES_COD
      ,PES_NOME
      ,PFI.PFI_CPF
      ,PFI.PFI_SEXO
FROM GER_PESSOA PES
LEFT JOIN GER_PESSOA_FISICA PFI ON PES.PES_COD = PFI.PES_COD
WHERE PES.PES_NOME = COALESCE($P{Nome},PES.PES_NOME)
  AND PFI.PFI_SEXO = COALESCE($P{Sexo},PFI.PFI_SEXO)
  AND PFI.PFI_CPF  = COALESCE($P{CPF},PFI.PFI_CPF)

jgust's picture
247
Joined: Jun 10 2010 - 6:39am
Last seen: 2 days 13 hours ago
Feedback
randomness