Supported functions and operators

The table below contains a listing of supported functions. Examples are shown below for each supported function.

Function Name	Parameters	Description
OR	Two or more Boolean expression parameters	Returns true if one or more parameters are true.

OR( [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars";
                [BT_CUSTOMERS.BC_CUSTOMERS_CREDITLIMIT] > 1000 )

Function Name	Parameters	Description
AND	Two or more Boolean expression parameters	Returns true if all parameters are true.

AND( [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars";
                [BT_CUSTOMERS.BC_CUSTOMERS_CREDITLIMIT] > 1000 )

Function Name	Parameters	Description
LIKE	Two parameters	Compares a column to a regular expression, using % as a wild card.

LIKE([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "%SMITH%")

Function Name	Parameters	Description
IN	Two or more parameters	Checks to determine if the first parameter is in the following list of parameters.

IN([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "Adam Smith"; "Brian
                Jones")

Function Name	Parameters	Description
NOW	None	The current date

NOW()

Function Name	Parameters	Description
DATE	Three numeric parameters: year, month, and day	A specified date

DATE(2008;4;15)

Function Name	Parameters	Description
DATEVALUE	One text parameter "year-month-day"	A specified date

DATEVALUE("2008-04-15")

Function Name	Parameters	Description
CASE	Two or more parameters	Evaluates the first, third, etc. parameter, and returns the second, fourth, etc. parameter value. If there are an odd number of parameters, the last parameter is returned if no other parameter evaluates to true. Note that when using this function, the formula must be set on a new column as shown below.

CASE( [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars"; "European Cars";
                [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "AsiaCars"; "Asian Cars"; "Unknown Cars"
                )

Function Name	Parameters	Description
COALESCE	One or more parameters	Returns the first non-null parameter.

CASE( [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars"; "European Cars";
                [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "AsiaCars"; "Asian Cars"; "Unknown Cars"
                )

Function Name	Parameters	Description
DATEMATH	One or more parameters; see DateMath Javadoc for full syntax	Returns a date based on an expression. Note that this function does not return a timestamp irrespective of the implementation details in the description below.

DATEMATH("0:ME -1:DS") - 00:00:00.000 of the day before the last day of the current month
DATEMATH("0:MS  0:WE") - 23:59:59.999 the last day of the first week of the month
DATEMATH("0:ME") - 23:59:59.999 of the last day of the current month
DATEMATH("5:Y") -  the current month, day and time 5 years in the future
DATEMATH("5:YS") - 00:00:00.000 of the first day of the years 5 years in the future 

The table below contains a listing of supported aggregate functions.

Function Name	Description
SUM	Sums a specific columns values determined by grouping.
COUNT	Counts a specific columns values determined by grouping.
AVG	Averages a specific columns values determined by grouping.
MIN	Selects the minimum column value determined by grouping.
MAX	Selects the maximum column value determined by grouping.

The table below contains a listing of supported operators.

Operator	Description
=	Returns true if two expressions are equal.
>	Returns true if first expression is larger than the second.
<	Returns true if first expression is smaller than the second.
>=	Returns true if first expression is larger than or equal to the second.
<=	Returns true if first expression is smaller than or equal to the second.
<>	Returns true if two expressions are not equal.
+	Adds two values.
-	Subtracts two values.
*	Multiplies two values.
/	Divides two values.