Create Calculated Field

All users who have permission to create reports in Reporting 2.0 can create calculated fields. Fields are created from the Calculated Fields area of the Data panel in the report builder. See View My Calculated Fields.

However, to publish fields globally, users must have permission to manage global calculated fields. If a user does not have that permission, then the option to publish the field globally cannot be toggled on.

PERMISSION NAME PERMISSION DESCRIPTION CATEGORY
Reporting - Manage Global Calculated Fields

Grants the ability to publish calculated fields to all users. This permission cannot be constrained.

Note: Calculated fields can be created by all users who have permission to create reports in Reporting 2.0. However, in order to publish the calculated field globally, a user needs permission to manage global calculated fields.

Reporting

To create a calculated field, click the plus icon to the right of the Calculated Fields section in the report builder. This opens the calculated field editor. Inline help text appears for some fields on the editor when you hover over the field.

Step 1 - Enter Name/Description

Enter a name for the field in the Name field, up to 80 characters.

Enter a description in the Description field, up to 500 characters. Note: The description will be visible to all users if the field is a global field.

Step 2 - Select Output Type

The output type is the data type of the field. Select one of the following in the Output Type field:

  • Boolean
  • Datetime - When you select this option, you can also disable the Localize time zone toggle under Date Format. The toggle is enabled by default, which applies the user’s local time zone automatically. When the toggle is disabled, Reporting 2.0 applies UTC time zone to date and time values in the calculated field.
  • Number - When this option is selected, you can also define a decimal point value between 0 and 10. The default value is None. Note: Selecting "None" in the Decimal Point drop-down means that no rounding should be applied.
  • String

Step 3 - Create Formula

The Formula field is where you create your formula for the calculation.

Standard and custom report fields, as well as existing calculated fields, are available to add from the Fields section. Sometimes, a standard report field and a custom report field may have nearly identical names. In such cases, the calculated field recognizes and includes the exact field that is selected by the report owner. The fields that display are based on the current state of the report, so some fields will not be available to include because they are not available for the type of report you are creating.

You can filter the fields by entering field names to narrow down your search.

Add a field to your formula by clicking the field. The field appears in the formula and will have curly brackets around it.

Note: When adding a calculated field to the formula, the field description displays at the bottom of the Fields tab. Calculated fields can only be referenced up to one level.

Note: When both standard and custom fields have the exact same names, the calculated field includes the standard field value by default.

Operators are used to perform a calculation using the fields you have added to your formula (e.g., ({Training.Training Hours} * {Training.Training Price})).

Click Show Operators to view the operators. The operator will be added 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., ")").

The following operators are available and must be entered into the formula manually:

Math Operators Logical Operators Text Operators

Add

 +

Equal

 = and == Concatenate &

Subtract

 -

Not Equal

 <> and !=    

Multiply

 *

Less than

 <    

Divide

 /

Greater than

 >    

Parentheses

 ( )

Less than or Equal

 <=    
Power of (e.g., 2^3 = 2x2x2 = 8) ^

Greater than or Equal

 >=    
   

AND

 &&    
   

OR

 II    

Functions are organized into categories based on math, logic, date, and text. Selecting a function shows its description at the bottom of the Functions tab.

To add a function, double click it or select it and hit the Enter key. The function is added at the cursor's position in the Formula field, and then the cursor is repositioned at the end of the object and is followed by a space.

If the function takes at least one argument, then the function is added to the Formula field at the cursor's position, then the Formula field cursor is repositioned between the function's parentheses.

You can also nest one function within another function up to five levels in the Formula field, except for an aggregate function which can be nested up to one level within another non-aggregate function.

  • Aggregate functions cannot be nested within another aggregate function.
  • Aggregate functions can only be nested up to one level with non-aggregate functions (e.g., logical, numeric functions).
How to View Help Text for Functions

Help text is included for each function. To view the help text, click a function. The explanation of the function appears at the bottom of the Functions tab.

The following functions are available:

Function Description How to Use

Example
GMAX

Returns the highest numeric value of the field in the report. If the report is summarized, it returns the highest numeric value of the field in each group.

 MAX(numeric_field)

MAX(salary)
GMIN

Returns the lowest numeric value of the field in the report. If the report is summarized, it returns the lowest numeric value of the field in each group.

 MIN(numeric_field) MIN(salary)
GCOUNT Returns the aggregated number of field values in the report. If the report is summarized, it returns the number of field values in each group. GCOUNT(field) GCOUNT(User Name)
GCOUNTUNIQUE Returns the aggregated number of unique field values in the report. If the report is summarized, it returns the number of unique field values in each group. GCOUNTUNIQUE(field) GCOUNTUNIQUE(User Name)
GSUM Returns the aggregated sum for all rows of a given numeric field. If the report is summarized, it returns the aggregated sum for each group of a given numeric field. SUM(numeric_field) SUM(salary)
GAVG Returns the aggregated average for all rows of a given numeric field. If the report is summarized, it returns the aggregated average for each group of a given numeric field. AVG(numeric_field) AVG(salary)
Function Description How to Use

