ART Wiki

A Reporting Tool

Brought to you by: eliboni, tanyona

Reports

Labels: manual (35)

Authors:

Reports

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, ods, slk 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, ods, odt 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 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
XMLA Datasource	For mondrian via xmla, the xmla datasource name as it appears in the datasources.xml file on the xmla server
XMLA Catalog	For mondrian or mondrian via xmla, the catalog name as it appears in the mondrian cube xml file. For SSAS, the database name.
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". Another RDBMS may use syntax like "exec my_sp".

Tag	Description
`:username:`	Replaced by the username of the user who is executing the query
`:date:`	Replaced by the current date (format YYYY-MM-DD)
`:time:`	Replaced by the current time (format YYYY-MM-DD HH:MI:SS)
`:reportId:`	Replaced by the report id
`:jobId:`	Replaced by the job id if running from a job
`:locale:`	Replaced by the currently chosen locale for the user interface e.g. en, de etc.

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

Crosstab

Rearranges the query output into crosstab format, exportable to spreadsheet, pdf etc. If you want values to be summed, include the summing in the SQL query e.g. select year,quarter,sum(amount) from orders group by year,quarter

The SQL result set is expected to have either 3 or 5 columns:

    SELECT xAxisCol "xAxisLabel", yAxisCol "yAxisLabel", Value FROM ...
    (data type: string, string, any)

    SELECT xAxisCol, xAxisAltSort, yAxisCol, yAxisAltSort, Value FROM ...
    (data type: string, string, string, string, any)

The AltSort columns are used to sort the x-axis (rows) and y-axis (columns). The following helps to illustrate this.

    /* input */                  /* input */
    // A Jan 14                   A 1 Jan 1 14
    // A Feb 24                   A 1 Feb 2 24
    // A Mar 34                   A 1 Mar 3 34
    // B Jan 14                   B 2 Jan 1 14
    // B Feb 24                   B 2 Feb 2 24
    // C Jan 04                   C 3 Jan 1 04
    // C Mar 44                   C 3 Mar 3 44
    //                          ^-----^------Used to sort the x/y axis

    /* output */                 /* output */
    //         y-axis                     y-axis          
    //           |                      |                
    //  x-axis - _   Feb Jan Mar           x-axis - _  Jan Feb Mar
    //           A    24  14  34                A   14  24  34   
    //           B    24  14  -                 B   14  24   -   
    //           C    -   04  44                C   04   -  44   
    //                   ^--- Jan comes after Feb!

Without the AltSort columns, as in the first output, Feb appears before Jan. This is because the string "Feb" is alphabetically before "Jan". However, Jan should appear before Feb because that is how they appear in the order of months. You can therefore use the month number in the sort column to ensure that Jan is displayed before Feb. Another example would be if you are displaying dates in a format like "Apr-2011" (MMM-YYYY). Apr-2011 is alphabetically before Jan-2011, so the alternative sort column could be set to YYYY-MM format (e.g. 2011-04) to order the period names in the right way.

Crosstab (html only)

Same as crosstab but limited to HTML output only

Tabular: Heatmap

Displays numeric information using shades of colour depending on the values. A number of options can be specified in the Options field to configure the output.

Property	Data Type	Default	Description
columns	Integer[]		Indexes of the columns that should be coloured, with the first column having an index of 1. If not specified, all columns are included in the colouring. If any cell in the applicable columns contains a non-numeric value, a javascript error will result, and will be indicated in the browser console.
colors	String[]		Colours that should be applied, beginning with colours for the lower values going up. The colour strings are specified in hex format e.g. `#ffffff`. For example for a two colour scheme in a red range, one can specify the setting as `"colors": ["#ffffff", "#ff4a53"]`
perColumn	Boolean	false	If true, each applicable column is graded and coloured separately. If false, the whole table is considered when grading the colours.

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

Bubble

    SELECT Value1, Value2 "SeriesName", Value3 [, normalisedValue3] FROM ...
    (data type: number, number, number [, number] )

