Reports

• Reports
  • Creating a new report
  • Report Types
    • Tabular
    • Charts
    • XY
    • Pie
    • Bars/Stacked Bars/Line
    • Time/Date Series
    • Speedometer
    • Chart Options
    • Update Statement
    • Dashboard
    • JasperReports: Template Query
    • JasperReports: ART Query
    • LOV: Dynamic
    • LOV: Static
    • Dynamic Job Recipients
  • General Report Options
    • General Options
    • i18n Options
    • Tabular Options
    • CSV Format Options
    • HtmlDataTable Extra Options
    • Excel Options
  • Running a report via URL
    • Report Options
    • Chart Options
    • Examples
  • Report Formats
  • Reports Page
  • Running Queries

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 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 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 xlsx report format, this setting has no effect.
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
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.
Template	The template file to use for the report
Max Running Reports	The maximum number of concurrent runs allowed for this report. 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
Use Groovy	Whether the report source is groovy rather than SQL
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

Report Types

Tabular

A tabular result exportable to xlsx, 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

Dashboard

Used to display multiple reports in a single page

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.

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.

Note:

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

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

Tabular Options

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

Property	Data Type	Default	Description
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 the xlsx report format

CSV Format Options

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

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

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.
refreshPeriodSeconds	Integer	Enables a report to be re-run or refreshed automatically after the first run. Minimum is 5 seconds.

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=htmlPlain&p-startdate=2006-04-04&p-description=Desktop


    runs report 120 in html plain 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=xlsx&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=xlsx&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.
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

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.