IMOS On-Prem - Custom Expression Functions

Error rendering macro 'excerpt-include' : No link could be created for 'IMOS'.

You can use custom expressions with functions to create custom Report Designer columns as well as Task & Alert and Advanced Pricing Rule Values. You can access various data objects, such as Voyages, Cargoes, Voyage Estimates, and Invoices, as well as many of the data fields within those objects. In addition, custom expressions enable you to use various functions to create formulas that calculate new fields and values that can be added to reports or output as Advanced Pricing Rules, or even to create complex conditional statements in Tasks and Alerts. The functions below are used when building reports, and mastering them will enhance the information you can extract.

The functions come in two types: named functions and operators. Named functions require you to specify a field or a value as an input parameter and then provide the result. You can choose to display this result, or you can choose to nest functions within one another to create more complex expressions.

Operators are simpler functions and look similar to mathematical operators. However, depending on on the type of the variable used in the operator expression, the results may be different from what is expected.

In addition to the explanations below, see our Sample Custom Expressions for use-case scenarios of the functions.

Functions

Function	Parameters	Return Type	Description	Example
ABS	(FieldID)	Numeric	Parameter field must be a Numeric field. Returns the absolute value of the parameter.	ABS(-5) returns 5.
AVG	(FieldID)	Numeric	Parameter field must be a Numeric field within a one-to-many join. Returns the mean average of the values within the join.	AVG(Voyage.VoyageBunkerSummaries.EndPrice) Returns the mean average of the End Price for all bunker types within a single voyage.
COALESCE	(FieldID1, FieldID2, ...)	String	Parameter fields are a list of fields of the same type. Returns the first value of the list of fields that is not null.	COALESCE(FirstDetails.Voyage.VesselName, FirstDetails.Vessel.Name) In the Operations Invoice Table, returns the Voyage Vessel Name for the Operations Invoice first, but if not Voyage (e.g., TCIP), returns the Vessel Object Vessel Name.
CONCAT	(FieldID, FieldID2, ...)	String	Parameter fields must be String types; try using TOSTRING(). Parameter fields can include non-field strings designated by "". Returns a single string that is a comma-separated list of the fields.	CONCAT(Voyage.VesselName, Voyage.VoyageNo) Returns MV AKTAIA, 1601.
CONTAINS	(FieldID, SearchString)	Boolean	First parameter must be a String type; this is the field within which you will be searching. Second parameter is a String that will be searched for; can be a field or manually designated between "". Returns True if the second parameter can be found entirely within the first parameter; otherwise, returns False.	CONTAINS(Voyage.VesselName, "AKTA") "MV AKTAIA" returns True. "MV BELISAMA" returns False.
COUNT	(FieldID)	Numeric	Parameter field must be a field within a one-to-many join. Returns the number of times within the join that the field is populated.	COUNT(Voyage.VoyageBunkerSummaries.FuelType) A vessel with four types returns 4. A vessel with two types returns 2.
COUNTDISTINCT	(FieldID)	Numeric	Parameter field must be a field within a one-to-many join. Returns the number of times within the join that the field is populated with a unique value.	COUNTDISTINCT(Voyage.VoyageBunkerSummaries.VoyageNo) Returns 1; VoyageBunkerSummaries is a one-to-many table, but each line has a single Voyage Number, and therefore is considered distinct.
DATE	(Year, Month, Day)	DateTime	Parameters are Integer values. Year must be four digits, corresponding to the year. Month must be two digits, corresponding to the month, from 01 to 12, where 01 is January, and 12 is December. Day must be two digits, corresponding to the day within the month, from 01 to 31. Returns a DateTime type.	DATE(2016, 03, 14) Returns DateTime of 14 March 2016, 00:00.
DATERANGE	(DateFieldID1, DateFieldID2)	String	Parameters are two DateFields. Returns a String type of the supplied DateFields in the format dd-MMM-yy (i.e., 01-Oct-16).	DATERANGE(Voyage.CommenceDateLocal, Voyage.CompleteDateLocal) Voyage Commence is 14 March 2016 and Voyage Complete is 7 April 2016. Returns 14-Mar-16 - 07-Apr-16.
DATETIME	(Year, Month, Day, Hours, Minutes, Seconds)	DateTime	Parameters are Integer values. Year must be four digits, corresponding to the year. Month must be two digits, corresponding to the month, from 01 to 12, where 01 is January, and 12 is December. Day must be two digits, corresponding to the day within the month, from 01 to 31. Hours must be two digits, corresponding to the 24 hours of a day, from 0 to 23. Minutes must be two digits, corresponding to the 60 minutes within an hour, from 0 to 59. Seconds must be two digits, corresponding to the 60 seconds within a minute, from 0 to 59. Returns a DateTime type.	DATETIME(2016, 03, 14, 09, 49, 53) Returns DateTime of 14 March 2016 @ 09:49:53.
DAY	(DateFieldID)	Numeric	Parameter field must be a DateTime type. Returns the integer representing the Day value of the DateTime parameter.	DAY(Voyage.CommenceDateGmt) Voyage Commence is 01 October 2016 @ 08:45:00. Returns 01.
DISPLAYVALUE	(EnumLiteral)	String	Specific use-case function only. Parameter field is a field with a coded value stored in the database. Returns a human-readable representation of the coded value.	No relevant examples.
ENDSWITH	(FieldID, SearchString)	Boolean	First parameter is a String type field. Second parameter is is a String that will be searched for at the end of the first parameter; can be a field or manually designated between "". Returns True if the first parameter contains the exact search string as its last characters; otherwise, returns False.	ENDSWITH(Voyage.VoyageBunkerSummaries.FuelType, "GO") Fuel Type "MGO" returns True. Fuel Type "LSMGO" returns True. Fuel Type "IFO" returns False.
HOUR	(DateFieldID)	Numeric	Parameter field must be a DateTime type. Returns the integer representing the Hour value of the DateTime parameter.	HOUR(Voyage.CommenceDateGmt) Voyage Commence is 01 October 2016 @ 08:45:00. Returns 08.
IF	(Condition, true, false)	Varies	First parameter is a Boolean expression; formatting can vary but is typically some sort of check for inequality (e.g., Cargo.CPQty > Cargo.BLQty). Second parameter is an expression that will be evaluated if the first parameter returns True. Third parameter is an expression that will be evaluated if the first parameter returns False.	IF(Cargo.CPQty > Cargo.BLQty, "Underloaded", "Not Underloaded") This example will evaluate the CP Qty and BL Qty of a Cargo, and if the CP Qty is greater than BL Qty, will return a String type value, displaying either Underloaded or Not Underloaded. For more information, see the further notes on IMOS - Custom Expression Functions#ConditionalExpressions.
INDEX	(FieldID, Num)		First parameter is a String type field. Second parameter is an integer, starting at 1. Returns the character in the provided String located at the position indicated by the Integer.	INDEX(VesselName, 4) VesselName is MV AKTAIA. Returns A.
INLIST	(Value, Target1, Target2, ...)	Boolean	First parameter is a field, or hardcoded value, that will be compared against the remaining parameters. All other parameters are either fields or hardcoded values and will be used to populate the list of values used for comparison. Returns True if the first parameter matches at least one of the supplied values of the other parameters; otherwise returns False.	INLIST(Voyage.VoyageBunkerSummaries.FuelType, "LSG", "LSMGO", "LGO", "LSF", "LSFO") Fuel Type "IFO" returns False. Fuel Type "LSG" returns True.
JOIN	(“,”, FieldID)	String	First parameter must be ",". Second parameter is a field in a one-to-many join. Returns a concatenated list of the values within the join.	JOIN(",", Voyage.VoyageBunkerSummaries.FuelType) Returns IFO, MGO, LSF, LGO.
JOINDISTINCT	(",", FieldID)	String	First parameter must be ",". Second parameter is a field in a one-to-many join. Returns a concatenated list of only the unique values within the join.	JOINDISTINCT(", ", Voyage.)
LEN	(FieldID)	Numeric	Parameter field must be a String type. Returns the length of the string.	LEN(Voyage.VesselName) Vessel Name is MV AKTAIA. Returns 9.
MAX	(FieldID)	Numeric	Parameter field must be a Numeric type in a one-to-many join. Returns the Maximum value within the Join set.	MAX(Voyage.VoyageBunkerSummaries.EndQty) Returns the highest End Qty for all bunker types within a single voyage.
MIN	(FieldID)	Numeric	Parameter field must be a Numeric type in a one-to-many join. Returns the Minimum value within the Join set.	MIN(Voyage.VoyageBunkerSummaries.EndQty) Returns the lowest End Qty for all bunker types within a single voyage.
MINUTE	(DateFieldID)	Numeric	Parameter field must be a DateTime type. Returns the integer representing the Minute value of the DateTime parameter.	MINUTE(Voyage.CommenceDateGmt) Voyage Commence is 01 October 2016 @ 08:45:00. Returns 45.
MONTH	(DateFieldID)	Numeric	Parameter field must be a DateTime type. Returns the integer representing the Month value of the DateTime parameter.	MONTH(Voyage.CommenceDateGmt) Voyage Commence is 01 October 2016 @ 08:45:00. Returns 10.
NOW		DateTime	No parameters. Returns the DateTime, in local server time, at the moment the expression is evaluated (the moment the report is run).
NOWGMT		DateTime	No parameters. Returns the DateTime, in GMT, at the moment the expression is evaluated (the moment the report is run).
ROWOFFSET	(n, ORDERBY(FieldA, FieldB)).FieldID	Varies	First parameter is an Integer; determines how many rows to look above or below current row. Second parameter is an ORDERBY function that takes a list of fields to order the Report Data by. Returns the value of the field designated by the final parameter after the ORDERBY function, but from the number of rows away designed by the first parameter.
SECOND	(DateFieldID)	Numeric	Parameter field must be a DateTime type. Returns the integer representing the second value of the DateTime parameter.	SECOND(Voyage.CommenceDateGmt) Voyage Commence is 01 October 2016 @ 08:45:00. Returns 00.
SPLIT	(FieldID, Delimiter, Index)	String	First parameter is a String type field. Second parameter is a specified String, i.e., ",". Third parameter is the index of the part you would like to return. Index is 0-based, meaning the first part is index 0. Returns the split part from the String according to the Index value.
SQRT	(FieldID)	Numeric	Parameter must be Numeric. Returns the Square Root of the parameter value.	SQRT(4) Returns 2.
STARTSWITH	(FieldID, SearchString)	Boolean	First parameter is a String type field. Second parameter is is a String that will be searched for at the end of the first parameter; can be a field or manually designated between "". Returns True if the first parameter contains the exact search string as its first characters; otherwise, returns False.	STARTSWITH(Voyage.VoyageBunkerSummaries.FuelType, "LS") Fuel Type "MGO" returns False. Fuel Type "LSMGO" returns True. Fuel Type "IFO" returns False.
SUBSTRING	(FieldID, StartIndex, Length(optional))	String	First parameter is a String type field. Second parameter is an Integer denoting the character position to begin creating the substring. (First character is position 0.) Third parameter is the optional length for the substring; otherwise function will substring until the end. Returns the substring of the first parameter designated by the StartIndex and Length parameters.	Vessel Name is MV AKTAIA. SUBSTRING(Voyage.VesselName, 3) Returns AKTAIA. SUBSTRING(Voyage.VesselName, 3, 4) Returns AKTA.
SUM	(FieldID)	Numeric	Parameter field must be a Numeric field within a one-to-many join. Returns the sum of the values within the join.	SUM(Voyage.VoyageBunkerSummaries.EndQty) Returns the sum of the End Qty for all bunker types within a single voyage.
TIME	(Hours (0-23), Minutes (0-59), Seconds (optional, 0-59), Milliseconds (optional, 0-999)))	Time	Parameters must be Integers. Hours must be two digits, corresponding to the 24 hours of a day, from 0 to 23. Minutes must be two digits, corresponding to the 60 minutes within an hour, from 0 to 59. Seconds must be two digits, corresponding to the 60 seconds within a minute, from 0 to 59. Milliseconds must be three digits, corresponding to the 1000 milliseconds within a second, from 0 to 999.	TIME(09, 45, 53) Returns 09:45:53.
TODAY		DateTime	No parameters. Returns the Date, in local server time, at the moment the expression is evaluated (the moment the report is run), with a time of 00:00.
TODAYGMT		DateTime	No parameters. Returns the Date, in GMT, at the moment the expression is evaluated (the moment the report is run), with a time of 00:00.
TOINT	(FieldID)	Numeric	Parameter is a String type field. Returns the value represented by the string, but as an Integer type.	TOINT(Voyage.VoyageNo) Voyage Number is 1601 (String type). Returns 1601 (Integer type).
TOLOCALESTRING	(FieldID)	String	Parameter is a Numeric type field. Returns the value of the number, visually formatted according to your locale.	TOLOCALESTRING(VoyagePnl) VoyagePnl is 1253.21. Your locale is fr-FR. Returns 1253,21.
TOSTRING	(FieldID)	String	Parameter is any non-String type field. Returns the value of the parameter, but as a String type.	TOSTRING(Voyage.VoyageBunkerSummaries.EndQty) End Qty is 330 (Integer type). Returns 330 (String type).
YEAR	(DateFieldID)	Numeric	Parameter field must be a DateTime type. Returns the integer representing the Year value of the DateTime parameter.	YEAR(Voyage.CommenceDateGmt) Voyage Commence is 01 October 2016 @ 08:45:00. Returns 2016.

