Create a Calculated Column

Permissions

You will require an Access Role with the following permissions:

• Reports

Create a Calculated Column

There may be certain cases where the specific data that you require from a report is not provided as standard from the Reports feature. In many cases, the information required can still be obtained, but only by building a formula that utilises existing columns, mathematical and logical expressions. The Calculated Columns tool allows you to do this.

1. From the Report Details page, click the Columns tab.
   
   
2. Click  . This will display the New Calculated Column window where you can build and define the calculated column.
   
   
   
3. Enter a Column Name. This will appear in the column header on the report preview.
   
   
4. Click on a function from the Function List to add it to the Formula Build Area. Functions are grouped into sections such as Date & Time and Financial which can be expanded to show the relevant functions within.
   
   
   

   Tip: A tooltip provides an explanation of how a function works and what each of the values within the function require you to enter. Hover your mouse over a function, within the Function List, to display the correct help information.

   
   
   
5. Replace the text, within the formula, for the values which need specifying (in this example year, month and day).
   
   
   

6. Optional: Enter operators (+ - / * etc.), if required, to build more complex formulas.

   
   
   

   Note: The formula will be continually checked for validity while it is within the Formula Build Area, providing access to the Save button when valid.

   
   
7. Click Save to create the Calculated Column. This will now appear in the report you are creating/editing.

Example

You require a report which tells you if an Expense Item total exceeds a specific value. A Calculated Column can be created which tells you if the claim total value is higher than a specified value, enabling you to identify high levels of spending within your organisation.

1. Click IF from the Logical section of the Function List.
   
   
2. This will provide you with the following values which need specifying:
   
   
   • [logical_test] -  Replace [logical_test] with [Total] by clicking on the field within the Function List, then cutting and pasting it in place of [logical_test].
     
     
   • >XXX - Type in > followed by the value you would like the expense item to exceed. For this example, 99 will be used.
     
     
3. Specify the following:
   
   
   • [value_if_true] - Replace [value_if_true] with the text that you want to appear if the Expense Item exceeds £99. This example displays "Exceeds".
     
     
   • [value_if_false] - Replace [value_if_false] with the text that you want to appear if the Expense Item does not exceed £99. This example displays "OK".