System Roles

All

Reporting Rights

All Rights

Adding Calculated Columns to Online Reports

Reporting details can be contained in multiple sources within Accolade. To combined related data values to be used for reporting or the creation of charts or presentations, users can create reports that include calculated columns that calculate and/or display combined data, allowing users to manipulate and analyze Accolade data values.

For example, a user may have several different project metrics that represent categories of project costs such as Administrative, Operating, Marketing, etc. In order to calculate and display the sum of these project costs within an Accolade report or chart, the user can include a calculated column in their report setup.

Example Icon

Writing a calculated column formula in Accolade is slightly different than writing an equation in math class, or using formulas in Excel. The most notable difference is the use of the equal sign (=).

For example, if you want to add the numbers 3 and 2:

  • In math class, the equation would read 3+2=, with a calculated value of 5.
  • In Excel, formulas start with the equal sign, so it would look like =3+2. When entered, the value in the selected cell would return as 5. If you wanted to add two cell values, it would look something like =A1+B1 or =SUM(A1:B1). Assuming the values in A1 and B1 were 3 and 2 respectively, it would return the value 5.

General Excel Formula Example

  • In Accolade online reporting, the equal sign is assumed, so it does not need to be included in the formula field. If you wanted to add the values of Column A and Column B, it would look like [Column A] + [Column B] or Sum([Column A], [Column B]). Assuming the first row values of Column A and Column B were 3 and 2 respectively, it would return the value 5.

General Online Reporting Calculated Columns Formula Example

To add a calculated column to an online report:

Note:  See below for a list of calculations that are currently supported for reporting.

  1. Create a new report or open an existing report for editing.
  2. On the Selected Columns tab, ensure the columns to be used in the calculation are included in the report.

Report calculations are executed from left to right. In order for selected columns to be used in a calculated column, they must a) be selected as a column for the report, and b) be included before they are referenced by a calculated column (this means they must appear to the left of the calculated column when displayed, or above the calculated column when selecting columns in the report setup).

  1. Make a selection in the Show check box to show or hide columns within the displayed report.

If you want to use the columns as a reference for calculated column formulas but do not wish to display them in the report, clear the Show check box. Hidden columns can still be used as a reference for calculated columns, but will not appear in the report when displayed.

  1. Click Green Add Icon Add Calculated Column.

The column is added with two fields - one for the display name for the column, and one for the formula to be calculated.

  1. In the top field, enter the column name to be displayed when the report is generated.
  2. In the Formula Icon field, enter the formula to be calculated within the report.

When entering your formula, the columns are referenced by enclosing the column display name inside of square brackets. In the example above, Column A is entered as [Column A].

  1. Select the appropriate datatype for the calculated column.
  • Date - Select this option when you want the column data to display as a Date datatype, for example when using the DateAdd function to project a future project date.
  • Number - Select this option when you want the column data to display as a Number datatype, for example when using the Sum function to combine the values of two or more columns of expense values.
  • String - Select this option when you want the column data to display as a String datatype, for example when using the Concatenate function to display user name and functions combined in one column.
  1. (Optional) Drag and drop the calculated column to a new location within the list.

Note that while the calculated column cannot be displayed in the report until after its reference columns are displayed, you can have calculated columns that are displayed between data columns as necessary. Using the example above, after the Sum column we could add Column C and Column D to the report and then add another calculated column, and so forth.

  1. Click OK to exit the dialog and return to the report settings.

  2. Finish setting up the report, as necessary.
  3. (Optional) Click Report Preview Icon in the upper right corner to display a preview of the report contents prior to saving, or click Report Download Icon to download the report to a spreadsheet file.

  4. Click Save to create a new report, or to save changes to an existing report.

Available Formulas for Calculated Columns in Reporting

The following section details the operators and functions that are available for use when creating your calculated column formulas.

Important! Note that the examples use "Column A", "Column B", and "Column C" to generically refer to the display names of different columns that are included in the report data.

Operators

Operators are process or mathematical parts of expressions, such as addition and multiplication, and relationships such as greater than or less than. Use the operators below within your calculated column expressions.

Operator Example
Additive and Subtractive Operators
+ (plus, plus sign) [Column A]+[Column B]
- (minus, dash) [Column A]-[Column B]
Multiplicative Operators
* (multiplied by, asterisk) [Column A]*5
/ (divided by, slash) [Column A]/[Column B]
% (mod, percent sign) [Column A]%10
Primary Operators
value (integers, text strings, dates, functions)

('this is a text string')

While single quotes work for dates, we recommend wrapping dates in pound or hash signs instead to ensure consistency of data types. For example, (#2021-10-19#).
( ) (parentheses) 1000-(6*[Column A])
Relational Operators
= (equals) [Column A]=250
<> (does not equal) [Column A]<>[Column B]
> (greater than, angle bracket) [Column A]>250
>= (greater than or equal to) [Column A]>=250
< (less than, angle bracket) [Column A]<500
<= (less than or equal to) [Column A]<=100
Logical Operators
or, || (double pipe) [Column A]<[Column B]||[Column A]<500
and, && [Column A]<[Column B]&&[Column A]<1000

Functions

The functions included in the calculated column formula determine in part what is returned in the column. Use the functions described below within your calculated expressions to return data within a calculated column.

For more information on the description or formatting for the calculations below, see Calculated Metric Expressions Reference.

Function Example Formula
Abs Abs([Column A])
Acos Acos([Column A])
Asin Asin([Column A])
Atan Atan([Column A])
Avg Avg([Column A], [Column B], [Column C])
Ceiling Ceiling([Column A])
Concatenate Concatenate('text1',[Column A], 'text2', 'textN')
Contains Contains([Column A], 'My Blue Heaven', 2, 7)
Cos Cos([Column A])
DateAdd DateAdd('D', 5, [Column A])
DateDiff DateDiff('D',[Column A], [Column B])
DatePart DatePart('M', [Column A])
Exp Exp([Column A])
Find Find([Column A], 'My Blue Heaven', 2, 20)
First First([Column A], [Column B], [Column C])
Floor Floor([Column A])
IEEERemainder IEEERemainder([Column A], [Column B])
If If([Column A]>500, 'Go ahead', 'Stop')
In In([Column A], 1, 2, 3)
IsNull IsNull([Column A], [Column B])
Join Join([Column A], [Column B], [Column C]), '|')
Length Length([Column A])
Log Log([Column A], 10)
Log10 Log10([Column A])
Lower Lower([Column A])
LTrim LTrim([Column A])
Max Max([Column A], [Column B], [Column C])
Min Min([Column A], [Column B], [Column C])
Now Now()
Pow Pow([Column A], 2)
Replace Replace([Column A], 'Blue', 'red')
Round Round([Column A], 2)
RTrim RTrim([Column A])
Search Search('blue', [Column A], 5, 30).
Sign Sign([Column A])
Sin Sin([Column A])
Sqrt Sqrt([Column A])
SubString SubString([Column A], 4, 10)
Sum Sum([Column A], [Column B], [Column C])
Tan Tan([Column A])
Trim Trim([Column A])
Truncate Truncate([Column A])
Upper Upper([Column A])

Notes:   

  • To delete a calculated column from a report, click the name of the report on the Charts & Reports page and click Report Details Icon to display the report columns. Click the Selected Columns tab, and click Grey Delete Icon in the corresponding calculated column row to delete it. Click OK to close the dialog and click Save to save the changes to the report.
Learn More: