Jasper parameters

[Marcelo.Costta]

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 10PES.PES_COD,PES_NOME,PFI.PFI_CPF,PFI.PFI_SEXOFROM GER_PESSOA PESLEFT JOIN GER_PESSOA_FISICA PFI ON PES.PES_COD = PFI.PES_CODWHERE   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>

[bigalex]

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

[Marcelo.Costta]

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!

[jgust]

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_SEXOFROM GER_PESSOA PESLEFT JOIN GER_PESSOA_FISICA PFI ON PES.PES_COD = PFI.PES_CODWHERE 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)[/code]