Open topic with navigation

Query-based Input Controls

Query-based input controls display a dynamic set of values for the user to choose from. They are input control resources in the repository, but instead of being based on a datatype or a static list of values, they perform a query to retrieve a list of values. For example, a report could have a city parameter, and the query-based input control could display the list of cities that exist in your data. Because the queries use standard syntax, you can include filters in a WHERE clause. In the previous example, you could restrict the list of cities to a certain country.

By including parameters, you can also create cascading input controls. A cascading input control is one whose choices depend on the selection of a previous input control. For example, after the user selects a country, the available city values are restricted to the chosen country. Cascading input controls are query-based controls that contain parameters returned by other controls.

Cascading input controls help make input controls easier to use and faster to display. Certain parameters in reports have a natural hierarchy, such as countries and cities or years and quarters, and the cascading input controls let the user find values based on this hierarchy. Instead of selecting cities from one large list that may need to scroll, users can make a selection from a smaller list where all choices are visible. Also, displaying long lists make the web page slow to load, so cascading input controls that reduce the size of the list make it faster to load. 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.

Creating a Query-based Input Control

In this first example, we create a query-based input control that returns a long list of all cities for the user to choose from.

1.     Log in as an administrator.

2.     Browse the repository and select the folder where you want to create the query-based input control.

3.     Right-click the folder and select Add Resource Input Control. The Add Input Control dialog appears:

4.     Select the type of query-based input control from the type drop-down list. This choice determines how the input control appears to users, either as a drop-down list, a set of radio buttons, a multi-select list, or a set of check boxes. In this example, we choose a single-select query-based input control.

5.     Specify the prompt text, parameter name, optional description, and appearance options in the same manner as when defining a regular input control.

6.     Click Next. Because we selected one of the query-based types, the Locate Query page appears:

If you have a suitable query resource defined in the repository, you could select it here as an external reference. In this example, we’ll define a query resource locally inside the input control resource.

7.     Click Next to define the local query resource. The query naming dialog appears:

Although the query resource is not visible in the repository, it may still have a name, ID and optional description within the query resource. However, the values for these fields are not important.

8.     Enter any name, and the ID is filled in automatically. Then click Next. The data source link page appears:

As with all query resources, the query resource inside the input control may optionally link to a data source, either in the repository, or its own internally defined one. If no data source is linked, the query in the input control uses the same data source as report. In this example, we take the default selection of not linking to a data source.

9.     Click Next. The query definition page appears:

10.     Select the query language, in this example SQL, and enter a query string. The SELECT statement should contain the names of all fields used in the display, value, or filter for the input control. In this example, the query returns three fields, country, state, and city, and the country field is used to limit the values to a single country. The ORDER BY clause ensures that the values from the query are sorted alphabetically when they appear in the input control.

For an example in a different query language, see section Domain-based Queries.

11.     Click Save to complete the query definition. The parameter values page appears:

On the parameter values page, you define which field in the results of the query are displayed, and which field contains values that become the parameter value when chosen.

First, specify the value column, which is the field whose value is passed to the report. The data type of the field must match the type of the corresponding parameter in the report.

Los Angeles | CA
San Francisco | CA
Denver | CO

Only the city value (without the state) is passed to the report. Showing additional field in this way can help users find the value they want in long lists of results.

The value and display columns may also be entirely different, for example, displaying the full name of a sales representative, but using the employee ID as the value returned by the input control. The only restriction is that all fields used in the value or display list must be selected by the query.

Built-in Parameters for Query-based Input Controls

The LoggedInUser and LoggedInUsername parameters are always available for query input controls; they are always available to reports, as well, even if an input control isn’t defined for them. The standard parameters are also provided for reports if they are defined as parameters in the JRXML.

Domain-based Queries

In the case of reports that use a Domain as the data source, any query-based input controls must contain a query against the Domain. When defining the query as shown in Adding an Input Control - Defining the Query, set the query language to Domain.

Domain queries have their own special syntax, the same that is used in the Domain design. A Domain-based query references fields, called items, by their item IDs, along with any set IDs that determine the path of the item within the Domain. For example, if you want your query input control to return a list store cities, where the field with ID ej_store_store_city is nested in the set with ID expense_join_store, you would use the following Domain query:

The list contained inside the <queryFields> tag in a Domain query is equivalent to the fields given in the SELECT statement of an SQL query. Given the query above, you can create an input control for a Domain-based report that lets the user select a city as a parameter to the report.