Conditional Expressions

IF Statements are a very powerful tool in reporting because they enable you to create complex conditional expressions. The IF statement of the formula editor supports the following Boolean logic operations:

Symbol	Logical Operations	Example
\|\|	Or	IF(VoyageStatus=="Completed" \|\| VoyageStatus=="Closed", ..., ...)
>=	Greater Than or Equal To	IF(CommenceDate >= TODAY(), ..., ...)
>	Greater Than	IF(CommenceDate > TODAY(), ..., ...)
==	Equality	IF(VoyageStatus=="Commenced", ..., ...)
<=	Less Than or Equal To	IF(CommenceDate <= TODAY(), ..., ...)
<	Less Than	IF(CommenceDate < TODAY(), ..., ...)
&&	And	IF(VoyageStatus=="Commenced" && CommenceDate > TODAY(), ..., ...)
!=	Inequality	IF(VoyageStatus!="Closed", ..., ...)
""	Blank	IF(VoyageStatus!="", ..., ...)

Boolean operations can be strung together to make complex conditions using parentheses (); for example:

IF( (VoyageStatus=="Commenced" && CommenceDate > TODAY()) || (VoyageStatus=="Scheduled" && CommenceDate < TODAY()), "Red", "White")

To see how they can be used in different circumstances, review the Report Designer tutorials and our sample reports.

