optional where clause jasper reports

Question

I am trying this query but without success.

SELECT name, phone_office, billing_address_city, billing_address_street, billing_address_country 
FROM accounts
WHERE ($P!{EmployeeID} is null or assigned_user_id = $P!{EmployeeID})
ORDER BY billing_address_country, billing_address_city

This url will filter by EmployeeID and works fine:

.../flow.html?_flowId=viewReportFlow&reportUnit=/reports/samples/EmployeeAccounts&EmployeeID=sarah_id

But when i remove the EmployeeID parameter i want to remove the filter where. So all results should be shown.

.../flow.html?_flowId=viewReportFlow&reportUnit=/reports/samples/EmployeeAccounts

My question is, what is the correct way of passing an optional where in sql query.

Answer 1

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"

Answer 2

I think optional where clause can be done in this way also :-

  SELECT name, phone_office, 
          billing_address_city, billing_address_street,
          billing_address_country 
  FROM accounts
  WHERE (assigned_user_id = $P{EmployeeID} or $P{EmployeeID} is null)
  ORDER BY billing_address_country, billing_address_city

In this condition if you do not pass any employee id it gives you all the values without applying where clause condition.