Supported built-in functions in query expressions

The Bold Reports® Query Expression Designer supports the following built-in functions. You use them to build expression columns in the Web Report Designer query designer. The functions fall into five groups: numbers, conditional, logical, date, and string.

Numbers

The Bold Reports® Query Expression Designer supports these number functions, such as ABS, ROUND, POWER, and SQRT.

Functions Syntax & Descriptions
ABS
    Syntax:
    ABS(numeric_expression)
    Description:
    Returns the absolute value of the given expression.
ACOS
    Syntax:
    ACOS(numeric_expression)
    Description:
    Returns the inverse cosine (also known as arccosine) of the given numeric expression.
ASIN
    Syntax:
    ASIN(numeric_expression)
    Description:
    Returns the inverse sine (also known as arcsine) of the given numeric expression.
ATAN
    Syntax:
    ATAN(numeric_expression)
    Description:
    Returns the inverse tangent (also known as arctangent) of the given numeric expression.
COS
    Syntax:
    COS(numeric_expression)
    Description:
    Returns the cosine of the angle specified in radians, in the given expression.
DEGREES
    Syntax:
    DEGREES(numeric_expression)
    Description:
    Returns the angle in degrees for the one specified in radians, in the given numeric expression.
EXP
    Syntax:
    EXP(numeric_expression)
    Description:
    Returns the exponential value of the given expression.
LOG
    Syntax:
    LOG(numeric_expression)
    Description:
    Returns the logarithm of the given expression to the specified base.
PI
    Syntax:
    PI()
    Description:
    Returns the constant value of PI.
POWER
    Syntax:
    POWER(numeric_expression, numeric_expression)
    Description:
    Returns the value of the given expression (expression1) to the specified power (expression2).
ROUND
    Syntax:
    ROUND(numeric_expression)
    Description:
    Returns a rounded value.
RADIANS
    Syntax:
    RADIANS(numeric_expression)
    Description:
    Returns the angle in radians for the one specified in degrees in the given numeric expression.
SIGN
    Syntax:
    SIGN(numeric_expression)
    Description:
    Returns a value representing the positive (+1), zero (0), or negative (-1) sign of the given numeric expression.
SIN
    Syntax:
    SIN(numeric_expression)
    Description:
    Returns the sine of the angle specified in radians, in the given expression.
SQRT
    Syntax:
    SQRT(numeric_expression)
    Description:
    Returns the square root of the given numeric expression.
TAN
    Syntax:
    TAN(numeric_expression)
    Description:
    Returns the tangent of the given numeric expression.

Conditional

The Bold Reports® Query Expression Designer supports these conditional functions: IF, IFNULL, ISNOTNULL, and ISNULL.

Functions Syntax & Descriptions
IF
    Syntax:
    IF(expression, true_part, false_part)
    Description:
    Returns either true part or false part, depending on the evaluation of the expression.
IFNULL
    Syntax:
    IFNULL(expression1,expression2)
    Description:
    If the expression is numeric/string/date, returns the first expression. If the first expression is NULL, returns the second expression.
ISNOTNULL
    Syntax:
    ISNOTNULL(expression)
    Description:
    If the expression is numeric/string/date is NULL, returns a string representing false, otherwise returns true.
ISNULL
    Syntax:
    ISNULL(expression)
    Description:
    Returns true if the given expression evaluates to null.

Logical

The Bold Reports® Query Expression Designer supports these logical functions: AND, NOT, and OR.

Functions Syntax & Descriptions
AND
    Syntax:
    (expression1) AND (expression2)
    Description:
    Returns true if both the expressions evaluates to true.
NOT
    Syntax:
    NOT(expression)
    Description:
    Returns the reversed logical value of the expression being evaluated.
OR
    Syntax:
    (expression1) OR (expression2)
    Description:
    Returns true if any of the expressions evaluates to true.

Date

The Bold Reports® Query Expression Designer supports these date functions, such as DATEADD, DATEPART, DAYDIFF, NOW, and YEAR.

Functions Syntax & Descriptions
DATEADD
    Syntax:
    DATEADD(numeric_expression, date_expression)
    Description:
    Adds the number of days to the specified date.