Heat Map

    SELECT Value1, Value2, Value3 [, Option1, Option2, ...] FROM ...
    (data type: number, number, number [, string, string, ...] )


    Example:
    SELECT x, y, z, "upperBound=100"        
    FROM myvalues

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
```
For bubble charts, the size of the bubbles is determined by Value3. The actual size of the bubbles as they appear in the chart is also relative to the values on the y axis (Value2). If your values for Value3 are much larger than those for Value2 for example, you may find the whole chart filled in one colour, with no bubbles. In this case, you can increase the range of the y axis by setting the y-axis Min and y-axis Max fields for the query. Alternatively, you can include an extra column in the result set that contains a normalised value that will be used to scale the size of the bubbles e.g.
```
SELECT Value1, Value2 "MySeries", Value3,
Value3 * (SELECT max(Value2) FROM mytable)/(SELECT max(Value3) FROM mytable)
FROM mytable
```
For heat map charts, the following options are available. Option names and values are case sensitive. Options are defined in the result set with a column value whose syntax is <option>=<value> i.e. the option name and value separated by =

Option	Possible Values	Default	Description
upperBound	Any number, positive or negative	1	The highest value displayed. If you don't specify this option, the chart may appear blank if all your values are above 1.
lowerBound	Any number, positive or negative	0	The lowest value displayed
scalePos	top left bottom right	bottom	The default position of the colour scale relative to the chart
scaleLabel	Any string		The title of the colour scale
scaleTextPos	topleft topright bottomleft bottomright	topleft	The position of the scale title relative to the colour scale
scaleBorder	Hex colour code e.g. `#00E000`		The colour of the border displayed around the colour scale box
stripWidth	Any positive integer	10	Width/thickness of the colour scale
subdivisions	Any positive integer	10	For grey scale or a 2 colour scheme, i.e. if no `color#n` options are configured, the number of shades of grey or shades of the 2 colour scheme to use
`color#<n>` e.g. `color#1`, `color#2`	`<number>:<hex colour code>` e.g. `0.0:#FF0000`		The colour to use for a given range of values. The colour is determined by `Value3` of the result set. The number is the lower bound of the range. The option value has the lower bound and colour separated by `:` e.g. to display 0-10 in red and 11-50 in green, you'll define two columns in the result set, `"color#1=0:#FF0000", "color#2=11:#00FF00"`
lowerColor	Hex colour code		The colour of the lowest value if you want a 2 colour scheme with a linear gradient from the lower colour to the upper colour e.g. set `lowerColor` to white (`#FFFFFF`) and `upperColor` to red (`#FF0000`) if you want values to be represented as shades of red
upperColor	Hex colour code		The colour of the highest value if you want a 2 colour scheme with a linear gradient from the lower colour to the upper colour e.g. set `lowerColor` to white (`#FFFFFF`) and `upperColor` to red (`#FF0000`) if you want values to be represented as shades of red

Group

Groups the report data.

For example, if a query's tabular output looks like:

    AA | AA | B | C | D
    AA | AA | E | F | G
    AA | BB | H | J | K
    AA | BB | X | Y | Z

using "Group on 2 columns", the data would be grouped by the first 2 columns, and the output would look like:

    AA | AA
    B | C | D
    E | F | G
    AA | BB
    H | J | K
    X | Y | Z

For a "Group on n columns" report, the query must be ordered by the the first n-1 columns.

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 reports in a single portal-like page

Dashboard: Gridstack

Used to display reports in a single portal-like 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.

JPivot: Mondrian

Used to provide OLAP (slice and dice) analysis using the mondrian engine. The MDX for the query should be put in the source section and the xml file that defines the mondrian cube should be selected in the template field. More details about JPivot reports can be found in the JPivot Reports section of the manual.

JPivot: Mondrian XMLA

Used to provide OLAP analysis by accessing a mondrian server via the xmla protocol.

JPivot: Microsoft XMLA

Used to provide OLAP analysis by accessing an SQL Server Analysis Services server via the xmla protocol.

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. An example report is included in the demo database.

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 with the file and fileZip report formats

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. Also a NumberTool object named numberTool is available for formatting numbers e.g. $numberTool.format('#,##0.00',$result.amount). Similarly, a DateTool object named dateTool is available for formatting dates. An example report is included in the demo database.

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 with the file and fileZip report formats

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. An example report is included in the demo database.

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 with the file and fileZip report formats

ReactPivot

Generates a pivot-table-like report based on tabular data using the ReactPivot library. Javascript for the react pivot configuration needs to be specified in the Javascript field. This report type is useful when you want summary and analysis of numeric data by category.

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. Performance should be acceptable up to 100,000 rows, depending on the user's computer.

PivotTable.js: CSV Local

Enables generation of a pivot table using data from a user supplied csv file. Any delimited file can be used, not only csv.

PivotTable.js: CSV Server

Generates a pivot table based on data from a specific csv file uploaded to the server. This file can be uploaded using the Add files button. The file name will also need to be specified in the Options field as follows.

Property	Data Type	Description
dataFile	String	The name of the csv file e.g. `myfile.csv`

XDocReport: FreeMarker engine - Docx

You can use MS Word (docx), LibreOffice Writer (odt) or MS PowerPoint (pptx) docments as a template for reports. You simply create the document as you would want it to appear and then insert fields that will be replaced with data at runtime. This uses the XDocReport library. You can have a look at the XDocReport wiki for examples on how to create templates for different needs. The query results will be contained in a variable named results which can be used in the FreeMarker or Velocity expressions. You can also use an object named jdbc to run queries that you define within the template. This works the same way as with Jxls Template Query reports. See the Jxls Reports section for more details.

Displays a report based on the selected docx template, which uses FreeMarker syntax for expressions within the document. The query as defined in the SQL source will be used to generate the results.

XDocReport: Velocity engine - Docx

Displays a report based on the selected docx template, which uses Velocity syntax for expressions within the document. The query as defined in the SQL source will be used to generate the results.

For velocity templates you can use syntax like $!result.description in case a field may contain null values. Also for velocity templates, a NumberTool object named numberTool is available for formatting numbers e.g. $numberTool.format('#,##0.00',$result.amount). Similarly, a DateTool object named dateTool is available for formatting dates.

XDocReport: FreeMarker engine - ODT

Displays a report based on the selected odt template, which uses FreeMarker syntax

XDocReport: Velocity engine - ODT

Displays a report based on the selected odt template, which uses Velocity syntax

XDocReport: FreeMarker engine - PPTX

Displays a report based on the selected pptx template, which uses FreeMarker syntax

XDocReport: Velocity engine - PPTX

Displays a report based on the selected pptx template, which uses Velocity syntax

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.

Dygraphs: CSV Local

Enables generation of a dygraphs chart using data from a user supplied csv file. Any delimited file can be used, not only csv. The first column of the data, if a date, should have dates formatted as yyyy/MM/dd e.g. 2017/02/17, and datetime data formatted as yyyy/MM/dd HH:mm e.g. 2017/02/17 16:02 or yyyy/MM/dd HH:mm:ss e.g. 2017/02/17 16:02:59. The csv data should not contain any quotes.

Dygraphs: CSV Server

Generates a dygraphs chart based on data from a specific csv file uploaded to the server. This file can be uploaded using the Add files button. The file name will also need to be specified in the Options field as follows.

Property	Data Type	Description
dataFile	String	The name of the csv file e.g. `myfile.csv`

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

DataTables: CSV Local

Enables display of data from a user supplied csv file in a DataTables table. Any delimited file can be used, not only csv.

DataTables: CSV Server

Enables display of data from a specific csv file uploaded to the server. This file can be uploaded using the Add files button. The file name will also need to be specified in the Options field as follows.

Property	Data Type	Description
dataFile	String	The name of the csv file e.g. `myfile.csv`

Fixed Width

Generates fixed width output. The Options field determines the formatting of the output and can have the following properties.

