Menu ▾ ▴

Reports

manual (35)
Timothy Anyona Timothy Anyona

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.

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 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

OR

    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

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.

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.

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 Default 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 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.

Related

Discussion: Using postgres cast in :: stye leads to error
Discussion: calling reports directly with parameters.
Wiki: Settings

Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.