Create Calculated Field

Custom Reports Deprecation: Cornerstone will be deprecating the legacy reporting solution "Custom Reports" with the February ’21 Release. For more information, see the announcement in the May '20 release notes and in the May '20 Release Summary & Matrix.

Calculated fields enable organizations to generate aggregated reports based on totals of data. Organizations can create configurable calculated fields to generate calculated data totals. Administrators can filter calculations by data points such as gender, location, etc. using conditional statements. Note: Calculated fields are not available for multi-module reports.

Calculated fields can only be used in summarized reports. A report must be summarized by a field prior to adding a calculated field to the report.

Calculated fields can be created when creating new custom reports and when editing existing custom reports.

PERMISSION NAME PERMISSION DESCRIPTION CATEGORY
Calculated Fields - Modify Grants ability to view, modify, create, and delete any calculated fields that have been created by themselves or public fields in Analytics. This permission cannot be constrained. This permission works in conjunction with other Custom Report - Create permissions. Reports - Analytics

Locating the Create Calculated Fields Option

To create a calculated field, go to Reports > Analytics and click the Add New Custom Report link. Then, click the plus icon to the right of the Calculated Fields heading. This opens the Calculated Field Wizard.

Calculated Field Wizard

Enter the following information for the calculated field:

  • Name - Enter a name for the calculated field. The character limit is 500.
  • Type - Select whether the field is Public or Private.
    • If a field is private, the field is only visible and accessible by the user who created the field.
    • If a field is public, all users with permission to view calculated fields can view, copy, and utilize the field. Other users can also use any public field when creating another calculated field. However, only the user who created the field can edit the field.

Formula

The formula for a calculated field is built using the available fields and field functions in the Fields panel and the operator buttons in the Operators section.

Operators

Operators can be used to perform a calculation using the available fields (e.g., ({Training.Training Hours}*{Training.Training Price})).The available operators are Add +, Subtract -, Multiply *, Divide /, and Parentheses (). Click an operator button to add the operator to the current position of the cursor within the Formula field. If the cursor is not currently within the Formula field, the operator is added either to the beginning of the formula or to the previous location of the cursor within the Formula field. Note: When using parentheses, for each open parenthesis (i.e., "("), you must also include a closed parenthesis (i.e., ")").

Fields

The Fields panel displays a list of all available fields in Analytics, grouped by field type. Click a field type to view the fields within the field type. You can also search for a field using the Search field at the top of the Fields panel.

Fields can be added to the Formula field in the following ways:

  • Type the field name into the Formula field (e.g., ({Training.Training Hours}*{Training.Training Price})). Each field name should be surrounded by curly brackets.
  • Numeric fields appear in the Fields panel with a Numeric Field icon to the left of it and can be added to the Formula field by clicking the field name. This adds the name of the numeric field to the current position of the cursor within the Formula field. If the cursor is not currently within the Formula field, the field is added either to the beginning of the formula or to the previous location of the cursor within the Formula field.
  • Non-numeric fields appear in the Fields panel without a Numeric Field icon and can only be used in the Formula field using field functions. See the Field Functions section below for additional information.

Field Functions

Functions are available for all fields, numeric and non-numeric. Note: Functions may only include a single field, and which field types are allowed is dependent upon the function. Nesting of functions is not allowed.

A "G" at the beginning of the function name indicates that the function always returns group level values, even for individual rows.

To select a field function, hover the cursor over the field in the Fields panel. All available functions are displayed.

For non-numeric fields, only the GCOUNT and GCOUNTUNIQUE fields are available.

Function Field Types Allowed Syntax Description
ABS Numeric ABS({numericfield})

This function returns the absolute value for numeric field values.

Example: ABS({Transcript.FinalGrade})

This formula will return the absolute value of a Final Grade custom field.
GAVG Numeric GAVG({numericfield})

This function returns the average of all values for a numeric field. Note: This function aggregates values and returns group values, even for individual rows.

Example: GAVG({Training Price})*5

This formula will return five times the average cost of the training included in the report. Each row in a group will contain the same value.
GSUM Numeric GSUM({numericfield})

This function returns the sum of all values for a numeric field. Note: This function aggregates values and returns group values, even for individual rows.

Example: GSUM({User Salary})

If User Salary is a custom field within a portal, this function returns the sum of salaries of all the users in the report at each of the summary levels.
GMIN Numeric GMIN({numericfield})

This function returns the minimum of all values for a numeric field. Note: This function aggregates values and returns group values, even for individual rows.

Example: GMIN({User Salary})

If User Salary is a custom field within a portal, this function returns the lowest user salary out of all users that match the field criteria and report criteria.
GMAX Numeric GMAX({numericfield})

This function returns the maximum of all values for a numeric field. Note: This function aggregates values and returns group values, even for individual rows.

Example: GMAX({User Salary})

