System Roles

All

Reporting Rights

All Rights

Adding Calculated Columns to Accolade Excel 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 an Accolade Excel report created using the Accolade Office Extensions add-in, 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 Excel Calculated Columns Formula Example

To add a calculated column to an Accolade Excel 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 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. 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 left 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. Click Formula DataType Icon to 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. Finish setting up the report, as necessary.
  2. Click Done to apply your changes and generate the report.
  3. Save your changes to the 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 an Accolade Excel report, click Data Reports in the Accolade menu on the Excel ribbon and click the name of the report to open it for editing. Expand the Columns tab, and click Grey Delete Icon in the corresponding calculated column row to delete it. Click Done to apply your changes and generate the report and save your changes to the report.
Learn More: