...
Here are some sample custom expressions with functions you can use to create custom Report Designer columns, Task & Alert and Advanced Pricing Rule Values, and Cargo Matching programs:
| Table of Contents | ||||
|---|---|---|---|---|
|
Note: Each expression assumes a certain base table in the report and, therefore, is not simply "plug and play."
Identifying Voyage Commence Month and Quarter
This expression uses nested IF statements to see if the numeric value of the month of the Commence Date of the voyage is within a list of numbers. If it is in that list, it then returns the name of the Month, rather than the number; otherwise, it moves on to the next condition. Note that the expression also skips the comparison for month 12; this comparison is unnecessary because the final result can only be December.
...
(IF(INLIST(TOLOCALESTRING(MONTH(CommenceDateGmt)), "11"), "Nov","Dec")))))))))))))))))))))
The Next Step
This example uses INLIST to perform the comparison because it makes it trivial to convert this same logic to apply Voyages to Quarters instead.
...
(IF(INLIST(TOLOCALESTRING(MONTH(CommenceDateGmt)), "7", "8", "9"), "Q3","Q4")))))
Calculating Bunkers ROB on Departure from Previous Port
This expression uses ROWOFFSET to pull the Bunker ROB on departure from the previous port for display on the current row. This may be useful in cases where you want to show Sea Leg Commence ROB and Sea Leg Complete ROB, as opposed to ROB on Arrival and ROB on Departure.
...
- -1 indicates that the ROWOFFSET function will return the result of the row preceding the current row.
- ORDERBY(VesselCode, VoyageNo, VoyageItineraries.VoyageItineraryBunkers.FuelType, VoyageItineraries.Seq) tells the ROWOFFSET function that when determining what is the previous row, it should first order the returned data by Vessel Code, then Voyage Number, then Bunker Type, and finally Itinerary Sequence number.
.VoyageItineraries.VoyageItineraryBunkers.RobDeparture: The field name at the end of ROWOFFSET tells the function which specific field to pull from in the offset row. In this case, the function is pulling the previous row's Departure ROB.
The Next Step
You may notice that this function produces incorrect results when it encounters a previous row that is for a different vessel. This is because ROWOFFSET does not understand when different rows are no longer related to one another; it is a naive function. You can enhance this function by adding in new functions and making a more powerful expression.
...
You will notice that ORDERBY is the same for both the Condition and the Result ROWOFFSET. This is necessary when using an IF in most scenarios because it is the only way to guarantee that IMOS is checking the same row in both the Condition Rowoffset and the Result Rowoffset. Though it seems long, wrapping ROWOFFSET functions in IF statements greatly improves their usability. It also provides the flexibility needed to define the relationship between the rows.
Extracting Market Freight From Calculation Description
This expression can be used to extract a Market Freight Rate $/MT that has been back-calculated from the $/day provided by Baltic. This requires the IMOS Trading Module as well as the Market Data Feeds Module.
...
This function uses SUBSTRING to extract only the part of the Calculation Description that is relevant to the Freight Rate. The nested IF statements are used to determine where the first space character occurs in the Calculation Description and then returns that location as the Length of the first Substring. It does have a limitation in that it can only extract a freight in the format of ###.####, therefore assuming a maximum $/MT of $999.9999.
Adding Custom Font Colors
You can add custom font colors to a column:
...
In this example, the font color of the Opr Type column is set to green if the Operation Type is OVOV. For all other Operation Types, the font color is red:
Calculating Total Voyage Days
You can create a simple custom column that adds the Port Days and Sea Days columns together:
...
- Heading: Total Voyage Days
- Expression: VoyageItineraries.PortDays+VoyageItineraries.SeaDays
- Font Color: "Blue"
- Width: 125.00
Calculating Voyage P&L Results Based on Snapshots
You can calculate the P&L results from saved P&L Snapshots in the database, based on a period you enter and the saved calculation options:
- Join the Voyage Pnl Table to the Voyage table.
- Filter on the new column Period Range Gmt.
- Enter the date range to which you want the calculation applied.
- To see just the relevant voyages, also apply a filter for Voyage Range GMT.
Calculating the Duration of a Date Range
You can calculate the difference between two dates as a number of days, using date and/or date-time values. For example:
TODAY() - DueDate
Calculating the Age Days for Invoices
You can calculate the age days for invoices using the alternative IF function. For example:
DueDate == null ? 0 : (TODAY() - DueDate)
Calculating the Difference between Daily Costs
You can use a field in another row to calculate the difference between the previous Daily Cost and today's:
- Add custom column Previous Cost, using this expression:
ROWOFFSET(−1, ORDERBY (VesselCode,VoyageNo)).Daily Cost
It takes the data from the Daily Cost field that is one row above, when the list is sorted by Vessel Code and Voyage No. - Add custom column Delta Cost, using this expression:
Daily Cost − Previous Cost
Adding Red Highlight for Next Dry Dock within 30 Days
| Insert excerpt | ||||||
|---|---|---|---|---|---|---|
|