Generally, parameters can be used in the query associated with a report even if not all the languages support them.

JasperReports executes queries, passing the value of each parameter used in the query to the statement.

This approach has a major advantage with respect to concatenating the parameter value to the query string—you do not have to take care of special characters or sanitize your parameter, since the database can do it for you. At the same time, this method places limits on the control you have on the query structure. For example, you cannot specify a portion of a query with a parameter.

Using Parameters in a SQL Query

Parameters can be used in SQL queries to filter records in a where condition or to add/replace pieces of raw SQL or even to pass the entire SQL string to execute.

In the first case the parameters are used as standard SQL parameters, in example:

SELECT * FROM ORDERS WHERE ORDER_ID = $P{my_order_id}

In this example my_order_id is a parameter that contains the ID of the order to read. This parameter can be passed to the report from the application that is running it to select only a specific order. Please note that the parameter here is a valid SQL parameter, meaning that the query can be executed using a prepared statement like:

SELECT * FROM ORDERS WHERE ORDER_ID = ?

and the value of the parameter my_order_id is then passed to the statement.

In this query:

SELECT * FROM ORDERS ORDER BY $P!{my_order_field}

my_order_field cannot be treated as a SQL parameter. JasperReports considers this parameter a placeholder (note the special syntax $P!{}) which is replaced with the text value of the parameter (in this case "ORDERDATE DESC").

With the same logic, a query can be fully passed using a parameter. The query string would look like:

$P!{my_query}

The number of parameters in a query is arbitrary. When passing a value using the $P!{} syntax, the value of the parameter is taken as is, the user is responsible of the correctness of the passed value (SQL escaping is not performed by JasperReports in this case). When using a parameter in a query, a default value must be set for the parameter to allow Jaspersoft Studio to execute the query to retrieve the available fields.

Using Parameters with Null Values

The parameter form $P{parametername} doesn't work correctly with null values. In an operation in which your value could be null, use the form $X{EQUAL,fieldname,parametername}.

For example:

In this case $P should be used, because we don't have $X{GREATER,..}, and Null has no meaning for the operation “greater than”.

Let's compare two expressions:

"select * where num_column = $P{num_param}"

and

"select * where $X{EQUAL, num_column, num_param}"

Both generate the same output if parameter has not Null value: "select * where num_column = 1"

However, if the parameter has a Null value the output is different:

The key difference that databases don't understand "= null", but "is null". So if you want your query with the condition "=" to work with null values, you need to use $X{EQUAL/NOTEQUAL, column, parameter}.

IN and NOTIN Clauses

JasperReports provides a special syntax to use with a where condition: the clause IN and NOTIN.

The clause is used to check whether a particular value is present in a discrete set of values. Here is an example:

SELECT * FROM ORDERS WHERE SHIPCOUNTRY IS IN ('USA','Italy','Germany')

The set here is defined by the countries USA, Italy and Germany. Assuming we are passing the set of countries in a list (or better a java.util.Collection) or in an array, the syntax to make the previous query dynamic in reference to the set of countries is:

SELECT * FROM ORDERS WHERE $X{IN, SHIPCOUNTRY, myCountries}

where myCountries is the name of the parameter that contains the set of country names. The $X{} clause recognizes three parameters:

JasperReports handles special characters in each value. If the parameter is null or contains an empty list, meaning no value has been set for the parameter, the entire $X{} clause is evaluated as the always true statement “0 = 0”.

Relative Dates

The relative dates feature enables you create reports that to filter information based on a date range relative to the current system date. To do this, use the following template:

<Keyword> <+/-> <N> where:

For example, if you want to look at Sales for the prior month, your expression would be MONTH - 1.

The class attribute of a JasperReports Parameter of type Relative Date should have one of the following values:

Following are two examples of when and how to use relative dates:

If you want to set a relative date as a default value expression of a JasperReports parameter, use the following relative date-builder pattern:

For queries, Relative dates are not supported by the $P{} function because it can handle only a limited number of classes (standard Java classes Integer, String etc.). Instead, a new implementation of pluggable functions and parameters in JasperReports supports relative dates with $X{} functions.

The following is a JRXML example that shows data from the previous day:

Relative dates currently do not support keywords like “Week-To-Date” (from the start of the current week to the end of the current day). However, you can emulate that by using IS_BETWEEN:

Passing Parameters from a Program

Jaspersoft Studio passes parameters from a program “caller” to the print generator using a class that extends the java.util.Map interface. For example:

fillReport is a key method that allows you to create a report instance by specifying the file name as a parameter, a parameter map, and a data source. (This example uses a dummy data source created with the class JREmptyDataSource and an empty parameter map created using a java.util.HashMap object.)

Let’s see how to pass a simple parameter to a reporting order to specify the title of a report.

The first step is to create a parameter in the report to host the title (that is a String). We can name this parameter REPORT_TITLE and the class is java.lang.String (“Definition of REPORT_TITLE”).

All the other properties can be left as they are. Drag the parameter into the Title band to create a text field to display the REPORT_TITLE parameter.

To set the value of the REPORT_TITLE parameter in our application, modify the code of the previous source code example by adding:

We have included a value for the REPORT_TITLE parameter in the parameter map. You do not need to pass a value for all the parameters. If you don’t provide a value for a certain parameter, JasperReports assigns the value of Default Value Expression to the parameter with the empty expression evaluated as null.

Printing the report, Jaspersoft Studio includes the String This is the title of the report in the Title band. In this case we just used a simple String. However, it is possible to pass much more complex objects as parameters, such as an image (java.awt.Image) or a data source instance configured to provide a specified subreport with data. The most important thing to remember is that the object passed in the map as the value for a certain parameter must have the same type (or at least be a super class) of the type of the parameter in the report. Otherwise, Jaspersoft Studio fails to generate the report, returning a ClassCastException error.