The following samples provide expressions that may be used across a variety of reporting scenarios.
To retrieve the difference of time between two dates, the dates can be subtracted from one another. The following expression returns a colon-separated string of the number of days, hours, minutes, and seconds the two dates differ by, in that order.
The same logic can be applied for properties that hold dates as values.
If only the amount of days is desired between two dates, the DateDiffDay function can be used to retrieve the only amount of days.
The Count function retrieves the number of items within a given collection. The following expressions return the number of items in an order.
Expressions support an array of arithmetic functions that can be applied to all fields that contain a number.
In the following expression, an order’s base price is added to the same order’s tax total.
In the following expression, the amount of time remaining before an invoice’s due date is calculated.
In the following expression, the total volume of an order’s contents is calculated.
In the following expression, a ratio of an item’s weight compared to the rest of the order’s contents is determined.
#4 – SUM Function
Using the Sum function, multiple values residing within a collection can be added together. In the following expression, all of the prices inside of an order’s price modifiers are added together and returned.
Sum([Orders.Price Modifiers.Price])
Functions can be applied in different locations in an expression. The following expression returns the same result as the previous expression.
[Orders.Price Modifiers].Sum([Price])
The previous expression can be defined further to only include price modifiers that are of type ‘Tax’.
[Orders.Price Modifiers][ToStr([Type]) == 'Tax'].Sum([Price])
#5 – MIN / MAX Function
Using the Min function or the Max function, the minimum or the maximum values within a given collection can be retrieved respectively.
[Orders.Price Modifiers].Max([Price])
[Orders.Price Modifiers].Min([Price])
#6 – Equality of Given Value
Expressions can be used to narrow down collections to specific items. In the following expression, the driver’s orders are narrowed down to orders that pertain only to ‘ABC Inc’.
[Driver Order Details.Order][[Company] == 'ABC Inc.']
#7 – Upper / Lower Function
String values displayed on a report can be displayed in all uppercase letters or lowercase letters using the Upper and Lower functions respectively.
Upper([Price Set.Internal Name])
Lower([Price Set.Internal Name])
#8 – Average Function
The Avg function can be used to calculate the average value of a collection of numeric values.
Avg([Driver Order Details.Order.Mileage])
#9 – Contains Function
The Contains function determines if a string value contains a specific sub-string. If the sub-string exists within the string, the function returns as ‘True’. In the following expression, only orders that contain the sub-string ‘ASAP’ are retrieved.
[Orders][Contains('ASAP', [Comments])]#10 – Greater or Lesser than Date
Dates can be compared to determine if a given date has occurred after or before another date.
[Orders][[Date Submitted] >= #1/1/2016#]
[Orders][[Date Submitted] <= #12/31/2015#]
#11 – Boolean Values
Using Boolean logic, collections can be narrowed down to only those that meet specific conditions, true or false. In the following expression, the equality operator is used to only retrieve orders that require a COD.
[Orders][[COD Required at Delivery] == True]
Furthermore, only orders that do not require a COD can be retrieved.
[Orders][[COD Required at Delivery] == False]
Expressions can also be written to use the inequality operator to retrieve orders that do not require a COD. The returned values from the following expression are equivalent to those returned by the previous expression.
[Orders][[COD Required at Delivery] != True]
#12 – Between Two Values
Using the Between keyword, collections can be narrowed down by their field’s values lying between two given operands. In the following expression, only orders that weigh between or equal to 0 and 100 units are returned.
[Orders][[Weight] Between (0, 100)]
Dates can also be used as operands with the Between keyword. In the following expression, only orders that were submitted between or equal to the two given dates are returned.
[Orders][[Date Submitted] Between (#6/1/2016# , #6/30/2016#)]
#13 – Getting Single or Multiple Property Values
Using the Single function, we can retrieve a specific field’s value in a collection of returned items, such as orders or locations. In the following expression, the delivery city is returned from an order with a given tracking number.
[Orders][[Tracking Number] == '4787'].Single([Delivery Location.City])
Multiple values can be retrieved using a similar method. In the following expression, all of the orders that contain a user-defined field with a given name are returned, followed by the addition of the returned order’s total costs using the Sum function.
[Orders][[User Defined Fields.Label] == 'International AWB'].Sum([Total Cost])
#14 - Dates
In any expression, dates are expressed using the following syntax:
#1/1/2016#
Dates can be used as parameters to narrow down a collection of items. In the following expression, only orders that were submitted on the given date are returned.
[Orders][[Date Submitted] == #6/23/2014#]
#15 – Boolean AND / OR
Multiple conditions can be defined when narrowing down a collection of items. Using the AND operator, items are only returned when both conditions are met. In the following expression, only orders that require a COD and contain a required COD amount greater than 0 are returned.
[Orders][[COD Required at Delivery] == True AND [COD Amount] > 0]
Expressions can also use the OR operator to narrow down collections to meet only one of the provided conditions. In the following expression, orders that are assigned to either the given route name or assigned to a given company are returned.
[Orders][[Route Name] == 'My Route' OR [Delivery Location.Company Name] == 'ABC Inc.']
#16 – Decimal Rounding
Using the Round function, numeric fields can be rounded to the closest whole number. In the following expression, all orders returned will have their distance rounded to the nearest unit.
Round([Orders.Distance])
The Round function can also be supplied a numeric parameter to include rounding to a number of decimal places. In the following expression, the calculated value is rounded to the nearest two decimal places.
Round([Orders.Items.Weight] / [Orders.Weight] * 100, 2)
#17 – IN Operator
Using the In keyword, collections can be narrowed down by their field’s values existing within an array of values. In the following expression, only orders that are being delivered to Ontario or Vancouver are returned.
[Orders][[Delivery Location.City] In ('Ontario', 'Vancouver')]#18 - String Manipulation
Using expressions, fields that contain strings as values can be combined or manipulated to create entirely new strings. In the following expression, an order’s delivery address for both line one and line two are combined into one string then separated between a new line.
[Orders.Delivery Location.Address 1] + NewLine() + [Orders.Delivery Location.Address 2]
The same expression can also be rewritten using the Concat function, which concatenates all strings passed to it as parameters.
Concat([Orders.Delivery Location.Address 1], NewLine(), [Orders.Delivery Location.Address 2])
Strings within a collection can also be combined using the Sum function. In the following expression, all of the names of an order’s price modifiers are combined then separated by a new line.
[Orders.Price Modifiers].Sum([Name] + NewLine())
If a string is too long, it can be truncated using the Remove function. In the following expression, an order’s description is shortened if it exceeds 30 characters in length.
Remove([Orders.Description], 30)
Strings can also be padded with any character on either the left or right side. In the following expression, an order’s tracking number is padded on the left side with up to 8 zeroes. If the length of the tracking number exceeds 8, no zeroes are added to the left side of the string.
PadLeft([Orders.Tracking Number], 8, '0')
#19 – NULL Values
Occasionally, fields may be left blank and without a value and may be undesired on a report. To test for empty values, the question mark (?) operator is used. In the following expression, all of the labels for an order’s user-defined fields are displayed if they include a defined value.
[Orders].[User Defined Fields][[Value] != ?].Sum([Value] + NewLine())
#20 - IIF
The IIF function can be used to display a specific piece of data over another depending on if a particular condition is met. In the following expression, if today has passed the invoice’s defined due date, the value that will appear in this field will be ‘OVERDUE’. Otherwise, the value that will appear in this field will be ‘PENDING’.
IIF(Today() > [Invoice.Due Date], 'OVERDUE', 'PENDING')