If User Salary is a custom field within a portal, this function returns the maximum user salary out of all users that match the field criteria and report criteria.
GSTDEV Numeric GSTDEV({numericfield})

This function returns the standard deviation of all values for a numeric field. Note: This function aggregates values and returns group values, even for individual rows.

Example: GSTDEV({User Salary})

If User Salary is a custom field within a portal, this function returns the standard deviation of salaries out of all users that match the field criteria and report criteria.
GCOUNTALL All GCOUNTALL({field})

This function returns the number of non-blank values for a field. Note: This function aggregates values and returns group values, even for individual rows.

Example: GCOUNTALL({Training ID})

This returns the total number of training IDs that match the field criteria and report criteria.
GCOUNTUNIQUE All GCOUNTUNIQUE({field})

This function returns the number of unique, non-blank values for a field. Note: This function aggregates values and returns group values, even for individual rows.

Example: GCOUNTUNIQUE(({Training Status})

This provides the number of unique transcript statuses found within the report.

Conditions

The Conditions section enables you to filter the data that is included when calculating the field value. For example, using conditions, you can filter the data by OU, date range, field values, or custom field values. Note: If an item in a report does not match the calculated field condition, the item is excluded from the formula calculation. However, the item is still included in the report and a value is not displayed for the calculated field. See the Use Cases section below for additional information on how calculated field conditions affect the report output.

Set the following options for each condition:

  • AND/OR - From the drop-down list, select whether the condition uses AND or OR logic. If the condition uses AND logic (intersecting logic), then the data record is included only if both conditions are met. If the condition uses OR logic (combining logic), then the data record is included only if either condition is met.
    • All AND/OR conditions are with regards to the initial condition. Because of this, the first condition does not have an AND/OR option.
  • Field Name - From the drop-down list, select the field that is used in the condition.
  • Operator - Select the appropriate operator for the condition (e.g., is equal to, is not equal to, is before, is on or after).
  • Value - Either enter or select the appropriate value for the condition.

The following are example conditions:

  • Training Purpose is equal to HTT.
  • User Last Hire Date is on or before 10/01/2012.

To delete a condition, click the Trash Can icon to the right of the condition.

Field value if condition is not met

In this field, select the value that is used if the conditions for the calculated field are not met. The following options are available:

  • Zero (0) - If this option is selected, then the calculated field returns a value of zero if the conditions are not met.
  • Null/Ignore - If this option is selected, then the calculated field returns no value if the conditions are not met.

Field value for calculation errors

In this field, enter the default value that is used for the calculated field if there is an error when calculating the field value. That is, if there is an error when calculating the field value in the report, this default value is used.

The default value is a simple static text value, and is displayed in reports exactly as entered. No formulas can be entered in this field.

Create, Save, or Cancel

After setting the formula and conditions for a calculated field, click Save in the upper-right corner of the pop-up. The system validates the calculated field formula. When a calculated field is created, it then appears in the Calculated Fields section for the corresponding report type. Calculated fields are also available for selection in the Fields panel of the Calculated Field Wizard.

To discard any unsaved changes to the calculated field, click Cancel in the upper-right corner or close the pop-up by clicking the X in the upper-right corner.

Use Cases

In the following use cases, the following data is used:

Calculated Field Formula = GAVG(UserSalary).

Full Name Division Salary
Curtis Simms Product Management 10000
Kumar Verma Product Management 20000
Jane Smith Product Management 40000

Use Case 1

No Calculated Field Conditions

Division Full Name Salary Average of Salaries
Product Management 23333.33
Curtis Simms 10000 23333.33
Kumar Verma 20000 23333.33
Jane Smith 40000 23333.33

In this scenario, the Average of Salaries column displays the average of all three users ((10000+20000+40000)/3=23333.33) because no conditions or filters are set. The same value is displayed in all rows because it is a group level function (GAVG).

Use Case 2

Calculated Field Condition: (UserSalary >= 20000)

Division Full Name Salary Average of Salaries
Product Management 30000
Curtis Simms 10000
Kumar Verma 20000 30000
Jane Smith 40000 30000

In this scenario, the Average of Salaries column displays the average of only the users that meet the calculated field condition ((20000+40000)/2=30000). Because the calculated field condition excludes salaries that are less 20000, Curtis Simms is excluded from the formula and has no value in the Average of Salaries column. However, Curtis Simms is still included in the report because no report filters are set.

Use Case 3

Calculated Field Condition: (UserSalary >= 20000); Report Filter: (UserSalary >= 20000)

Division Full Name Salary Average of Salaries
Product Management 30000
Kumar Verma 20000 30000
Jane Simms 40000 30000

In this scenario, the Average of Salaries column displays the average of only the users that meet the calculated field condition ((20000+40000)/2=30000). Because the report filter excludes salaries that are less 20000, Curtis Simms is excluded from the report and does not affect the Average of Salaries calculation.