Building Transaction Reports

Owned by Joss Herrmann
Last updated: Sept 07, 2016 by Emma Pearce (Unlicensed)
5 min read

Create Report:

Create a new Report (Settings > Reports > Create Report) and fill in the required fields.

Select "Transaction Report" (Type dropdown field)

 Click "Create"

Create Variable:

Since we have no rows added yet, we only see a list of existing global variables grouped by the first part of the variable name (separated by a '_'). Global variables are available to every report. Please note that reports variables with the same name (case sensitive) are overriding global variables.

To make our report more reusable, we're going to create a report variable for the date period we want to use for counting the transactions. 
Click on "Add Variable" (middle of page):

 Input Fields:

  • Name: The name can only contains letters and '_'. The first part of the name separeted by a '_' represents the category the variable is added to when viewing. The only effect of a category is to visually group the variables.
  • Description: A description what it is used for
  • Type: Type of the variable (see description on the right)

In our case we select a "Date Range Variable" which contains a start and a end date, the period we count the transactions in.


After clicking "Create", the variables are grouped be "Report Scope" and "Global Scope" since we added our report variable "period".

Create Row:

Now we click "Add new Row" to create our first row:

Input fields:

  • Display Name: see report fields
  • Name: see report fields
  • Description: see report fields
  • Type: The type of a row describes where it collects the data (see description on the right). Depending what type is selected the options to configure the type are displayed below:
    • Invoice: Collects data from the invoices table
    • Invoice Line: Collects data from the invoice lines table
    • Profit and Loss: Collects data from the ledger accounts imported from the bookkeeping software
    • KPI: Collects data from "Manual Data Points" (time based static values) (see Settings > Manual Data Points)
    • Formula: Use existing report variables or row results to calculate a value
    • /wiki/spaces/SVA/pages/10420360: (Structured Query Language) Use a predefined SQL query (see Settings > SQL Query). These SQL queries can only be defined/edited by admins (for security reasons)
    • Alias Normalisation Factor: (currently not in use)

Enter display name and description and, as we want to count invoices, select type "Invoice". The options are now displayed below:

Input Fields:

  • Visibility: Select if the row should be displayed on the dashboard table/chart. Select "hidden" if the result is only used for calculations.
  • Aggregate: A row can only have one result (but different columns in case of grouping). Select the method how the the results (of each column) should be aggregated.
  • Column: The database column used for calculation or a column based function (like "distinct") depending of the selected aggregate function.
  • Date Filter: The Date Range Variable (Global or Report scope) that contains the date period where the invoices are collected from. Please note that reports variables with the same name (case sensitive) are overriding global variables.
  • Filter Button: It's possible to add additional filters by comparing a column to a variable (global/report scope, only matching variable types are shown), e.g. invoice "Total Value" "greater than" "number_zero" to count only "real" invoices
  • Filter Logic: allows boolean operations on the defined filters (currently not implemented)

In our case, the form should look like this:

After clicking "Create" we should have the preview of our row at the bottom of the report screen:

If there is a zero amount, check what the date is that you are currently creating the report. If it is at the very start of a month the report will try to pull data from the month you are in. E.g. 7 September, there will only be 7 days worth of data which could be nothing or very little. 

Change Period:

To change the period in which we count the invoices, we have to select our "Report Scope" > "period" variable and press the edit tool after the current value:

In the popup window for editing the Date Range Variable we have the following options (the options are depending to the variable type, the Date Range Variable in our case. See "Variable Types" for other types):

Input fields:

  • Range: Select different types of periods, in our case select "Fiscal Date"
  • '-': specify how many years/quarters you want to go back from the current date/period
  • years/quarters: the type of period

  • Length of Period: How long should the period be (years/quarters)

To calculate the invoices your customised date range, it should look like the following:

Press "Save" than "Update" to see the new result at the preview section at the bottom.

Adding the new Report to a Dashboard

First we should create a new dashboard. You can also add the report to an existing dashboard but keep in mind that on the predefined dashboard the changes will be overwritten on the next update.

After adding the report to the dashboard, the output can be configured on the dashboard (see Configure a Report on a Dashboard).