Example
DATE Returns a date value from the year, month, and day values you enter.

DATE(year,month,day)

  • Year must be 4 digits
  • Month must be 2 digits
  • Day must be 2 digits
 DATE(2020,02,25) returns 02/25/2020 as Date type
DATEADD Returns a date by adding the specified number of intervals to the specified date.

DATEADD(interval, number, date, time)

Note: Interval determines which part of the date will be added. The following inputs can be used: Year, Quarter, Month, Day, Week, Hour, Minute, and Second.

  • DATEADD(Hour, 1, "01/27/2020 11:24:00") returns 01/27/2020 12:24:00
  • DATEADD(Minute, 2, "01/27/2020 11:24:00") returns 01/27/2020 12:26:00
  • DATEADD(Second, 3, "01/27/2020 11:24:00") returns 01/27/2020 12:26:03
DATEDIFF Returns the difference between two dates.

DATEDIFF(interval, date1, date2)

  • Interval determines what part of the date will be subtracted. The following inputs can be used: Year, Quarter, Month, Dayofyear, Day, Week.
  • Date and time values can be entered as MM/DD/YYYY or YYYY-MM-DD.
  • DATEDIFF(Day,01/25/2020,01/27/2020) returns 2 as Numeric type
  • DATEDIFF(Day,01/27/2020,01/25/2020) returns -2 as Numeric type
  • DATEDIFF(Year,12/31/2020,01/01/2021) returns 1 as Numeric type
DATEVALUE Returns the date value of a given Date/Time or Expression. DATEVALUE(expression)
  • DATEVALUE(“01/25/2020”) returns 01/25/2020 as Date type
  • DATEVALUE(“01/25/2020 08:25:59.248”) returns 01/25/2020 as Date type
DATETIMEVALUE Returns the Date & Time value of a given Date/Time or Expression. DATETIMEVALUE(expression)
  • DATEVALUE(“01/25/2020”) returns 01/25/2020 00:00:00.000 as Date/Time type
  • DATEVALUE(“01/25/2020 08:25:59.248”) returns 01/25/2020 08:25:59.248 as Date/Time type
DAY Returns a day of the month as a numeric value. DAY(date)
  • If the value of Transcript_Due_Date is 01/25/2020, DAY(Transcript_Due_Date) generates 25 as Numeric type
  • DAY(DATEVALUE(“01/25/2020”)) returns 25 as Numeric type
  • If the current day is 01/07/2020, DAY(TODAY()) returns 7 as Numeric type
MONTH Returns the month of a year. The number corresponds to each month of the year from 1 (January) to 12 (December). MONTH(date)
  • If the value of Transcript_Due_Date is 05/14/2020, MONTH(Transcript_Due_Date) generates 5 as Numeric type
  • MONTH(DATEVALUE(“02/27/2020”)) returns 2 as Numeric Type
  • If the current day is 07/23/2020, MONTH(TODAY()) returns 7 as Numeric type
HOUR Returns the hour of the DateTime specified. HOUR(datetime) HOUR(DATEVALUE("1/25/2020 08:25:59.248"))returns 8
NOW Returns the Date/Time value of the current moment NOW()

For the following examples, the current date is 01/25/2020 and current time is 08:25:59.248

  • NOW() returns 01/25/2020 08:25:59.248 as Date/Time type
  • DAY(NOW()) returns 25 as Numeric type
  • MONTH(NOW()) returns 1 as Numeric type
TODAY Returns the current date TODAY()

For the following examples, the current date is 07/18/2020 and current time is 10:54:38.885

  • TODAY() returns 07/18/2020 as Date/Time type
  • DAY(TODAY()) returns 18 as Numeric type
  • MONTH(TODAY()) returns 7 as Numeric type
YEAR Returns the year of a given date YEAR(date)
  • If the value of Transcript_Due_Date is 02/24/2019, YEAR(Transcript_Due_Date) generates 2019 as Numeric Type
  • YEAR(DATEVALUE(“08/12/2021”)) returns 2021 as Numeric Type
  • If the current day is 07/23/2020, YEAR(TODAY()) returns 2020 as Numeric Type
Function Description How to Use

Example
IF Evaluates an expression and returns a value based on whether the expression was True or False.

IF(logical_expression, trueValue, falseValue)

  • logical_expression is the expression to be evaluated as true or false
  • trueValue is the return value if the expression was true
  • falseValue is the return value if the expression was false

