How to set JasperReports 5.6 Equal Clause Function to IS NOT NULL

Question

I'm working with iReport Designer and JasperServer 5.6 on a MySQL database and I'm trying to make my report return all results when the parameter is null.

I've been looking at the documentation here: http://jasperreports.sourceforge.net/sample.reference/query/, which has been quite helpful except it doesn't have what I want. The closest I've gotten is here in the documentation:

The $X{EQUAL, column_name, parameter_name} clause function

The function expects three mandatory clause tokens:

• The first token represents the function ID and always takes the fixed value EQUAL.
• The second token is the SQL column (or column combination) to be used in the clause.
• The third token is the name of the report parameter that contains the value to compare to.

If the parameter's value is not null, the function constructs a = ? clause. If the parameter's value is null, the function generates a IS NULL clause.

All the parameters I'm inputting are the id's of the records I'd like to see, so when I use this I get no results because an id or the Primary Key cannot be null.

Ex.

SELECT *
FROM User
WHERE $X{EQUAL, user.id, user_id}

Inputting 1 will return user id 1 and inputting nothing, or null, will return me nothing. What I want it to return instead is all users in the table.

Is there an easy fix to this problem, like having this function return IS NOT NULL when this happens? Is there something else in JasperServer or iReports that will help me or is there something I can do in SQL that will ignore a WHERE clause when I have this parameter set to null?

Answer 1

You can use the below logic in your query's WHERE clause to achieve what you are asking (this works in postgres, but it should work in MySQL as well):

where (($P{parameter1} is null) or (user.id = $P{parameter1}))

If the parameter is null the first part of the OR comparator will return true, and the query will ignore the rest of the expression, which should give you all users in the user table. If it is not null, it will skip over the first part and execute the query for the user.id passed to the parameter.

Hope this helps!