Reports

29 min

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:

PDF: best format for displaying the report to users, can easily be printed or shared
Excel: best format if post-editing is needed or further calculations are necessary. Great care is taken that all data in the reports is kept editable in Excel: numbers stay numbers so you can add sums or averages, dates stay dates so you can change ordering or group dates in pivottables.

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

Go to reports

Reports are meant to have a 'responsive' layout, so they look good on whatever page size or export format that is selected. As a consequence, reports are not meant to generate 'pixel-perfect' documents like invoices or order summaries. Use Documents for this purpose.

Getting started: set up a new report

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

{
  "subtitles": [],
  "content": {}
}

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

subtitles: an array containing the subtitles for the report
content: an array containing the actual content for the report

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:

{
  "subtitles": [],
  "content": [
    [
      {
        "type": "table",
        "query": "select firstname,lastname from tm.contact",
        "columns": [
          {
            "caption": "First name"
          },
          {
            "caption": "Last name"
          }
        ]
      }
    ]
  ]
}

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:

table
groupedtable
pivottable
subreport

Preview and testing environment

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.

Content types

Content item type markdown

This content item will be rendered as formatted text.

{
  "subtitles": [],
  "content": [
    [
      {
        "type": "markdown",
        "text": "# What is Lorem Ipsum?\n\nLorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book."
      }
    ]
  ]
}

Content item type table

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:

{
  "subtitles": [],
  "content": [
    [
      {
        "type": "table",
        "query": "select id,startts,nameen from tm.event order by startts",
        "columns": [
          {
            "caption": "Id",
            "width": "80px"
          },
          {
            "caption": "Date",
            "type": "dateminute",
            "width": "120px"
          },
          {
            "caption": "Name"
          }
        ]
      }
    ]
  ]
}

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

type: defines the type of content item, in this case table.
query: the query that is executed to retrieve the data for the table. you can use all views in the public data model here.
columns: array of column objects that define how individual columns should look

The number of columns should correspond exactly to the number of result columns from the query.

You can configure each column using the following keys:

Key	Description
caption	The caption to be used in the header for this column
type	defines how this column should be visually rendered, for example "datetime" or "percentage"
width	width for the column, for example "120px"
summary	defines what to use for this column in the (optional) summary row

And these are the column types that can be used:

Type	Description
number	right align
number0	right align, rounded to 0 decimals
number2	right align, rounded to 2 decimals
currency	right align, rounded to 2 decimals, formatted with €
currency0	right align, rounded to 0 decimals, formatted with €
percentage	right align, rounded to 2 decimals, formatted with %
percentage0	right align, rounded to 0 decimals, formatted with %
date	DD/MM/YYYY
datetime	DD/MM/YYYY HH:MI:SS
dateminute	DD/MM/YYYY HH:MI
right	right align
center	center align

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

{  
   "subtitles":[  

   ],
   "content":[  
      [  
         {  
            "type":"table",
            "query":"select \ntm.order.\nid, tm.order.createdts, \ncontact.lastname, \ncontact.firstname, \ntm.order.totalamount, \n(select count(*) from tm.ticket where orderid=tm.order.id) as nbroftickets\nfrom tm.order\nleft join tm.contact on tm.order.contactid=contact.id\norder by tm.order.id desc \nlimit 10",
            "columns":[  
               {  
                  "caption":"Id",
                  "width":"80px"
               },
               {  
                  "caption":"Created",
                  "type":"datetime",
                  "width":"120px"
               },
               {  
                  "caption":"Lastname"
               },
               {  
                  "caption":"Firstname"
               },
               {  
                  "caption":"Totalamount",
                  "type":"currency",
                  "summary":"SUM"
               },
               {  
                  "caption":"# tickets",
                  "type":"number",
                  "summary":"SUM"
               }
            ]
         }
      ]
   ]
}

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.

Remark in this example that the query contains \n characters that represent json-encoded newlines. This is done automatically for you when using the query editor in the Reports module: simply position the cursor in the query, and press the Edit query button. This will bring you to a query editor window where you can preview your query and format it over multiple lines. Correct json-encoding is done automatically.

Content item type groupedtable

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:

{
  "subtitles": [],
  "content": [
    [
      {
        "name": "Orders per sales channel per delivery scenario",
        "type": "groupedtable",
        "query": "select saleschannel.nameen as saleschannel, deliveryscenario.nameen as deliveryscenario, count(*) as nbroforders, sum(totalamount) as totalamount from tm.order\nleft join tm.saleschannel on saleschannelid=saleschannel.id\nleft join tm.deliveryscenario on deliveryscenarioid=deliveryscenario.id\n group by saleschannel.nameen, deliveryscenario.nameen\n order by saleschannel.nameen, deliveryscenario.nameen\n",
        "groupheader": "Sales channel {{ query.group }}",
        "columns": [
          {
            "caption": "Delivery scenario"
          },
          {
            "caption": "# orders",
            "type": "number0",
            "width": "15%"
          },
          {
            "caption": "Totalamount",
            "type": "currency",
            "width": "15%"
          }
        ]
      }
    ]
  ]
}

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:

