Operators and functions for use in custom columns
function now(){ window.location.reload(true); }Custom columns are report fields that are derived from user-defined formulas. Users create these formulas by combining columns (or fields) available in the report with operators and functions.
The operators and functions listed in the sections below are supported for use in custom columns.
- Numeric operators
- Numeric functions
- Text string operators
- Text string functions
- Logical functions and operators
- Conversions
- Date and DateTime operators
- Date and DateTime functions
Numeric operators
Operator^{1} |
Use this operator to specify… |
---|---|
+ |
Addition |
- |
Subtraction |
* |
Multiplication |
/ |
Division |
(...) |
Parentheses |
= |
Two values are equal |
< |
Less than |
> |
Greater than |
<= |
Less than or equal |
>= |
Greater than or equal |
!= |
Not equal |
Numeric functions
Function |
Use this function to specify... |
---|---|
Abs(x) |
Absolute value |
Mod(x,y) |
Modulo (remainder of x/y). Modulo is always positive. |
Max(x,y) |
Whichever value is larger |
Min(x,y) |
Whichever value is smaller |
Pow(x,y) |
x to the power of y |
Ceiling(x) |
Rounds towards infinity: 1.1 → 2, -1.1 → -1 |
Floor(x) |
Rounds towards -infinity: 1.1 → 1, -1.1 → -2 |
RoundAwayFromZero(x) |
Rounds away from zero: 1.1 → 2, -1.1 → -2 |
RoundTowardsZero(x) |
Rounds towards zero: 1.1 → 1, -1.1 → -1 |
RoundHalfTowardsZero(x) |
Rounds to nearest, ties going towards zero: 1.5 → 1, -1.5 → -1 |
RoundHalfAwayFromZero(x) |
Rounds to nearest, ties going away from zero: 1.5 → 2, -1.5 → -2 |
RoundHalfEven(x) |
Rounds to nearest, ties going to nearest even integer: 1.5 → 2, 2.5 → 2 |
Text string operators
Operator^{1} |
Use this operator to… |
---|---|
+ |
Concatenate (combine) two strings |
= |
Compare two values for equality^{2} |
!= |
Compare two values for non-equality |
Text string functions
Function |
This function… |
---|---|
Len(TextField) |
Displays the number of characters in a text string |
Left(TextField, length) |
Displays the first length characters from a text string |
Mid(TextField, start, length) |
Displays characters from a text string, beginning with the start position, length characters long |
Right(TextField, length) |
Displays the last length characters from a text string |
Find(TextField, "sub") |
Displays the location from the left (represented by an integer) of sub in TextField. If sub is not found, -1 displays. |
RFind(TextField, "sub") |
Displays the location from the right (represented by an integer) of sub in TextField. If sub is not found, -1 displays. |
IsEmpty(TextField) |
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) |
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). |
Logical operators and functions
Logical functions and operators can be used in string or numeric formulas.
Operators
Operator |
Use this operator to specify the... |
---|---|
&& |
Logical AND operator. Indicates whether both operands are true. |
|| |
Logical OR operator. Indicates whether either operand is true. |
Functions
Function |
Use this function to… |
---|---|
If(cond,true,false) |
Logical IF statement: IF cond THEN true ELSE false cond must be a numeric value or a logical comparison true and false must be the same type, whether string or numeric |
IsNull(variable) |
Display whether the variable is:
If the variable is null, the field displays 1 (true result)
If the variable has a value, field displays 0 (false result) variable must be a name of another report column; any other value will yield a true result. |
Conversions
String numeric conversions are used to convert text to numbers and vice versa.
Function |
Use this function to… |
---|---|
TextToNumber(s) |
Converts a text string to a numeric value. The number can be used in numeric custom fields. This function will only work with text string fields that are comprised of numerals (for example, EmployeeID). |
NumberToText(s) |
Converts a number to a string. The string can be used in text string custom fields. You can specify the string format used in the output for this function by entering the format after the field name. For example:
Decimal values will not be rounded, regardless of the output format chosen. For example, if you choose "0.###", the number 5 will display as 5, while the number 5.35 will still display as 5.35. |
DateTimeToDate | Converts DateTime to a date value, dropping the time component |
DateToDateTime | Converts a date string value to a DateTime string value |
TextToDate | Converts text to a date value |
TextToDateTime | Converts text to a DateTime value |
Date and DateTime operators
Date operators are used to compare dates.
Operator |
Use this operator to determine whether... |
---|---|
= |
Two dates or datetimes are equal |
< |
One date or datetime falls after another |
> |
One date or datetime falls before another |
<= |
One date or datetime falls before or is equal to another |
>= |
One date or datetime falls after or is equal to another |
!= |
Two dates or datetimes are not equal |
Date and DateTime functions
Date functions are used to display dates and carry out calculations using dates. For example, you can use date functions to:
- Add days or months to a date field and return a new date
- Return a numeric value for a date's month, day, or year, or
- Return the latest anniversary of a user's start (for use, for example, in calculating payout owing for accrued time off)
You can use any of the date functions that return numeric values in numeric formulas.
Function |
This function… |
---|---|
Date(year, month, day) |
Returns the date value For example, enter Date(2009, 2, 3) in the formula to return February 3rd, 2009 in the field This function cannot be used with DateTime values. |
Today() |
Returns today's date |
Day(date) |
Returns the day of the month of the date entered For example, if an employee started work on May 3, entering Day(UserStartDate) will return a value of 3 for that user |
Month(date) |
Returns the numeric month of the date entered For example, if an employee's end date is September 3, entering Month(UserEndDate) will return a value of 9 for that user. |
Year(date) |
Returns the numeric year of the date entered For example, if an employee's start date is September 3, 2006, entering Year(UserStartDate) will return a value of 2006 for that user |
DayDiff(earlierdate,laterdate)^{3} |
Returns the number of days between the two dates you enter For example, if an employee's start date is April 3, 2009, and you enter DayDiff(UserStartDate,Date(2009,4, 13)), the value 10 will be returned in the field |
RecentAnniversary(date)^{4} |
Returns the date of most recent anniversary of date entered For example, if today is August 11, 2009, entering RecentAnniversary(UserStartDate) for an employee who started on July 13, 2006 will return a value of July 13, 2009 |
AddDays(date, #ofdays) |
Adds the #ofdays value to the date value, returning a new date For example, if an employee's start date is December 3, 2008, and you enter AddMonths(UserStartDate,5), December, 2009 will be returned in the field |
AddMonths(date, #ofmonths)^{5} |
Adds the #ofmonths value to the date value, returning a new date For example, if an employee's start date is December 3, 2008, and you enter AddMonths(UserStartDate,3), February 3, 2009 will be returned in the field |
DateTime functions
Function |
This function… |
---|---|
DateTime |
Converts date and time values, or a character string, into a DateTime value |
Hour(datetime) |
Returns the hour |
Minute(datetime) |
Returns the minute |
Second(datetime) |
Returns the second |
HourDiff(earlierdatetime,laterdatetime) |
Returns the number of hours between two DateTime values |
MinuteDiff(earlierdatetime,laterdatetime) |
Returns the number of minutes between two DateTime values |
SecondDiff(earlierdatetime,laterdatetime) |
Returns the number of seconds between two DateTime values |
AddHours(datetime,#ofhours) |
Returns a new DateTime that adds the specified number of hours to the value of this instance |
AddMinutes(datetime,#ofminutes) |
Returns a new DateTime that adds the specified number of minutes to the value of this instance |
AddSeconds(datetime,#ofseconds) |
Returns a new DateTime that adds the specified number of seconds to the value of this instance |
1 Operators follow standard order of operations rules. For example: 2 + 2 * 2 = 6, but (2 + 2) * 2 = 8
2 String comparisons are not case-sensitive.
3 If the laterdate value entered is falls before the earlierdate value, the result will be a negative value.
4 If you enter February 29th, may return February 28th of the most recent year. If you enter a future date, the function will still return a past value.
5 Returns the last day of the month if the day of the month does not exist in the new month (for example, if the initial date is the 31st, but there are only 30 days in the returned month