Calculated Fields - Supported Functions - 6.5.7

Name: Description

Type

Is Mandatory?

Is Variable Length?

ADD - Returns the sum of all the numbers given as parameters

Numeric

ADD(8, 101.4) = 109.4
ADD(8, 101.4, 10, 20) = 139.4
ADD(NULL, 10, 20, NULL) = 30

value1: Initial Value

Numeric

Yes

No

value2: Value to be added

Numeric

Yes

Yes

SUBTRACT - Returns the difference between provided numbers

Numeric

SUBTRACT(101.4, 1.4) = 100
SUBTRACT(101.4, -8) = 109.4
SUBTRACT(NULL, 10) = -10

value1: Initial value

Numeric

Yes

No

value2: Value to be subtracted

Numeric

Yes

No

MULTIPLY - Returns the product of all the numbers given as parameters

Numeric

MULTIPLY(10, 1.1) = 11
MULTIPLY(NULL, 10) = NULL
MULTIPLY(10, 10, 10, 1.1234) = 1123.4

value: Initial value

Numeric

Yes

No

multiplier: Value to be multiplied

Numeric

Yes

Yes

DIVIDE - Returns the quotient after a number is divided by a divisor

Numeric

DIVIDE(15, 3) = 5,
DIVIDE(15, 4) = 3.75
DIVIDE(100, 1000) = 0.1
DIVIDE(0, 4) = 0
DIVIDE(15, 0) = NULL
DIVIDE(NULL, 4) = NULL
DIVIDE(15, NULL) = NULL

value: Initial value

Numeric

Yes

No

divisor: Value to divide by

Numeric

Yes

No

MOD - Returns the remainder after a number is divided by a divisor

Numeric

MOD(15, 3) = 0
MOD(15, 4) = 3
MOD(100, 1000) = 100
MOD(10, 0.5) = 0
MOD(0, 4) = 0
MOD(15, 0) = NULL
MOD(NULL, 4) = NULL
MOD(15, NULL) = NULL

value: Initial value

Numeric

Yes

No

divisor: Value to divide the initial value by

Numeric

Yes

No

ROUND - Returns the value of number rounded to the nearest Integer

Numeric

ROUND(100) = 100
ROUND(100.495) = 100
ROUND(100.5) = 101
ROUND(100.998) = 101
ROUND(-100.495) = -100
ROUND(-100.5) = -100
ROUND(-100.998) = -101
ROUND(NULL) = NULL

value: Initial value

Numeric

Yes

No

FLOOR - Returns the largest Integer value that is less than or equal to the parameter

Numeric

FLOOR(10) = 10
FLOOR(10.8) = 10
FLOOR(-10.8) = -11
FLOOR(NULL) = NULL

value: Initial value

Numeric

Yes

No

CEILING - Returns the smallest Integer value that is greater than or equal to the parameter

Numeric

CEILING(10) = 10
CEILING(10.8) = 11
CEILING(-10.8) = -10
CEILING(NULL) = NULL

value: Initial value

Numeric

Yes

No

ABS - Returns the absolute value of the parameter

Numeric

ABS(10.1) = 10.1
ABS(-10.1) = 10.1
ABS(0) = 0
ABS(NULL) = NULL

value: Initial value

Numeric

Yes

No

TO NUMBER - Returns Number value represented by the input string

Numeric

TO NUMBER('-10.1') = -10.1
TO NUMBER('10.100') = 10.1
TO NUMBER('10.1.1') = NULL
TO NUMBER('1,100.1') = NULL
TO NUMBER('10.1a') = NULL
TO NUMBER('invalid') = NULL
TO NUMBER(NULL) = NULL

value: Initial value

Numeric

Yes

No

LESS THAN - Returns TRUE if value1 is less than value2. Otherwise returns FALSE

Boolean

LESS THAN(10.0, 10) = FALSE
LESS THAN(10.0, 9.9) = FALSE
LESS THAN(10.0, 10.01) = TRUE
LESS THAN(-10.0, -9.9) = TRUE
LESS THAN(-10.0, -10.01) = FALSE
LESS THAN(NULL, 10) = NULL

value1: Initial value

Numeric

Yes

No

value2: Value to be compared against

Numeric

Yes

No

LESS THAN OR EQUALS - Returns TRUE if value1 is less than or equal to value2. Otherwise returns FALSE

Boolean

LESS THAN OR EQUALS(10.0, 10) = TRUE
LESS THAN OR EQUALS(10.0, 9.9) = FALSE
LESS THAN OR EQUALS(10.0, 10.01) = TRUE
LESS THAN OR EQUALS(NULL, 10) = NULL

value1: Initial value

Numeric

Yes

No

value2: Value to be compared against

Numeric

Yes

No

GREATER THAN - Returns TRUE if value1 is greater than value2. Otherwise returns FALSE

Boolean

GREATER THAN(10.0, 10) = FALSE
GREATER THAN(10.0, 9.9) = TRUE
GREATER THAN(10.0, 10.01) = FALSE
GREATER THAN(-10.0, -9.9) = FALSE
GREATER THAN(-10.0, -10.01) = TRUE
GREATER THAN(NULL, 10) = NULL

value1: Initial value

Numeric

Yes

No

value2: Value to be compared against

Numeric

Yes

No

GREATER THAN OR EQUALS - Returns TRUE if value1 is greater than or equal to value2. Otherwise returns FALSE

Boolean

GREATER THAN OR EQUALS(10.0, 10) = TRUE
GREATER THAN OR EQUALS(10.0, 9.9) = TRUE
GREATER THAN OR EQUALS(10.0, 10.01) = FALSE
GREATER THAN OR EQUALS(NULL, 10) = NULL

value1: Initial value

Numeric

Yes

No

value2: Value to be compared against

Numeric

Yes

No

Name: Description

Type

Is Mandatory?

Is Variable Length?

CONCAT - Concatenates the provided string values into a single string

String

CONCAT('Join', ' ', 'these', ' ', 'words') = 'Join these words'
CONCAT('Join', NULL, 'these', NULL, 'words') = 'Jointhesewords'
CONCAT(NULL, NULL) = ''

value1: Initial value

String

Yes

No

value2: Value to be concatenated to the initial value

String

Yes

Yes

SUBSTRING - Returns the substring of the value. Substring starting at startPos, till endPos (if provided, otherwise, till end of the value) is returned.
Note: The position index starts with zero. Value of 0, will point to the first character on the left.

If negative values are used for position index, then index calculation is done from right. Value of -1, will point to the first character on the right.

String

SUBSTRING('The fox jumped over the fence', 0) = 'The fox jumped over the fence'

SUBSTRING('The fox jumped over the fence', 0, 10) = 'The fox ju'

SUBSTRING('The fox jumped over the fence', 10, 20) = 'mped over '

SUBSTRING('The fox jumped over the fence', 10, 100) = 'mped over the fence'

SUBSTRING('The fox jumped over the fence', -10) = ' the fence'

SUBSTRING('The fox jumped over the fence', -10, 25) = ' the f'

SUBSTRING('The fox jumped over the fence', 0, -10) = 'The fox jumped over'

SUBSTRING('The fox jumped over the fence', 30, 40) = ''

SUBSTRING(NULL, 30, 40) = NULL

value: Initial value

String

Yes

No

startPos: Character position which will be start of extracted substring

Numeric

Yes

No

endPos: Character position till which the substring will be extracted.
If not provided, then substring till the end of the initial value will be extracted

Numeric

No

No

UPPER - Returns the value with all alphabets converted to Upper Case

String

UPPER('this is all lower') = 'THIS IS ALL LOWER'
UPPER('tHis IS MixEd Case') = 'THIS IS MIXED CASE'
UPPER('') = ''
UPPER(NULL) = NULL

value: Initial value

String

Yes