Property	Data Type	Default	Description
fieldLengths	Integer[]. required		Used to define the field lengths of the output e.g. `[5,6]` means that the first field will have a length of 5 and the second field will have a length of 6
fieldLengthsByName	Object[]. required		Each object specifies the field name and the field length e.g. `[{"order_id": 8}, {"order_date": 14}]` means the `order_id` field will have a length of 8 and the `order_date` field will have a length of 14. You must specify either the fieldLengths or the fieldLengthsByName property.
includeHeaders	Boolean	true	Determines whether the column names will be included in the output as the first row
defaultAlignmentForHeaders	String		Either left, right or center. If present, overrides any field alignment set and uses the given alignment for the header columns
padding	String	space	A single character defining the default padding to use for fields. Must not be the empty string.
useDefaultPaddingForHeaders	Boolean	true	Determines if the headers should use the global padding setting or use the padding set for individual fields
dateFormat	String		Determines the format that should be used for DATE columns
dateTimeFormat	String		Determines the format that should be used for DATETIME columns
numberFormat	String		Determines the format that should be used for numeric columns
fieldDateFormats	Object[]		Each object specifies the date format and an array of fields that should use that format e.g. `[{"dd/MM/yyyy": ["order_date", "due_date"]}, {"dd-MMM-yyyy": ["payment_date"]}]`
fieldNumberFormats	Object[]		Each object specifies the number format and an array of fields that should use that format e.g. `[{"0.00": ["amount"]}, {"#,##0.#": ["volume"]}]`
fieldAlignmentByName	Object[]		Each object specifies the alignment to be used and an array of field names that should use that alignment e.g. `[{"right": ["amount", "volume"]}]`. The alignment is either left, right or center.
fieldAlignmentByPosition	Object[]		Each object specifies the alignment to be used and an array of field positions that should use that alignment e.g. `[{"right": [2, 5]}]`. The alignment is either left, right or center. The first field in the output is marked as position 0.
fieldPaddingByName	Object[]		Each object specifies the padding to be used and an array of field names that should use that padding e.g. `[{"_": ["amount", "volume"]}]`. The padding should be a single character.
fieldPaddingByPosition	Object[]		Each object specifies the padding to be used and an array of field positions that should use that padding e.g. `[{"_": [2, 5]}]`. The padding should be a single character. The first field in the output is marked as position 0.
extension	String	txt	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.
trimValues	Boolean	false	Whether to remove leading and trailing whitespaces in fields

The demo database has a few sample fixed width reports and jobs.

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
quotationTriggers	String	`"\"\n"`	String containing characters which should trigger quoting of a field. This is in addition to the delimiter which will always trigger quoting.

In addition to these properties, the following properties available with fixed width reports can also be used, having the same configuration and effect as in fixed width reports: includeHeaders, dateFormat, dateTimeFormat, numberFormat, fieldNumberFormats, fieldDateFormats, lineSeparator, trimValues.

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. This includes \n, \r, \t and \.
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

Datamaps

Enables display of data on a map, using the Datamaps library, using data from a database. Javascript for the datamaps 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
datamapsJsFile	String. required		The file name of the datamaps javascript file. Appropriate files can be downloaded from https://github.com/markmarkoh/datamaps/tree/master/dist.
width	String	500px	CSS width for the div that contains the map
height	String	300px	CSS height for the div that contains the map
dataFile	String		The file name of the json or csv file that contains data elements for the map. This file can be uploaded using the Add files option.
dataType	String	json	Either csv if the dataFile specified is in csv format or json if it's in json format.
mapFile	String		The file name of a file that contains the custom map in TopoJSON format. When using this option, use the datamaps.none.min.js datamaps js file. This file can be uploaded using the Add files option. It is important to note that if using the Add files option to upload files, files with multiple dots in the name are not allowed, so such files need to be renamed e.g. datamaps-none-min.js

If using a custom map and you have a shapefile, you can Import the zip file with shapefile specification files to http://mapshaper.org/ and then use the Export option to convert the shapefile to TopoJSON. You will then use the TopoJSON file for the report.

Datamaps: File

Enables display of data on a map, using the Datamaps library, using data from a csv or json file. Requirements and options are similar to those of the Datamaps report type.

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.

Saiku: Connection

Provides a connection that will be used to generate cubes in the Analytics view. ART uses the Saiku library to provide the Analytics functionality.

You would first create a Datasource from the Configure | Datasources menu. The Datasource Type should be OLAP and the Database Type will need to be either Olap4J Mondrian or Olap4j XMLA. You would then fill the other datasource details as appropriate and then specify this datasource in the Datasource field when creating the saiku connection report. After doing this, if you click on the Analytics menu and then the New query icon, you will see in the Cubes drop down, your connection and the cubes it has.

If you use an Olap4j Mondrian datasource, you can specify different roles that different users will have when using the connection. To do this, create a rule for the saiku connection report. Use a dummy column name. Next, specify rule values for that rule for different users. If you need to specify multiple roles, add multiple rule values.

Also for Olap4j Mondrian datasources, you can omit the Catalog part from the JDBC URL and instead upload the catalog (schema xml file) to use in the Template field. Note that when you test or save an Olap4j Mondrian datasource without specifying the Catalog, you will get an error. You can ignore this error if you will be specifying the Catalog in the saiku connection.

It is also worth noting that saiku uses mondrian 4, and as such if using olap4j mondrian, or mondrian xmla datasources, it is best if the schema xml files are in mondrian 4 structure.

Saiku: Report

When you perform some analysis from the Analytics view, you can save that analysis. When you do this, this is saved as a Saiku: Report type. The report source for this report type is a JSON representation of a saiku QueryModel. You can have a look at it to see what is generated. It isn't recommended to build this by hand.

Once you have created a saiku report, you can give users access to it like with other report types. The users will also need to be given access to the saiku connection that is the basis for the saiku report. Users will see a list of saiku reports they have access to by clicking on the Open query icon at the top of the Analytics view. Users can then run the reports, edit and save new reports, and delete reports they have created. Users cannot overwrite or delete reports for which they don't have exclusive access i.e. they are the only ones who have access to the report. Only junior admins and above can overwrite or delete any saiku report.

OrgChart: Database

Displays an organisation chart using data from a database. It makes use of the OrgChart library to display the org chart. The database query must have at least a column named id, another named parent_id, one named name and another named title. These names are case sensitive. The id column is a unique id among the returned records. It would typically represent a position. The parent_id column refers to the id that is the parent of the given record. The name column would typically be an employee name and appears at the top half of an org chart node. The title column would typically be the role or job title for the employee and would appear at the bottom half of an org chart node.

Javascript for the chart configuration can be specified in the Javascript field. Also, custom css can be specified in the CSS field. In addition, a number of options that can be specified in the Options field.

Property	Data Type	Default	Description
nodeTitle	String	name	The name of the field that will be displayed in the top half of a node
nodeId	String	id	The name of the field that is a unique identifier among all the records
toggleSiblingsResp	Boolean	false	Whether users can show/hide left/right sibling nodes respectively by clicking left/right arrow.
depth	Integer	999	The level that the org chart is expanded to when it is shown initially
exportButton	Boolean	false	Whether the export button is displayed
exportFilename	String	OrgChart	The name of the file used when exporting
exportFileextension	String	png	Either png of pdf. Determines whether the export will be to png or pdf
parentNodeSymbol	String	fa-users	The icon to be displayed for nodes that have children nodes under them
draggable	Boolean	false	Whether users can drag & drop the nodes. Note that this feature doesn't work on IE
direction	String	t2b	How the hierarchy is rendered. One of t2b ("top to bottom"), b2t ("bottom to top"), l2r ("left to right") or r2l ("right to left").
pan	Boolean	false	Whether users can pan the org chart by mouse drag&drop
zoom	Boolean	false	Whether users can zoom in or out using the mouse
zoominLimit	Double	7	The zoom in limit
zoomoutLimit	Double	0.5	The zoom out limit
nodeContent	String	title	The name of the field that will be displayed in the bottom half of the node. Set to null or the empty string to have this section omitted from nodes.

OrgChart: JSON

Displays an organisation chart using data from a JSON definition specified in the report source section

OrgChart: List

Displays an organisation chart using data from a html unordered list definition specified in the report source section

OrgChart: Ajax

Displays an organisation chart using data from a url specified in the report source section

ReportEngine

Generates tabular or pivot table output using data from a database. It uses the ReportEngine library. A number of options can be specified in the Options field.

Property	Data Type	Default	Description
groupColumns	Object[]		An array of group column definition objects. Each object has the following properties. id - a string for the column index or name of the group column, showDuplicateValues - a boolean for whether duplicate values should be displayed, index - an integer for the column index when used with the ReportEngine: File report type.
showTotals	Boolean		Whether sub-totals should be displayed when using group columns
showGrandTotal	Boolean		Whether the grand-total should be displayed when using group columns
sortValues	Boolean	false	Whether values are already sorted in the input data. When using group columns, the data must be sorted. If it doesn't come in as sorted, you can set this property to true.
dataColumns	Object[]		An array of data column definition objects. Each object has the following properties. id - a string for the column index or name of the data column, calculator - a string representing a calculator to use if a group column is in use. The string would be one of the following (case sensitive). SUM, COUNT, AVG, MIN, MAX, FIRST, LAST. calculatorFormatter - a string to determine how the calculator value should be displayed. This would be according to Java String.format() syntax e.g. "%,.2f"
pivot	Boolean	false	Whether to output the data as a pivot table
pivotHeaderRows	String[]		For pivot table output, column index or column names of columns to be used as pivot table header rows. For ReportEngine: File report types, this must be column indices.
pivotData	data column object		For pivot table output, the definition of the column that will provide the data for the pivot table
separator	String	,	For ReportEngine: File report types, the field separator used within the file
firstLineIsHeader	Boolean	true	For ReportEngine: File report types, whether the first line in the file is a header row
url	String		For ReportEngine: File report types, if the data is coming from a url, the url to the data file

A note that pivot table output may not display correctly with pdf, docx, odt report formats.

ReportEngine: File

Generates tabular or pivot table output using data from a file

MongoDB

Allows you to query a MongoDB database. To use it, first create a datasource of type MongoDB. Set the URL as appropriate. Next, create the report, setting the report Type as MongoDB and selecting the mongodb datasource in the Datasource field. In the report source field, enter the groovy script that will be used to retrieve data from the mongodb database. The syntax of querying can either be that of the MongoDB Java Driver, the Jongo library or the Morphia library. ART passes a MongoClient connection object to the groovy script with the variable name mongoClient. This is the variable that is to be used as the connection to the mongodb server. ART also passes any report parameters specified using the parameter name as the variable name.

If it is required to display the returned results in a table, the groovy script must return a List of objects that are to be displayed. Also, the groovy script is run through a sandbox and only classes contained in the WEB-INF\groovy-whitelist.txt file will be allowed to be used within the script. If you use a class that is not contained in the whitelist, a SecurityException error will occur when you run the report. If you get such an error, add the class mentioned to the whitelist and run the report again.

Example using MongoDB Java Driver syntax

import org.bson.Document;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoCursor;
import com.mongodb.client.MongoDatabase;

MongoDatabase db = mongoClient.getDatabase("test");
MongoCollection<Document> collection = db.getCollection("mycol");
MongoCursor<Document> cursor = collection.find().iterator();

List<Document> records = new ArrayList<>();
try {
    while (cursor.hasNext()) {
        records.add(cursor.next());
    }
} finally {
    cursor.close();
}

return records;

Example using Jongo syntax

import com.mongodb.DB;
import org.bson.Document;

import org.jongo.Jongo;
import org.jongo.MongoCollection;
import org.jongo.MongoCursor;

DB db = mongoClient.getDB("test");

Jongo jongo = new Jongo(db);
MongoCollection friends = jongo.getCollection("mycol");
MongoCursor<Object> cursor = friends.find("{title: 'MongoDB'}").as(Object.class);

List<Object> records = new ArrayList<>();
try {
    while (cursor.hasNext()) {
        records.add(cursor.next());
    }
} finally {
    cursor.close();
}

return records;

Example using Morphia syntax

package art.groovy

import dev.morphia.annotations.Entity;
import dev.morphia.annotations.Id;
import dev.morphia.Datastore;
import dev.morphia.Morphia;
import dev.morphia.query.Query;
import org.bson.types.ObjectId;


@Entity(value = "employeesCol", noClassnameStored = true)
class Employee{
    @Id
    ObjectId id
    String name
    String department
}

Morphia morphia = new Morphia();

morphia.map(Employee.class);

Datastore datastore = morphia.createDatastore(mongoClient, "employeesDb");

Query<Employee> query = datastore.createQuery(Employee.class);
List<Employee> records = query.asList();

return records;

For Morphia queries, you need to specify a package name for the groovy script. This package needs to be art.groovy in order not to get a security exception. The other alternative to not specifying this package name is to turn off the groovy sandbox within the WEB-INF\art-custom-settings.json file. Changes to this file require the Custom Settings cache to be cleared in order to take effect.

In the Options field, there are a number of attributes that can be specified.

