ART Wiki

A Reporting Tool

Brought to you by: eliboni, tanyona

Manual

Labels: manual (35)

Authors:

Introduction
- Administration Overview

Introduction

ART is a Java web application that enables quick deployment of SQL query results.

ART Administrators define reports, users, etc.
ART Users run reports and view the results in a browser or in a variety of file formats.

ART is open source software distributed under the GPLv3 license.

Administration Overview

The Administrators define a number of items including

ART Database used to store ART objects e.g. report definitions
Settings used to configure how ART works
Datasources against which reports are run
Reports that can be run
Report Groups used to group reports
User Groups used to group users
Users who can access the application
User Group Membership to add or remove users from user groups
Access Rights to determine which users or user groups can access which reports, report groups or jobs
Parameters to be used by reports
Rules to be applied to reports
Rule Values to be used with specific users or user groups
Jobs to run reports at scheduled times
Schedules to manage schedules that can be used for running jobs
Caches to enable clearing of ART object caches
Connections to view datasource connection pools
Loggers to manage which application debug information is logged by ART

The typical process when defining a report is

Obtain the SQL statement for the report
Create the report
Grant access rights to allow users to run the report

ART Database

The ART database is the database used by ART to hold details like users, report definitions etc. Use the Configure | ART Database menu to define details of the ART database.

Field	Description
Database Type	The database software that the ART database runs on
JNDI	Whether the ART database is a JNDI datasource
Database Protocol	An indication of the kind of syntax the database uses
JDBC Driver	The JDBC driver name
URL	The database JDBC URL, or if you are using a JNDI datasource, the JNDI name of your datasource e.g. `jdbc/MyDatasource`. You can also use the full JNDI url e.g. `java:comp/env/jdbc/MyDatasource`
Username	The user to use to connect to the ART database. The user needs SELECT, INSERT, UPDATE and DELETE rights on the ART tables.
Password	The database user's password
Test SQL	A short SQL query used to determine if a connection is alive e.g. "Select 1"
Connection Pool Timeout (Mins)	How long in minutes an idle connection should be maintained in the connection pool before being closed. This setting applies to the ART database as well as all report datasources.
Max Pool Connections	The maximum number of connections a connection pool can open to the same datasource. Further requests are queued. This setting applies to the ART database as well as all report datasources.

Settings

Settings

Certain settings are used to configure how ART works. Use the Configure | Settings menu to manage ART settings.

Setting	Description
SMTP Server	The host name for the email server used to send emails
SMTP Port	The port to use for SMTP
Use StartTLS	Defines whether to use the StartTLS protocol when sending emails
Use SMTP Authentication	Defines whether the SMTP server requires a username and password in order to send emails
SMTP Username	The username to be used when sending emails if the email server is configured to require SMTP authentication
SMTP Password	The password to be used when sending emails if the email server is configured to require SMTP authentication
SMTP From	An email address to be used as the "From" email address
SMTP Server	An already configured smtp server that can be used for sending emails
Default Authentication Method	The authentication method that will be used by ART. OAuth authentication is currently experimental and is subject to change.
Windows Domain Controller	Used with windows domain authentication. The IP address of the windows domain controller.
Allowed Windows Domains	Used with windows domain authentication. The domain name of the windows domain used for authentication. Multiple domains can be specified, each separated by a comma.
LDAP Server	Used with LDAP authentication. IP address of the LDAP server.
LDAP Port	Used with LDAP authentication. LDAP server port.
LDAP Connection Encryption Method	Used with LDAP authentication. Defines which protocol to use for the LDAP connection.
LDAP URL	Used with LDAP authentication. LDAP server URL. If the LDAP server and port fields have been used, leave this setting blank. This setting only provides an alternative way of specifying the location of the LDAP server.
Use Anonymous Bind	Used with LDAP authentication. Defines whether to use anonymous bind when connecting to the LDAP server in order to search for and authenticate users.
LDAP Bind DN	Used with LDAP authentication. The DN to use when connecting to the LDAP server in order to search for and authenticate users.
LDAP Bind Password	Used with LDAP authentication. The password to use when connecting to the LDAP server in order to search for and authenticate users.
LDAP User ID Attribute	Used with LDAP authentication. The LDAP attribute which will be used to match ART usernames.
LDAP Authentication Method	Used with LDAP authentication. The authentication method to be used with the LDAP server
LDAP Realm	Used with LDAP authentication. The LDAP realm when using the Digest-MD5 authentication method. If blank, the default realm will be used.
HTTP Authentication Variable	Used with http header authentication. The name of the http header that will contain the username of an already authenticated user.
OAuth Provider	The OAuth 2.0 provider to use for oauth authentication
OAuth Client ID	The Client ID from the oauth provider
OAuth Client Secret	The Client Secret from the oauth provider
OAuth User Matching	The field in ART to use to match the oauth user
Azure Tenant ID	For oauth authentication using the Microsoft Azure provider, the tenant id of the Azure account if using a Single tenant application
Default Max Rows	The default maximum number of rows to output for a report
Specific Max Rows	The maximum number of rows to output for specific report formats, defined as a comma separated list of settings with each setting in the format `viewmode:value` e.g. `htmlGrid:5000,xls:10000`. Report formats are case sensitive.
PDF Font Name	Name of a custom font that should be used in generation of pdf output, and charts. For jasper reports, custom fonts need to be defined in the jrxml file. See the Tips documentation for details on how to use custom fonts with jasper reports.
PDF Font File	Path to a font file that contains the custom font
PDF Font Directory	Path to a directory that contains font files, one of which may be used in the pdf font name field
PDF Font Encoding	Encoding to use for the custom font
PDF Font Embedded	Whether the custom font should be embedded in the generated pdf output
Administrator Email	Email address which is displayed in link at the bottom of ART web pages
Date Format	Format to be used for date fields. Format strings to be used is as per the Java SimpleDateFormat class.
Time Format	Format to be used for time fields. Format strings to be used is as per the Java SimpleDateFormat class.
Report Formats	The report formats that will be available to users when they run a report, defined as a comma separated list. Report format names are case sensitive and the order specified will be respected in the list shown to users.
Max Running Reports	The maximum number of reports that can be running at any one time
Show Header in Public User Session	Whether to show the menu bar and page footer for reports that are run by a public user
Scheduling Enabled	Defines whether scheduled jobs will run
Max File Upload Size (MB)	Maximum file upload size. Set to -1 for no limit.
ART Base URL	The base URL for ART e.g. `http://art-server:8080/art`. This is used with a number of features including publish job reminder emails. Don't put a slash at the end.
System Locale	The locale to use in non-interactive scenarios e.g. jobs. An example is en. Leave blank to use the default, which will be the server locale.
Default Language	The language to use by default for the user interface
Hide Language	Whether to hide the language selection box on the login page
JWT Token Expiry (Mins)	The expiry time in minutes for JWT tokens generated for REST API authentication. A value of 0 means no expiry.
Help Link	A URL to your own page that provides help with the ART application. If set, a Help menu will be added to the main application menu, and the link will open in a new window/tab.
Job Error Notification	Email address to send emails to when an error occurs while running a job. Multiple email addresses can be specified separated by commas.
Pipeline Error Notification	Email address to send emails to when an error occurs while running a pipeline. Multiple email addresses can be specified separated by commas.
Encryption Key	Used in updating the encryption key used by ART
Error Notification To	Email address to send emails to when errors occur within the application. Multiple email addresses can be specified separated by commas. Set blank to disable error notification. The SMTP server used is the one configured in the Settings page.
Error Notification Subject Pattern	A string representing how the error notification email subject will look like. The syntax is Logback syntax.
Error Notification Level	The level for which error notification emails will be sent
Error Notification Logger	The logger for which error notification emails are sent. Blank means notifications are sent for errors logged from all classes. If you would like to only receive notifications for errors occurring in jobs, you can set this to `art.jobrunners.ReportJob`. Multiple loggers can be specified separated by commas.
Error Notification Suppress After	The condition at which to suppress sending of duplicate error emails. See the Whisper documentation for details.
Error Notification Expire After	The amount of time after which duplicate error email suppression is expired. See the Whisper documentation for details.
Error Notification Digest Frequency	The frequency with which to send duplicate error email digest messages. See the Whisper documentation for details.
Minimum Password Length	The mimimum password length when a password is set from the Password page. If set to 0, this check is not done.
Minimum Lowercase Characters	The minimum number of lowercase characters in a password as set from the Password page. If set to 0, this check is not done.
Minimum Uppercase Characters	The minimum number of uppercase characters in a password as set from the Password page. If set to 0, this check is not done.
Minimum Numeric Characters	The minimum number of numeric characters in a password as set from the Password page. If set to 0, this check is not done.
Minimum Special Characters	The minimum number of special characters in a password as set from the Password page. If set to 0, this check is not done.
Options	Additional settings in JSON format
CSS	Custom css to override existing styles within the application

Options

A number of options can be specified in the Options field. These are defined in JSON format with the following possibilities.

Property	Data Type	Description
dtExtraOptions	Object	Extra options for the htmlDataTable report format. Refer to the [Reports] section for more details.
excel	Object	Extra options for xls and xlsx report formats. Refer to the [Reports] section for more details.
homeDtOptions	Object	DataTable options for the home page. For the lengthMenu option, use a 1-dimentional array with -1 as the placeholder for the All option e.g. `[10, 20, -1]`, and not a 2-dimentional array as in the DataTables documentation. For other options, use as per the DataTables documentation, e.g. setting the `pageLength` option of this object can change the default number of reports displayed in the home page.
configDtOptions	Object	Similar to homeDtOptions but applies to config pages
oauth	Object	Options for an oauth provider

OAuth Options

Options for an oauth provider can be provided in an object as follows.

Property	Data Type	Description
providerName	String	The name for the provider that is displayed to users on the login page. This is optional and can be used to provide a name different from the default.
providerType	String	The name of the OAuthProvider enum corresponding to a provider type e.g. "MicrosoftAzure". This is optional for use when specifying providers in the art-custom-settings.json file, to give an indication of the type of provider.
clientId	String	The client id. This should not be set in the Settings options but rather the OAuth Client ID field should be used. It should be used when specifying providers in the art-custom-settings.json file.
clientSecret	String	The client secret. This should not be set in the Settings options but rather the OAuth Client Secret field should be used. It should be used when specifying providers in the art-custom-settings.json file.
scope	String	The scope to use. This is not required if using the providers listed on the Settings page. By default openid will be used, or as per defaults for listed providers e.g. for Microsoft Azure, the default is `openid profile`.
usernameAttribute	String	The name of the claim or attribute within the id token that contains the username to use. This is not required if using the providers listed on the Settings page. This defaults to sub, or the defaults for the listed providers.
active	Boolean	When using the art-custom-settings.json file, can be used to turn off an oauth provider. Default is true.
userMatching	String	The name of the OAuthUserMatching enum corresponding to a user matching method e.g. "Email". This should not be set in the Settings options but rather the OAuth User Matching field should be used. It can optionally be used when specifying providers in the art-custom-settings.json file.
azureTenantId	String	For the Microsoft Azure provider, the tenant id of the Azure account if using a Single tenant application. This should not be set in the Settings options but rather the Azure Tenant ID field should be used. It should be used when specifying providers in the art-custom-settings.json file.

OAuth Providers

Some notes are provided for oauth providers as follows.

Microsoft Azure

You will need to set the Azure Tenant ID field if using a Single tenant application
For the logout experience, if you don't want the user to be prompted to choose the account to logout from, you will need to add an Optional claim to your application. To do this, within Azure, select your application under the App registrations menu then select the Token configuration option and click on Add optional claim. In the given screen, select the Token type as ID and then check the login_hint claim and click on Add.

OAuth Notes

Some general oauth notes are provided below that apply to all oauth providers.

The login redirect or callback url to register with the oauth provider is <art base url>/oauthCallback. e.g. http://localhost:8080/art/oauthCallBack. This is the url that ART will set in the redirect_uri parameter when initiating a login request with the oauth provider, and is the url that the provider will redirect to after successful login.

Logout redirect URL

The logout redirect url to register with the oauth provider is <art base url>/login. e.g. http://localhost:8080/art/login. This is the url that ART will set in the post_logout_redirect_uri parameter when initiating a logout request with the oauth provider, and is the url that the provider will redirect to after successful logout.

Global Logout

To have your ART instance participate in global logout or single logout or front-channel logout, the url to register with the oauth provider is <art base url>/oauthLogout. e.g. http://localhost:8080/art/oauthLogout

Updating the encryption key

ART uses symmetric encryption to store some fields e.g. datasource passwords. This requires the use of an encryption key, which is included in the WEB-INF\classes\art\encryption\AesEncryptor.java file. If you would like to change the key used to encrypt values within the application, take the following steps.

Using an encryption key

Backup the application files and the ART database. If something goes wrong, it may not be possible to decrypt password fields and so they may all need to be re-entered. This not only applies to datasources but also to reports which have open/modify passwords, destinations e.g. ftp servers among others.
Login to ART. Ensure that no other users are using the appliction when embarking on an encryption key update.
Once you have logged in and confirmed that the application is working OK, go to the file system and modify the WEB-INF\art-custom-settings.json file and set the encryptionKey field to the key to be used. This would use AES encryption and so the key would need to be either 128 bits (16 bytes), 192 bits (24 bytes) or 256 bits (32 bytes). If there was already a key set in this field, copy it somewhere in case something goes wrong and you need to replace it. Save the file. Do not stop the application or make this change while the application is not running. The application needs to be running already.
Now go to the browser and on the Settings page, for the Encryption Key field, click on the Update button. If you get a success message, the update was successful. View the logs using the View | Logs menu to confirm that there were no errors or warnings. If there was an error, modify the art-custom-settings.json file and return the encryptionKey field the way it was before you attempted the update.
If successful, in order to test/confirm, stop the application and then start it again. If you can run reports, the update should be successful.
Once you have changed the encryption key, when you perform an upgrade of the application, you need to copy this key to the new version's art-custom-settings.json file before you first start the application. You can do this by unzipping the art.war file to a directory e.g. one named art. You can use any zip utility for this as a .war file in just a .zip file. Make the necessary change and then deploy the new directory e.g. art to your applicaiton server.
In case you would like to go back to the default key, repeat this same process and set the encryptionKey field to the empty string "" when updating.

Using an encryption password

Instead of specifying the encryption key directly, you can supply a password which will be used to generate the encryption key. Take the same steps as for using an encryption key but specify the password in the encryptionPassword.password field. Additionally, set the desired key length of the generated key in the encryptionPassword.keyLength field. The key length must be either 128, 192 or 256 as per the AES algorithm.

Testing string encryption/decryption

You can test or encrypt/decrypt strings used in the ART application by running the ART encryptor on the command line. To do this, open a command prompt window and navigate to the WEB-INF\classes folder. Use the command java -cp "../lib/*;../etc/*;." art.encryption.AesEncryptor on windows or java -cp "../lib/*:../etc/*:." art.encryption.AesEncryptor on linux. You can then supply a string to encrypt/decrypt together with appropriate parameters. An example of encrypting a string using a password may be ...AesEncryptor -e -t "clear text to be encrypted" -p "encryption password" -l 256. You can use the help option to see available parameters e.g. ...AesEncryptor -h.

Note:

If you change the ART Database and that database was based on a different encryption key, you will get errors. You would therefore need to remember which encryption keys were used with which database.

Custom Settings

There are a number of settings that can be specified in the WEB-INF\art-custom-settings.json file. When settings in this file are changed, the Custom Settings cache needs to be cleared for them to take effect.

Setting	Data Type	Default	Description
showErrors	Boolean	true	Whether exception details are shown in the user interface
showErrorsApi	Boolean	true	Whether exception details are shown when making api calls
exportDirectory	String		Custom directory for export files i.e. files generated by reports and jobs. If blank, these files are placed in the WEB-INF\work\export directory.
workDirectory	String		Custom work directory for art files e.g. templates. If blank, the WEB-INF\work directory is used.
checkExportFileAccess	Boolean	false	Whether export files should be checked for user access before being accessed
enableGroovySandbox	Boolean	true	Whether to apply a sandbox when running groovy scripts
enableEmailing	Boolean	true	Whether sending of emails is enabled
jwtSecret	String		String used to sign JWT tokens used in API authentication. If blank, token based api authentication is disabled.
encryptionKey	String		Key used for encryption of fields in the application e.g. datasource passwords. Must be either 16 bytes (128 bits), 24 bytes (192 bits) or 32 bytes (256 bits) as per the AES algorithm.
encryptionPassword.password	String		Password used to generate the key for encryption of fields in the application. Can be any length. If specified, it will take precedence over the encryptionKey field.
encryptionPassword.keyLength	Integer	128	The desired key length when a password is used to generate the encryption key. Must be either 128, 192 or 256 as per the AES algorithm.
allowRepositoryLogin	Boolean	false	Whether to allow login using ART Database credentials
allowHttpHeaderAuthentication	Boolean	false	Whether to allow http header authentication
demo	Boolean	false	Whether the demo users in the demo database are listed in the login page
useCache	Boolean	true	Whether art objects e.g. users, reports etc are cached. A change in this setting would require an application restart.
enableCsrf	Boolean	true	Whether to enable CSRF protection. A change in this setting would require an application restart.
allowedCommandUrls	String[]		Array of pages that you can call while providing authentication details e.g. from curl. A change in this setting may require an application restart if enableCsrf is true.
oauthProviders	Object[]		Array of oauth provider options as per the format specified in the Settings section
allowUnicodeFileNames	Boolean	false	Whether to allow non-English characters in file names for reports
allowExtraInternalLogin	Boolean	false	Whether to attempt login using internal authentication details after an external authentication attempt has failed
allowInternalLogin	Boolean	true	Whether to allow login using internal authentication
oauthAutoLogin	Boolean	false	Whether to go directly to the oauth provider login page if only one oauth provider is set

Config Directory

You can specify a different location for the art-custom-settings.json file by using the art.configDirectory system property when starting the application server e.g. -Dart.configDirectory=C:\art. This property, if set, would also indicate where the groovy-whitelist.txt file will be looked for. These two files are never written to by ART, only read, and so the permissions for the directory and files can be set accordingly.

Datasources
- Creating a new datasource
- Options

Datasources

A datasource is a database against which you want to run reports. Use the Configure | Datasources menu to manage datasources.

datasources

Use the Add button to create a new datasource
Use the Edit and Delete buttons at the top to edit or delete multiple datasources and the buttons on the side to edit or delete individual datasources
Use the Search field to search for a particular datasource. You can also search by individual columns using the appropriate search fields below the column headings.
You can sort the datasources list by clicking on the column headings

Creating a new datasource

From the Datasources page, use the Add button and then specify the settings for the datasource. ART can run reports against any datasource for which a JDBC driver is available.

Field	Description
ID	An auto-generated ID used to identify the datasource
Name	A name to identify the datasource
Description	A description for the datasource
Active	Whether the datasource is available for use
Database Type	The database software that the database runs on
JNDI	Whether the datasource is a JNDI datasource
Database Protocol	An indication of the kind of syntax the database uses
JDBC Driver	The JDBC driver name
URL	The JDBC URL of the target database. If you are using a JNDI datasource, set this to the JNDI name of your datasource e.g. `jdbc/MyDatasource`, or the full JNDI url e.g. `java:comp/env/jdbc/MyDatasource`
Username	The database user on the target database. It is recommended that this user should have the least rights on the database and tables you plan to use in your reports, mostly only SELECT rights on the relevant tables.
Password	The password for the database user
Test SQL	A short SQL query that can be used to determine if a connection is OK e.g. `select 1`
Connection Pool Timeout (Mins)	How long in minutes an idle connection should be maintained in the connection pool before being closed
Options	Options for the datasource. The options are specified in JSON format.

Note:

Connections do not always close gracefully - for example if a network outage occurs a broken connection might stay in the pool and would throw an error when used. The Test SQL query is used every Timeout minutes to validate the connection. If it does not run successfully, the connection is closed and removed from the pool. If using a driver that supports the JDBC 4.0 specification, you can leave the Test SQL field blank for a more efficient connection test.

Options

A number of options can be specified in the Options field. These are defined in JSON format with the following possibilities.

Property	Data Type	Description
limitClause	String	The syntax of a "LIMIT" clause in an SQL statement e.g. "limit {0}". This would be used when the datasource is used in a view report. The "{0}" would be replaced with the limit value at run time. This option doesn't need to be specified if the Database Protocol field is set.
limit	Integer	The default limit to use when the datasource is used in a view report
hikariCp	Object	Allows for specifying some HikariCP configuration options e.g. `{ "hikariCp" : { "readOnly" : true} }`. If you would like to specify options to be used for all datasources, you can specify these options in the WEB-INF\classes\hikaricp.properties file.
queryTimeoutSeconds	Integer	For JDBC datasources, enables setting of a query timeout for reports that use this datasource. If the timeout expires before the query completes, the query execution will stop and an exception will be thrown. The value if defined should be >= 0, with 0 meaning no timeout.

Users
- Creating a new user

Users

Use the Configure | Users menu to manage users.

users

Use the Add button to create a new user
Use the Edit and Delete buttons at the top to edit or delete multiple users and the buttons on the side to edit or delete individual users
Use the Search field to search for a particular user. You can also search by individual columns using the appropriate search fields below the column headings.
You can sort the users list by clicking on the column headings

Note:

It is possible to log into ART by specifying the ART Database username and password. This can be disabled by changing the allowRepositoryLogin property in the art-custom-settings.json file.

Creating a new user

From the Users page, use the Add button and then specify the details for the user.

Field	Description
ID	Auto-generated ID used to identify the user
Username	A username for the user, used to login to the application
Password	The password for the user. You can use the Blank option to specify setting of a blank password. If you have configured an SMTP server in the Settings page, you can use the Generate and send option to generate a random password and send an email to the user informing him of his credentials.
Full Name	The user's full name for identification purposes
Email	The user's email address
Description	A description for the user
Active	Whether the user account is active or disabled
Can Change Password	Whether the user can change his password
Public User	Whether the user can run reports without having logged into ART first
Default Report Group	A report group that will be pre-selected in the reports page when the user logs in
Start Report	A report ID for a report that should be run when the user logs in. Parameters can be specified after the report id e.g. `1?p-param1=value1&p-param2=value2`
User Groups	Any user groups that the user should belong to
Roles	The roles that the user has

Authentication

Authentication

Use the Configure | Settings menu to define the authentication method to be used by ART.

Internal Authentication

Internal authentication authenticates users with a username and password combination defined and stored within ART. Users can change their passwords using the Password menu located under their username on the far right of the menu bar.

You can explicitly specify to login using internal authentication using a url like art/login?authenticationMethod=internal

Windows Domain Authentication

If the users are in a windows domain environment, you can have them log in to ART using the usernames and passwords they use to log in to windows.

Do the following to configure ART to use windows domain authentication.

In the Settings page, set the Default Authentication Method to Windows Domain

Set the Windows Domain Controller field to the domain controller machine name (IP address should also work). You can do the following to get the domain controller machine name.

ping <domain name> (This will get the ip address of the domain controller)
ping -a <ip address of domain controller> (This will get the hostname of the domain controller)

Set the Allowed Windows Domains field to the windows domain name

For each user who requires access to ART, you will also need to create a user within ART with the same username as their windows login username and grant them appropriate access to reports. The users can now log in to ART using their windows username and password.

You can explicitly specify to login using windows domain authentication using a url like art/login?authenticationMethod=windowsDomain

LDAP Authentication

Do the following to configure ART to use LDAP authentication.

In the Settings page, set the Default Authentication Method to LDAP
Provide values for the LDAP fields. If you fill the ldap server and port fields, leave the url field blank. If you use the url field, leave the server and port fields blank.

For each user who requires access to ART, you will also need to create a user within ART with the same username as their uid (or samAccountName for Active Directory). The users can now log in to ART using their ldap uid and password.

You can explicitly specify to login using ldap authentication using a url like art/login?authenticationMethod=ldap

HTTP Header Authentication

ART can have authentication done by another system and receive an authenticated via a http header. Do the following to use http header authentication.

Change the allowHttpHeaderAuthentication setting in \WEB-INF\art-custom-settings.json to true
In the Settings page, change the Default Authentication Method to HTTP Header
Set the HTTP Authentication Variable field to the name of the http header that will be used to pass the authenticated username

For each user who requires access to ART, you will also need to create a user within ART with the same username as will be passed in the http header.

You can explicitly specify to login using http header authentication using a url like art/login?authenticationMethod=httpHeader

OAuth Authentication

ART can authenticate users using an external OAuth 2.0 provider/server. Do the following to use oauth authentication.

In the oauth provider, create and configure an application that will allow your ART instance to authenticate users using the provider. See the oauth notes in the Settings section for the URLs to set.
In the Settings page, change the Default Authentication Method to OAuth
Set the OAuth fields and any other oauth JSON options in the Settings page or in the \WEB-INF\art-custom-settings.json file

For each user who requires access to ART, you will also need to create a user within ART with the same username as that in the oauth provider.

You can explicitly specify to login using oauth authentication using a url like art/login?authenticationMethod=oauth

Roles
- Creating a new role

Roles

Roles are used to group permissions that users have within the application. Use the Configure | Roles menu to manage roles.

Creating a new role

When creating a new role, the following fields are available

Field	Description
ID	An auto-generated ID used to identify the role
Name	A name for the role
Description	A description for the role
Permissions	The permissions included in the role

Note:

If you change the roles or permissions of a user, they will need to log out and log in again for the new permissions to take effect.

Permissions

Permissions

Permissions determine the actions that users can perform within the application. A user can be assigned individual permissions directly using the Configure | Permissions Configuration menu, or they can get their permissions through roles assigned to them or through permissions or roles assigned to a user group they belong to. Permissions within the application are fixed and include the following.

Permission	Description
view_reports	Allows a user to run reports
save_reports	Allows a user to save a PivotTable.js or Gridstack Dashboard report state
schedule_jobs	Allows a user to schedule jobs. He will also be able to view job output and job archives.
view_jobs	Allows a user to view job output and archives
configure_jobs	Allows a user to configure jobs. He will also be able to view job output and archives, view job log files and view running jobs.
view_logs	Allows a user to view application logs
configure_users	Allows a user to configure users
configure_settings	Allows a user to configure settings
configure_user_groups	Allows a user to configure user groups
configure_datasources	Allows a user to configure datasources
configure_reports	Allows a user to configure reports. He will also be able to view and cancel running queries. He can also configure drilldowns, rules, rule values, parameters, parameter default values and fixed parameter values.
configure_caches	Allows a user to clear caches
configure_connections	Allows a user to view connections
configure_loggers	Allows a user to configure loggers
configure_report_groups	Allows a user to configure report groups
configure_schedules	Allows a user to configure schedules
configure_holidays	Allows a user to configure holidays
configure_destinations	Allows a user to configure destinations
configure_access_rights	Allows a user to grant or revoke access rights
configure_user_group_membership	Allows a user to configure user group membership
configure_report_group_membership	Allows a user to configure report group membership
configure_smtp_servers	Allows a user to configure smtp servers
configure_encryptors	Allows a user to configure encryptors
migrate_records	Allows a user to import/export records
configure_roles	Allows a user to configure roles and role permissions
configure_permissions	Allows a user to assign permissions or roles to a user or user group and view permission usage
self_service_reports	Allows a user to create self service reports
configure_pipelines	Allows a user to configure pipelines
configure_start_conditions	Allows a user to configure start conditions

Access Rights
- Access Rights Configuration

Access Rights

Access rights determine which entities have rights to which objects.

Access Rights Configuration

Access rights can be granted or revoked in a number of ways and from a number of places, including using the Configure | Access Rights menu. From this page one can directly assign access rights for users or user groups to different reports, report groups or jobs. This is done by selecting the appropriate users or user groups and the different objects and then clicking on the Grant button. To revoke access, select the users and appropriate objects and then use the Revoke button. The Revoke All button can be used to revoke all access rights on a particular object, or for a particular user. The Show button can be used to view current access rights.

The primary purpose of access rights is to determine which users can run which reports. In summary, a user can run a report if

He directly has the configure_reports permission
He belongs to a role that has the configure_reports permission
It's an lov report
He has direct access to it
He belongs to a user group that has direct access to it
He has direct access to any of the report's groups
He belongs to a user group that has direct access to any of the report's groups

For jobs, by default only the owner of a job has access to its output, particularly for published jobs. Shared jobs allow the output of a job to be shared with other users. To share a job's output, enable the Allow Sharing field on the job and grant access to the job to the users or user groups with whom the output will be shared using the Access Rights Configuration page. These users will be able to access the job's output from the Jobs menu.

User Groups

Use the Configure | User Groups menu to manage user groups.

User groups are used to logically treat a set of users as one entity. If a group is granted access to a report, members of that group get access to the report. This allows for easier management of access rights. A user can belong to zero, one or many user groups.

Report Groups
- Creating a new report group
- Options
  - i18n Options

Report Groups

Report groups assist in assigning access rights to reports. Use the Configure | Report Groups menu to manage report groups.

Creating a new report group

When creating a report group, the following fields are available.

Field	Description
ID	An auto-generated ID used to identify the report group
Name	A name for the report group. It should not contain any of the following characters. `,;`.
Description	A description for the report group
Hidden	Whether the report group will be displayed in the reports page
Options	Options used to further configure the report group. The options are specified in JSON format.

Options

A number of options can be specified in the Options field. These are defined in JSON format with the following possibilities.

i18n Options

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

Property	Data Type	Description
name	Object[]	Enables localization of the report group name. 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. `"i18n": {"name": [{"en, sw": "Test Name"}, {"de": "Another Name"}] }`

Reports

Use the Configure | Reports menu to manage reports.

Creating a new report

Use the Add button from the Reports Configuration page to create a new report.

Field	Description
ID	Auto-generated ID used to identify the report
Name	Name of the report
Report Group	Report groups to which the report will be belong
Active	Whether the report can be run or not
Short Description	Short description of the report. For charts, this will appear as the title of the chart.
Description	Longer description of the report for the benefit of users
Type	The type of report
Comment	Developer comment
Contact Person	Can be used to store the name of the contact or reference person for the report
Code	An alternative name or code that can be used to run the report via url. ART doesn't enforce the uniqueness of this field.
Help Link	A URL to your own page that provides help with the report. If set, a Help button will be added to the select report parameters page, and the link will open in a new window/tab.
Hidden	Whether the report is listed to users or not
Datasource	The datasource against which the report will be executed
Uses Rules	This specifies if the report will use rules
Parameters In Output	This determines if the selected parameter values should be displayed in the report output
Display Resultset	The resultset to display if the sql source contains multiple sql statements. Leave as 0 if the sql source doesn't have multiple statements. Set to 1 to use the first statement, 2 to use the second, etc. Set to -1 to use the first select statement (first statement that returns a resultset), regardless of how many statements exist. Set to -2 to use the last statement, regardless of how many statements exist. Your RDBMS may not support multiple statements in a query or may require some configuration for it to work. See the Multiple Statements section for more details.
Default Report Format	The default report format that should be selected for this report. Please note that not all report formats are available for all report types.
Omit Title Row	For tabular reports, whether the title row should be omitted in xls, xlsx report formats
Hidden Columns	For tabular reports, a comma separated list of column indices or column names for columns that should not be included in the generated output
Total Columns	For tabular reports, a comma separated list of column indices or column names for columns that should be totalled in the generated output. You can use the special name all to specify totalling of all numeric columns.
Date Format	For tabular reports, the format that should be used for date fields. Leave blank to use the default. The format is as per Java SimpleDateFormat format.
Time Format	For tabular reports, the format that should be used for time fields. Leave blank to use the default. The format is as per Java SimpleDateFormat format.
Number Format	For tabular reports, the format that should be used for numeric columns. Leave blank to use the default.
Column Formats	For tabular reports, the formats that should be used for specific date or numeric columns. Each specification comes in a new line, in the format `<column index or column name>:<format>`. Examples `1:dd/MM/yyyy` or `due_date:dd MMM yyyy` or `amount:#,##0.00`. You can specify multiple column names or indices separated by comma e.g. `1,3 : dd/MM/yyyy` or `unit_price, total_cost: #,##0.00`.
Null Number Display	For tabular reports, a string that should be used for numeric columns where the value is null. Leave blank to use the default. For xls and xlsx report formats, this setting has no effect.
Null String Display	For tabular reports, a string that should be used for string columns where the value is null. Leave blank to use the default.
Escape HTML	For tabular reports, whether the output should be html encoded when using html report formats
Locale	For tabular reports, the locale to be used when formatting dates and numbers e.g. `de` or `en_US`. Leave blank to use the default. For xls and xlsx report formats, this setting has no effect. For fixed-width and csv report types, this determines how the date is rendered.
Fetch Size	The number of rows retrieved by the database driver per trip to the database. Leave as 0 to use the driver's default. This value represents a trade-off between memory and query execution time and may be useful to set for reports that return large amounts of data in order to avoid out-of-memory errors. Not all databases honour this setting.
Page Orientation	The orientation to be used in generated documents e.g. with pdf output
LOV Use Dynamic Datasource	For dynamic lov reports, whether the lov should use a dynamic datasource parameter when the lov is a chained parameter
Open Password	For reports that can be exported to pdf, a password that is to be required in order to open the pdf file. In addition, for tabular reports, a password that is to be required in order to open a file with xlsx, docx output. Blank (empty string) passwords are not allowed and will result in no password being set i.e. the file will not request for a password when opening. You can use the None option to remove a password setting that was set previously.
Modify Password	For reports that can be exported to pdf, a password that can be used in order to modify certain aspects of the file (the owner password). In addition, for tabular and group reports, a password that is to be required in order to modify xlsx output. The xlsx file generated will be read-only and will require a password in order to unprotect the worksheet. Blank (empty string) passwords are not allowed and will result in no password being set i.e. the file will not request for a password when editing. You can use the None option to remove a password setting that was set previously.
Encryptor	An encryptor that should be used to encrypt or password protect report output
x-axis Label	For charts, the x axis label
y-axis Label	For charts, the y axis label
Width	For charts, the chart width
Height	For charts, the chart height
Background Colour	For charts, the background colour
y-axis Min	For charts, the minimum value for the y-axis. Leave as 0 to use the full data range.
y-axis Max	For charts, the maximum value for the y-axis. Leave as 0 to use the full data range.
Rotate x-axis labels at	For charts, the number of categories at which the labels will be displayed vertically instead of horizontally. Set to 1 to always display labels vertically.
Remove x-axis labels at	For charts, the number of categories at which labels are omitted from the chart. Set to 1 to always omit labels.
Secondary Charts	For charts, a comma separated list of report IDs of other charts which are to be displayed with this one. These charts will use separate y-axes. Some chart types cannot have secondary charts, including pie charts, speedometer, bubble and heat map charts.
Template	The template file to use for the report
Source Report	A report ID of a report which should be used to provide the report source for this report
Link	For the link report type, the link to open. If it is an external url, it needs to start with the protocol section e.g. `http://` or `https://`.
Open In New Window	For link reports, whether the link will open in a new window when clicked from the ART home page
Max Running Reports	The maximum number of concurrent runs allowed for this report. A value of 0 means no maximum.
Max Running Reports Per User	The maximum number of concurrent runs allowed for this report per user. A value of 0 means no maximum.
Max Running Reports For Datasource	The maximum number of concurrent report runs on the configured datasource while this report is running. A value of 0 means no maximum.
Report Formats	Comma separated list of report formats to be displayed for the report
Run Immediately	Whether to run a report immediately when it's link is clicked in the home/reports page, instead of showing the parameters page
Use Groovy	Whether the report source is groovy rather than SQL
Saved Options	Dynamically generated options e.g. when saving the state of a PivotTable.js report
Javascript	Additional configuration in Javascript syntax
CSS	Additional css to be used with the report
Javascript After	Additional Javascript after the report has been initialized
Options	Provides additional options depending on the report type. The options are specified in JSON format with different report types expecting different JSON specifications.
Source	The SQL query used to retrieve the required data

Note:

You can use a stored procedure to return query results. When defining the query, in the SQL source section, use the syntax for calling a stored procedure in the RDBMS you are using e.g. for MySQL you can use something like "call my_sp".

Report Types

Tabular

A tabular result exportable to spreadsheet, pdf etc.

Tabular (html only)

A tabular result that can only be displayed in HTML format. This may be used to embed HTML code in the SQL query in order to modify display colours of certain columns etc. To do this, concatenate the SQL with the required HTML tags. e.g. For MySQL, to display positive values in green and negative values in red, you can use something like

    SELECT col1,
    CASE WHEN int_col2>0 THEN
    concat("<div style='background-color: green'>",cast(int_col2 as char),"</div>") 
    ELSE
    concat("<div style='background-color: red'>",cast(int_col2 as char),"</div>") 
    END as "My Formatted Column",
    col3
    from my_table

Charts

Display the results as a chart, exportable to pdf or png.

The layout of the SQL must follow a specific syntax for each different type of chart

XY

    SELECT Value1, Value2 "SeriesName" FROM ...
    (data type: number, number )

Dynamic Series

    SELECT Value1, Value2, SeriesName FROM ...
    (data type: number, number, string)

Pie

    SELECT Category, Value FROM ...
    (data type: string, number )

Bars/Stacked Bars/Line

Static Series

    SELECT Item, Value1 "SeriesName1" [, Value2, ...] FROM ...
    (data type: string, number [, number, ...] )

Dynamic Series

    SELECT Item, SeriesName, Value FROM ...
    (data type: string, string, number)


    Example:
    SELECT Product, Region, SUM(VOLUME) FROM sales group by product,region

Time/Date Series

Static Series

    SELECT Timestamp|Date, Value1 "SeriesName1" [, Value2, ...] FROM ...
    (data type: timestamp|date, number, [, number, ...] ). Timestamp/Dates must be unique.

Dynamic Series

    SELECT Timestamp|Date, SeriesName, Value FROM ...
    (data type: timestamp|date, string, number). Timestamp/Dates must be unique.


    Example:
    SELECT ORDER_DATE, PRODUCT, SUM(VOLUME) FROM orders group by order_date,product

Speedometer

    SELECT DataValue, MinValue, MaxValue, UnitsDescription [, Range1, Range2, ...] FROM ...
    (data type: number, number, number, string)
    Ranges represent optional columns and each range has 3 values separated by :  i.e.
    RangeUpperValue:RangeColour:RangeDescription (data type: number, string, string).
    RangeUpperValue can be a percentage.


    Example:
    SELECT reading, 0, 100, "degrees",
    "50:#00FF00:Normal",
    "80%:#FFFF00:Warning",
    "100:#FF0000:Critical"
    FROM temperature_reading

Chart Options

Certain options can be specified in the Options field to configure the chart output.

Property	Data Type	Default	Description
seriesColors	Object		Defines specific colours to be used for given chart series. Each property of the object consists of the series ID as the property name and the hex colour code as the property value. Multiple properties can be specified e.g. `"seriesColors": {"0" : "#ff4a53", "2" : "#4a538a"}`. The first series has an ID of 0.
dateFormat	String		For date series and time series charts, defines the date format to be used on the x-axis, overriding the one automatically assigned by the chart.
dynamicSeries	Boolean	false	Whether the data represents a dynamic series chart
itemLabelAnchor	String	OUTSIDE12	Determines the positioning of data label values. Possible values is as per the JFreeChart ItemLabelAnchor class.

Note:

ART uses the cewolf and jfreechart libraries to generate charts. These libraries in turn use standard java AWT to plot charts. In order to work correctly, AWT needs a graphic display. If you are using a "headless" workstation (i.e. a Unix box without X) you need to start the JVM with the option -Djava.awt.headless=true
You can specify specific colours to be used for the series displayed in a chart by adding columns to the query resultset in the format seriesColor:<series index>:<hex color code> e.g. for a pie chart,
```
SELECT description, volume, "seriesColor:0:#ff8000"
FROM orders
```

Update Statement

Used to execute statements that do not return any rows e.g. INSERT/UPDATE/DELETE statements

Text

Used to define a piece of text that can be displayed

Dashboard

Used to display multiple reports in a single page

Dashboard: Gridstack

Used to display multiple reports in a single page, allowing for specifying exact positioning of report items within the dashboard, specifying the height and width that the report items should occupy, allowing for resizing the items dynamically using the mouse, and moving the items around using drag-and-drop.

JasperReports: Template Query

To generate formatted reports e.g. documents given to customers, you can use JasperReports. To create a jasper report, use the JasperSoft Studio report designer.

Displays a jasper report based on the selected jrxml template. The query within the jasper report template will be used to generate the results. The query will be run against the selected datasource. You can define parameters that will be passed to the jasper report. Jasper report parameters are defined with a specific type. The following mapping of ART and jasperreport parameter types should be used.

ART parameter type	JasperReports parameter type
Varchar, Text	java.lang.String
Date, DateTime	java.util.Date
Integer	java.lang.Integer
Double	java.lang.Double
Multi-Value parameters	java.util.List

If the report contains a subreport, set the Subreport Expression property of the subreport object to the file name of the subreport with a .jasper extension e.g. "subreport1.jasper" (include the quotes). When creating the report in ART, upload the main report's .jrxml file using the main template field and upload the subreport's .jrxml file using the resources field. If there are multiple subreports, you can select multiple files and upload all of them at once using the Start upload button. You can also select files by dragging from the file explorer and dropping to the browser page. Once you have uploaded the subreports, also specify the file names in the Options field using the subreports property that takes a list of strings e.g. {"subreports": ["subreport1.jrxml", "subreport2.jrxml"]}.

JasperReports: ART Query

Displays a jasper report based on the selected jrxml template. The query as defined in the SQL source will be used to generate the results. The query will be run against the selected datasource.

Jxls: Template Query

Displays a report in MS Excel format(xls or xlsx) based on a pre-formatted Jxls template. The query within the Jxls template will be used to generate the results. The query will be run against the selected datasource. More details about Jxls reports can be found in the Jxls Reports section of the manual.

Jxls: ART Query

Displays a report in MS Excel format(xls or xlsx) based on a pre-formatted Jxls template. The query as defined in the SQL source will be used to generate the results. The query will be run against the selected datasource.

FreeMarker

