Calculations

Calculations

Calculations can be added to any view in agileBase.

Note: Before adding a calculation, it’s best if the view contains some data. agileBase is better able to check for calculation errors if there’s visible data.

Adding a calculation

1. Load the view 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:

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. 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 ‘view fields’ tab selected and press the ‘fields’ button

Helpful Hints

Just a small pointer to help… rather than use “IF / ELSE” statements use “CASE WHEN”.