Sometimes, you may want the input control to display more information than the actual value returned. As with standard query-based input controls, you can select more fields, and then display those fields in your input control. For example, to make the list of cities unambiguous, you could include the state and country in your display. In that case, the Domain-based query must also retrieve those items:

Then, when specifying your visible query columns, as shown in The COUNTRY Input Control, you would add the 3 fields to the list in the order you want them to appear. When specifying fields in the list of visible query columns, use the full ID of the field, including any set IDs. For example, the following list of fields:

expense_join_store.ej_store_store_country

expense_join_store.ej_store_store_state

expense_join_store.ej_store_store_city

creates a list of values such as the following for users to choose from (the separator | is added automatically):

USA | CA | Los Angeles

USA | CA | San Francisco

USA | OR | Portland

USA | WA | Redmond

Finally, the Domain-based query also has the option to filter the query results, as shown in the following example:

The <queryFilterString> tag contains a DomEL (Domain Expression Language) expression that references the full ID of the fields, including any set IDs. For more information about DomEL, see the JasperReports Server User Guide. The <queryFilterString> tag in a Domain query is equivalent to the WHERE clause of an SQL query. The list of fields in the <queryFields> tag must include all fields being referenced in the filter string.

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 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 would 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 selects a different state in the first control, the list of cities in the second control updates accordingly.

Parameter substitution in query input controls follows the same approach as for JasperReports queries. Queries of all types of query connections can use parameter substitution, and $P, $P! and $X (for SQL queries) parameters are supported. For more information on using $P, $P! and $X to build dynamic queries, refer to the JasperReports Ultimate Guide and the iReport Ultimate Guide.

In almost all cases, the parameters appear in the filter (WHERE) clause of the query. Single-select query input controls return single values that are referenced with the $P syntax, and multi-select query input controls return collections that are handled by the $X syntax.

Parameters in Input Control Queries

The example in this section shows how to create cascading input controls for selecting a country and a city. This is done by writing the query of the second input control (city) with a syntax that references a parameter name. The syntax is the same used by JasperReports in the report queries. A parameter is referenced using the following convention:

$P{parameter name}

So if we have an input control called COUNTRY, the query to get the cities from a hypothetical table called ACCOUNTS looks like this:

select city from ACCOUNTS where country = $P{COUNTRY}

When the user selects a country from the COUNTRY input control, the result is used to perform the query of the CITY input control, and the CITY input control is refreshed to show the result.

There are two additional ways to use a parameter in a cascading input control query. The first is used when the value held by parameter_name is not a simple value, but a chunk of the query (or in extreme cases even the whole query). It has the syntax:

$P!{parameter_name}

With the $P!{} syntax, the value of the parameter is treated as raw text. The server replaces the placeholder with the value of the referenced parameter without performing extra checking and value escaping, as is done when using the plain $P{} syntax.

Secondly, there is the $X{} syntax that is used when the value of a parameter is a collection. 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. Now, the cities are selected in a multi-select input control that returns a collection, and the $P{} syntax is insufficient for substituting a collection into an SQL query. The $X{} syntax appears as follows:

When 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:

$X{} takes three positional arguments:

•    First is the collection operator, either IN of NOT IN.

•    Second is the table column that is being compared.

•    Third is the parameter that provides the collection of values, in other words, the name a multi-select input control.

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.

Step-by-Step Example

In this example, we’ll create a simple report that displays all the accounts of a city, using the SugarCRM sample database shipped with JasperReports Server. This example uses iReport to create a report and the JasperReports Server plug-in to crate the import control resources in the repository.

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

select * from accounts where billing_address_city = $P{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.

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 (this because we want to get the countries using an SQL query, but since this is just a common input control, we may use any other type of input control, like a list of values or even a multiple-select list of values).

Edit a local resource for the query, set a name for it (“query”) and set the query language to SQL.

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

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.

Now that we have an input control named COUNTRY, we can reference the COUNTRY parameter in any query-based input control. This is what we are going to do with the second input control. Set its name to CITY. Its definition is similar to the COUNTRY control, so the type must be Single Select Query. The query resource must be of type SQL; it’s used in the COUNTRY parameter’s where condition:

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 in Cascading Input Control Showing Country and Cities appears. It consists of the two simple input controls, and the CITY control is not populated until the user selects a country.

Open topic with navigation