ART Wiki

A Reporting Tool

Brought to you by: eliboni, tanyona

Parameters

Authors:

Parameters

Parameters

Parameters enable different output to be generated depending on values selected or input by a user.

Creating a new parameter

You can create a parameter either by using the Configure | Parameters menu and selecting the Add button or from the Reports Configuration page, finding the report you want to add a parameter to, selecting the More | Parameters option and selecting the Add New button.

Field	Description
ID	An auto-generated ID used to identify the parameter
Name	The name of the parameter as it appears in reports' SQL source. It should not contain spaces or other special characters e.g. `!"#$%&'()*+,./:;<=>?@[\]^{}~`.
Short Description	An optional brief description to additionally identify the parameter
Description	A description for the parameter
Label	The label for the parameter in the select parameters page
Help Text	Help text displayed as a tooltip in the select parameters page
Placeholder Text	Placeholder text displayed in the field in the select parameters page
Parameter Type	Whether the parameter will have a single value (Single-Value) or whether it can have multiple values (Multi-Value)
Data Type	The type of data that the parameter can hold
Control Type	The type of user interface control to be used for the parameter
Date Format	For Date, DateTime and Time data types, the date format to use. Leave blank to use the default. The format is as per Java SimpleDateFormat format.
Allow Input	For Date, DateTime and Time data types, whether the user is allowed to type directly in the parameter value box. The user can still select the value using the provided icon.
Default Value	The value for the parameter to use when the parameter is first displayed in the select parameters page, or the value to use if the report is run by direct url and the parameter is not specified within the url. For Multi-Value parameters, multiple values can be specified, with each value on a new line.
Default Value Report	A report that provides the default values. This needs to be an LOV report.
Use Default Value In Jobs	Whether the default value or values should be used as the parameter value when running jobs
Values	Values that will be available for selection. The format is similar to that of an LOV: Static report and is an alternative to the Use LOV option.
Use LOV	Whether the parameter provides a list of values to choose from an LOV report
LOV Report	If Use LOV is specified, the report that generates the available values. This needs to be an LOV report.
Shared	Whether the parameter is available for selecting when defining report parameters, through the Add Existing option.
Hidden	Whether the parameter is displayed in the select parameters page
Drilldown Column Index	The index of the column of the parent report that will set this parameter's value when used with drilldown reports. The first column has an index of 1.
Drilldown Column Name	The name of the column of the parent report that will set this parameter's value when used with drilldown reports
Fixed Value	Whether the parameter uses fixed values
Allow Null	Whether a checkbox will be displayed for the user to specify passing NULL as a parameter value. If null values are passed for a parameter, ensure that you use the x-parameter syntax otherwise you will likely get incorrect results. To specify NULL value via url, add `-null` to the parameter name and include that in the url e.g. `&p-param1-null=true&p-param2=test` would pass a NULL value for the parameter named `param1`.
Required	When a report is run from the select parameters page, whether to enforce presence of a value for this parameter
Chained Parents	For a chained parameter, a comma separated list for parameter names which will act as the parent/trigger for this parameter
Chained Depends	For a chained parameter, a comma separated list of parameter names of additional parameters which may provide input to this parameter
Javascript	Additional configuration in Javascript syntax
Options	Options used to further configure the parameter. The options are specified in JSON format.

Parameters in SQL Source

To pass parameter values to an SQL query, in the report's sql source, surround the parameter name with the # character i.e. #param_name#. This will replace that parameter placeholder occurrence with ? in the SQL query and the database will put the parameter value as appropriate when the query is run.

Single-Value Parameters

Single-value parameters are used to pass single values to the SQL query. For example, the following query has two parameters.

    SELECT *
    FROM orders
    WHERE order_date > #date#
    AND vendor_name like #vendor# -- do not put the ' character around the name

Take the following steps to use a single-value parameter

Create the report, putting the parameter placeholder in the appropriate place within the SQL
After saving the report, click on the Parameters button for the just saved report
Click on the Add New button
Fill in the appropriate fields for the parameter
In the Parameter Name field, specify the parameter name as used in the SQL source e.g. date for the example above. Avoid having spaces in parameter names.
Save the parameter
You can also use the Add Existing button to use a parameter that was created previously and marked as Shared.

Note:

When editing report parameters from the Report Parameters page, clicking on the link with the parameter name enables you to edit core parameter definition attributes, while clicking on the Edit button allows you to edit extra attributes specifically related to that report parameter.
The same parameter (#parameter_name#) can be used several times in the same query. All occurrences will be substituted with the same value.
Avoid using the names rules, recipient, columns, condition, limitClause, order. These are used in various ART functionality, and using them for your parameters may result in the query not working as expected.
There is a slight difference between Varchar and Text parameters. Varchar parameters provide a text box for input of a few characters while Text parameters provide a text area for input of much longer strings.
For Date and DateTime parameters, the following values can be used

Value	Meaning
now	The date and time when the report is run
today	The date when the report is run, with the time being 00:00
add `days, weeks, months, years, hours, minutes, seconds, milliseconds` increment	The date when the report is run plus the given increment e.g. `add days 1` for the run date plus one day, `add months -1` for the run date minus one month.
firstday `month, year` offset	The first day of the month or year when the report is run, considering the given offset e.g. `firstday month` for the first day of the current month, `firstday month -1` for the first day of the previous month, `firstday month +1` for the first day of the next month, `firstday month =1` for the first day of January of the current year.
lastday `month, year` offset	The last day of the month or year when the report is run, considering the given offset. Similar to firstday.

Boolean Parameters

There is no boolean data type for single-value parameters because different RDBMSs implement the boolean data type differently. As a workaround, you can create a static LOV report to return the boolean states true and false, as used in your database, and use that LOV report to provide the boolean parameter values.

Example main query

    SELECT * FROM mytable WHERE my_boolean_column=#boolean_param#

Example static LOV query for boolean values

    true|True
    false|False

Lastly, create a Single-Value parameter named boolean_param, with data type as Varchar, set Use LOV to Yes and set the LOV Report to the report created for this purpose above.

If the boolean states are "1" and "0" instead of "true" and "false", you can create a static LOV query like the one below and set the parameter data type to Integer instead of Varchar.

    1|True
    0|False

When using the Checkbox control type, the "true" value will be the first lov value and the "false" value will be the second lov value. You can also type the values directly in the Values field instead of creating an LOV report. To start the checkbox as checked, put a value in the Default Value field.

Multi-Value Parameters

Multi-value parameters are used to pass multiple values to the SQL query. To define a multi-value parameter, put the WHERE...IN clause in the desired location in your query and include the parameter placeholder to indicate where the values will go e.g.

    SELECT *
    FROM orders
    WHERE product_name IN(#product_list#)

Take the following steps to use a multi-value parameter

Create the report, putting the WHERE...IN clause and parameter placeholder in the appropriate place within the SQL
Create the parameter definition using the same process as with single-value parameters
Now when a user runs the query, they will be presented with a parameters section where they can select one or more values for the parameter

Note:

If the values for a multi-value parameter are not derived from an LOV, the end user will enter the multiple values required in the text box provided, with each value put on a new line.
When setting the Default Value for a multi-value parameter, several values can be set as defaults by entering each value on a separate line in the field provided. Default values can also come from an LOV report that can be specified in the Default Value Report field.

X-Parameter Definitions

There exists an alternative syntax for specifying IN, NOT IN, = and <> comparisons. This syntax takes the form $x{<comparator>,<column_name>,<parameter_name>}. It should be noted that with x-parameter definitions, the parameter name is case sensitive.

IN, NOT IN

This is used with multi-value parameters. IN clauses take the format $x{in,<column_name>,<parameter_name>} and NOT IN clauses take the format $x{notin,<column_name>,<parameter_name>} e.g.

    SELECT *
    FROM orders
    WHERE $x{in,product_name,product_list}

EQUAL, NOT EQUAL

This is used for single-value parameters. Equal comparators take the format $x{equal,<column_name>,<parameter_name>} and would typically be equivalent to column_name=#parameter_name#. In case NULL is passed, the syntax would be converted to column_name IS NULL. Not equal comparators take the format $x{notequal,<column_name>,<parameter_name>} and would typically be equivalent to column_name<>#parameter_name#. In case NULL is passed, the syntax would be converted to column_name IS NOT NULL.

Javascript Options

Options can be specified in the Javascript field, in Javascript syntax to define certain aspects of parameters.

Mask Options

Options can be specified in an object variable named maskOptions that may contain RobinHerbots Inputmask configuration. e.g.

var maskOptions = {
    mask: "AA-99"
};

Datepicker Options

Options can be specified in an object variable named datepickerOptions that may contain DatePicker options. e.g.

var datepickerOptions = {
    minDate: "2024-01-05"
};

Date Range Options

Options can be specified in an object variable named daterangeOptions that may contain Date Range Picker options. e.g.

var daterangeOptions = {
    minDate: "2024-01-05"
};

Parameter Options

When defining a parameter, a number of options can be specified in the Options field. These are defined in JSON format with the following possibilities.

i18n Options

Certain parameter fields can be localized by specifying an i18n object in the Options field with the following possible values.

Property	Data Type	Description
label	Object[]	Localization for the parameter label. Each object has the language code as the key and the translation as the value. Multiple language codes can be specified separated by comma e.g. `{"label": [{"en, sw": "Test Label"}, {"de": "Another Label"}]`
helpText	Object[]	Localization for the parameter help text. Similar format to label.
defaultValue	Object[]	Localization for the parameter default value. Similar format to label.
placeholderText	Object[]	Localization for the parameter placeholder text. Similar format to label.

A sample i18n configuration may be "i18n": { "label": [{"en, sw": "Test Label"}, {"de": "Another Label"}], "helpText": [{"lt": "Sample help text"}] }

Date Range Options

For DateRange parameters, options can be specified in a dateRange object with the properties below. ART uses the Date Range Picker library to provide the date range picker. Some of the options are given below. Others are available as per the Date Range Picker documentation.

Property	Data Type	Default Value	Description
fromParameter	Object		A specification of the parameter that should get the "From" date of the date range. This object has two properties, name which specifies the parameter name and format which specifies the date format of the parameter. The format is as per Java SimpleDateFormat format and the default is yyyy-MM-dd.
toParameter	Object		A specification of the parameter that should get the "To" date of the date range. Similar specification to fromParameter.
format	String	yyyy-MM-dd	The format of dates displayed in the date range picker text input. The format is as per Java SimpleDateFormat format. If you use the locale.format property, that will need to be in Moment.js (momentjs.com) format.
ranges	String[]	`["default"]`	Pre-defined, custom date ranges available for selection. Can be set to null if custom ranges are not required. Possible values include today, yesterday, last7Days, last30Days, thisMonth, lastMonth, thisQuarter, lastQuarter, thisYear, lastYear, thisWeek, lastWeek, yearToDate, monthToDate, quarterToDate, weekToDate.
startDate	String		A specification of the start date when the date range picker is first displayed. You can use syntax like "add days -1", "add months 2" etc to specify a date in the past or future. In this case the resulting date is as per the format option.
endDate	String		A specification of the end date when the date range picker is first displayed. Similar to startDate.
timeOnly	Boolean	false	Whether the picker should only show time values. Useful when timePicker: true has also been specified and there's need for only time ranges.

Chained Parameters

A chained parameter is one whose values depend on the value selected in another parameter. The parameter that triggers the change is called the "parent". Only Single-Value parameters can be chained parents. A chained parameter can be used as a parent for another parameter, which in turn can have another child parameter and so on.

Example

Suppose we want to view customers who reside in a particular city. We want the user to select a country and then have cities in that country displayed. He'll then pick one of these cities and run the report. We have a CUSTOMERS table, and one of the columns it has is CITY_ID. We also have a COUNTRIES table which has COUNTRY_ID and COUNTRY_NAME columns. Finally, we have a CITIES table with CITY_ID, CITY_NAME and COUNTRY_ID columns.

We could take the following steps to set up the report.

Create a Dynamic LOV report named Countries that will display country names. Depending on what country is selected, the available cities will change. The countries report would have the following SQL.
```
SELECT COUNTRY_ID, COUNTRY_NAME
FROM COUNTRIES
ORDER BY 2
```
Create a Dynamic LOV report named Cities that will display city names. The query needs to have a parameter label for the country parameter. This will be replaced at runtime with the country selected.
```
SELECT CITY_ID, CITY_NAME
FROM CITIES
WHERE COUNTRY_ID = #countryId#
ORDER BY 2
```
Once you have saved the Cities report, click on the Parameters option to assign the countryId parameter
Use the Add New option to create a Single-Value parameter with a Name of countryId, Label of Country, Data Type of Integer, set it to Shared, set it to Use LOV and select Countries as the LOV Report.
Create the main report. The SQL for the report will need to have a placeholder for the cityId parameter.
```
SELECT FIRST_NAME, LAST_NAME, EMAIL
FROM CUSTOMERS
WHERE CITY_ID = #cityId#
```
Once you have saved the main report, click on the Parameters option to allocate the report parameters
Use the Add Existing option to add the countryId shared parameter created earlier
Use the Add New option to create a Single-Value parameter with a Name of cityId, Label of City and Data Type of Integer. Set the Chained Parents field to countryId i.e. the name of the parent parameter for this chained parameter. If the parent parameter data type is Date or DateTime, in the Chained Parents field, put the name of the parent parameter and a "-hidden" suffix e.g. countryId-hidden, and then set the Chained Depends field to the name of the parent parameter e.g. countryId. Save the parameter.

Now when you run the main report, you are presented with two drop down boxes. Depending on what country you select, a different set of cities is displayed, from which you can choose and run the report to see the final results.