Property	Data Type	Default	Description
columns	String[]		Names of columns that should be included in the tabular output. This may be useful when querying documents that may have different attributes. If not specified, ART will use the first document retrieved to determine the columns that will be included in the output.
columnDataTypes	Object[]		Each object specifies a column name and the data type that is to be used for that column e.g. `[{"col1": "numeric"}]`. The possible data types are string, numeric, date and datetime. The default is `string` so if a column is to be treated as a general string, it doesn't need to be specified.
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.
outputDateFormat	String	dd-MMM-yyyy	The format of output for columns with a date specification. The format is as per Java SimpleDateFormat format.
outputDateTimeFormat	String	dd-MMM-yyyy HH:mm:ss	The format of output for columns with a datetime specification. The format is as per Java SimpleDateFormat format.
showColumnFilters	Boolean	true	Determines if column filters will be displayed in the output.
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`.

Another alternative for querying MongoDB is to use the Calcite MongoDB JDBC driver under the JDBC datasource type. This would allow use of normal SQL to query MongoDB. Yet another alternative for querying MongoDB is to install and use Apache Drill. Drill allows you to use normal SQL to query a MongoDB database. When using drill, you wouldn't use the MongoDB datasource type or the MongoDB report type. You would use the JDBC datasource type with the drill driver, and then use any of the other report types e.g. Tabular etc. The drill jdbc driver can be obtained from the drill installation and copied to the ART_HOME\WEB-INF\lib directory.

Note:

Instead of using the MongoDB report type, one can specify any of the other report types e.g. Tabular, enable the Use Groovy option and use the kind of code as given above to generate the report.

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.

File

Outputs the contents of a query as it is e.g if using the "FOR XML" clause in SQL Server. 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

Link

Opens a link as provided in the Link field.

AwesomeChartJs

Generates charts using the AwesomeChartJs library. The resultset or returned data needs to have two columns with the first providing the labels and the second providing the data values. A number of options can be specified in the Options field.

Property	Data Type	Default	Description
width	Integer	600	The width of the chart in pixels
height	Integer	300	The height of the chart in pixels
title	String		The title of the chart
chartType	String	bar	The type of chart. One of pie, doughnut, exploded pie, horizontal bars, pareto, bar

ApexCharts.js

Generates charts using the ApexCharts.js library. Javascript for chart configuration needs to be specified in the Javascript field.

jqPlot

Generates charts using the jqPlot library. Javascript for chart configuration needs to be specified in the Javascript field and custom css can be specified in the CSS field. A number of options can be specified in the Options field. This is in addition to other other jqPlot options that can also be specified in the options field.

Property	Data Type	Default	Description
plugins	String[]		Names of plugins to be used, that correspond to file names in the art\js\jqPlot-1.0.9\plugins\ directory that will be included e.g. "barRenderer"
postTemplate	String		An optional file name of a javascript file that contains chart configuration. This javascript is run after the plot has been produced and so can be used to include event handlers. This file can be uploaded using the Add files button.

For the Javascript configuration, set the data to be used in the data variable, which has already been initialized as an empty array. Other options need to be set in the configOptions variable.

XML

Generates XML output.

JSON

Generates JSON output.

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"]`. A note that ods and odt report formats will not display images.
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
quotationTriggers	String	`"\"\n"`	String containing characters which should trigger quoting of a field. This is in addition to the delimiter which will always trigger quoting.

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, xdocreport, 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.
splitColumn	Integer	For group reports, the group column i.e. the column index by which grouping should be done e.g. `splitColumn=2`
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.
prettyPrint	Boolean	For json and jsonBrowser report formats, determines if the json output should be pretty printed
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.
directDownload	Boolean	For file output, determines if the file should be served directly instead of showing a "Download" link
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: (group)

    http://server:port/art/runReport?
    reportId=122&splitColumn=2&p-param=abc


    runs report 122 in group on 2 columns mode (splitColumn parameter). 
    The parameter named param is set to "abc".

