Calculations
From GTwM
Calculations can be added to any report in agileBase.
Note: Before adding a calculation, it's best if the report contains some data. agileBase is better able to check for calculation errors if there's visible data.
Contents |
Adding a calculation
- View the report you want to add the calculation to
- Select the 'report fields' tab at the bottom of pane 3 then the calculations button on the left
- Fill in the details required and press Create Calculation
For the calculation definition, text is entered in SQL format, the only addition being that fields are referenced in agileBase format, i.e. surrounded by curly braces
{field name}
or if you need to reference a field in a particular table or report that's joined on to the current one
{report name.field name}
or
{table name.field name}
An example calculation definition is shown in the screenshot.
All SQL calculation operations can be entered, for example addition, subtraction, multiplication and division are performed with + - / and *.
The full list of functions available can be seen at http://www.postgresql.org/docs/current/interactive/functions.html
Some example calculations:
| Calculate VAT | {item.cost} * 0.175 |
| Work out a person's age | age({contacts.date of birth}) |
| Show a person's age in years only | extract(year from age({contacts.date of birth}) |
| Calculate the estimated completion date of a job, given a estimate number field that is the number of months to complete | (('1 month':: interval * {jobs.estimate}) + {jobs.start date}) |
| or if feeling pessimistic | (('3 months':: interval * {jobs.estimate}) + {jobs.start date}) |
| Generate a random dice throw | ceiling(random() * 6) |
Aggregate calculations
If you have joins in your report, then you can add in aggregate calculations such as totals and averages into the report.
Note: aggregate calculations can always be added in the report summary in pane 3 which is often an easier way to do so (and you get a nice chart).
Implementation notes
- Unlike standard SQL, you can reference another calculation in the report by name, you don't have to rewrite it
- Field names aren't case sensitive
- In fact, all calculation content will be converted to lower case on submission. The functions upper(), lower() and initcap() can be used to transform text if necessary
- Division by zero errors will be caught by agileBase in many cases. When this happens, null will be returned for the value
Removing a calculation
Once a calculation's been entered, it's treated just like any other report field. To remove it or change it's position in the report, keep the 'report fields' tab selected and press the 'fields' button