{
  "subtitles": [],
  "content": [
    [
      {
        "name": "Orders per sales channel per delivery scenario",
        "type": "groupedtable",
        "query": "select saleschannel.nameen as saleschannel, deliveryscenario.nameen as deliveryscenario, count(*) as nbroforders, sum(totalamount) as totalamount from tm.order\nleft join tm.saleschannel on saleschannelid=saleschannel.id\nleft join tm.deliveryscenario on deliveryscenarioid=deliveryscenario.id\ngroup by saleschannel.nameen, deliveryscenario.nameen\n order by saleschannel.nameen,deliveryscenario.nameen\n",
        "groupheader": "Sales channel {{ query.group }}",
        "columns": [
          {
            "caption": "Delivery scenario",
            "summary": "Total for {{ query.group }}",
            "grandsummary": "Grand total"
          },
          {
            "caption": "# orders",
            "type": "number0",
            "width": "15%",
            "summary": "SUM",
            "grandsummary": "SUM"
          },
          {
            "caption": "Totalamount",
            "type": "currency",
            "width": "15%",
            "summary": "SUM",
            "grandsummary": "SUM"
          }
        ]
      }
    ]
  ]
}

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

Content item type pivottable

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

Consider following example:

{
  "subtitles": [],
  "content": [
    [
      {
        "name": "Orders per delivery scenario per payment scenario",
        "type": "pivottable",
        "rowheaderquery": "select id,nameen from tm.paymentscenario order by id",
        "rowheaderwidth": "250px",
        "colheaderquery": "select id,nameen from tm.deliveryscenario order by id",
        "valuequery": "select paymentscenarioid, deliveryscenarioid,count(*) from tm.order group by paymentscenarioid,deliveryscenarioid",
        "valuewidth": "80px",
        "valuetype": "number"
      }
    ]
  ]
}

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:

the id for the row
the id for the column
the value

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:

{
  "subtitles": [],
  "content": [
    [
      {
        "name": "Orders per delivery scenario per payment scenario",
        "type": "pivottable",
        "rowheaderquery": "select id,nameen from tm.paymentscenario order by id",
        "colheaderquery": "select id,nameen from tm.deliveryscenario order by id",
        "valuequery": "select paymentscenarioid, deliveryscenarioid,count(*) from tm.order group by paymentscenarioid,deliveryscenarioid",
        "valuewidth": "80px",
        "valuetype": "number",
        "rowsummary": true,
        "rowsummarycaption": "Total",
        "rowsummarywidth": "70px",
        "rowheaderwidth": "250px",
        "colsummary": true,
        "colsummarycaption": "Total"
      }
    ]
  ]
}

Content item type subreport

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:

{
  "subtitles": [],
  "content": [
    [
      {
        "type": "subreport",
        "name": "sub",
        "query": "select id,nameen from tm.saleschannel",
        "items": [
          {
            "name": "Orders per delivery scenario for sales channel {{ sub.nameen }}",
            "type": "table",
            "query": "select deliveryscenario.nameen, count(*) from tm.order\n left join tm.deliveryscenario on deliveryscenarioid=deliveryscenario.id\n where saleschannelid={{ sub.id }} \ngroup by deliveryscenario.nameen",
            "columns": [
              {
                "caption": "Delivery scenario"
              },
              {
                "caption": "Nbr"
              }
            ]
          }
        ]
      }
    ]
  ]
}

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.

Inline sql

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

The result of the query should always be a single string value.

Translation

A report definition can be made multi-language:

in queries you can use the {{ lang }} variable, that will be substituted with the current languagecode.
strings can be translated using the translation module.

Multiple sections

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.

Report settings

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

Setting	Description
Report info
Name	the name of the report
Description	a short description of the contents of the report that is useful for the user generating the report. Can contain an explanation of how the numbers in the report are calculated
Type	the report type defines the parameters that need to be filled in by a user when generating the report (see below)
Use in	where to use the report. Sales will make the report available in the Orders app. External sales will make the report available for External sales users
Output
Default format	default format for the report. A user can always choose the format he prefers.
PDF page size	PDF-specific option defining the paper size of the pdf when rendering the report. It can be specified by using keywords like A4, A4 landscape or US-letter, or by specifying actual sizes in cm or mm for example 10cm 20cm for a page of 10cm wide and 20cm high.
Excel page width	Excel-specific option defining the page width
Excel scaling	Excel-specific option for scaling the width

Report type and parameters

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:

Type	Description
1 - Simple	none
3 - Parameter Event	{{ eventid }}
5 - Parameter Period	{{ start }}, {{ end }}
9 - Parameter Period User	{{ start }}, {{ end }}, {{ userid }}
10 - Parameter Multi Event	{{ eventids }}
11 - Parameter Period And Multi Event	{{ start }}, {{ end }}, {{ eventids }}
12 - Parameter 2 Period	{{ start }}, {{ end }}, {{ start2 }}, {{ end2 }}

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.

{
  "subtitles": [],
  "content": [
    [
      {
        "type": "table",
        "query": "select date_trunc('day',createdts),count(*), sum(totalamount) from tm.order where createdts between {{ start }}::timestamp and {{ end }}::timestamp group by date_trunc('day',createdts) order by date_trunc('day', createdts)",
        "columns": [
          {
            "caption": "Date",
            "type": "date"
          },
          {
            "caption": "# Orders"
          },
          {
            "caption": "Totalamount"
          }
        ]
      }
    ]
  ]
}

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

Exporting and importing reports

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

Select the reports you want to export
Click on the Export button
A textarea appears containing a structured version of the selected reports. You can copy this and store it in a textfile for later reference

In order to import one or more reports:

Click on the Import button
Paste the exported report definitions in the textarea and press Import

Scheduling reports

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

Scheduling

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.

Only reports of report type 'Simple' can be scheduled.