Reports

ticketmatic has powerful reporting capabilities use regular sql queries on the ticketmatic public data model https //www ticketmatic com/docs/db 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 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 docid\ gczf4ohu7omfvriqxraef 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 http //en wikipedia org/wiki/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