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.
| 1. | Log in as an administrator. |
| 2. | Browse the repository and select the folder for the query-based input control. |
| 3. | Right click the folder's name 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 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 type. |
| 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 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.
| 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 link data source page appears: |
| |
| Adding an Input Control - Linking to a Data Source |
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.
| 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. Country limits 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. |
| 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 specify which fields in the query result are displayed, and which field contains values that become the parameter value. when chosen.
| a. | First, specify the value column. This is the field whose value is passed to the report. The datatype of the field must match the type of the corresponding parameter in the report. |
| b. | Next, specify the visible columns. These are the fields whose values appear in the input control the user chooses from. In the simplest case, enter the 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 can 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 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.
LoggedInUserRoles
Collection
<String>
The roles assigned to the current user. This is helpful for parameters that use $X.
Attributes defined on users or at the server-level can also be used in reports and query-based input controls. For more information, see Managing Attributes.
Attribute-based Parameters for Queries and Reports
Parameter Name
Type
Notes
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> (such as att1). If there's no attribute with this name for this user, the parameter is empty.
This parameter is available only if it's defined in a query or as a report parameter.
ServerAttribute_
<attribute-name>
String
The value of the named attribute defined at the server level. If there's no attribute with this name at the server-level, the parameter is empty.
This parameter is available only if it's defined in a query or as a report parameter.
Attribute_<attribute-name> String
The value of the named attribute is determined hierarchically. The first value found for the named attribute in the following order:
| • | On the logged-in user. |
| • | At the server level. |
If there's no attribute with this name defined in those locations, the parameter is empty.
This parameter is available only if it's defined in a query or as a report parameter.
Recommended Comments
There are no comments to display.