IF Statements and JOIN Functions

Combining one-to-many functions such as MAX and AVG within an IF statement can have unpredictable results. In cases where you need to check a MAX value, it is better to store the result of the MAX function in a separate column, which you will then use in the IF statement.

Alternative IF Statement Syntax

The Custom Expression Editor supports an alternative, short-hand format for IF conditionals.

Boolean statement ? true result : false result

The same sample above can be written in short-hand as:

Cargo.CPQty > Cargo.BLQty ? "Underloaded" : "Not Underloaded"

Operators

Operator Symbol

Valid Expressions

Example

+

String + String

Using the + operator with two String types as the operands will concatenate the two strings together and return the value as a string.

VesselName + "-" + VoyageNo

MV AKTAIA-1602

Numeric + Numeric

Using the + operator with two Numeric types as the operands will add the two numbers together and return the sum as a numeric.

2 + 2

4

CPQty + 10000

CPQty is 30000

40000

Boolean + Boolean

Using the + operator with two Boolean types as the operands will return a Boolean that is the result of a logical OR function.

False + False

False

False + True

True

True + True

True

DateTime + DateTime

Using the + operator with two DateTime types as the operands will return a number that is the difference, in days, between the two dates.

The returned number will be negative if the first operand is an earlier date than the second operand.