Example 4: (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 5: (dashboard)

    http://server:port/art/runReport?
    reportId=124&p-param1=value1&p-param2=value2


    runs report 124, setting values for parameters param1 and param2

Example 6: (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 7: (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 8: (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
htmlGrid	Browser (Grid)	Shows results in a web page. The data can be sorted by clicking on the column headers. Not available for scheduled jobs.
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.
xls	Spreadsheet (xls)	Creates a Microsoft Excel spreadsheet file (xls format). Excel/OpenOffice/LibreOffice compatible
xlsZip	Spreadsheet (zip xls)	Creates a Microsoft Excel spreadsheet file (xls format), compressed using Zip
xlsx	Spreadsheet (xlsx)	Creates a Microsoft Excel spreadsheet file (xlsx format)
slk	Spreadsheet (slk)	Creates a Microsoft Excel spreadsheet file (slk format)
slkZip	Spreadsheet (zip slk)	Creates a Microsoft Excel spreadsheet file (slk format), compressed using Zip
tsv	File (tsv)	Creates a tab-separated-values file
tsvZip	File (zip tsv)	Creates a tab-separated-values file, compressed using Zip
tsvGz	File (gzip tsv)	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.
xml		Creates xml output displayed on the browser
rss20		Output data as a rss2.0 compliant feed
docx	Document (docx)	Creates a Microsoft Word document file (docx format)
odt	Document (odt)	Creates an Open Document Format text document file (odt format)
ods	Spreadsheet (ods)	Creates an Open Document Format spreadsheet file (ods format)
pptx	Document (pptx)	Creates a Microsoft PowerPoint document file (pptx format). Only available for XDocReport - PPTX reports.
json	JSON	Outputs data in json format
jsonBrowser	JSON (Browser)	Outputs data in json format, for display in a browser. Data is output in a `<pre>` tag.
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 (zip csv)	Creates a comma-separated-values file, compressed using Zip
csvGz	File (gzip csv)	Creates a comma-separated-values file, compressed using GZip
txt	File (txt)	For Fixed Width reports, creates a fixed-width text file.
txtZip	File (zip txt)	For Fixed Width reports, creates a fixed-width text file, compressed using Zip
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 File, FreeMarker, Velocity and Thymeleaf reports, outputs the results to a file
fileZip	File (zip)	For File, FreeMarker, Velocity and Thymeleaf reports, outputs the results to a file, compressed using Zip
xmlFile	File (xml)	For XML reports, outputs the results to a file
xmlZip	File (zip xml)	For XML reports, outputs the results to a file, compressed using Zip
jsonFile	File (json)	For JSON reports, outputs the results to a file
jsonZip	File (zip json)	For JSON 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.
Use xlsx, slk or tsv report formats for large data sets. In particular, if you use the xls format for large data sets, you are likely to get out of memory errors. This is mainly due to the nature of the xls file format.

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.

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

ART Wiki

A Reporting Tool

Reports

Reports

Creating a new report

Tags

Report Types

Tabular

Tabular (html only)

Crosstab

Crosstab (html only)

Tabular: Heatmap

Charts

XY

Pie

Bars/Stacked Bars/Line

Time/Date Series

Speedometer

Bubble

Heat Map

Chart Options

Group

Update Statement

Text

Dashboard

Dashboard: Gridstack

JasperReports: Template Query

JasperReports: ART Query

JPivot: Mondrian

JPivot: Mondrian XMLA

JPivot: Microsoft XMLA

Jxls: Template Query

Jxls: ART Query

FreeMarker

Velocity

Thymeleaf

ReactPivot

PivotTable.js

PivotTable.js: CSV Local

PivotTable.js: CSV Server

XDocReport: FreeMarker engine - Docx

XDocReport: Velocity engine - Docx

XDocReport: FreeMarker engine - ODT

XDocReport: Velocity engine - ODT

XDocReport: FreeMarker engine - PPTX

XDocReport: Velocity engine - PPTX

LOV: Dynamic

LOV: Static

Dynamic Job Recipients

Dygraphs

Dygraphs: CSV Local

Dygraphs: CSV Server

DataTables

DataTables: CSV Local

DataTables: CSV Server

Fixed Width

CSV

TSV

C3.js

Plotly.js

Chart.js

Datamaps

Datamaps: File

Leaflet

OpenLayers

Saiku: Connection

Saiku: Report

OrgChart: Database

OrgChart: JSON

OrgChart: List

OrgChart: Ajax

ReportEngine

ReportEngine: File

MongoDB

View

File

Link

AwesomeChartJs

ApexCharts.js

jqPlot