DATENAME
    Syntax:
    DATENAME(date_part, date_expression)
    Description:
    Returns a string representing the specified date_part of the given date expression.
DATEPART
    Syntax:
    DATEPART(date_part, date_expression)
    Description:
    Returns an integer value representing the specified date_part of the given date expression.
DATESUB
    Syntax:
    DATESUB(numeric_expression, date_expression)
    Description:
    Returns the date subtracted from the specified date.
DAY
    Syntax:
    DAY(date_expression)
    Description:
    Returns a numeric value representing the day part of the specified date.
DAYDIFF
    Syntax:
    DAYDIFF(start_date_expression, end_date_expression)
    Description:
    Returns a numeric value representing the difference between two specified dates.
HOUR
    Syntax:
    HOUR(date_expression)
    Description:
    Returns the hour of the given date as an integer.
MINUTE
    Syntax:
    MINUTE(date_expression)
    Description:
    Returns a numeric value representing the minute part of the date resulted from specified date expression.
MONTH
    Syntax:
    MONTH(date_expression)
    Description:
    Returns a numeric value representing the month part of the date resulted from specified date expression.
NOW
    Syntax:
    NOW()
    Description:
    Returns the current date and time.
TODAY
    Syntax:
    TODAY()
    Description:
    Returns the current date.
YEAR
    Syntax:
    YEAR(date_expression)
    Description:
    Returns a numeric value representing the year part of the date resulting from the specified date expression.
MAX
    Syntax:
    MAX(expression)
    Description:
    Returns the maximum value in the given expression.
MIN
    Syntax:
    MIN(expression)
    Description:
    Returns the minimum value in the given expression.

String

The Bold Reports® Query Expression Designer supports these string functions, such as CONCAT, CONTAINS, LEFT, SUBSTR, and UPPER.

Functions Syntax & Descriptions
CHAR
    Syntax:
    CHAR(integer_expression)
    Description:
    Converts the given integer ASCII code into a character.
CONCAT
    Syntax:
    CONCAT(string_expression1, string_expression2,…, string_expressionN)
    Description:
    Returns a string value resulting from the concatenation of two or more string values.
CONTAINS
    Syntax:
    CONTAINS(string_expression, substring_expression)
    Description:
    Returns true if the given string expression contains the specified substring expression.
ENDSWITH
    Syntax:
    ENDSWITH(string_expression substring_expression)
    Description:
    Returns true if the given string expression ends with the specified substring expression.
LEFT
    Syntax:
    LEFT(string_expression, numeric_expression)
    Description:
    Returns the specified number of characters from start of the given string expression.
LEN
    Syntax:
    LEN(string_expression)
    Description:
    Returns the number of characters in the given string expression.
LOWER
    Syntax:
    LOWER(string_expression)
    Description:
    Returns a lower case converted string value from a given string expression.
LTRIM
    Syntax:
    LTRIM(string_expression)
    Description:
    Returns the string value with any leading blanks removed from string expression.
MAX
    Syntax:
    MAX(expression)
    Description:
    Returns the maximum value in the given expression.
MIN
    Syntax:
    MIN(expression)
    Description:
    Returns the minimum value in the given expression.
RIGHT
    Syntax:
    RIGHT(string_expression, numeric_expression)
    Description:
    Returns the specified number of characters from end of the given string expression.
RTRIM
    Syntax:
    RTRIM(string_expression)
    Description:
    Returns the string value with any trailing blanks removed from string expression.
STARTSWITH
    Syntax:
    STARTSWITH(string_expression, substring_expression)
    Description:
    Returns true if the given string expression starts with the specified substring expression.
SUBSTR
    Syntax:
    SUBSTR(string_expression, starting_index, length_of_the_string)
    Description:
    Returns a specific length of string starting from specific index from the given string expression.
UPPER
    Syntax:
    UPPER(string_expression)
    Description:
    Returns an upper case converted string value from a given string expression.

Frequently asked questions

Which function adds days to a date?

The DATEADD function, with the syntax DATEADD(numeric_expression, date_expression), adds the number of days to the specified date.

What does the IFNULL function return?

If the first expression is NULL, IFNULL returns the second expression. Otherwise, it returns the first expression.

See also