CUSTOMER ZONE

Creating a Custom Column with Numeric and Text String Formula.

Find resources designed to help you get the most from Replicon

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.

Start a free Replicon trial today based on your business needs

Start Free Trial