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 in your data. Because the queries use standard syntax, you can include filters in a WHERE clause, for example, to 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 the user can choose from.

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

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.

Like all queries, the query 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 the report. In this example, we use the default of not linking to a data source.

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

On the parameter values page, you specify which fields in the query result are displayed, and which field contains values that become the parameter value. when chosen.

Los Angeles | CA

San Francisco | CA

Denver | CO

Only the city value (without the state) is passed to the report. Showing additional fields 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're 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're defined as parameters in the JRXML.

Built-in Parameters for Query-based Input Controls

Parameter Name

Type

Notes

LoggedInUser

User

The currently logged in user. 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 current user's organization.

LoggedInUserRoles

Collection
<String>

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

LoggedInUserAttributes

Map<String, String>

The 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 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 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's no match, the parameter is empty.

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

Domain-based Queries

For reports that use a Domain as the data source (professional edition only), a query-based input control 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 one that's 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 of 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'd 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.