• Personal tools
  • Log in
• Navigation
  • Main Page
  • Resources
  • Recent changes
  • Random page
• Toolbox
  • What links here
  • Related changes
  • Special pages

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 1 Adding a calculation 2 Aggregate calculations 3 Implementation notes 4 Removing a calculation

Adding a calculation

1. View the report you want to add the calculation to
2. Select the 'report fields' tab at the bottom of pane 3 then the calculations button on the left
3. 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