Reports

Ticketmatic has powerful reporting capabilities. Use regular sql queries on the Ticketmatic public data model to quickly define new reports. Reports are automatically rendered in a good-looking uniform layout, allowing you to focus on the content of the report. The report layout will automatically adapt to the page size the user selects.

Each report can be rendered by the user in multiple formats:

Reports are managed in the Settings app. Click the button below to go to the Reports module:





Go to the Reports module and click on the Add button. A new report is created with an empty definition:

The definition is a json-structure, containing 2 main keys:

The content contains the main definition of the report and consists of an array of sections, where each section contains an array of content items.

For example, this is a report definition, whith 1 section, containing 1 content item:

This report will simply display a list of contacts in your account. In this example we use the content item table to display this list. There are 4 types of content items that can be used in sections:

While you are editing a report, you will see the Preview pane on the right displaying your changes in realtime. You can language and parameters to test how the report looks in that context.

This content item will be rendered as formatted text.

This content item will be rendered as a simple table, with a header and optionally a summary row. For each column in the table, you can define a caption, type, width and summary value.

Consider this example:

This example will show a simple list of events. Remark following fields in the content item definition:

You can configure each column using the following keys:

And these are the column types that can be used:

The summary key allows you to define an optional summary row. Consider following example:

This example will display a list of the last 10 orders, with the totalamount and the number of tickets per order. We add a summary row with the sum of all totalamounts and number of tickets by specifying "summary": "SUM" in the respective columns.

This content item type will be rendered as a grouped table. The result from the base query will be grouped by the values of the first column. To render a correct report it is mandatory that you put the first column of the base query as first item in the order by-clause of the base query. You can specify an optional groupheader that will be shown before each group, and an optional summary that will be shown after each group and can contain summary values. Finally, you can specify a grandsummary that will be shown after all groups.

Consider this example:

This example will display a table that is grouped by sales channel (= the first column in the query). For each sales channel, the number of orders and totalamount will be shown per delivery scenario.

The columns key should contain an array of columns for each column in the query, except the first column. You can specify the same keys per column as in the content item type table.

Remark the key groupheader: this will add a row for each group. You can use {{ query.group }} to reference the current value for the group. You can also use inline sql in the group header (see below).

Consider following example for adding summary and grandsummary rows:

By specifying summary and/or grandsummary keys you can automatically add (sub)totals for groups or the whole grouped table.

This content item type will be rendered as a pivot table with a dynamic number of columns and rows.

Consider following example:

This example will display a table with the number of orders per delivery scenario and payment scenario.

The rowheaderquery should contain a query that returns an id and a caption for each row. Similarly, the columnheaderquery should contain a query that returns an id and a caption for each column.

The valuequery should contain a query that has 3 columns:

You should make sure that there are no duplicates for the combination rowid, columnid in the valuequery resutl. The values from the valuequery will be placed in the correct cell, based on the ids for row and column.

You can add a row and/or column summary to the pivot table:

This content item type is actually a 'meta' content item type: it allows you to add a dynamic number of content items to a report, based on the results of a query.

Consider following example:

You define the main query in the query key. For each resulting row, the items in the items key will be generated.

The items can contain all valid content item types, including other subreports, so you can nest multiple levels deep if needed.

You can reference columns from the main query by using syntax {{ <subreport name>.<column name> }}. For example {{ sub.nameen }} in the example will be replaced by the name of the sales channel for that row.

You can use inline sql in the report definition in all places where a string is expected: for example the name of the report or the groupheader in a grouped table.

For example: <inlinesql>select name{{ lang }} from tm.event where id={{ id }}</inlinesql> will display the name of the event with id {{ id }}.

A report definition can be made multi-language:

All content items in a section are rendered to a grid with the same number and size of columns. This allows you to easily have multiple content items that are layed out coherently under each other. Different sections in a report are rendered to separate grids. (In excel for example they will be rendered as separate tab pages). This allows you to merge multiple data tables in a single report.

Go to the settings tab to configure the settings for the report:

An important property of a report is the type. Depending on the report type the user generating the report will need to fill in certain parameters. These parameters are available in the report definition to use in queries.

The table below displays the available report types and the corresponding parameters:

For example, when selecting type Parameter period, the user will need to enter a start and end date when generating the report. These dates can then be used in the report definition. Consider this example report that will generate a list of the number of orders and totalamount of order per day, for a selected period.

{{ start }} and {{ end }} will be replaced by the actual parameter values selected by the user.

You can export one or more reports in order to transfer them between accounts:

In order to import one or more reports:

Use the scheduling options in the report settings to periodically send the report to a selected list of recipients:

You can send a report once a week or once a month at a specified time. You can define one or more recipients, CC and BCC addresses.