Displays a report in the browser based on a FreeMarker template. The query as defined in the SQL source will be used to generate the results. The data can be accessed in the template by iterating over the results variable. If you are sure a particular column will never have null values, you can use the simple dot notation to access it e.g. ${result.description} to access a column in the result set named "description". If the column can have null values, use the default-value operator "!" after the column name e.g. ${result.description!}. (See http://freemarker.org/docs/dgui_template_exp.html#dgui_template_exp_missing). Report parameters are also available using the parameter name e.g. ${param1.value}. Variables named contextPath and artBaseUrl are available e.g. to include css or javascript files. artBaseUrl is only available if it is set in the Settings page and contextPath is only available for interactive reports and is not available with jobs.

A number of options can be specified in the Options field.

Property	Data Type	Default	Description
extension	String	txt	The extension to use for generated file names

Velocity

Displays a report in the browser based on a Velocity template. The query as defined in the SQL source will be used to generate the results. The data can be accessed in the template by iterating over the results variable. If you are sure a particular column will never have null values, you can use the simple dot notation to access it e.g. ${result.description} to access a column in the result set named "description". If the column can have null values, use quiet reference notation e.g. $!{result.description}. (See https://velocity.apache.org/engine/2.0/user-guide.html#quiet-reference-notation). Report parameters are also available using the parameter name e.g. ${param1.value}. Variables named contextPath and artBaseUrl are available e.g. to include css or javascript files. artBaseUrl is only available if it is set in the Settings page and contextPath is only available for interactive reports and is not available with jobs.

A number of options can be specified in the Options field.

Property	Data Type	Default	Description
extension	String	txt	The extension to use for generated file names

Thymeleaf

Displays a report in the browser based on a Thymeleaf template. The query as defined in the SQL source will be used to generate the results. The data can be accessed in the template by iterating over the results variable e.g. ${result.description} to access a column in the result set named "description". Report parameters are also available using the parameter name e.g. ${param1.value}. Variables named contextPath and artBaseUrl are available e.g. to include css or javascript files. artBaseUrl is only available if it is set in the Settings page and contextPath is only available for interactive reports and is not available with jobs.

A number of options can be specified in the Options field.

Property	Data Type	Default	Description
extension	String	txt	The extension to use for generated file names

PivotTable.js

Generates a pivot table based on data from a database - making use of the PivotTable.js library. Javascript for custom configuration can be specified in the Javascript field.

LOV: Dynamic

Creates an LOV query whose values are based on an sql query. The query can have either one column, or two columns if the parameter value and the text displayed to the user needs to be different

    SELECT city_id, city_name
    FROM cities

    SELECT city_id
    FROM cities

If using groovy data, return a List of the values to be used, or if you require the value and display text to be different, return a List of GroovyRowResult or DynaBean where the first column is the value to be used and the second is the display text. If using a Map, return a List of Maps whose keys are the display text and whose values are the values to be used.

LOV: Static

Creates an LOV query whose values are set in the sql source section. Values are separated by new lines. If the value and display text need to be different, separate the two with a |

    Toaster
    pr-01|Laptops
    pr-02|Desktops

Dynamic Job Recipients

Defines a query that can be used to specify dynamic recipients when scheduling a job. It can have either one column, or multiple columns. In either case, the first column must contain email addresses for the recipients.

Dygraphs

Generates a time series chart using data from a database - making use of the dygraphs library. Javascript for custom configuration can be specified in the Javascript field. The query must have a minimum of two columns. The first can be a date or number field, and will be the data on the x-axis. The second column must be a number and will represent the first series data. Additional columns of number type can be included, which will be data for additional series. There should be no record with a null date in the query result. If you zoom into the data by clicking and dragging, you double-click on the chart to zoom out.

DataTables

Enables display of data from a database in a table using the DataTables library. Javascript for custom configuration can be specified in the Javascript field. In the Options field, there are a number of attributes that can be specified.

Property	Data Type	Default	Description
outputDateFormat	String		The format of output data for date columns. If blank, the data will be output as is. The format is as per the Java SimpleDateFormat format.
outputDateTimeFormat	String		The format of output data for datetime columns. If blank, the data will be output as is. The format is as per the Java SimpleDateFormat format.
showColumnFilters	Boolean	false	Determines if column filters will be displayed
dtOptions	Object		Options related to the DataTables output. Available options are as per the DataTables documentation. Where a `<th>` column class name is required, you can use `rcol-<column name>`. Spaces and other characters apart from numbers, letters, underscores and hyphens will be replaced with a hyphen; so for a report column named `Due Date`, the class name to use would be `rcol-Due-Date`.

CSV

Generates csv output. The Options field can be used to modify the format of the output and it can have the following properties.

Property	Data Type	Default	Description
delimiter	String	,	The delimiter to use
quote	String	"	The character to use to quote fields in which the delimiter appears
quoteAllFields	Boolean	false	Whether all the data should be quoted, whether the data contains the delimiter or not
extension	String	csv	The file name extension to use

TSV

Generates tsv output. The Options field can be used to modify the format of the output and it can have the following properties.

Property	Data Type	Default	Description
escapeChar	String	\	The character used to escape special characters in TSV
extension	String	tsv	The file name extension to use

C3.js

Generates charts using the C3.js library. The demo database contains some sample reports of this type. A number of options can be specified in the Options field. Also, javascript for chart configuration can be specified the Javascript field and custom css can be specified in the CSS field.

Property	Data Type	Default	Description
chartTypes	String[]		Chart types that should be available for dynamic changing of the chart type. Possible values include line, spline, step, area, areaSpline, areaStep, bar, scatter, pie, donut, gauge or all.
x	String		For bar and line charts, the column to use as the x-axis category
value	String[]		The columns to use for data values
type	String		The initial chart type. Possible values include line, spline, step, area, area-spline, area-step, bar, scatter, pie, donut, gauge.
groupedTooltip	Boolean	true	Whether to show grouped tooltips
showLegend	Boolean	true	Whether to show the legend
rotatedAxis	Boolean	false	Whether the axis should be rotated. For bar charts, setting to true will result in a horizontal bar chart.
showTooltip	Boolean	true	Whether tooltips should be shown
legendPosition	String		The legend position. One of bottom, right, inset.
width	Integer		The width of the chart in pixels
height	Integer		The height of the chart in pixels
xAxisLabel	String		The x axis label
yAxisLabel	String		The y axis label
xAxisLabelPosition	String		The x axis label position. See http://c3js.org/reference.html#axis-x-label
yAxisLabelPosition	String		The y axis label position. See http://c3js.org/reference.html#axis-x-label
groups	String`[][]`		Description of columns that should be grouped e.g. to get a stacked bar chart. See http://c3js.org/reference.html#data-groups

Plotly.js

Generates charts using the plotly.js library. A number of options can be specified in the Options field. Also, javascript containing chart configuration can be specified in the Javascript field.

Property	Data Type	Default	Description
barmode	String		Set to stack to get a stacked bar chart
xColumn	String		The column to use as the x-axis category
yColumns	String[]		The columns to use for data values
type	String		The plotly type e.g. pie, scatter, bar
mode	String		The plotly mode e.g. lines, lines+markers
chartTypes	String[]		Chart types that should be available for dynamic changing of the chart type. Possible values include line, bar, scatter, pie, donut.
title	String		The chart title
xAxisTitle	String		The x axis title
yAxisTitle	String		the y axis title
showLegend	Boolean	true	Whether to show the legend
showText	Boolean	false	For bar charts, whether the values should be shown on the bars
textPosition	String		With showText as true, the position of the values. Sets the plotly textposition property.
width	Integer		The width of the chart in pixels
height	Integer		The height of the chart in pixels
hole	Double (0.0 - 1.0)		For donut charts, the size of the hole in the middle of the chart
bundle	String		Use cartesian to use charts available in the cartesian bundle. See https://github.com/plotly/plotly.js/tree/master/dist
orientation	String		Use h to get horizontal bar charts
hoverInfo	String		Determines what is shown in tooltips. Sets the plotly hoverinfo property.
pieValueColumn	String		For tabular reports, the column that should be used for pie chart values. If not specified, the last numeric column in the data will be used.

Chart.js

Generates charts using the Chart.js library. Javascript for chart configuration needs to be specified in the Javascript field. Also, a number of options can be specified in the Options field.

Property	Data Type	Default	Description
width	Integer	300	The width of the chart in pixels
height	Integer	100	The height of the chart in pixels

Leaflet

Enables display of data on a web map or tiled map using the Leaflet library. Javascript for the leaflet configuration needs to be specified in the Javascript field and custom css can be specified in the CSS field. In the Options field, there are a number of attributes that can be specified.

Property	Data Type	Default	Description
height	String	400px	CSS height for the div that contains the map
dataFile	String		The file name of a file that contains extra data to be used with the map e.g. a GeoJSON file. This file can be uploaded using the Add files option.
jsFiles	String[]		File names of javascript files that need to be incorporated for the map display e.g. leaflet plugins. The files can be uploaded using the Add files option.
cssFiles	String[]		File names of additional css files to be used with the display. The files can be uploaded using the Add files option.

OpenLayers

Enables display of data on a web map using the OpenLayers library. Javascript for the openlayers configuration needs to be specified in the Javascript field and custom css can be specified in the CSS field. In the Options field, there are a number of attributes that can be specified. These attributes are the same as those for Leaflet reports.

View

Used as a basis for creating self service reports. The view report will define the columns that should be available for selection and the joins and basic conditions for the reports. The report source will contain placeholders to be replaced when the view or self service reports are run. Examples are given below.

Example with a MySQL datasource

    select #columns#
    from my_table
    where #condition#
    #limitClause#

Example with an SQL Server datasource

    select #limitClause# #columns#
    from my_table
    where #condition#

Example with more a complex base query

    select #columns#
    from my_table a
    inner join my_other_table b
    on a.id=b.id
    where a.name like 'S%' and #condition#
    #order#
    #limitClause#

The #columns# placeholder will be replaced with the selected columns. The #condition# placeholder will be replaced by the selected report conditions. The #limitClause# placeholder will be replaced by the number of records to display when previewing the view or self service report and needs to be placed in the appropriate place according to the database in use. The #order# placeholder will be replaced by the selected order configuration and corresponds to the ORDER BY clause.

A number of options can be configured in the Options field. This would be specified under a property named view in the JSON definition.

Property	Data Type	Default	Description
columns	String	*	The specification of the columns to be made available for selection e.g. `col1, col2`
omitColumns	String[]		Column names that should be omitted from the list of available columns
columnLabels	Object[]		Specification of column labels. Each object specifies a column name as the property and the column label as the value.
columnDescriptions	Object[]		Specification of column descriptions. Each object specifies a column name as the property and the column description as the value. The column description is displayed as a tooltip when selecting columns.
conditionColumns	String[]		Column names that should be included in the list of condition columns. If not specified, all available columns will be included in the conditions list.
omitConditionColumns	String[]		Column names that should be omitted from the list of condition columns
sortColumns	Boolean	true	Whether the available columns and condition columns should be sorted
valueSeparator	String	,	The character used to separate multiple values in conditions where multiple values may be specified e.g. when using the in operator.
filterOptions	Object`[]`		Specification of filter options for particular condition columns as per the jQuery QueryBuilder Filters documentation. Each object would have a column property which would specify the name of the column and an options property which would be an object as per the jQuery QueryBuilder documentation e.g. `"filterOptions":[{"column": "myid", "options": {"input": "number"}}]`. You can use the column name all to specify options that should be applied to all filters.
limitClause	String		The syntax of the limit clause e.g. `limit {0}`. The placeholer "{0}" will be replaced with the configured limit value. This option can be specified on the datasource so that it doesn't need to be repeated for every view. It is also not necessary to specify it for common databases.
limit	Integer		The number of records to return when testing/running the view. The view report is not intended to be used as an actual report and so if this option is not defined, the view report will return a maximum of 10 records when you test/run it. This limit is not applied to the self service reports that will be created, but is only applicable for the view report.

Link

Opens a link as provided in the Link field.

Note:

If the output for a report creates a file e.g. pdf, this file is stored in the WEB-INF\work\export directory

Javascript Field

For reports that make use of the Javascript field, a number of variables are typically available to be used or set.

Variable	Type	Available/To be set	Description
jsonData	Javascript	Available	Data for the report. An array of objects representing the rows from the report query.
configOptions	Javascript	To be set	Used to specify config options in accordance with the specific report type, especially javascript charts
reportParams	Javascript	Available	Javascript object containing report parameter values
report	Java	Available	A java variable accessed using JSTL syntax. Contains the report object.

Parameter Values

Parameter values can be accessed in the javascript field by use of the reportParams variable, with the properties of the object being parameter names. An example of getting the contents of a parameter named param1 is as follows.

var param = reportParams.param1;
console.log(param);

Multi value parameters will be represented as an array of values, while for date or time parameters, one would need to use new Date(param) to get a date object out of the value.

General Report Options

A number of options can be specified in the Options field of a report that are applicable to several report types. These options are defined in JSON format with the following possibilities.

General Options

Property	Data Type	Description
c3	Object	Allows for custom configuration of C3 Chart output with tabular reports. The properties are same as those specified for the C3.js report type.
plotly	Object	Allows for custom configuration of Plotly Chart output with tabular reports. The properties are same as those specified for the Plotly.js report type.
refreshPeriodSeconds	Integer	Enables a report to be re-run or refreshed automatically after the first run. Minimum is 5 seconds.
queryTimeoutSeconds	Integer	Enables setting of a query timeout for a report that uses a JDBC query. If the timeout expires before the query completes, the query execution will stop and an exception will be thrown. The value if defined should be >= 0, with 0 meaning no timeout.
fileName	String	Allows for specifying the base file name that should be used for report output. Parameter values can be included by specifying the parameter name enclosed with # e.g. `#param1#`. To include the default generated name, use the tag `{default}`. To include the localized report name, use the tag `{reportName}`. To include the burst id when run in a Burst Job, use the tag `{burstId}`. This base file name does not include the extension. The extension will be automatically provided depending on the report and report format.
run	String	Enables a report to be run immediately it's link is clicked in the home/reports page. Additional url options can be specified in this option e.g. `"run": "allowSelectParameters=true"` or `"run": "allowSelectParameters=true&startSelectParametersHidden=true"`
allowShowSql	Boolean	Whether to display the Show SQL option when running the report. Default is true. Can also be set in the Settings Options to specify a default for all reports.
allowShowSelectedParameters	Boolean	Whether to display the Show Selected Parameters option when running the report. Default is true. Can also be set in the Settings Options to specify a default for all reports.
allowSaveSelectedParameters	Boolean	Whether to display the Save Parameter Selection option when running the report. Default is false. Can also be set in the Settings Options to specify a default for all reports.

i18n Options

An option named i18n can be specified to provide localization of some aspects of the report. This option is an object and has the following properties. Some properties may only be applicable to Tabular reports.

Property	Data Type	Description
name	Object[]	Enables localization of the report name. It is an array of objects where 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. `"i18n": {"name": [{"en, sw": "Test Name"}, {"de": "Another Name"}] }`
shortDescription	Object[]	Enables localization of the short description, and is similar to the name property
description	Object[]	Enables localization of the description, and is similar to the name property
columnNames	Object[]	Enables localization of column names e.g. `"i18n": { "columnNames": [ {"col1": [{"lt": "Some name"}]}, {"2": [{"lt": "A name"}]} ]}`. You can specify a column name e.g. "col1" or a column index e.g. "2", the first column being index 1
data	Object[]	Enables localization of data output e.g. `"i18n": { "data": [ {"Shop": [{"sw": "Duka"}]}, {"house": [{"fr": "maison"}, {"sw": "nyumba"}]} ]}`. The data items you specify for translation are case sensitive.

PDF Options

For report types that can generate pdf output, the following options can be specified.

Property	Data Type	Default	Description
pdfCanPrint	Boolean	true	Whether the Print menu is enabled in the generated pdf
pdfCanCopyContent	Boolean	true	Whether one can select and copy text in the generated pdf
keyLength	Integer	128	The encryption key length to be used. Can only be either 40, 128 or 256.
preferAes	Boolean	true	Whether the AES algorithm should be used if available

Clone Options

For clone reports (reports where the Source Report field points to some parent report ID), the following options can be specified.

Property	Data Type	Default	Description
useParentParameters	Boolean	true	Whether the clone report will use parent report's parameters, or if false, use its own parameters.
useParentRules	Boolean	true	Whether the clone report will use the parent report's rules, or if false, use its own rules.

Tabular Options

For tabular report types, the following options can be specified.

Property	Data Type	Description
imageColumns	String[]	An indication of the column indices or column names for blob columns that contain images. The first column has an index of 1. e.g. `["2"]` or `["image"]` or `["3", "image"]`.
dtOptions	Object	Options to be used with the htmlDataTable report format. Available options are as per the DataTables documentation. Where a `<th>` column class name is required, you can use `rcol-<column name>`. Spaces and other characters apart from numbers, letters, underscores and hyphens will be replaced with a hyphen; so for a report column named `Due Date`, the class name to use would be `rcol-Due-Date`.
dtExtraOptions	Object	Extra options for the htmlDataTable report format
excel	Object	Extra options for xls and xlsx report formats

CSV Format Options

For Tabular reports, the following options can be specified for use with the csv report format.

Property	Data Type	Default	Description
omitHeaderRow	Boolean	false	Whether the header row should be omitted
delimiter	String	,	The delimiter to use
quote	String	"	The character to use to quote fields in which the delimiter appears
quoteAllFields	Boolean	false	Whether all the data should be quoted, whether the data contains the delimiter or not
extension	String	csv	The file name extension to use
lineSeparator	String	Operating System line separator	For csv and tsv report formats, the line separator character to use. e.g. `"\n"` for LF or `"\r\n"` for CRLF

HtmlDataTable Extra Options

For Tabular reports, an option named dtExtraOptions can be specified to provide some extra configuration of how data table output will be displayed. This option can also be specified in the Settings page, under the Options field to give a default to be used for all tabular reports. The object has the following properties.

Property	Data Type	Default	Description
pdf	Boolean	false	Whether pdf export will be available in the output
fixedHeader	Boolean	false	Whether the header row should be fixed when scrolling down
options	Object		DataTable options as per the DataTables documentation e.g. setting the `pageLength` option in the Settings options can change the default pagelength for all tabular reports. For the `lengthMenu` option, just use a 1D array with -1 as the placeholder for the All option e.g. `[10, 20, -1]`.

Excel Options

For Tabular reports, an option named excel can be specified to provide some extra configuration of how xls or xlsx output will be displayed. This option can also be specified in the Settings page, under the Options field to give a default to be used for all tabular reports. The object has the following properties.

Property	Data Type	Default	Description
autoWidth	Boolean	false	Whether all the columns should have the widths adjusted to fit the largest content
autoWidthColumns	String[]		The indices or names of columns to have widths adjusted to fit the largest content. The first column is index 1.
autoFilter	Boolean	false	Whether to have column filters in the generated output
omitTitleRow	Boolean	false	Whether to omit the title row that has the report name and execution time
fixedHeader	Boolean	false	Whether the header row should be fixed when scrolling down
sheetName	String		The worksheet name in the output file. Default is the report name.
columnWidths	Object		An object indicating column indices or names and their widths e.g. `"columnWidths: {"1": 2000, "D": 5000}`. The first column index is 1.

Template Result Options

For jxls, freemarker, velocity and thymeleaf report types, the following options can be specified to determine the column names to use with the results object.

Property	Data Type	Default	Description
useColumnLabels	Boolean	true	Whether to use column labels (aliases) as the property names. If false, column names will be used.

Running a report via URL

It is possible to run a report directly via URL. The report format and parameters are included in the URL. The basic URL for running a report is something like http://server:port/art/runReport?reportId=x&other_parameters

If direct URL access is needed without requiring the user to log in to ART beforehand, public=true must be included in the URL together with a user parameter indicating the username under whose permissions the report will be run. In addition, this user must have the Public User field set to Yes. e.g. http://localhost:8080/art/runReport?reportId=1&public=true&user=test

Report Options

Some report options can be specified in the URL to determine how the run should be handled.

Option	Data Type	Comments
reportId	Integer	The id of the report to run
reportName	String	The name of the report to run. If present, will be used instead of the report id.
reportCode	String	The code of the report to run. If present, will be used instead of the report id or report name.
p-{parameter_name}	String	Specifies report parameters e.g. `p-duedate=2010-05-06`
reportFormat	String	Specifies the report format that should be used for the output generated by the report e.g. `reportFormat=pdf`. Not all report formats are available for all report types.
public	Boolean	If present, determines that the user doesn't need to be logged in to ART to view the report results e.g. `public=true`. If authentication is required, don't include this parameter.
showSelectedParameters	Boolean	If present, indicates that the used or selected report parameters should be included in the report output e.g. `showSelectedParameters=true`. Omit the parameter entirely if this is not required.
showSql	Boolean	If present, indicates that the final SQL used to generate the report results is shown in the browser e.g. `showSql=true`. Omit the parameter entirely if this is not required.
allowSelectParameters	Boolean	If present, indicates that the parameters box should be displayed in the browser to allow the user to re-run the report by changing the reports parameter values.
startSelectParametersHidden	Boolean	If present, indicates that the parameters box should start as hidden/collapsed. The parameters box can then be displayed by clicking on the Parameters button.
refreshPeriodSeconds	Integer	Enables a report to be re-run or refreshed automatically after the first run. Minimum is 5 seconds.
showColumnFilters	Boolean	For the htmlDataTable report format, if present indicates that column filters should be included in the output
isFragment	Boolean	If set to true e.g. `isFragment=true`, the report information header and footer showing the start time of execution and the number of rows retrieved will not be shown.
headerOnly	Boolean	For tabular reports, if present indicates that only the header row should be output
tabular	Boolean	Allows running non-tabular report types as Tabular, giving the data from the sql query as the output. Not all report types can use this option.

Chart Options

For chart reports, additional options specific to charts can be specified in the URL.

Option	Data Type	Comments
showLegend	Boolean	If present, indicates that the legend should be shown e.g. `showLegend=true`. Omit the parameter entirely if this is not required.
showLabels	Boolean	If present, indicates that data labels should be shown e.g. `showLabels=true`. Omit the parameter entirely if this is not required.
showData	Boolean	If present, indicates that the data used to generate the chart should be shown e.g. `showData=true`. Omit the parameter entirely if this is not required.
showPoints	Boolean	If present, indicates that data points on the chart should be highlighted e.g. `showPoints=true`. Omit the parameter entirely if this is not required.
rotateAt	Integer	Indicates the number of categories after which the x-axis labels will be displayed vertically instead of horizontally e.g. `rotateAt=1`. This may aid in the readability of the labels for some charts.
removeAt	Integer	Indicates the number of categories after which the x-axis labels will be removed completely e.g. `removeAt=10`.
chartWidth	Integer	Indicates the width of the chart in pixels e.g. `chartWidth=1000`. The maximum is 2048.
chartHeight	Integer	Indicates the height of the chart in pixels e.g. `chartHeight=700`. The maximum is 1024.
yAxisMin	Double	Indicates the minimum value of the y-axis e.g. `yAxisMin=-10.5`.
yAxisMax	Double	Indicates the maximum value of the y-axis e.g. `yAxisMax=100`
backgroundColor	String	Indicates the hex colour code for the background color of the chart e.g. `backgroundColor=#FFFFFF`.
labelFormat	String	Indicates the format of data labels e.g. `labelFormat={0} = {1} ({2})` or `labelFormat=off`. If set to "off", this indicates that labels should not be shown. For pie charts, `{0}` will display the pie section key, `{1}` will display the absolute section value and `{2}` will display the percent amount of the pie section. Setting it to `{0} = {1} ({2})` would display a string like "Laptops = 17 (42%)" for a section of the pie chart showing laptops numbering 17 and having a percentage value of 42%. For bar charts, use `{2}` to display the data value.

Examples

Example 1: (single-value parameters)

    http://server:port/art/runReport?
    reportId=120&reportFormat=html&p-startdate=2006-04-04&p-description=Desktop


    runs report 120 in html format.
    The #startdate# parameter (p-startdate) is set to 2006-04-04 
    and the #description# parameter (p-description) is set to "Desktop"

Example 2: (chart)

    http://server:port/art/runReport?
    reportId=121&p-date=2006-04-04&showLegend=true

Example 3: (pdf, public)

    http://server:port/art/runReport?
    reportId=123&reportFormat=pdf&public=true&user=guest


    generates report as a pdf file without requiring the user to log in to ART

Example 4: (show selected parameters)

    http://server:port/art/runReport?
    reportId=125&reportFormat=xls&p-startdate=2006-04-04&showSelectedParameters=true

    generates report and includes the parameter value used in the report output

Example 5: (multi-value parameters)

    http://server:port/art/runReport?
    reportId=126&reportFormat=xls&p-category=Laptops&p-category=Desktops&p-category=Tablets

    the #category# multi-value parameter (p-category) has 3 values defined: 
    Laptops, Desktops, Tablets

Example 6: (default parameter values)

    http://server:port/art/runReport?
    reportId=127

    generates report using default parameter values

Note:

If the report url contains some non-ASCII characters in the parameter values and they don't seem to be interpreted correctly, you may need to configure your application server to explicitly handle urls using UTF-8 encoding e.g. for Tomcat, edit the TOMCAT_HOME\conf\server.xml file and add the URIEncoding attribute to the appropriate Connector element e.g.
```
<Connector port="8080" protocol="HTTP/1.1" URIEncoding="UTF-8"
    ...
```

Report Formats

When running reports interactively, or scheduling them for later execution, you can specify the format in which the results should be output. When running a report via url, you can specify the report format to be used using the reportFormat url parameter. Report format names are case sensitive and some report formats are not available for certain types of reports.

Report Format	Display Name	Description
html	Browser	Shows results in a web page
htmlFancy	Browser (Fancy)	Shows results in a web page, with minimal styling. Not available for scheduled jobs.
htmlPlain	Browser (Plain)	Shows results in a web page, with no styling
htmlDataTable	Browser (DataTable)	Shows results in a web page. Data is displayed in pages. The data can be sorted by clicking on the column headers and one can filter or display certain rows by specifying some text to search for. Not available for scheduled jobs.
xlsx	Spreadsheet (xlsx)	Creates a Microsoft Excel spreadsheet file (xlsx format)
tsv	File (tsv)	Creates a tab-separated-values file
tsvZip	File (tsv zip)	Creates a tab-separated-values file, compressed using Zip
tsvGz	File (tsv gz)	Creates a tab-separated-values file, compressed using GZip
pdf	Document (pdf)	Creates a pdf file
png	Image (png)	Creates a png file. Only available for charts.
docx	Document (docx)	Creates a Microsoft Word document file (docx format)
csv	File (csv)	Creates a comma-separated-values file. Can also use another delimiter as defined in the report Options field - using the delimiter property.
csvZip	File (csv zip)	Creates a comma-separated-values file, compressed using Zip
csvGz	File (csv gz)	Creates a comma-separated-values file, compressed using GZip
pivotTableJs	Browser (Pivot Table)	For tabular reports, shows the results as a dynamic pivot table
c3	Browser (C3 Chart)	For tabular reports, shows the data as a c3 chart
plotly	Browser (Plotly Chart)	For tabular reports, shows the data as a plotly chart
file	File	For FreeMarker, Velocity and Thymeleaf reports, outputs the results to a file
fileZip	File (zip)	For FreeMarker, Velocity and Thymeleaf reports, outputs the results to a file, compressed using Zip

Note:

There is a limit for the maximum number of rows that can be output. These limits can be modified from the Settings page.

Reports Page

The Reports page displays the list of available reports for a user, and can be accessed by clicking on the Reports menu. Users can have a particular report group pre-selected when they access the reports page by setting the Default Report Group field in the user configuration, or in the configuration of their user group. Another way to do this is to supply a reportGroup parameter in the url e.g. http://art-server:8080/art/reports?reportGroup=Sales.

Running Queries

You can view currently running queries by using the Configure | Running Queries menu. If you want the page to refresh automatically, you can modify the url and add the refreshPeriodSeconds parameter e.g. http://localhost:8080/art/runningQueries?refreshPeriodSeconds=30. The minimum refresh period is 5 seconds.

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
Javascript After	Additional Javascript after the parameter has been initialized
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 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.

Expressions
- Field Expressions
  - Username
  - Dates
- Groovy Expressions
  - Parameter values

Expressions

Expressions enable including of dynamic content in certain fields. Fields that can have expressions include

Job fixed file name
Job email subject
Job email message
Job sub directory
Parameter values and default values
Report source
Report short description
Report fileName JSON option

Field Expressions

Field expressions substitute certain known items. Field expressions start with f[ and end with ]f. Field expression names are case sensitive.

Username

You can use a field expression to substitute the username of the currently logged in user, or for jobs, the job owner. Use the value f[username]f to substitute the username.

Dates

You can substitute the current date by using the expression f[date]f. This will output the current date in yyyy-MM-dd format e.g. 2017-11-10. You can use the expression f[datetime]f to substitute the current date and time in yyyy-MM-dd HH:mm:ss.SSS format e.g. 2017-11-10 11:25:21.045. Both date and datetime field expressions can also specify a different date other that "now" and additionaly specify the output format, output format locale, input format and input format locale. These additional items are specified separated by | i.e f[date field name|date specification|output format|output format locale|input format|input format locale]f.

The date specification component can be the string "today" to specify today's date with the time component as zero, it can be the string "now" to specify the current date and time, or a date string in certain numeric formats e.g. yyyy-MM-dd or yyyy-MM-dd HH:mm:ss. In addition one can have simple date arithmetic by having a string starting with "add" and then specifying an offset from the current date e.g. add days 1 or add months -1. The duration offsets that can be specified include days, weeks, months, years, hours, minutes, seconds, milliseconds.

The output format component can be used to specify the desired output format of the resulting date using Java SimpleDateFormat syntax. Additionaly, a locale can be specified to define the locale which should be used with the given output format.

If the date specification is not in one of the common numeric formats, the format it is in can be specified in the input format section, and if required, the input format locale can be specified.

Groovy Expressions

Groovy code can be used to provide dynamic text. Groovy expressions start with g[ and end with ]g e.g. g[1+1]g. You can have longer code which includes imports etc. Groovy code is by default passed through a sandbox so if a SecurityException is thrown, the relevant class needs to be added to the WEB-INF\groovy-whitelist.txt file, or alternatively, turn off the sandbox in the WEB-INF\art-custom-settings.json file.

Parameter values

You can include parameter values as part of a groovy expression with a call like param1.value. If it is an LOV parameter and you would like to output the display value, you can use syntax like param1.textValue. An example full expression would be g[param1.textValue]g.

Dynamic SQL
- Using Groovy
  - Accessing parameter values
  - Examples
- Using XML tags
  - Operators
  - Examples

Dynamic SQL

It is possible to create dynamic SQL queries, allowing you to modify the structure of the query based on user parameters, possibly running different queries depending on the user input.

Using Groovy

You can use groovy in the SQL source section to achieve dynamic sql. Set the Use Groovy field of the report to specify that you are using groovy in the sql source.

The groovy script may return a string that has the sql to be executed or it may return a java.util.List of Map<String, Object> or GroovyRowResult objects that contain the data to be output. If using a Map it would be better to use a LinkedHashMap in order to have predictable order of columns. If returning a List of objects, the following options can be specified in the Options field of the report.

Property	Data Type	Description
columns	String[]	Names of columns that should be included in the output. This is optional and may be useful to specify the order of columns to be displayed.
columnDataTypes	Object[]	Each object specifies a column name and the data type of that column e.g. `[{"col1": "numeric"}]`. The possible data types are string, numeric, date and datetime. The default is `string`.
columnLabels	Object[]	Each object specifies a column name and the heading to be used for that column e.g. `[{"col1": "Column 1"}]`. If not specified, the column name is used as the heading.

The groovy code is run through a sandbox and depending on the code in the script, you may need to specify additional classes in the WEB-INF\groovy-whitelist.txt file. If you get a security exception when running the report, add the indicated class to the groovy whitelist file and run the report again. You can also disable the sandbox completely by modifying the WEB-INF\art-custom-settings.json file and setting the enableGroovySandbox property to false. Modifications of the art-custom-settings.json file require clearing the Custom Settings cache in order to take effect.

Accessing parameter values

To access parameter values in groovy code, use the name of the parameter and call the value method e.g. param1.value. For File parameters, use the parameter name directly instead of calling the value method. Also, file parameter values are always passed as a list so access the parameter variable using list syntax e.g. fileParam1[0]. If the file parameter has Multiple Files enabled, this list may have more than one value.

Examples

Integer parameter

def sql

//assumes the report has an integer parameter named id
if(id.value == 0) {
    sql = 'select id from users'
} else {
    sql = 'select name from users'
}

File parameter

//assumes the report has a file parameter named importFile
if (importFile == null) {
    println('report run from url or file parameter not available')
} else if (importFile[0].size == 0) {
    println('empty file or no file selected')
} else {
    InputStream inputStream = importFile[0].getInputStream();
    Scanner sc = null;

    try {
    sc = new Scanner(inputStream, "UTF-8");
        while (sc.hasNextLine()) {
            String line = sc.nextLine();
            println(line);
        }
    } finally {
        if (inputStream != null) {
            inputStream.close();
        }
        if (sc != null) {
            sc.close();
        }
    }
}

The demo database contains some sample reports that use groovy in the sql source to execute different queries based on parameter input.

Using XML tags

You can use xml tags in the SQL source section to achieve dynamic sql. The syntax is as follows. Tag names are case sensitive.

    <IF>
    <EXP1>value1</EXP1> <OP>operator</OP> <EXP2>value2</EXP2>
    <TEXT> ... </TEXT>
    <ELSETEXT> ... </ELSETEXT>
    </IF>

ART parses the query and leaves only the text in the <TEXT> tag if the condition (value1 operator value2) is true or the text in the <ELSETEXT> tag if the condition is false. The EXP1 or EXP2 contents can be static values, single-value parameters or :tags, while the OP content is an operator (see supported list below).

For example in the following query:

    SELECT *
    FROM <IF><EXP1>#level#</EXP1><OP>equals</OP><EXP2>summary</EXP2>
    <TEXT> orders_summary </TEXT>
    <ELSETEXT> orders_details </ELSETEXT>
    </IF>
    WHERE VENDOR like #vendor#
    <IF><EXP1>#date#</EXP1><OP>is not null</OP>
    <TEXT> AND order_date > #date# </TEXT>
    </IF>

if the #level# parameter is set to "summary" and the #date# parameter is not null, ART rewrites the query as:

    SELECT *
    FROM orders_summary
    WHERE VENDOR like #vendor#
    AND order_date > #date#

if the #level# parameter is not set to "summary" and the #date# is null, ART rewrites the query as:

    SELECT *
    FROM orders_details
    WHERE VENDOR like #vendor#

Operators

Operator	Description
eq or equals	equals (case insensitive)
neq or not equals	not equals (case insensitive)
ln	less than (numbers)
gn	great than (numbers)
la	less than (alphabets) (case insensitive)
ga	great than (alphabets) (case insensitive)
is blank or is null	returns true if EXP1 is blank (EXP1 can never be the null object)
is not blank or is not null	returns true if EXP1 is not blank (EXP1 can never be the null object)
starts with	returns true if EXP1 string begins with EXP2 (case insensitive)
ends with	returns true if EXP1 string ends with EXP2 (case insensitive)
contains	returns true if EXP1 string contains EXP2 string within it (case insensitive)
eq cs or equals cs	equals (case sensitive)
neq cs or not equals cs	not equals (case sensitive)
la cs	less than (alphabets) (case sensitive)
ga cs	great than (alphabets) (case sensitive)
starts with cs	returns true if EXP1 string begins with EXP2 (case sensitive)
ends with cs	returns true if EXP1 string ends with EXP2 (case sensitive)
contains cs	returns true if EXP1 string contains EXP2 string within it (case sensitive)

Examples

Dynamic OR/AND selection:

    SELECT *
    FROM orders_summary
    WHERE VENDOR like #vendor#
    <IF><EXP1>#logic#</EXP1><OP>equals</OP><EXP2>OR</EXP2>
    <TEXT> OR country = #country# </TEXT>
    <ELSETEXT> AND country = #country# </ELSETEXT>
    </IF>

Dynamic ORDER BY: The order by part is driven by the user input

    SELECT *
    FROM orders_summary
    WHERE VENDOR like #vendor#
    <IF><EXP1>#sortby#</EXP1><OP>equals</OP><EXP2>Vendor</EXP2>
    <TEXT> ORDER BY 1 </TEXT>
    <ELSETEXT> ORDER BY 2 </ELSETEXT>
    </IF>

Dynamic query selection: A different query is executed depending on user input

    <IF><EXP1>#showaddr#</EXP1><OP>equals</OP><EXP2>Y</EXP2>
    <TEXT>
    SELECT name, code, address, phone FROM VENDOR
    WHERE VENDOR like #vendor#
    </TEXT>
    <ELSETEXT>
    SELECT name, code, vat, pay_term FROM VENDOR
    WHERE VENDOR like #vendor#
    </ELSETEXT>
    </IF>

Dynamic SQL with tags: The condition is verified only if the date supplied by the user (#date#) is earlier than the system date at report execution time (:DATE tag)

    SELECT *
    FROM orders_summary
    WHERE VENDOR like #vendor#
    <IF><EXP1>#date#</EXP1><OP>la</OP><EXP2>:DATE</EXP2>
    <TEXT> AND order_date > #date# </TEXT>
    </IF>

Check user input: Show a warning instead of executing the query

    <IF><EXP1>#value#</EXP1><OP>ln</OP><EXP2>10000</EXP2>
    <TEXT> SELECT ... </TEXT>
    <ELSETEXT> SELECT 'Value too High' "Warning" </ELSETEXT>
    </IF>

Dynamic WHERE condition: E.g. to drill down on null values

    SELECT * FROM customers
    WHERE 
    <IF><EXP1>#email#</EXP1><OP>equals</OP><EXP2>null</EXP2>
    <TEXT>customer_email is null</TEXT>
    <ELSETEXT>customer_email=#email#</ELSETEXT>
    </IF>

Multiple Statements

You may want to run some statements before or after the select statement for your report, e.g. to create a temporary table, create an index etc. Enter all your statements in the sql source section of the report, with each statement ending in a ; and specify which statement should be used as the report's results by setting the Display Resultset field of the report.

Display ResultSet	Description
0	The sql source doesn't contain multiple statements
1, 2, ... n	Use the specified statement, with the first statement being 1
-1	Use the select statement, regardless of how many statements exist
-2	Use the last statement, regardless of how many statements exist

If you set the Display Resultset to -2 to use the last statement, ensure that your database driver is at least JDBC 3.0 compliant.

Some RDBMSs may require extra configuration to allow for execution of multiple statements in a query, and some may not support it at all. Some examples are given below.

RDBMS	Comment
SQL Server	No extra configuration needed
PostgreSQL	No extra configuration needed
MySQL	Add the property allowMultiQueries=true to the jdbc url of the query's datasource e.g. `jdbc:mysql://localhost/mydb?allowMultiQueries=true`

Dynamic Datasources

When you define a report, you specify the datasource from which data will be retrieved. Sometimes, you may have several databases that have the same schema but contain different data e.g. a live production database, a test database, a database that has data as at end of month etc. You may want to have the possibility of running the same query over these different databases. Rather than creating several reports with identical sql but different datasources, you can create one report and have the datasource as a parameter that can be selected at runtime. This eliminates the work of creating multiple reports and makes management of the relevant sql much easier - if you need to change the sql, you only have to do it in one place. The process of using dynamic datasources is as follows.

Create a datasource for each of your target databases
Create the report that will retrieve the data you want. What you select in the Datasource field is not important as the report will use a dynamically specified datasource. It is advisable though to set this to a valid, default datasource for your sql.
Define a single-value parameter for your report, setting the Data Type to Datasource. If you would like the available datasources to be selected from a list, create and specify the LOV report that will display the desired datasources.

When you run the report, it will use the value of the datasource parameter to determine which datasource to run the report on. The value of this parameter should be a datasource id or datasource name. If it passes the "isNumeric" test, it will be assumed that it is referring to a datasource id, otherwise a check against datasource names will be done.

Rules

Rules are used to filter report results. They allow the same report to be filtered depending on the user that is running it.

The following steps need to be performed in order to use rules:

Create the rule
Use the Configure | Rules menu and then the Add button to specify the rule name
Link the rule to a report
On the report definition page, select the Uses Rules option
In the SQL source section, use the special, hard coded placeholder #rules# where you want the rule values to go e.g
```
SELECT * from transactions
where #rules#
```

On the reports configuration page, find the report and use the More | Rules menu to specify which column the rule values will apply to

Assign rule values to users or user groups
Use the Configure | Rule Values menu to assign rule values to users or user groups. Select the user or user group and rule name, and specify the rule values that will apply to that user or user group. If you want the user or user group to have multiple rule values, add each value separately. i.e enter the first value, then click on Add, then enter the next value etc.

Example:

You have a table named "employees" with the a column named "region"
Create a rule named "GeoArea"
Set up a report that selects rows from the employees table and link it to the rule named "GeoArea" on the column "employees.region"
Link the user to the rule "GeoArea" for values NORTH and EAST

When the user runs the report, he will extract only the rows where the "region" column has the values NORTH or EAST (i.e. the SQL query will be modified on the fly before execution by adding AND employees.region IN ('NORTH','EAST') to the WHERE clause).

Note:

If a report is linked to a rule but the user who runs the report has not been set up for that rule, the report execution will not be performed and an error message will be displayed.
If the report uses multiple rules, each user that needs to execute it must have values set for all the rules that the report uses.
If the report uses parameters, the placeholder #rules# shouldn't be used for any parameter as it will conflict with the special placeholder for rules.
If you don't want results filtered for a given user e.g. an administrator, manager etc, define a rule value for this user where the rule value is ALL_ITEMS.

Encryptors

Encryptors allow you to encrypt or password protect report output. Use the Configure | Encryptors menu to manage encryptors.

Field	Description
ID	Auto-generated ID used to identify the encryptor
Name	A name to identify the encryptor
Description	A description for the encryptor
Active	Defines whether the encryptor will be applied
Encryptor Type	Specifies the type of encryption to be used. Password encryptors are used to specify open and modify passwords for xlsx and pdf output.
Open Password	For Password encryptors, the file open password to be used with xlsx, pdf, docx output
Modify Password	For Password encryptors, the file edit password to be used with xlsx and pdf output

LOV Reports

LOV reports are used to generate parameter values for other reports. Dynamic LOV reports get their values from an sql query while static LOV reports use fixed values defined in the sql source section. An LOV report must have either one or two columns. The value of the first column is passed to the parameter. The value of the second column (if available) is displayed to the user. For example, for the following dynamic LOV query

    SELECT location_id, location_name FROM LOCATIONS

Users see values from the location_name column while the actual parameter match is performed using values from the location_id column.
If the parameter options don't come from a database, you can use a static lov with something like the following in the sql source. If the parameter value and the display value are different, separate them with a |

    local|Local
    international|Worldwide

Example

This section shows how to define a simple report to retrieve information about the reports stored in the ART database. The report has one parameter (the report name), obtained from a dynamic lov.

In order to proceed with this example you need to:

Set up a datasource that points to the ART database
Define a user and a report group

LOV Report
This report is the one used to retrieve the list of values (i.e. the list of available reports that will be shown as a parameter). Use the Configure | Reports menu and then the Add button to create a new report. Set the name to "ART Report Names", the type to LOV: Dynamic and the following as the SQL source:
```
SELECT NAME FROM ART_QUERIES ORDER BY NAME
```

Select the ART database as the Datasource and Save.

Main Report
This report retrieves the details of the reports that are defined in ART. It has one parameter whose values are retrieved from the "ART Report Names" report created above. Following the same process used for the previous report, name the report "ART Reports" and for the SQL source use:
```
SELECT NAME
, SHORT_DESCRIPTION
, DESCRIPTION        
, UPDATE_DATE
FROM ART_QUERIES WHERE NAME = #report_name#
```

Select the ART database as the Datasource and Save.

Use the Configure | Parameters menu and then the Add button to create a new single-value paramter named report_name. Select the Use LOV option and select "ART Report Names" in the LOV Report field. Click on the Save button to save the parameter details.

Use the Configure | Reports menu to go back to the Reports Configuration page. Find the main report, ART Reports, click on the More | Parameters button and Add a new parameter, setting the parameter to the report_name parameter that has just been created.

Use the Configure | Access Rights menu to define users who can run the main report. Now you can log in as the user and run the report.

Note:

You don't have to assign access to the LOV report itself
You can't use rules with static lovs. If you have a PostgreSQL database, you can create a dynamic lov that will return the static values, with syntax like the following
```
select id from
(select unnest(ARRAY[1, 5, 10]) as id) as id_list
where #rules#
```

Drill Down Reports

Drill Down Reports

This functionality provides a main report with links from which a user can click to get to a different (drill down) report. For example, the main report can display summary information and a user can click on the drill down report link to display detailed information about a particular item.

The main report can be a tabular report displayed in one of the html report formats, or a chart. The drill down report can be of any type.

Defining the main report

The main report is created like any other report. Once the report is saved, use the More | Drilldowns button to add or modify the drill down reports that will be available for that report.

Defining the drill down report

The drill down report is created like any other report. The only requirement is that it needs to have at least one single-value parameter defined, with this parameter having the Drilldown Column Index field with a value greater than or equal to 1 or the Drilldown Column Name set. The Drill Down Column refers to the column in the main report that will provide the value for the parameter. If the value will come from the first field, the drill down column index will have a value of 1 and so on. Alternatively, you can set the drill down column name field to the name of the column which will supply the value.

Example

Sales Summary (Main report)

select REGION as "Region", SUM(VOLUME) as "Volume"
from ORDERS
where ...

Sales Details (Drill Down report)

select REGION, CITY, ITEM, VOLUME
from ORDER_DETAILS
WHERE REGION = #region#

On the Drill Down report, when defining the #region# parameter, set the Drilldown Column Index field to 1. This means this parameter will match with the first column value on the Main Report i.e. "Region". Alternatively, set the Drilldown Column Name field to Region.

From now on this report will appear as an available Drill Down report.

On the Main report, select the More | Drilldowns option and select Add to create the Drilldown definition.

When running the Main Report, a new column will appear on the right. Click on it and the drill down report will be executed and the #region# parameter will match with the first column of the Main Report.

Drilldown definition

Field	Description
ID	An auto-generated ID used to identify the drilldown
Drilldown Report	The drill down report
Header Text	The column title/header of the drilldown link column
Link Text	The text of the drilldown link
Report Format	The report format that will be used to display the drill down report results
Open In New Window	Whether the result of the drill down report should be opened in a new window
Allow Select Parameters	Whether the user will be provided with the parameters box to select/change parameters after the drill down report has run if they wish to re-run the report
Run Immediately	Whether the drill down report will run immediately

Using the main report's parameters

In addition to using the main report's column values, a drill down report can also use the main report's parameter values. If in our example above the Main Report allowed the user to select a city, e.g.

    select REGION "Region", SUM(VOLUME) "Volume"
    from ORDERS
    WHERE CITY=#city# AND ...

The Drill Down Report can make use of this #city# parameter even if it is not among the columns displayed by the Main Report's select statement. To use the value of the #city# parameter in the Drill Down Report,

Define the parameter with the same name as in the Main Report
Set the Drilldown Column Index for this parameter to 0, since the value of this parameter will not be coming from the columns displayed in the Main Report

So the Drill Down Report would look something like

    select REGION, CITY, ITEM, VOLUME
    from ORDER_DETAILS
    WHERE REGION = #region# and CITY=#city#

Here the #region# parameter will still be set as Drilldown Column 1, since it's getting its value from the Main Report's column 1, while the #city# parameter will be set as Drilldown Column 0, since it's not getting its value from any of the Main Report's columns (the Main Report's select doesn't include the city column).

The parameter values from one report are passed down to all drill down reports down the line. So if this drill down report had another drill down report, that report can also use the value of the #city# parameter set in the Main Report.

Note:

Multiple Drill Down Reports can be linked to one Main Report. Extra columns will appear with links allowing the user to select which drill down report to run.
A Drill Down Report can have several parameters to match any number of column values from the Main Report. It can also have parameters that use values from the Main Report's parameters.
If the Main Report is a chart, the drill down parameter will get the following values
- Drilldown column 1 = data value
- Drilldown column 2 = category name
- Drilldown column 3 = series name

Dashboards

Dashboards

Dashboards are used to display multiple reports on a single page. Most report types can be included in a dashboard with exceptions including Saiku and Link reports.

Each dashboard component or portlet uses AJAX to dynamically load itself within the page. Users can refresh or minimize each portlet independently by clicking on the buttons at the top of the portlet frame. When a user runs a dashboard, he can choose to modify the default parameters used by the reports in the dashboard. All the embedded reports are parsed and their parameters, if any, are displayed. If several reports use the same parameter, the parameter is displayed only once.

dashboard-example

Regular Dashboards

You define a dashboard by creating a report of type Dashboard and specifying the details of the dashboard using XML syntax in the Source field.

Dashboard Properties

A number of tags are used to define properties of the dashboard.

Tag	Data Type	Description
`<DASHBOARD>`	None	The parent/containter tag for the dashboard definition
`<COLUMN>`	None	The parent/container tag for a dashboard column. A dashboard can have any number of columns.

Column Properties

A dashboard can have a number of columns, enclosed within <COLUMN> tags. These columns have a number of properties which are specifed using the following tags.

Tag	Data Type	Default	Description
`<SIZE>`	String	auto	The size of the column. Either small, medium, large or auto.
`<PORTLET>`	None		The parent/containter tag for a portlet. A column can have any number of portlets.

Portlet Properties

A dashboard column can have a number of portlets, enclosed within <PORTLET> tags. These portlets have a number of properties which are specifed using the following tags.

Tag	Data Type	Default	Description
`<TITLE>`	String		The title of the portlet
`<REPORTID>`	String		The ID of an ART report that should be displayed in the portlet e.g. `25`. You can add parameters after the report ID, being careful to use `&` where `&` would be used in a url e.g. `25&reportFormat=htmlGrid`
`<REPORTNAME>`	String		The name of an ART report that should be displayed in the portlet e.g. `Sales Report`. You can add parameters after the report name, being careful to use `&` where `&` would be used in a url e.g. `Sales Report&reportFormat=htmlGrid`
`<REPORTCODE>`	String		The code of an ART report that should be displayed in the portlet e.g. `sales1`. You can add parameters after the report code, being careful to use `&` where `&` would be used in a url e.g. `sales1&reportFormat=htmlGrid`
`<URL>`	String		An external url that should be displayed in the portlet e.g. `http://www.example.com`. If both url and report id tags are present, the content of the report id tag will be used.
`<REFRESH>`	Integer	-1	The time in seconds after which the portlet refreshes itself. -1 means no auto-refresh. The minimum refresh time allowed is 5 seconds. Setting a low value may overload your servers/databases. A portlet can be refreshed manually by clicking on the refresh button in the portlet header.
`<ONLOAD>`	Boolean	true	Whether the report or url content is retrieved when the dashboard is run. If set to false, the portlet content is not loaded initially and can be loaded later manually by using the refresh button.

Example Syntax

    <DASHBOARD>

        <COLUMN>
            <!-- column size: auto|small|medium|large. default is auto-->
            <SIZE>medium</SIZE>

            <!-- create a new portlet within this column
            to embed an ART report (tabular, chart, text, etc) -->
            <PORTLET>
                <TITLE>Portlet title</TITLE>

                <!-- (optional, default is true) load content when page is displayed -->
                <ONLOAD>false</ONLOAD>

                <!-- (optional, default is -1 meaning never) refresh content every 30 seconds-->
                <REFRESH>30</REFRESH>

                <!-- embed ART report -->
                <REPORTID>2</REPORTID>
            </PORTLET>

            <!-- create a new portlet within this column
            to embed an external html page -->
            <PORTLET>
                <TITLE>Portlet title</TITLE>
                <URL>http://my.site.com/page.html</URL>
            </PORTLET>

            <!-- .. you can add as many portlets as you want -->
        </COLUMN>

        <COLUMN>
            <!-- you can add as many columns as you want -->
        </COLUMN>

    </DASHBOARD>

Note:

Tag names are case sensitive (use uppercase)
If you need to use xml special characters within tag content, ensure to replace them with the relevant xml character entities e.g. if the <URL> tag should contain a url like http://www.example.com/page.html?param1=1&param2=test, you'll need to replace the & character with &, to have the url as http://www.example.com/page.html?param1=1&param2=test. For a list of the 5 special xml characters and their respective replacement strings or character entities, see https://xmltutorial.info/xml/special-characters-in-xml/
For pdf output of dashboards, only tabular and standard chart reports will be included in the pdf output.

Gridstack Dashboards

Gridstack dashboards allow for specifying exact positioning of report items within a dashboard, specifying the height and width that report items should occupy, and allow for resizing and moving of items using drag-and-drop. The gridstack.js library is used to provide this functionality. Use the Dashboard: Gridstack report type to create a gridstack dashboard.

Dashboard Properties

Gridstack dashboards are also defined using xml syntax. A number of tags are used to define properties of the dashboard.

Tag	Data Type	Default	Description
`<DASHBOARD>`	None		The parent/containter tag for the dashboard definition
`<DASHBOARDWIDTH>`	Integer	12	The number of columns to be taken up by the grid, forming the basis of the horizontal axis (x axis) of the grid co-ordinates.
`<FLOAT>`	Boolean	false	Whether report items can be moved to arbitrary locations on the dashboard. If false, grid items can only snap to given locations.
`<ANIMATE>`	Boolean	false	Whether animation is used when grid items are moved around
`<DISABLEDRAG>`	Boolean	false	Whether dragging of items is disabled
`<DISABLERESIZE>`	Boolean	false	Whether resizing of items is disabled
`<CELLHEIGHT>`	String	60px	The dimensions of one unit of the grid co-ordinates on the vertical axis (y axis). It can be defined as px, em or rem units e.g. 10em or 10rem. It can also be defined as "auto", in which case the height will be calculated from the cell width.
`<VERTICALMARGIN>`	String	20px	The size of the vertical gap between cells. It can be defined as px, em or rem units e.g. 2em or 2rem.
`<ALWAYSSHOWRESIZEHANDLE>`	Boolean	false	Whether the resizing handles on the bottom corners of a cell are shown, even if the user is not hovering over the item.
`<DASHBOARDHEIGHT>`	Integer	0	The maximum number of rows or y units on the grid co-ordinate system. Items cannot be resized or moved beyond this limit. A value of 0 means no maximum.
`<ITEM>`	None		The parent/container tag for a dashboard item. The dashboard can have any number of items.

Item Properties

A gridstack dashboard can have a number of items, enclosed within <ITEM> tags. These items have a number of properties which are specifed using the following tags.

Tag	Data Type	Default	Description
`<XPOSITION>`	Integer		The x axis position of the item. From 0 to `<DASHBOARDWIDTH>`
`<YPOSITION>`	Integer		The y axis position of the item
`<WIDTH>`	Integer	2	The width of the item. This defines the number of columns that the item will occupy, in relation to the number of columns of the whole grid as defined by the dashboard's `<DASHBOARDWIDTH>` property.
`<HEIGHT>`	Integer	2	The height of the item. This defines the number of rows that the item will occupy, one row being the size defined in the dashboard's `<CELLHEIGHT>` property.
`<AUTOWIDTH>`	Boolean	false	Whether the width should be automatically set according to the item contents
`<AUTOHEIGHT>`	Boolean	false	Whether the height should be automatically set according to the item contents
`<NORESIZE>`	Boolean	false	Whether element resizing is disabled
`<NOMOVE>`	Boolean	false	Whether element moving is disabled
`<AUTOPOSITION>`	Boolean	false	Whether to ignore the `<XPOSITION>` and `<YPOSITION>` and instead place the element in the first available position.
`<LOCKED>`	Boolean	false	Whether the widget will be locked. If it is locked, another widget will not be able to move it during dragging or resizing. Even though it is locked, the widget can still be dragged or resized. You need to specify the `<NORESIZE>` and `<NOMOVE>` options to prevent this and thereby completely lock the widget.
`<MINWIDTH>`	Integer	0	The minimum width beyond which the item cannot be resized
`<MINHEIGHT>`	Integer	0	The minimum height beyond which the item cannot be resized
`<MAXWIDTH>`	Integer	0	The maximum width beyond which the item cannot be resized. A value of 0 means no maximum.
`<MAXHEIGHT>`	Integer	0	The maximum height beyond which the item cannot be resized. A value of 0 means no maximum.

In addition to these tags that are unique to gridstack items, the following tags of regular dashboard portlets should also be used with gridstack items, with the same effect: <TITLE>, <REPORTID>, <REPORTNAME>, <REPORTCODE>, <URL>, <REFRESH>, <ONLOAD>.

Example Syntax

    <DASHBOARD>

        <ITEM>
            <TITLE>Report 1</TITLE>
            <REPORTID>1</REPORTID>
            <XPOSITION>0</XPOSITION>
            <YPOSITION>0</YPOSITION>
            <WIDTH>4</WIDTH>
            <HEIGHT>4</HEIGHT>
        </ITEM>

        <ITEM>
            <TITLE>Report 2</TITLE>
            <REPORTID>2</REPORTID>
            <XPOSITION>8</XPOSITION>
            <YPOSITION>0</YPOSITION>
            <WIDTH>4</WIDTH>
            <HEIGHT>4</HEIGHT>
        </ITEM>

    </DASHBOARD>

Tabbed Dashboards

Both regular and gridstack dashboards can have their components displayed within tabs. This is done by adding the optional <TABLIST> tag to the dashboard xml and specifying the number of tabs, and which portlets/items should appear in which tabs.

Tablist Properties

The <TABLIST> tag is used to specify that the dashboard portlets/items should be displayed in tabs. A tablist has a number of properties which are specifed using the following tags.

Tag	Data Type	Default	Description
`<DEFAULTTAB>`	Integer	1	The index of the tab that will be active when the dashboard is displayed. The first tab has an index of 1.
`<TAB>`	None		The parent/container tag for a tab. A tablist can have any number of tabs.

Tab Properties

A tablist can have a number of tabs, enclosed within <TAB> tags. The tabs have a number of properties which are specifed using the following tags.

Tag	Data Type	Default	Description
`<TITLE>`	String		The title/heading of the tab. Even though this tag is optional, it is recommended to set it so as to have better identification of the different tabs.
`<ITEM>`	Integer		The index of the item to display, as it appears in the main dashboard xml definition. The dashboard portlets/items are assigned an index according to the order in which they appear in the xml. The first item has an index of 1 e.g. setting this to 1 indicates that the first portlet/item in the main dashboard xml should be displayed. A tab can have any number of items.

Example Syntax

    <DASHBOARD>

        <ITEM>
            <TITLE>Report 1</TITLE>
            <REPORTID>1</REPORTID>
            <XPOSITION>0</XPOSITION>
            <YPOSITION>0</YPOSITION>
            <WIDTH>4</WIDTH>
            <HEIGHT>4</HEIGHT>
        </ITEM>

        <ITEM>
            <TITLE>Report 2</TITLE>
            <REPORTID>2</REPORTID>
            <XPOSITION>8</XPOSITION>
            <YPOSITION>0</YPOSITION>
            <WIDTH>4</WIDTH>
            <HEIGHT>4</HEIGHT>
        </ITEM>

        <TABLIST>
            <DEFAULTTAB>1</DEFAULTTAB>
            <TAB>
                <TITLE>Tab 1</TITLE>
                <ITEM>2</ITEM>
            </TAB>
        </TABLIST>

    </DASHBOARD>

Jxls Reports

Jxls Reports

ART uses the Jxls library to provide support for reports based on pre-formatted MS Excel spreadsheets. For details on the syntax of these Jxls templates, see the documentation available on the Jxls website. Generally, you'll follow the following steps to create a Jxls report.

Create an Excel file (either xls or xlsx) with any text, formulas and formatting that will be required in the final report. This is the Jxls template.
Insert Jxls syntax to define where the raw data should go
Create a new report in ART and select the Jxls template to use in the Template field

ART Query

If the report is of type Jxls: ART Query, the sql used to retrieve the data will be the one defined in the SQL source section. You can use parameters, rules or any other features of ART reports. In the template, use the identifier results to output report values. e.g.

    jx:each(items="results" var="row" lastCell="E10")

    ${row.description}

Template Query

If the report is of type Jxls: Template Query, the sql used to retrieve the data will be the one defined in the template. The datasource used for the query will be the one selected in the datasource field of the report. Use the jdbc.query method to run the sql e.g.

jdbc.query('select * from cities')

You can alternatively pass the datasource id or datasource name of the datasource to use. In this case use the jdbc.querydb method to run the sql e.g.

jdbc.querydb(1, 'select * from cities')

jdbc.querydb('mydb', 'select * from cities')

Parameters

If you would like to use parameters within the template query, you can use parameter placeholders (?) and specify the parameter name in additional parameters to the jdbc.query call e.g.

jx:each(items="jdbc.query('select * from mytable where col1=? and col2=?', param1, param2)"
var="item" lastCell="E10")

Alternatively, you can use ART query syntax by calling the jdbc.artquery method e.g.

jx:each(items="jdbc.artquery('select * from orders where order_date between #param1# and #param2#')"
var="item" lastCell="E10")

To use ART query syntax with a specific datasource, use the jdbc.artquerydb method e.g.

jdbc.artquerydb('mydb', 'select * from cities where col2 in(#param#)')

Avoid using the following names for parameters as these are used for special purpose variables i.e. results, jdbc, params, locale.

Options

You can define a number of options in the Options field of the report.

Property	Data Type	Default	Description
areaConfigFile	String		The name of the file that has configuration details of areas within the template e.g. `jxls2config.xml`. This is an xml file that can be uploaded using the Add files option.

Self Service
- Self Service Reports
- Self Service Charts

Self Service

The self service option allows users to create their own reports.

Self Service Reports

Users can create their own reports by using the Self Service | Reports menu. They will need to have the self_service_reports permission and will create reports based on View reports that have been created and for which they have access. Self service reports allow users to create reports without knowledge of SQL.

To create a report, a user will select a view from the Views dropdown and then select which columns should be displayed in the report. The columns on the left hand side are those available, while those on the right hand side are those that have been selected to be displayed in the report. If no column is selected, all columns will be displayed. Columns can be selected by double clicking, or clicking and using the arrow buttons as appropriate. You can also shift+click or control+click to select the columns you would like and then use the left or right arrow buttons to move the columns as required. To select or remove all the columns at once, the double arrows can be used. The order of selected columns can also be changed by use of the up and down arrow buttons. You can also select multiple columns and move them up or down together.

Once the columns have been selected, you can use the Preview button to have an idea of what the output will be like. The Limit box can be used to determine how many records should be included in the preview. If you want to retrieve all the records, use the Run button.

Once you have an idea of how the data will look like, you can also specify conditions that will limit the data in the report in the Conditions section. You can define rules to be satisfied by selecting the column and specifying the condition that records should meet. You can use the Add rule button to add more rules. Rules are typically added using an AND condition but you can also click on the OR button to specify that they should be considered using OR logic. You can also add a distinct rule group that may have it's own rules using the Add group button and you can delete rules or rule groups using the appropriate Delete button.

For Tabular reports, you can also specify a default order for the results by using the Add button under Order and specifying the desired column and order direction.

Once you are satisfied with the report, you can Save it, and if you need to modify it, you can click on the Save button again and specify the Overwrite option. If you don't want to overwrite, but create a new report, you can uncheck this option. Self service reports that have been created are available for editing by selecting them from the Reports dropdown list.

Self Service Charts

When on the self service reports page, the Report Type option provides for creating charts. You need to select one non-numeric column that will act as the x axis, and one or more numeric columns that will be displayed on the y axis of the chart. You can also select what chart type to create in the Initial Chart Type field, and in case you want to allow changing of chart types on the fly, you can select which chart types to make available in the Available Chart Types field.

Jobs

A job is a report that has been scheduled to run automatically. In order to schedule a report, a user needs to have the schedule_jobs permission. Once logged in, a user's scheduled reports are available from the Jobs menu.

Scheduling a new job

Take the following steps to schedule a new job

From the Reports page, select the report to schedule and once the "select report parameters" page appears, click on the Schedule button. You can also schedule a job by going to the Reports Configuration page, finding the report to schedule and selecting the More | Schedule option.
Specify the job details and Save
The job will now run as per the schedule defined

Field	Description
ID	Auto-generated ID used to identify the job
Name	Name of the job
Job Type	The job type
Output Format	The output format for the job
Active	Whether the job can run or not
Number of Runs to Archive	For publish jobs, indicates how many runs should be retained for viewing from the View \| Archives page e.g. setting it to 5 will retain output from the last 5 runs in the Archives page. This allows users to access past job output as per their requirements.
Allow Sharing	For publish jobs, whether the job output can be accessed by other users, apart from the job owner
Allow Splitting	Whether rule values for shared users should be applied so that each user gets different output
Fixed File Name	The base file name to be used for generated output. To include the burst id when used with a Burst Job, use the identifier `{burstId}`. This base file name does not include the extension. The extension will be automatically provided depending on the report and output format. File names can only contain english alphabet letters, numbers, hyphen and space. Any other characters will be replaced with underscores. Leave this field blank to use a default generated file name.
Dynamic Destination	For Burst jobs, a name or alternative name for a dynamic destination to be used by the burst output. Use the `{burstId}` identifier to indicate the burst id e.g. "Branch {burstId}" will send burst id 001 to a destination with the name or alternative name "Branch 001" if it exists, burst id 002 will be sent to destination "Branch 002" and so on.
Destinations	Any destinations to which the generated output should be sent. Destinations are configured from the Configure \| Destinations menu.
Sub-Directory	An additional sub-directory that should be used with destinations. You can use the `{burstId}` identifier with Burst Jobs. This would imply that each burst file is put in a different directory.
Batch File	The name of a batch file or script that should be run after the job completes. The batch file must exist in the WEB-INF\work\batch directory. You only set the file name in this field e.g. `test.bat` or `test.sh`. ART passes the file name of the generated output as the first parameter to the batch file so you can use this e.g. to copy the file to another location, a batch file may contain the following command - `copy ..\export\jobs\%1 C:\temp`.
Pre Run Report	The report id of an Update Statement report that should be run before the job is run. Multiple reports can be specified separated by commas.
Post Run Report	The report id of an Update Statement report that should be run after the job has run. Multiple reports can be specified separated by commas.
Code	An optional, additional identifier for the job. ART doesn't enforce the uniqueness of this field.
From	The email address that will be used as the "From" email address of an email job
To	The email address to send job output to. Multiple addresses can be specified separated by `,`
Dynamic Recipients	A dynamic recipients report that specifies email addresses to send job output to, with the email addresses being obtained from a database query.
Cc	The "Cc" email address for a job. Multiple addresses can be specified separated by `,`
BCc	The "BCc" email address for a job. Multiple addresses can be specified separated by `,`
Reply To	The email address that will be used when a user clicks on Reply in the received email. If this is not set, the "From" email configured on the smtp server or job is used. Multiple addresses can be specified separated by `,`
Subject	The subject of the email
Template	A thymeleaf template file that can be used as a template for the email body
SMTP Server	The smtp server to use, if different from the one configured in the Settings page
Message	The body contents of the email. For email inline jobs you can use `f[data]f` to specify where the report data should go. If not specified, the data will come after the message.
Manual	Whether the job will run on an automatic schedule or not
Schedules	The schedules with which the job should run. If a schedule is updated, the job's run schedule is also updated. If a schedule is selected, the individual schedule fields are not considered.
Schedule	This field is only used to populate the individual schedule fields, which can be modified thereafter.
Second	The second that the job should run. If not specified, defaults to 0.
Minute	The minute that the job should run. If not specified, defaults to a random minute (0-59).
Hour	The hour that the job should run. If not specified, defaults to a random hour between 3-6.
Month	The month that the job should run. If not specified, defaults to every month.
Day	The day of the month that the job should run i.e. 1-31. If not specified, defaults to every day.
Week Day	The day of the week that the job should run i.e. 1-7 or SUN-SAT. Note that you cannot specify both the Month Day and the Week Day. Specify one of the two, leaving the other one blank, or setting it to "?"
Year	The year that the job should run. If not specified, defaults to every year.
Time Zone	The time zone for the defined schedule
Start Date	The date when the job should start running. If blank, the job will start running on the current date, as per it's schedule.
End Date	The date when the job should stop running. If blank, the job will continue to run indefinitely.
Runs	The number of times the job should run. If specified, this will override the End Date.
Extra Schedules	Definitions of extra schedules on which the job should run, in addition to the main schedule defined in the individual schedule fields. Schedules can be defined as Quartz cron expressions, with each schedule on a new line. Alternatively, groovy code can be used, with the code returning a Trigger or a List of Triggers. If using groovy code, the field should start with "g[" and end with "]g".
Holidays	Defines dates or times on which the job should not run. These dates can be defined using quartz cron expressions as explained in the CronCalendar documentation, with each holiday definition on a new line. Alternatively, groovy code can be used, with the code returning a Calendar or a List of Calendars. If using groovy code, the field should start with "g[" and end with "]g".
Shared Holidays	Shared holidays configured from the Configure \| Holidays menu that should be applied for this job.
Next Run After Holiday	Definition of the next run if a job run falls on a configured holiday
Custom Run After Holiday	If the Next Run After Holiday field is set to Custom, this defines a cron expression that includes the next run date
Start Condition	A condition to be satisfied before a scheduled job is run
Error Notification Email	An email address that should be notified if an error occurs while running the job. Multiple email addresses can be specified separated by commas. The SMTP server used is that configured in the Settings page.
Options	Any optional job options, specified in JSON format

Note:

Not all report types can be scheduled
If the output for a job creates a file e.g. publish to pdf, this file is stored in the WEB-INF\work\export\jobs directory

You can initiate running of a given job id by running a command like the following on the command line. You would need to add "runJob" to the allowedCommandUrls option in the art-custom-settings.json file.

curl -d "username=admin&password=admin&id=5" http://localhost:8080/art/runJob

Job Options

Some options can be defined in the Options field. Options are specified in JSON format and include the following.

Property	Data Type	Default	Description
logInterval	Integer	0	For jobs that use dynamic recipients, this specifies an interval or number of records after which an item is included in the job log to indicate progress of the job. A value of 0 means this intermediate logging is not done.

Job Types

Email (Attachment)

The output is emailed as an attachment to the specified recipients (in the "To" section). The attachment type can be selected in the Output Format field.

Email (Inline)

The output is emailed to the specified recipients, in the email body.

Publish

The output is saved to a file. The file is reachable from the Jobs page. Once the file is generated, a notification email is sent to the specified recipients (in the "To" section). Leave the "To" area empty if you don't want a notification email to be sent.

Alert

Send an email to the specified recipients if the first column of the first row in the query result has a value greater than zero. You can construct a simple SQL query to return a non zero value in case of a certain business condition.

Example query to be used for an alert. Send an email if a big order is made

    SELECT count(*)
    FROM todays_order_details
    WHERE quantity>100

Just Run It

Simply run the report. May be used to run stored procedures or perform data updates.

Conditional Email (Attachment)

If the result set has records, an email with the output attached is sent to the specified recipients. If it has no records, no email is sent.

Conditional Email (Inline)

If the result set has records, an email is sent to the specified recipients with the output contained in the email body. If it has no records, no email is sent.

Conditional Publish

If the result set has records, the output will available from the Jobs page. A notification email is sent to the specified recipients if this is configured. If the result set has no records, no output is available on the Jobs page and no email is sent.

Print

Prints the report output to the default printer

Burst

Generates report files for each distinct value in a resultset. This requires that the query for the report be ordered by the first column, which will be the burst-id column. When the value of this column changes, a new file is generated. Burst jobs are only possible with Tabular reports. The files generated will have the burst-id in their file names so different files can be identified and copied or ftped to different locations for example. The generated files are located in the \WEB-INF\work\export\jobs directory.

Shared Jobs

By default, only the owner of a job has access to its output, particularly for published jobs. Shared jobs allow the output of a job to be shared with other users. To share a job's output, enable the Allow Sharing field and select the users or user groups with whom the output will be shared using the Configure | Access Rights menu. These users will be able to access the job's output from the Jobs menu.

Split Jobs

If the report for a shared job uses rules, you can specify that the rule values for the shared users be applied so that each user may get different output. To do this, enable the Allow Splitting field. If this field is disabled and the report uses rules, the rule values of the job owner will be applied and all shared users will get the same output. If the report doesn't use rules, the value of this field will have no effect. A single, identical output will be generated for all users.

Random Start Times

You may not require an exact time when a job runs, as long as it runs in a certain window of time. If you leave the Hour and Minute fields blank when creating the job, the job will be assigned a random hour between 3-6 and a random minute between 0-59 in which it will run. This may be useful for jobs that your require to run at night. Additionally, you can specify an explicit time range in which the job should run. To do this, in the Hour field, define the required start time and end time separated by | e.g. 4|7, 4:30|6, 12:45|13:15. The job will then be assigned a random execution time in this range.

Quartz Properties

You can modify some aspects of the scheduler e.g. thread count by modifying the WEB-INF\classes\quartz.properties file. Documentation of configuration options can be found here. The following are example configurations.

Database Type	Quartz Configuration
PostgreSQL	`org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate` `org.quartz.dataSource.ArtDs.validationQuery = select 1`
SQL Server	`org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.MSSQLDelegate` `org.quartz.dataSource.ArtDs.validationQuery = select 1`

Running Jobs

You can see which jobs are currently running by using the Configure | Jobs menu and then click on the Running Jobs button. If you want the page to refresh automatically, you can modify the url and add the refreshPeriodSeconds parameter e.g. http://localhost:8080/art/runningJobs?refreshPeriodSeconds=30. The minimum refresh period is 5 seconds.

Holidays

A holiday is a specification of dates or times when a job should not run. Use the Configure | Holidays menu to manage holidays.

Field	Description
ID	An auto-generate ID used to identify the holiday configuration
Name	A name for the holiday configuration
Description	A description for the holiday configuration
Definition	The holiday configuration. The dates or times can be specified using quartz cron expressions as explained in the CronCalendar documentation, with each date specification on a new line. Alternatively, groovy code can be used, with the code returning a Calendar or a List of Calendars. If using groovy code, the field should start with "g[" and end with "]g".

Destinations
- Creating a new destination
- Options

Destinations

You can configure destinations to which publish job output is sent. Use the Configure | Destinations menu to manage destination configurations.

Creating a new destination

When adding a destination, the following fields are available.

Field	Description
ID	An auto-generated ID used to identify the destination
Name	A name for the destination
Alternative Names	Alternative names for the destination that can be used with Burst job output. Each name is on a new line.
Description	A description for the destination
Active	Whether the destination is active or not
Destination Type	The type of destination
Server	For FTP and SFTP destinations, the IP address or host name of the ftp or sftp server. For Network Share destinations, the IP address or hostname of the machine where the share is located.
Port	For FTP and SFTP destinations, the ftp or sftp port of the server. You can leave this as 0 to use the default port for the respective protocol.
User	The user to use to connect to the destination. For Amazon S3 destinations, this is the IAM user's access key id.
Password	The destination user's password. For Amazon S3 destinations, this is the IAM user's secret access key.
Private Key File	For SFTP destinations, the private key file to use if authenticating using public key authentication
Private Key Passphrase	For SFTP destinations, the passphrase of the private key file being used, if the private key file has a passphrase
Domain	For Network Share destinations, an optional specification of the user's domain
Path	The location of the destination. For Network Share destinations, this is the share name. For FTP and SFTP destinations, this is an optional directory path where files should be copied. For Amazon S3 destinations, this is the bucket name.
Sub-Directory	An optional sub-directory path within the destination where files should be copied
Create Directories	Whether directories in the path should be created before the file is copied. The user will need appropriate permissions for the creation of directories.
Options	Additional options for the destination

Note

For Amazon S3 destinations, the IAM user needs the s3:GetBucketLocation permission in order for the file upload to be successful. In addition, the user will need at least the s3:PutObject permission on the bucket where the file is to be copied.

Options

Some destinations can have additional options that can be specified. These are included in the Options field using JSON syntax.

FTP Options

For FTP destinations, the following options are available.

Attribute	Data Type	Default	Description
connectTimeoutSeconds	Integer	60	The timeout (in seconds) to use when connecting to the server.
controlKeepAliveTimeoutSeconds	Long		The time period (in seconds) after which to send a keep alive (NOOP) message. Zero disables.

SFTP Options

For SFTP destinations, the following options are available.

Attribute	Data Type	Default	Description
sessionConnectTimeoutSeconds	Integer	0	The timeout (in seconds) to use when opening a session. Zero indicates "no timeout".

For Network Share destinations, the following options are available.

Attribute	Data Type	Default	Description
timeoutSeconds	Integer	60	The timeout (in seconds) for read/write operations.
multiProtocolNegotiate	Boolean	false	For suspected SMB1 shares, this can be enabled in order to get an error message indicating that SMB1 is not supported.
smbDialects	String[]		The smb dialects to use as per the SMB2Dialect enum e.g. `"smbDialects": ["SMB_3_0"]`. Normally you would not need to set this, but you can set it to use a particular smb version/dialect.

Amazon S3 - AWS SDK Options

For Amazon S3 - AWS SDK destinations, the following options are available.

Attribute	Data Type	Default	Description
region	String	us-east-1	The region that will process the requests. This is not necessarily the region where the bucket is located. You can set a region near where the ART server is in order to get faster operation. See the s3 regions documentation for a list of possible region values.

SMTP Servers

SMTP Servers

You can configure smtp servers to be used by individual jobs. You may want a job to use a different smtp server from the one configured in the Settings page.

Use the Configure | SMTP Servers menu to manage smtp server configurations. When adding an smtp server, the following fields are available.

Field	Comment
ID	An auto-generated ID used to identify the smtp server configuration
Name	A name for the smtp server configuration
Description	A description for the smtp server configuration
Active	Whether this configuration is active or not. If it is not active, jobs that use this smtp server will not send emails.
Server	The IP address or host name of the smtp server
Port	The smtp port to use
Use StartTLS	Whether to use STARTTLS when connecting to the server
Use SMTP Authentication	Whether to use a username and password when connecting to the smtp server
Username	The username to use to connect to the smtp server, typically an email address on the server
Password	The password to use to connect to the smtp server
From	The "From" email address for jobs that will use this smtp configuration. This can be left blank in which case individual "From" email addresses specified within jobs will be used.

Note:

Some antivirus programs may block java applications from sending emails. If you get Connection refused errors, you can try and temporarily disable the antivirus program on the server and see if the connection will be successful. If so, find out how to add exceptions in the antivirus program and add an exception for the java.exe file in use. For Avast Antivirus, you may need to turn off the Scan outbound emails(SMTP) option in the Mail Shield.

Dynamic Recipients

Dynamic recipients allows you to email report results to a dynamic list of people. This may be useful where the recipients may change from day to day or week to week e.g. sending an email to staff who are low on their targets. It may also be useful to send filtered report results e.g. send to managers only the sales for their branch.

Using the dynamic recipients feature will involve the following process

Create the main report with the data to be sent to the desired recipients
Create a report of type Dynamic Job Recipients that will contain details of the recipients, including their email addresses
Schedule a job for the main report and select the dynamic recipients report you created in the Dynamic Recipients field

There are several ways to use dynamic recipients.

Dynamic Recipients only

If you want all the recipients to get the same data and the same email message, then define a dynamic recipients report with only one column. This column should contain the email addresses of the recipients. The name of the column doesn't matter. Example:

    SELECT email
    FROM employee

Dynamic Recipients + Personalization

If you want all the recipients to get the same data, but you would like some personalization of the email message e.g. having a greeting like Dear John instead of Dear Employee, then define a dynamic recipients report where the first column contains the recipient email addresses, and any additional columns that you would like to use in the email message e.g.

    SELECT email, first_name, other_name last_name, order_count
    FROM employee, orders
    WHERE employee.id=orders.employee_id

When defining the job for your data query, in the email message section, you can then use column labels as placeholders where personalized details will be put. The labels consist of column names from the recipients report surrounded by # signs e.g. you can have a message like

    Dear #first_name# #last_name#
    You are now at #order_count# orders, which is below the target of 100.

Each person in the dynamic recipients report list will get a personalized email with the column labels substituted with his values.

Dynamic Recipients + Filtering

If you want the recipients to get different data, you will define a dynamic recipients report where the first column contains the recipient email addresses, any additional columns with personalization information if you want, and 2 other special, hard coded columns named recipient_column and recipient_id. These will be used to filter the data query. i.e. WHERE <recipient_column> = <recipient_id>. The main, data query will also need to have a special, hard coded label, #recipient#, in the where clause.

If the values in the recipient_id column are numbers, include an additional hard coded column named recipient_id_type, with the value of this column being the string "number". You can include columns named open_password and modify_password to contain dynamic passwords that should be used for the dynamic output if using xlsx or pdf report formats.

Example:
If we want to email users only transactions that they created, our data query can be something like

    SELECT *
    FROM transactions
    WHERE transaction_date = CURDATE()
    AND #recipient#

We can then define a dynamic recipients query like

    SELECT email, "transaction_user" recipient_column, employee_id recipient_id, "number" recipient_id_type
    FROM employee

This will cause the #recipient# placeholder in the data query to be replaced with the condition transaction_user = <employee_id>, where <employee_id> will be different for each recipient, as per the dynamic recipients query. We can then schedule a job for the main report and set the Dynamic Recipients field to the dynamic recipients report, and all the recipients will get a separate email with their specific data only.

If we also want to include personalization fields in the email message body, we can add these to the dynamic recipients query e.g

    SELECT email, "transaction_user" recipient_column, employee_id recipient_id, "number" recipient_id_type, first_name
    FROM employee

and then we can include the personalization placeholders in the email message field as desired.

Note:

With the personalization and filtering options, you can include columns with the name email_cc, email_bcc or email_reply_to to specify email addresses that should be included in the cc, bcc or reply-to fields of the email sent.
Email address columns can have multiple email addresses separated by ,

Pipelines

A pipeline is a definition of jobs that should be run in a particular order. Use the Configure | Pipelines menu to configure pipelines.

Field	Description
ID	An auto-generate ID used to identify the pipeline
Name	A name for the pipeline
Description	A description for the pipeline
Active	Whether the pipeline can run or not
Serial Jobs	A comma separated list of job ids that should be run in the order given. You can use all to specify that all jobs should be run. You can also specify jobs that use a given schedule by using the syntax schedule:`<schedule name or id>` e.g. `schedule: daily`. You can specify multiple schedules by separating them with ; e.g. `schedule: daily;5`. You can also use + after a job id to indicate running of all jobs from that id to the last e.g. `13+`. You can also use - to specify job ranges e.g. `1-5`. You can specify jobs whose report belongs to a certain report group by using the syntax reportGroup:`<report group name or id>` e.g. `reportGroup: test`. You can specify multiple report groups by separating them with ; e.g. `reportGroup: test;admin`. You can also use - before the job id or definition to identify jobs that should be excluded e.g. `-5`.
Continue On Error	For Serial jobs, whether the next jobs should be run if the current one encounters an error
Parallel Jobs	A comma separated list of job ids that can be run in parallel. The definition is similar to that for the serial jobs field.
Duration (Mins)	For Parallel jobs, the duration in minutes in which the jobs should run
End Time	For Parallel jobs, the time that the job runs should end. Will be applied if Duration is not set.
Per Minute	For Parallel jobs, the number of jobs to be run per minute. Will be applied if Duration and End Time fields are not set.
Schedule	A schedule on which the pipeline will run
Start Condition	A condition to be satisfied before a scheduled pipeline is run
Error Notification Email	An email address that should be notified if an error occurs while running the pipeline. Multiple email addresses can be specified separated by commas. The SMTP server used is that configured in the Settings page. Only a single email will be sent at the end of the pipeline containing all the errors of different jobs in the pipeline.

You can initiate running of a given pipeline by running a command like the following on the command line. You would need to add "runPipeline" to the allowedCommandUrls option in the art-custom-settings.json file.

curl -d "username=admin&password=admin&id=5" http://localhost:8080/art/runPipeline

Start Conditions

A start condition is a definition of a condition to be checked before a scheduled job or pipeline runs. Use the Configure | Start Conditions menu to configure start conditions.

Field	Description
ID	An auto-generate ID used to identify the start condition
Name	A name for the start condition
Description	A description for the start condition
Retry Delay (Mins)	The amount of time to wait in minutes if the condition is not fulfilled, before making the next try
Retry Attempts	The number of times to retry the condition
Condition	The condition to check for before running the job or pipeline. This could either be an integer representing a report id, or groovy code which would return true or false. For a report id, the first column of the result needs to be an integer and if it's greater than 0, then the condition will be considered as fulfilled and the job will run. For groovy code, if the return result is true, then the condition will be considered as fulfilled and the job will run. If the condition is not fulfilled, it will be tried again after the delay interval. After the retry attempts are exhausted and the condition is still false, the job or pipeline will not be run.

Record Migration
- Export
- Import

Record Migration

You can export records from your art instance and import them into another art instance, or re-import them to your instance at a later date.

Export

To export records,

Use the Configure | Export Records menu, or go to the configuration page of the type of record you would like to export and use the Export or Export All buttons. The Export All button will export all records of the given type, while the Export button will export selected records.
Select the Record Type if not already selected. The All option will export all record types.
The IDs field contains a comma separated list of ids of records to export. If blank, all records of the given type will be exported.
Select the Location to export to
You can export directly to another art instance by selecting the Datasource location and selecting that instance's datasource in the Datasource field. You would need to have created a datasource that points to the art database for the target instance. Also note that Datasource export will not include template files associated with the objects. This may affect objects like reports, encryptors, parameters and these template files would need to be manually copied from the source instance to the destination instance. Also for Datasource export, if the useCache custom setting is enabled, you will need to use the Clear All cache option on the target instance or wait for about 10 minutes to ensure the exported records are properly recognized.
Click on the Export button to generate the export file or start the direct export to the selected datasource. Some export files may be generated as zip files depending on additional files that may be included in the export.
Export files are stored in the WEB-INF\work\export\records directory

You can automate export of all records by running a command like the following on the command line. You will need to have added the exportRecords endpoint to the allowedCommandUrls option of the art-custom-settings.json file.

curl -d "username=admin&password=admin&recordType=All" http://localhost:8080/art/exportRecords

Import

To import records,

Use the Configure | Import Records menu, or go to the configuration page of the type of record you would like to import and click on the Import button
Select the Record Type if not already selected. For the All option or if no record type is selected, the file names will be used to determine the type of record i.e. files starting with art-export- followed by the record type e.g. art-export-Datasources.json, art-export-Datasources(1).json, art-export-Datasources-22 Nov 2022.json.
Select the File to import. This will be a file from a previous export, and multiple files can be selected. Importing from an export file of a previous art version may be successful, or may import partially or with errors depending on changes that have happened between versions.
The IDs field contains a comma separated list of ids to import. If blank, all records in the given file will be imported.
The Existing Records field determines how to handle existing records, whether to skip, overwrite or attempt to insert records with the same name
The Use Export ID field determines if the import should use ids in the export file for inserted records
Click on the Import button to perform the import.
After the import is done, check the art logs for any "File not overwritten" messages, to see if you need to take any extra action.

Logs
- Configuring Loggers

Logs

ART uses the Logback library for application logging. By default, application logging is done to standard output and includes logging on errors, warnings and information messages. The location of the logs or the amount of logging generated can be modified by making appropriate configuration changes to the WEB-INF\classes\logback.xml file. Changes made to the logging configuration e.g. changing certain logging levels from "info" to "debug" automatically take effect after the configured scanPeriod. Scan period values can be specified as milliseconds, seconds, minutes or hours. See http://logback.qos.ch/manual/configuration.html for more details.

In addition to being available on the application server's standard output log files, e.g stdout.log or catalina.log on Tomcat, application logs can also be viewed from within ART. This means that you don't need to have access to the application server machine in order to view application logs.

Application logs can be viewed from the View | Logs menu. New logs are added to the bottom and you'll need to refresh the page to view them. Also note that this page doesn't display all the logs ever generated by the application, only the most recent ones.

Configuring Loggers

To add or modify logging for a particular class or package, you can modify the logback.xml file and add the logger there or alternatively, use the Configure | Loggers menu to add the logger. Loggers added from the application interface will be lost when the application is restarted.

Translating ART
- Online
- Offline

Translating ART

You can translate ART so that the user interface is displayed in your language.

Online

ART has a translation project on the Crowdin platform. You can create an account on Crowdin and join the ART translation project at the following link. https://crowdin.com/project/artreporting. You can then translate online. If your language is not available, create a post on the ART sourceforge
forum requesting for the new language to be added to the Crowdin project.

Offline

Download the latest ART package, unzip it and unzip the art.war file using any zip/unzip software. Get the ArtMessages.properties file from the ART_HOME\WEB-INF\i18n directory and and make a copy of it, naming your new file in the format ArtMessages_xx.properties, where xx is the ISO 639-1 language code for your language. A List of these language codes can be found here. An example would be ArtMessages_pt.properties for Portuguese. If your language is written differently in different countries you can add the 2 letter country specifier to the file name so that you have a file name like ArtMessages_pt_BR.properties for Brazilian Portuguese. You can find a list of country codes here.
Change all the text in your new properties file after the = signs to your language
If your language uses a non ISO-8859-1 (Latin-1) character set (e.g. chinese, arabic, russian etc) name the file ArtMessages_xx-UTF8.properties and then decode it to the ISO-8859-1 character set with UTF-8 escapes using the native2ascii tool that is available with your Java installation. An example command is as below.
```
native2ascii -encoding utf-8 ArtMessages_xx-UTF8.properties ArtMessages_xx.properties
```
Once you have your translated properties file in the i18n folder, you can restart the application server to make the new translation available to the application, or wait for 1 hour after which the messages are refreshed.
Now you can log in to the application and include the lang=xx parameter in browser url to activate the new language e.g. http://localhost:8080/art/reports?lang=de
If you would like the new language to be listed for selection within the application e.g. on the login page, add the language code and the language name to the ART_HOME\WEB-INF\i18n\languages.properties file, save the file and restart the application server.
If in addition you would like this new translation to be included in future ART versions, create a post on the Help forum and attach the file there.
Just a note that the ArtMessages.properties file is not the only one with strings to be translated, although it has the most commonly used strings.

Wiki: Home

ART Wiki

A Reporting Tool

Manual

Introduction

Administration Overview

ART Database

Settings

Options

OAuth Options

OAuth Providers

Microsoft Azure

OAuth Notes

Login redirect URL

Logout redirect URL

Global Logout

Updating the encryption key

Using an encryption key

Using an encryption password

Testing string encryption/decryption

Custom Settings

Config Directory

Datasources

Creating a new datasource

Options

Users

Creating a new user

Authentication

Internal Authentication

Windows Domain Authentication

LDAP Authentication

HTTP Header Authentication

OAuth Authentication

Roles

Creating a new role

Permissions

Access Rights

Access Rights Configuration

User Groups

Report Groups

Creating a new report group

Options

i18n Options

Reports

Creating a new report

Report Types

Tabular

Tabular (html only)

Charts

XY

Pie

Bars/Stacked Bars/Line

Time/Date Series

Speedometer

Chart Options

Update Statement

Text

Dashboard

Dashboard: Gridstack

JasperReports: Template Query

JasperReports: ART Query

Jxls: Template Query

Jxls: ART Query

FreeMarker

Velocity

Thymeleaf

PivotTable.js

LOV: Dynamic

LOV: Static

Dynamic Job Recipients

Dygraphs

DataTables

CSV

TSV

C3.js

Plotly.js

Chart.js

Leaflet

OpenLayers

View

Link