01-Dec-12 + 01-Jan-13

-31

01-Jan-13 + 01-Dec-12

31

DateTime + Numeric

Using the + operator with a DateTime type and Numeric type as the operands will return a DateTime value that is the provided date time plus the number of days.

01-Dec-12 + 5

06-Dec-12

-

Numeric - Numeric

Using the - operator with two Numeric types as the operands will subtract the two numbers and return the difference as a Numeric.

5 - 3

2

Boolean - Boolean

Using the - operator with two Boolean types as the operands will return a Boolean that is the result of a logical OR function.

False - False

False

False - True

True

True - True

True

DateTime - DateTime

Using the - operator with two DateTime types as the operands will return a number that is the difference, in days, between the two dates.

The returned number will be negative if the first operand is an earlier date than the second operand. (This is the same behavior as the + operator.)

01-Dec-12 - 01-Jan-13

-31

01-Jan-13 - 01-Dec-12

31

DateTime - Numeric

Using the - operator with a DateTime type and Numeric type as the operands will return a DateTime value that is the provided date time minus the number of days.

01-Jan-13 - 5

21-Dec-12

*

DateTime * DateTime

Using the * operator with two DateTime types as the operands will return a number that is the difference, in days, between the two dates.

The returned number will be negative if the first operand is an earlier date than the second operand. (This is the same behavior as the + operator.)

01-Dec-12 * 01-Jan-13

-31

01-Jan-13 * 01-Dec-12

31

Numeric * Numeric

Using the * operator with two Numeric types as the operands will multiply the two numbers and return the product as a Numeric.

30000 * .1

3000

/

DateTime / DateTime

Using the / operator with two DateTime types as the operands will return a number that is the difference, in days, between the two dates.

The returned number will be negative if the first operand is an earlier date than the second operand. (This is the same behavior as the + operator.)

01-Dec-12 / 01-Jan-13

-31

01-Jan-13 / 01-Dec-12

31

Numeric / Numeric

Using the / operator with two Numeric types as the operands will divide the two numbers and return the quotient as a Numeric.

6 / 2

3