No

LOWER - Returns the value with all alphabets converted to Lower Case

String

LOWER('THIS IS ALL UPPER') = 'this is all upper'
LOWER('tHis IS MixEd Case') = 'this is mixed case'
LOWER('') = ''
LOWER(NULL) = NULL

value: Initial value

String

Yes

No

TO STRING - Returns the String value of passed parameter. Useful for converting Numeric, Date or Boolean value to String.
Note: Parameter of type ANY indicates, that function can take any argument type (String, Numeric, Date OR Boolean). 

String

TO STRING(100.01) = '100.01'
TO STRING(TRUE) = 'true'
TO STRING(FALSE) = 'false'
TO STRING(NULL) = NULL
TO STRING('This is a string value') = 'This is a string value'

value: Initial value

ANY

Yes

No

LENGTH - Returns the length of provided string value

Numeric

LENGTH('This is a string value') = 22
LENGTH('') = 0
LENGTH(NULL) = 0 

value: Initial value

String

Yes

No

POSITION - Returns the position of first occurrence of searchString in the value. Return value of -1, indicates that searchString was not found in the initial value.
If the startPos is provided, then search will be started from startPos, otherwise search will be done from start of the value.
Note: The position index starts with zero. Value of 0, will point to the first character on the left.

If negative values are used for position index, then index calculation is done from right. Value of -1, will point to the first character on the right.

Numeric

POSITION('This is a string value', 'This') = 0
POSITION('This is a string value', 'This', 1) = -1
POSITION('This is a string value', 'value', -10) = 17
POSITION('This is a string value', 'is') = 2
POSITION('This is a string value', 'is', NULL) = 2
POSITION('This is a string value', 'numeric') = -1
POSITION('This is a string value', NULL) = -1
POSITION(NULL, 'This is a string value') = -1

value: Initial value

String

Yes

No

searchString: String to be searched in the initial value

String

Yes

No

startPos: Character position from which search will be performed.
If not provided, the search will be performed from the starting of the initial value.

Numeric

No

No

CONTAINS - Returns TRUE if the searchString is contained in the value. Otherwise returns FALSE

Boolean

CONTAINS('This is a string value', 'is') = TRUE
CONTAINS('This is a string value', 'numeric') = FALSE
CONTAINS('This is a string value', NULL) = FALSE
CONTAINS(NULL, 'This is a string value') = FALSE

value: Initial value

String

Yes

No

searchString: String to be searched in the initial value

String

Yes

No

IN - Returns TRUE if the value is contained in the List. Otherwise returns FALSE

Boolean

IN('OPEN', 'TO DO', 'OPEN', 'IN PROGRESS') = TRUE
IN('DONE', 'TO DO', 'OPEN', 'IN PROGRESS') = FALSE
IN(NULL, 'TO DO', 'OPEN', 'IN PROGRESS') = FALSE
IN('DONE', '', NULL) = FALSE

value: Initial value

String

Yes

No

listItem: List in which the initial value is be searched

String

Yes

Yes

Name: Description

Type

Is Mandatory?

Is Variable Length?

FORMAT DATE - Returns the formatted date as per selected Pattern

String

NOW() = '2019-04-11T12:02:53.854+0530'
FORMAT DATE(NOW(), 'MMM dd, yyyy') = 'Apr 11, 2019'
FORMAT DATE(NOW(), 'MM/dd/yy hh:mm:ss aaa') = '04/11/19 12:02:53 PM'

date: Initial value

Date

Yes

No

pattern: Pattern value to be used to format the date

String

Yes

No

DAYS - Returns the days between provided date values

Numeric

NOW() = '2019-04-11T14:02:25.000+0530'
DAYS(NOW(), '2019-01-01T12:00:00.00+0530') = 100.09
DAYS('2019-12-31T12:00:00.00+0530', NOW()) = 263.91
DAYS(NOW(), '2019-12-31T12:00:00.00+0530') = -263.91
DAYS(NOW(), NULL) = NULL

