Optional Where Clause Jasper Reports

optional where clause jasper reports

Ok, let see the sample.

For example we have a query:

SELECT id, city, street FROM address WHERE city=$P{inputParamCity} ORDER BY city

But our inputParamCity can be undefined. In this case we got an error:

Error filling print... Error preparing statement for executing the report query : 
SELECT id, city, street FROM address WHERE city=? ORDER BY city

How we can fix it?

It is very simple - we can add another parameter with default expression like this:

<parameter name="whereClause" class="java.lang.String" isForPrompting="false">
    <defaultValueExpression><![CDATA[()$P{inputParamCity} == null || $P{inputParamCity}.isEmpty()) ? "1=1" : "city='" + $P{inputParamCity} + "'"]]></defaultValueExpression>
</parameter>

-if the inputParamCity parameter is undefined the "fake" clause "1=1" will be used, in other case the filter by city field will be applied.

And of course we have to modify the query expression - to use this new parameter. Our query expression in this case will be:

<queryString>
    <![CDATA[SELECT id, city, street FROM address WHERE $P!{whereClause} ORDER BY city]]>
</queryString>

The sample

The jrxml file:

<?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="optional_where_clause" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="d3648644-0087-4dfc-ac6d-87e82d9bb33e">
    <parameter name="inputParamCity" class="java.lang.String"/>
    <parameter name="whereClause" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[($P{inputParamCity} == null || $P{inputParamCity}.isEmpty()) ? "1=1" : "city='" + $P{inputParamCity} + "'"]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[SELECT id, city, street FROM address WHERE $P!{whereClause} ORDER BY city]]>
    </queryString>
    <field name="ID" class="java.lang.Integer"/>
    <field name="CITY" class="java.lang.String"/>
    <field name="STREET" class="java.lang.String"/>
    <detail>
        <band height="20" splitType="Stretch">
            <textField>
                <reportElement uuid="c2a80b99-e087-4839-8e77-841edd899255" x="0" y="0" width="100" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{ID}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement uuid="0aafcfd6-60f7-4272-8e7d-0aa77507204b" x="100" y="0" width="100" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{CITY}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement uuid="c8726513-8250-43ec-bafc-003e81094c27" x="200" y="0" width="100" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{STREET}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

In case using undefined inputParamCity parameter (the value is not set) the result will be:

In this case the query was used by engine is:

SELECT id, city, street FROM address WHERE 1=1 ORDER BY city

If we set, for example, the value Chicago for inputParamCity parameter the result will be:

In this case the query was used by engine is:

SELECT id, city, street FROM address WHERE city='Chicago' ORDER BY city

---

Notes:

• You can find more information in this post: JasperReports: Passing parameters to query

• You can modify the whereClause parameter's expression and the query expression. For example, you can move WHERE keyword from query expression to parameter's expression to prevent using the fake clause "1=1"

Conditional Where clauses in JasperReports

Before you have used the $P!{} expression the JDBC-Driver does all formatting for you.

But if you use the $P!{} expression you have to format yourself.

Something like this should work:

(
$P{some.date} == null 
? 
"" 
: 
"AND some_date >'" + (new SimpleDateFormat("dd.MM.yyyy HH:mm:ss.SSS")).format($P{some.date}) + "'"
)

Depending on your data type you have to customize dd.MM.yyyy HH:mm:ss.SSS.

If you don't want to use the $P!{} expression you can avoid it with the solution below.

I personally don't like this way. It also may cause a bad execution plan.

If don't want to use $P!{} because you worry about sql injection. It's needless as long your parameter $P{some.date} contains a safe data type like java.lang.Date.

---

Create a parameter. Let's call it ${is_null_pram} and add a default expression with param class Integer:

($P{some.date} == null ? 1 : 0)

Now you can query:

SELECT 
    * 
FROM foo 
WHERE
    bar = $P{bar}
    AND
        (
            some_date > $P{some.date}
            OR 1 = $P{is_null_pram}
        )

How to pass optional parameter to the report?

I usually follow this pattern: Define $P{MyParam} as you did. Then add $P{MyParam_SQL} with a default value like this:

$P{MyParam} == null ? "1=1" : "columnA = '" + $P{MyParam} + "'"

And the SQL in the report is like this:

SELECT columnA, ColumnB 
FROM table 
WHERE 
  some_filters
  AND $P!{MyParam_SQL}
  AND some_other_stuff

It's not fundamentally different from the other two suggested answers. But I find it easy to understand and maintain like this.

JasperReports: How to add a WHERE statement in SQL query depending on a Boolean parameter?

You can add additional parameter for setting additional clause value. After that you can use $P!{} syntax in query.

The sample:

<parameter name="param" class="java.lang.Boolean"/>
<parameter name="whereCond" class="java.lang.String" isForPrompting="false">
    <defaultValueExpression><![CDATA[$P{param} ? " AND isactive='Y'" : ""]]></defaultValueExpression>
</parameter>
<queryString>
    <![CDATA[SELECT * FROM shops WHERE region='Canada' $P!{whereCond} ORDER BY name]]>
</queryString>

---

Related Topics