Custom column formulas can include either numeric fields or text string fields. Other operators and functions are also available.

Creating Numeric Formulas:

  • A formula for Gross Margin % in the Project Summary report: ( BaseBillableAmount – BaseCostAmount ) / BaseBillableAmount * 100
  • A formula for Time Off % in the User Hours Details report: TimeOffHours / TotalHours * 100

Available Numeric Operators:

Operators and its Function

+ Addition
– Subtraction
* Multiplication
/ Division
( … ) Parentheses
= Two values are equal
< Less than
> Greater than
<= Less than or equal
>= Greater than or equal
!= Not equal

Note:
Operators follow standard order of operations rules.

Example:
2 + 2 * 2 = 6, but (2 + 2) * 2 = 8
2 + 2 / 2 = 3, but (2 + 2) / 2 = 2

Available Numeric Functions:

Abs(x): This function will Absolute value
Mod(x,y): This function will Modulo (remainder of x/y). Modulo is always positive.
Max(x,y): This function will Whichever value is larger
Min(x,y): This function will Whichever value is smaller
Pow(x,y): This function will x to the power of y
Ceiling(x): This function will Rounds towards infinity: 1.1 → 2, -1.1 → -1
Floor(x): This function will Rounds towards -infinity: 1.1 → 1, -1.1 → -2
RoundAwayFromZero(x): This function will Rounds away from zero: 1.1 → 2, -1.1 → -2
RoundTowardsZero(x): This function will Rounds towards zero: 1.1 → 1, -1.1 → -1
RoundHalfTowardsZero(x): This function will Rounds to nearest, ties going towards zero: 1.5 → 1, -1.5 → -1
RoundHalfAwayFromZero(x): This function will Rounds to nearest, ties going away from zero: 1.5 → 2, -1.5 → -2
RoundHalfEven(x): This function will Rounds to nearest, ties going to nearest even integer: 1.5 → 2, 2.5 → 2

Creating Text Formulas:

To identify users and their departments in a single field, you could combine parts of both their user name and department name, by creating a formula like this:

Left(UserName, 5)+Left(DepartmentName, 5)
If the user's name is Anderson, Richard and he belongs to the Sales department, the following will display for that user in the custom report column:
AnderSales

Available Text String Operators:
+ : This function will Concatenate (combine) two strings
= : This function will Compare two values for equality1
!= : This function will Compare two values for non-equality1
Note:
String comparisons are not case-sensitive.

Text String Functions:

Len(TextField): This function Displays the number of characters in a text string.
Left(TextField, length): This function Displays the first length characters from a text string.
Mid(TextField, start, length): This function Displays characters from a text string, beginning with the start position, length characters long.
Right(TextField, length): This function Displays the last length characters from a text string.
Find(TextField, "sub"): This function Displays the location from the left (represented by an integer) of sub in TextField. If sub is not found, -1 displays.
RFind(TextField, "sub"): This function Displays the location from the right (represented by an integer) of sub in TextField. If sub is not found, -1 displays.
IsEmpty(TextField): This function Displays whether or not a field is empty. If the field has a value, this field displays 0. If the field is empty, this field is blank.
IsNullOrEmpty(TextField): This function Displays whether a field is empty or null. If the field is empty or null, this field displays 1 (true result). If the field has a value, this field displays 0 (false result).

Note:
There is currently no way to access a custom column that exists in one report from another report. However, if you need to use a field in more than one report, you can simply copy and paste the formula.