Note: trueValue and falseValue must be the same data type.

  • IF(DATEDIFF(Day,Transcript_Due_Date,TODAY()) < 7, "Due Soon","Not Due Soon") Evaluates whether Transcript_Due_Date is less than 7 days from today:
    • Returns Due Soon as Text type for records that are less than 7 days
    • Returns Not Due Soon as Text type if not less than 7 days
  • IF(DATEDIFF(Day,Transcript_Due_Date,TODAY()) < 7,1,2)
    • Returns 1 as Numeric Type for records that are less than 7 days
    • Returns 2 as Numeric Type if not less than 7 days
CASE Evaluates an expression against multiple values and returns a corresponding result for any matches. Returns a default value if there were no matches.

CASE(expression,value1,result1,value2,result2,...,elseResult)

  • value is value being compared to expression
  • result: Return value if the expression was equal to its corresponding value
  • elseResult: Return value if there was no matched value

Notes:

  • All values should be the same data type
  • All results should be the same data type
  • Returns #ERROR! if any expression in the function gives an error

CASE(Transcript_Status,"Not Started",1,"In Progress",2,"Completed",3,4) Evaluates Transcript_Status and:

  • returns 1 as Numeric Type if status was Not Started
  • returns 2 as Numeric Type if status was In Progress
  • returns 3 as Numeric Type if status was Completed
  • returns 4 as Numeric Type if status was anything else
IFNULL Checks if an expression has a value and returns a set expression if does not. Otherwise, it returns the original value.

IFNULL(expression, setExpression)

  • setExpression: the replacement value if the result of the expression was null

Notes:

  • A field is not null if it contains a zero or blank spaces
  • expression and setExpression must contain the same data types

IFNULL(New_Division_2, "Not Assigned") evaluates New_Division_2 and:

  • returns the value of New_Division_2 as Text type if there was a value
  • returns Not Assigned as Text type for null values
ISNULL Checks if an expression has a value and returns True if it does, and returns False if it does not. ISNULL(expression)

ISNULL(New_Division_2) evaluates New_Division_2 and:

  • returns True as Boolean type if there is a value
  • returns False as Boolean type it was null
ISNUMBER Checks if a text value is a number and returns True if it is, otherwise returns False. Returns False for blank values. ISNUMBER(text)
  • ISNUMBER("2539") returns True as Boolean type
  • ISNUMBER("25 39") returns False as Boolean type
  • ISNUMBER("2,539") returns False as Boolean type
  • ISNUMBER("25.39") returns True as Boolean type
IFEMPTY

Checks if an expression has empty value and returns a set expression if it does. Otherwise, it returns the original value.

IFEMPTY(expression, setExpression)

  • setExpression: the replacement value if the result of the expression is empty

Notes:

  • expression and setExpression must contain the same data types

IFEMPTY([User].[Division], "Not Assigned") Evaluates [User].[Division] and:

  • returns the value of [User].[Division] as Text type if it is not empty

  • returns "Not Assigned" as Text type for empty values
IFNULLOREMPTY

Checks if an expression has null or empty value and returns a set expression if it does. Otherwise, it returns the original value.

IFNULLOREMPTY(expression, setExpression)

  • setExpression: the replacement value if the result of the expression is null or empty

Notes:

  • expression and setExpression must contain the same data types

IFNULLOREMPTY([User].[Division], "Not Assigned") Evaluates [User].[Division] and:

  • returns the value of [User].[Division] as Text type if it is neither null nor empty

  • returns "Not Assigned" as Text type for null or empty values
ISEMPTY Checks if an expression is empty and returns True if it is, and returns False if it is not. ISEMPTY(expression)

ISEMPTY([User].[Division]) Evaluates [User].[Division] and:

  • returns False as Boolean type if expression is not empty

  • returns True as Boolean type if it is empty
ISNULLOREMPTY Checks if an expression is null or empty and returns True if it is, and returns False if it is not. ISNULLOREMPTY(expression)

ISNULLOREMPTY([User].[Division]) Evaluates [User].[Division] and:

  • returns False as Boolean type if expression is neither null nor empty

  • returns True as Boolean type for null or empty values
Function Description How to Use

Examples
ABS

Returns the absolute value of a number. The absolute value of a number is always the positive value of the number.

 ABS(number)
  • ABS(-2539) returns 2539 as Numeric type
  • ABS({Transcript.FinalGrade}) returns the absolute value of a Final Grade field
FLOOR Returns a number rounded down to the nearest whole number. If the number is negative, the number is rounded towards zero. FLOOR(number)
  • FLOOR(8.9) returns 8 as Numeric type
  • FLOOR(8.9) returns --8-9 as Numeric type
  • FLOOR(9.1) returns 9 as Numeric type
  • FLOOR(9.1) returns 9 -10 as Numeric type
