You can use custom expressions to create custom Report Designer columns, Task & Alert and 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 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. 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. |
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. 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 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. |
| SWITCH | (Value, Test1, Val1, Test2, Val2, ... , DefaultVal) | String | All parameters must be String type. Returns the result of 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. | ||
| 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 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 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
Custom expressions support 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. The solution is to extract the date and time using YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND(), and casting it into DATETIME(). i.e. DATETIME(YEAR(columnName),MONTH(columnName),DAY(columnName),HOUR(columnName),MINUTE(columnName),SECOND(columnName)) | 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. Note: Division using static values will result in an integer quotient. Division with a numerator or denominator value derived from another column will produce a remainder in decimals. For more information, you may refer to the following article: How to obtain a quotient with remainder as decimals when dividing in Report Designer | 6 / 2 3 |