Formula Row Type

Owned by Joss Herrmann
Last updated: Mar 24, 2017 by Deborah Pender (Unlicensed)
2 min read

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: Use a predefined SQL query (see Settings > SQL Query). These SQL queries can only be defined/edited by admins (for security reasons)
    • Conversion Row: Collects data from Invoice Lines Table
    • Alias Normalisation Factor: (currently not in use)

Enter display name and description and, as we want to count invoices, select type "Formular". 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.
  • Formula: The calculation of the result of the row based on row results, variables and static numbers. All basic arithmetic operators (+-*/) as well as brackets can be used.
    • Variables: 
      The variable will be substituted by the value of a report variable. Keep in mind, that this only works with number variables.
      Format: $<variable_name>
    • Row results:
      Similar to a variable, the variable will be substituted by the result of a row. A type "Formula" row has to be classified before the current row (sorting). Keep in mind, that this only works on single column results
      Format: $<row_name>
    • Functions (build in):
      • ifnull($<varible/row_name>, <alternative value>): if the <variable/row_name> is 0, the <alternative_value> is used

This tutorial video creates a report that analyses Desexing- Castrations and Speys. A formula is used to add together the desexing total and targets are created based on national averages.