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.
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.
 Click the plus icon to the right of the Calculated Fields section in the report builder. This opens the calculated field builder.
 Enter a Name, up to 80 characters.
 Enter a Description, 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
 Number  When this option is selected, you can also define a decimal point value between 0 and 30. The default value is 2.
 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. 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.
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 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.
to view the operators. The operator will be added to the current position of the cursor within the
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.
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 

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

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) Note: Interval determines which part of the date will be added. The following inputs can be used: Year, Quarter, Month, Day, Week. 
DATEADD(day, 1, "01/27/2020 11:24:00") returns 01/28/2020 11:24:00 DATEADD(year, 1, "01/25/2020") returns 01/25/2019 DATEADD(week, 2, "01/01/2020") returns 01/15/2020 
DATEDIFF  Returns the difference between two dates. 
DATEDIFF(interval, date1, date2)


DATEVALUE  Returns the date value of a given Date/Time or Expression.  DATEVALUE(expression) 

DATETIMEVALUE  Returns the Date & Time value of a given Date/Time or Expression.  DATETIMEVALUE(expression) 

DAY  Returns a day of the month as a numeric value.  DAY(date)  
MONTH  Returns the month of a year. The number corresponds to each month of the year from 1 (January) to 12 (December).  MONTH(date) 

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

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

YEAR  Returns the year of a given date  YEAR(date) 

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)
Note: trueValue and falseValue must be the same data type. 

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)
Notes:

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

BLANKVALUE  Checks if an expression has a value and returns a set expression if does not. Otherwise, it returns the original value. 
BLANKVALUE(expression, setExpression)
Notes:

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

ISBLANK  Checks if an expression has a value and returns True if it does, and returns False if it does not.  ISBLANK(expression) 
ISBLANK(New_Division_2) evaluates New_Division_2 and:

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) 

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) 

FLOOR  Returns a number rounded down to the nearest whole number. If the number is negative, the number is rounded towards zero.  FLOOR(number) 

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) 

ROUND  Returns a number that is rounded to a specified number of digits.  ROUND(number,decimals) 

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) 

LEFT  Returns a set number of characters starting from the beginning of a text value. 
LEFT(textValue,number)


MID  Returns a set number of characters from a specified starting point of a text value. 
MID(textValue,num1,num2)


RIGHT  Returns a set number of characters starting from the end of a text value. 
RIGHT(textValue,number)


TEXT  Returns any specified value as a text value.  TEXT(anyValue)  
TRIM  Removes any spaces that come before or after a text value.  TRIM(textValue) 

CONCAT  Concatenates multiple text values.  CONCAT(text1,text2, ...) 

UPPER  Converts any letter in a specified text value to be uppercase.  UPPER(textValue) 

LOWER  Converts any letter in a specified text value to be lowercase.  LOWER(textValue) 

LEN  Returns the number of characters in a specified text string.  LEN(textValue) 

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:
 See the calculated field in the Global Fields section.
 Use the calculated field in a report.
 View the calculated field's data within the constraints of their permissions.
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.
To validate your formula while building it, click
. This prompts the following to occur: The formula is checked for syntax errors.
 If the validation fails, an error message displays with the reason and suggestions for correcting the formula.
 If the validation succeeds, a "Success" message displays.
Click
to save the field. If you have not yet validated your formula, the validation process will occur when you click .