Create Calculated Field

Custom Reports Deprecation: Cornerstone has begun deprecating the legacy reporting solution "Custom Reports" as of August 2022. See Deprecation of Custom Reports to Begin in August 2022.

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.

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.

You cannot add the following fields to a calculated field:

Training Language
Language (Curriculum)
Training Purpose (Curriculum)
Language (Playlist)
Training Purpose
Distributors
Language
Training Skills
Training Competencies

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.