Cascading Input Controls

A cascading input control is one whose values depend on the selection made in a previous input control. Cascading input controls are created by using parameters in the query string of a related input control. In other words, the parameter defined by an input control may be used in another query-based input control.

In the query-based example of cities and states such as:

Los Angeles | CA
San Francisco | CA
Denver | CO

the query may still generate a list of hundreds of cities to scroll through. Even though each city is easy to identify with the state, scrolling through a long list is time consuming. With cascading input controls, this example can have two input controls, one for the state and one for the city:

When input controls are displayed, the query for the state input control returns an alphabetical list of unique state names.
When the user selects a state, the query for the city input control is triggered and returns the list of cities for that state. The cities are displayed in the input control, and when the user selects one and submits it, the city name is passed as a parameter to the report.

The user makes two selections from much shorter lists, which is easier and quicker than using one long list of city and state names. The second input control is empty, showing no selections, until clicking on the first of the cascading input controls. If the user changes the state in the first control, the list of cities in the second control updates accordingly. If there were an especially large number of cities, more cascading input controls could be used to reduce the list, such as region or state. The values for each control are loaded only when the previous input has been selected, making for a convenient and speedy user experience.

The parameter values determined by each cascading input control may or may not be used in the report. For example, if the report only shows data about a city, the country input control exists only to speed up the choice of city. However, if the report also shows information such as city average compared to country average for a given measure, the country parameter is also used in the report.

Parameters in Input Control Queries

Parameter substitution in query input controls follows the same approach as for JasperReports queries. Queries of all types of data sources can use parameter substitution, and $P, $P! and $X (for SQL queries) parameters are supported. The $X notation has two principal forms explained in the following list:

$P{parameter_name}

The value of the parameter is substituted into the query. In cases where the parameter contains a string, the substitution mechanisms inserts the proper escape characters to create valid SQL. Use this for with single-select input controls and simple comparison operators such as greater-than or less-than. For example:

select name from EMPLOYEES where deals_closed > $P{DEALS}

Do not use $P{parameter_name} with equality because the parameter value can be null, and field = NULL is not valid SQL. Instead use $X{EQUALS, ...} as explained below.

$P!{parameter_name}

The value of the parameter is treated as raw text. The server replaces the parameter with the value of the input control without performing extra checking or value escaping. This is used in complex cases where the input control provides a piece of the query or sometimes the entire query.

$X{EQUALS, column, parameter_name} or $X{NOTEQUAL, column, parameter_name}

This syntax performs equality verification and handles the case when the parameter value is null. Use this everywhere instead of the old column = $P{parameter_name} syntax. The $X{EQUALS...} syntax performs the following substitution before submitting the query:

column = parameter_value -- when parameter_value is non-null

column IS NULL -- when parameter_value is NULL

$X{IN, column, parameter_name} or $X{NOT IN, column, parameter_name}

Use this parameter for cascading with multiple-select input controls. The $X{IN...} operator is true when the field value matches any one of the multiple values of the input control. In the country/cities example, we can allow the user to pick any number of countries, and show all the cities in the selected countries. The query-based input control would have the following query:

select city from ACCOUNTS where $X{IN, country, COUNTRIES}

If the user selects the values Canada, Mexico, and USA in the COUNTRIES multi-select input control, the $X{} syntax translates into the following query for the CITIES input control:

select city from ACCOUNTS where country IN ('USA','Canada','Mexico')

When defining these parameters in a report, don’t use a defaultValueExpression element. Due to a limitation in JasperReports Server, these parameters are null when a defaultValueExpression is provided.

The $X syntax also supports the following operators. They are all designed to handle null input by generating 0 = 0 when the parameter value is null:

Parameter Syntax Meaning

$X{GREATER, column, parameter}

column > parameter

$X{[GREATER, column, parameter}

column >= parameter

$X{LESS, column, parameter}

column < parameter

$X{LESS], column, parameter}

column <= parameter

$X{BETWEEN, column, start_param, end_param}

start_param < column < end param

$X{BETWEEN], column, start_param, end_param} start_param < column <= end param
$X{[BETWEEN, column, start_param, end_param} start_param <= column < end param
$X{[BETWEEN], column, start_param, end_param} start_param <= column <= end param

For more information on using $P, $P! and $X to build dynamic queries, refer to the JasperReports Ultimate Guide and the iReport Ultimate Guide.

The number of parameters that can be used in a query is arbitrary, just as the number of input controls that can be defined in a JasperReport is arbitrary. In addition to the standard input control parameters, a cascading input control query can use the built-in parameters described in Built-in Parameters for Query-based Input Controls.

Creating a Cascading Input Control

In this example, we’ll create a simple report where users select a country and then a city to display all the accounts of a city, using the SugarCRM sample database shipped with JasperReports Server. This example uses iReport to create a report and its input controls. iReport uses the JasperReports Server plug-in to upload these resources to the repository.

We start by creating a report with a parameter called CITY and the following report query:

select * from accounts where $X{EQUALS, billing_address_city, CITY}

In the detail band, we add three fields: name, shipping_address_city, and shipping_address_country. Then we publish the report on the server, using the Publish tool of the JasperReports Server plug-in in iReport.

Simple Report Filtered by City

Now define the input controls. Right-click the JasperReport node in the Repository Navigator and add the first input control by selecting Add > Input Control. This input control shows the list of countries in which accounts are present. It is not a cascading input control, but its value is used in the next control: the one that selects the city.

Set the name of this first input control to COUNTRY (the display name can be “Country”). Set the Input Control type to Single Select Query. Edit a local resource for the query, set a name for it (“query”) and set the query language to SQL.

Creating the COUNTRY Input Control

The query is just a simple query to select the countries. For instance:

select distinct shipping_address_country from ACCOUNTS
  order by shipping_address_country

To complete the local query resource, set the repository resource /datasources/JServerJdbcDS as the query’s data source. Finally, in the Value and Visible Columns tab, set the Value Column to shipping_address_country and make it (the only) visible column. The first input control, which selects the country, is now ready.

The COUNTRY Input Control

Now that we have an input control named COUNTRY, we can reference the COUNTRY parameter in another query-based input control. This is what we are going to do with the second input control named CITY. Its definition is also a Single Select Queryof type SQL. The query for CITY uses a $X{EQUALS, ..., COUNTRY} parameter in its where condition:

select distinct shipping_address_city from ACCOUNTS
  where $X{EQUALS, shipping_address_country, COUNTRY}
  order by shipping_address_city

This time the column to be used in the Value and Visible Columns field is shipping_address_city.

When you run the JasperReport, if everything has been correctly configured, the dialog box shown in the following figure appears. It consists of the two simple input controls, and the CITY control is not populated until the user selects a country.

Cascading Input Control Showing Country and Cities

Version: 
Feedback