Welcome to the Veson Nautical Knowledge Base. In the Help Center, you can view the same articles and contact support as needed.
IMOS - Custom Expression Functions
You can use custom expressions to create custom Report Designer columns, Task & Alerts, Advanced Pricing Rule Values, and Data Dictionary Elements. You can access various data objects, such as Voyages, Cargoes, Estimates, and Invoices, as well as many 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 following functions 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 the variable type used in the operator expression, the results may differ 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 in 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.
When used in conjunction with an IF statement, format the condition to read "== true". For example, IF(CONTAINS(Description, "Demurrage Commission for XADDCOM") == true, (AmountBase * -1), AmountBase) |
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. |
CURRENTUSER |
| String | No parameters. Returns the name of the current user in the column. | When logged in as "admin", the column will reflect the value "admin". |
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. |
DATETIMERANGE | (DateTimeFieldID1, DateTimeFieldID2) | String | Parameters are two DateTimeFields. Returns a String type of the supplied DateTimeFields in the format MM-dd-yyyy HH:mm (i.e., 10-01-16 00:00). | DATETIMERANGE(CommenceDateLocal,CompleteDateLocal) Voyage Commence is 01 January 2019 00:00 and Voyage Complete is 16th January 2019 19:46. Returns 1/1/2019 00:00 - 1/16/2019 19:46 |
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. For IF statements, the TRUE and FALSE values should be of the same data type. | 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. IF(COSTCATEGORY == "Freight", TOSTRING(Details.AmountBase), "")) IF(COSTCATEGORY == "Freight", Details.AmountBase, 0) |
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 the 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 designated 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 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, the 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. |
SWITCH | (Value, Test1, Val1, Test2, Val2, ... , DefaultVal) | String | All parameters must be String type. Returns the result of the logical switch for the first Value parameter. If none of the specified test expressions resolve - DefaultVal value will be used as a result. | SWITCH(TOSTRING(MONTH(CommenceDateGmt)), "1", "January", "2", "February", "3", "March", "Unknown Month") CommenceDateGmt in January returns "January". CommenceDateGmt in July returns "Unknown Month".
|
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. |
|
TOFORMATTEDLOCALESTRING | (FieldID, FormatString(optional)) | String | Parameter must be a numeric or datetime field. Returns locale-specific formatting for the numeric or date |
Still need help? Visit the Veson Nautical Help Center.
Copyright © 2017-2025 Veson Nautical LLC All Rights Reserved - Privacy Notice | End User Terms of Use