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.

yup i know it is a parameter, but are you supplying table name or data? as from the query `WHERE $P!{EmployeeID} is null` , the parameter should supply table name. and here `assigned_user_id = $P!{EmployeeID}` it should supply data. i guess your query itself is not correct — Shann, Oct 16 '13 at 11:30
can you show your data from table accounts and what is your desired output from query — Shann, Oct 16 '13 at 12:02
possible duplicate of [passing empty field as parameter in jasper ireport](http://stackoverflow.com/questions/16460446/passing-empty-field-as-parameter-in-jasper-ireport) & [JasperServer - null values for input controls](http://stackoverflow.com/q/11082379/876298) — Alex K, Oct 16 '13 at 12:11
Why you use P!{par} instead P{par}? [http://stackoverflow.com/questions/15145575/passing-sql-operators-to-query-through-ireport-parameter/15151942#15151942](http://stackoverflow.com/questions/15145575/passing-sql-operators-to-query-through-ireport-parameter/15151942#15151942) — sanBez, Oct 16 '13 at 15:20
@AlexK Just to clarify, i want to know how can i remove the where if the url doesn have &EmployeeID=sarah_id — anvd, Oct 16 '13 at 16:54
from my pov @anvd is passing parameter instead of field name, and the using the same parameter to filter in assigned_user_id field. — Shann, Oct 16 '13 at 18:19

score 11 · Accepted Answer · edited May 23 '17 at 11:50

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:

enter image description here

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:

enter image description here

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"

Be careful when you have two parameters in the where clause. In my case didn't with the same logic. Im using iReport 5.6 — Rodolfo Velasco, Jul 24 '15 at 22:01

score 2 · Answer 2 · answered Oct 21 '13 at 07:58

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.

optional where clause jasper reports

2 Answers2

The sample

Linked

Related