Note: For the examples the date value is shown without any format

aDate: Initial value

Date

Yes

No

bDate: Number of days will be calculated between this value and the initial value

Date

Yes

No

BUSINESS DAYS - Returns the business days between provided date values.
Note: For calculating Business Days, days marked as weekend days in the "Page settings" in Report page are excluded

Numeric

NOW() = '2019-04-11T14:08:47.225+0530'
Weekend Days = Saturday and Sunday
BUSINESS DAYS(NOW(), '2019-01-01T12:00:00.00+0530') = 72.27
BUSINESS DAYS(NOW(), '2019-12-31T12:00:00.00+0530') = -187.73
BUSINESS DAYS('2019-12-31T12:00:00.00+0530', NOW()) = 187.73
BUSINESS DAYS(NOW(), NULL) = NULL

Note: For the examples the date value is shown without any format

aDate: Initial value

Date

Yes

No

bDate: Number of business days will be calculated between this value and the initial value

Date

Yes

No

NOW - Returns the current date and time value

Date

NOW() = '2019-04-11T12:02:53.854+0530'
Note: For the examples the date value is shown without any format

None

N/A

N/A

N/A

IS BEFORE - Returns TRUE if the aDate is chronologically before bDate. Otherwise returns FALSE

Boolean

NOW() = '2019-04-11T12:02:53.854+0530'
IS BEFORE(NOW(), '2019-04-11T12:00:00.000+0530') = FALSE
IS BEFORE(NOW(), '2019-04-12T12:00:00.000+0530') = TRUE
IS BEFORE(NOW(), NOW()) = FALSE
Note: For the examples the date value is shown without any format

aDate: Initial value

Date

Yes

No

bDate: Date against which the initial value will be compared

Date

Yes

No

IS AFTER - Returns TRUE if the aDate is chronologically after bDate. Otherwise returns FALSE

Boolean

NOW() = '2019-04-11T12:02:53.854+0530'
IS AFTER(NOW(), '2019-04-11T12:00:00.000+0530') = TRUE
IS AFTER(NOW(), '2019-04-12T12:00:00.000+0530') = FALSE
IS AFTER(NOW(), NOW()) = FALSE
Note: For the examples the date value is shown without any format

aDate: Initial value

Date

Yes

No

bDate: Date against which the initial value will be compared

Date

Yes

No

Name: Description

Type

Is Mandatory?

Is Variable Length?

EQUALS - Returns TRUE if value1 is equal to value2, otherwise returns FALSE

Boolean

EQUALS('Hello', 'hello') = FALSE
EQUALS('Hello', 'Hello') = TRUE
EQUALS(100, 100.0) = TRUE
EQUALS(0, 0.00001) = FALSE
EQUALS(NOW(), NOW()) = TRUE
EQUALS(TRUE(), TRUE) = TRUE
EQUALS(TRUE(), FALSE) = FALSE
EQUALS(NULL, NULL) = NULL
EQUALS(0, NULL) = NULL

value1: Initial value

ANY

Yes

No

value2: Value to be compared with the initial value

ANY

Yes

No

NOT EQUALS - Returns TRUE if value1 is not equal to value2, otherwise returns FALSE

Boolean

NOT EQUALS('Hello', 'hello') = TRUE
NOT EQUALS('Hello', 'Hello') = FALSE
NOT EQUALS(100, 100.0) = FALSE
NOT EQUALS(0, 0.00001) = TRUE
NOT EQUALS(NOW(), NOW()) = FALSE
NOT EQUALS(TRUE(), TRUE) = FALSE
NOT EQUALS(TRUE(), FALSE) = TRUE
NOT EQUALS(NULL, NULL) = NULL
NOT EQUALS(0, NULL) = NULL

value1: Initial value

ANY

Yes

No

value2: Value to be compared with the initial value

ANY

Yes

No

AND - Returns the value with ANDing of all parameters. Returns FALSE if any one the parameter is FALSE. Otherwise returns TRUE

Boolean

AND(TRUE, TRUE) = TRUE
AND(TRUE, FALSE) = FALSE
AND(FALSE, TRUE) = FALSE
AND(FALSE, FALSE) = FALSE
AND(TRUE, TRUE, NULL, TRUE, NULL, NULL) = TRUE
AND(TRUE, TRUE, NULL, FALSE, NULL, NULL) = FALSE
AND(NULL, NULL) = NULL

value1: Initial value

Boolean

Yes

No

value2: Value to be ANDed with the initial value

Boolean

Yes

Yes

OR - Returns the value with ORing of all parameters. Returns TRUE if any one of the parameter is TRUE. Otherwise returns FALSE

Boolean

OR(TRUE, TRUE) = TRUE
OR(TRUE, FALSE) = TRUE
OR(FALSE, TRUE) = TRUE
OR(FALSE, FALSE) = FALSE
OR(FALSE, FALSE, NULL, FALSE, NULL, NULL) = FALSE
OR(FALSE, TRUE, NULL, FALSE, NULL, NULL) = TRUE
OR(NULL, NULL) = NULL

value1: Initial value

Boolean

Yes

No

value2: Value to be ORed with the initial value

Boolean

Yes

Yes

NOT - Returns the negated value of parameter. Returns FALSE for TRUE value, and vice versa

Boolean

NOT(TRUE) = FALSE
NOT(FALSE) = TRUE
NOT(NULL) = NULL

value: Initial value

Boolean

Yes

No

TRUE - Returns TRUE value

Boolean

TRUE() = TRUE

None

N/A

N/A

N/A

FALSE - Returns FALSE value

Boolean

FALSE() = FALSE

None

N/A

N/A

N/A

IF ELSE - Returns the value1 if condition is TRUE, otherwise returns value2

ANY

IF ELSE(CONTAINS('The fox jumped over the fence', 'over'), 'Present', 'Absent') = 'Present'
IF ELSE(CONTAINS('The fox jumped over the fence', 'wire'), 'Present', 'Absent') = 'Absent'
IF ELSE(IS AFTER(NOW(), '1999-12-31T23:59:59.00+0530'), 'POST Y2K', 'PRE Y2K') = 'POST Y2K'
IF ELSE(NULL, 10, 20) = NULL
IF ELSE(TRUE, NULL, 20) = NULL
IF ELSE(FALSE, NULL, 20) = 20

condition: Condition to determine which value to return

Boolean

Yes

No

value1: Value to be returned if condition evaluates to TRUE

ANY

Yes

No

value2: Value to be returned if condition evaluates to FALSE

ANY

Yes

No

IF NULL - Returns value1 if it is not null, otherwise returns value2.
Function is useful to provide a default value, if the value of a field is not available (NULL)

ANY

IF NULL(100, 200) = 100
IF NULL(100, NULL) = 100
IF NULL(NULL, 100) = 100
IF NULL(NULL, 'Hello') = 'Hello'
IF NULL(NULL, NOW()) = '2019-04-11 15:27:45.631'
IF NULL(NULL, TRUE) = TRUE
IF NULL(NULL, NULL) = NULL

value1: Initial value

ANY

Yes

No

value2: Value to be returned if initial value is NULL

ANY

Yes

No

COALESCE - Returns the first value which is not null.
Function is useful to pick the first field which has a value

ANY

COALESCE(100, 200, NULL, 300, NULL, 500) = 100
COALESCE(NULL, NULL, 100, 200) = 100
COALESCE(NULL, NULL, NULL, 'Hello') = 'Hello'
COALESCE(NULL, NULL, NOW()) = '2019-04-11 15:48:20.548'
COALESCE(NULL, TRUE) = TRUE
COALESCE(NULL, NULL, NULL) = NULL

value1: Initial value

ANY

Yes

No

value2: Value to be returned if previous parameter values are NULL

ANY

Yes

Yes