CEILING Returns a number rounded up to the nearest whole number. If the number is negative, the number is rounded away from zero. CEILING(number)
  • CEILING(8.9) returns 9 as Numeric type
  • CEILING(8.9) returns 9-8 as Numeric type
  • CEILING(9.1) returns 10 as Numeric type
  • CEILING(9.1) returns 10 -9 as Numeric type
ROUND Returns a number that is rounded to a specified number of digits. ROUND(number,decimals)
  • ROUND(71.4684,0) returns 71 as Numeric type
  • ROUND(71.4684,2) returns 71.47 as Numeric type
  • ROUND(71.4684,3) returns 71.478 as Numeric type
Function Description How to Use

Examples
Contains Compares two text values and returns True if the first value contains the second value, otherwise it returns False. CONTAINS(text1,text2)
  • CONTAINS("Not Started","Started") returns True as Boolean type
  • CONTAINS("Not Started","started") returns False as Boolean Type
  • CONTAINS("Not Started","ot") returns True as Boolean type
LEFT Returns a set number of characters starting from the beginning of a text value.

LEFT(textValue,number)

  • number specifies the number of characters to return from textValue
  • LEFT("Reporting",6) returns Report as Text type
  • LEFT("Reporting",4) returns Repo as Text type
  • LEFT("Reporting",2) returns Re as Text type
  • LEFT("Reporting",0) returns Blank
MID Returns a set number of characters from a specified starting point of a text value.

MID(textValue,num1,num2)

  • num1 specifies the number of characters from the left to use as the starting point
  • num2 specifies the number of characters to return from textValue
  • MID("Reporting",2,4) returns port as Text type
  • MID("Reporting",0,4) returns Repo as Text type
  • MID("Reporting",5,3) returns rti as Text type
  • MID("Reporting",4,0) returns Blank
RIGHT Returns a set number of characters starting from the end of a text value.

RIGHT(textValue,number)

  • number specifies the number of characters to return from textValue
  • RIGHT("Reporting",6) returns orting as Text type
  • RIGHT("Reporting",4) returns tingasText type
  • RIGHT("Reporting",2) returns ngasText type
  • RIGHT("Reporting",0) returns Blank
TEXT Returns any specified value as a text value. TEXT(anyValue)
  • TEXT(5) returns 5 as Text type
  • TEXT(Current_Compensation) returns the current_compensation value as Text type
TRIM Removes any spaces that come before or after a text value. TRIM(textValue)
  • TRIM(" Reporting Fun") returns Reporting Fun as Text type
  • TRIM("Reporting Fun ") returns Reporting Fun as Text type
  • TRIM("Reporting Fun") returns Reporting Fun as Text type
CONCAT Concatenates multiple text values. CONCAT(text1,text2, ...)
  • CONCAT("Reporting","Is","Great") returns ReportingIsGreat as Text type
  • CONCAT("Reporting ","Is ","Great") returns Reporting Is Great as Text type
  • CONCAT("Transcript due date is ",TEXT(DATEDIFF(Day,Transcript_Due_Date,TODAY())), " days away") returns Transcript due date is 3 days away as Text type
UPPER Converts any letter in a specified text value to be uppercase. UPPER(textValue)
  • UPPER("Reporting Fun") returns REPORTING FUN as Text type
  • UPPER("Reporting 5") returns REPORTING 5 as Text type
LOWER Converts any letter in a specified text value to be lowercase. LOWER(textValue)
  • LOWER("Reporting Fun") returns reporting fun as Text type
  • LOWER("Reporting 5") returns reporting 5 as Text type
LEN Returns the number of characters in a specified text string. LEN(textValue)
  • LEN("Reporting Fun") returns 13 as Numeric type
  • LEN("Reporting 5") returns 11 as Numeric type
  • LEN("A C") returns 3 as Numeric type
  • LEN("AC") returns 2 as Numeric type

Step 4 - Publish Field Globally

This option is visible to users who have permission to manage global calculated fields. This option is off by default. Toggle it to on to set the field to publish globally once the field is saved.

A field that is published globally appears in the Global Fields section. Any user with permission to view the fields that were used to create the calculated field can:

  1. See the calculated field in the Global Fields section.
  2. Use the calculated field in a report.
  3. View the calculated field's data within the constraints of their permissions.

See View Global Calculated Fields.

Once a field is published globally, the field cannot be unpublished. The field can be deleted. See Edit, Copy, or Delete Calculated Field.

Step 5 - Validate and Save

You can validate your formula while building it or when the calculated field is saved. The Calculated Field Editor performs validations to ensure correctness and successful generation of reports with calculated fields.

To validate your formula while building it, click Validate. This prompts the following to occur:

  1. The formula is checked for errors.
  2. If the validation fails, an error message displays with the reason and suggestions for correcting the formula.
  3. If the validation succeeds, a "Success" message displays.

Click Save to save the field. If you have not yet validated your formula, the validation process will occur when you click Save.