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.

Creating a Query-based Input Control

In this first example, we create a query-based input control that returns a 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:

Adding an Input Control - Naming

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:

Adding an Input Control - Locating the Query

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:

Adding an Input Control - Naming the Query

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:

Adding an Input Control - Linking to a Data Source

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:

Adding an Input Control - Defining the Query

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 Domain-based Queries.

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

Adding an Input Control - Setting Parameter Values

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.

a. 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.
b. Next, specify the visible columns, which are the fields whose values appear in the input control that the user chooses from. In the simplest case, enter same field as the value column. If you add multiple fields to the visible columns, the input control displays the fields together, in the order listed, separated by a vertical bar (|). In this example, the user may see and choose from:

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.

Built-in Parameters for Query-based Input Controls

Parameter Name

Type

Notes

LoggedInUser

User

The user that is currently logged in. This parameter isn’t available in query input controls, but is used as a parameter to the report.

LoggedInUsername

String

The user name of the current user.

LoggedInUserFullName

String

The full name of the current user.

LoggedInUserEmail
Address

String

The email address of the current user.

LoggedInUserEnabled

Boolean

Indicates whether the current user is enabled.

LoggedInUserExternally
Defined

Boolean

Indicates whether the current user is authenticated externally.

LoggedInUserTenantId

String

In the commercial editions, the name of the organization of the current user.

LoggedInUserRoles

Collection
<String>

The roles assigned to the current user. This is helpful for parameters that use $X.

LoggedInUserAttributes

Map<String, String>

The profile attributes of the logged-in user. This parameter isn’t usable in query input controls, but it is used as a parameter to the report. If the user has no attributes, the parameter is an empty map.

LoggedInUserAttribute
Names

Collection
<String>

The names of the profile attributes of the logged-in user. This is helpful for parameters that use $X. If the user has no attributes, the parameter is an empty map.

LoggedInUserAttribute
Values

Collection
<String>

The values of the profile attributes of the logged-in user. This is helpful for parameters that use $X. If the user has no attributes, the parameter is an empty map.

LoggedInUserAttribute_
<attribute-name>

String

For the logged-in user, the value of the attribute matching the name passed as <attribute-name> (like att1). If there is no match, the parameter is empty.

This parameter is only available if it is defined in a query or as a report parameter.

Domain-based Queries

In the case of reports that use a Domain as the data source (an option available in the professional edition of JasperReports Server), 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.

The query language Domain ("sl") is selected when opening Domain-based queries created in JasperServer 3.5 or earlier. It is used only for backward compatibility and should not be selected for new Domain-based queries.

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:

<query>
  <queryFields>
    <queryField id="expense_join_store.ej_store_store_city" />
  </queryFields>
</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:

<query>
  <queryFields>
    <queryField id="expense_join_store.ej_store_store_city" />
    <queryField id="expense_join_store.ej_store_store_state" />
    <queryField id="expense_join_store.ej_store_store_country" />
  </queryFields>
</query>

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:

<query>
  <queryFields>
    <queryField id="expense_join_store.ej_store_store_city" />
    <queryField id="expense_join_store.ej_store_store_country" />
    <queryField id="expense_join_store.ej_store_store_state" />
  </queryFields>
  <queryFilterString>expense_join_store.ej_store_store_country == 'USA' and
        expense_join_store.ej_store_store_state == 'CA'
  </queryFilterString>
</query>

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.

Version: 
Feedback
randomness