Custom Fields Expression Editor

The Custom Fields Expression Editor supports a variety of math, date-time, string and logical functions. The following are examples of simple expressions.

Simple Formulas

You can perform basic math, such as add, subtract, multiply and divide data in fields:

  • Addition (+)

  • Subtraction (-)

  • Multiplication (*)

  • Division (/)

  • Equal (=)

Functions

  • Average returns the average of the values in a group in a number format only.

    Format: Number

    Parameter values:

    Parameter Description
    field The field that contains the value. You can only select numeric data fields.

    For example, this function:

    returns:

  • Case returns a value when the first condition is met (like an IF-THEN-ELSE statement). Once a condition is true, it will stop reading and return the result. If no conditions are true, it will return the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns NULL.

    Format: Number

    Parameter values:

    Parameter Description
    condition The expression to evaluate.
    true The value to return if the condition is true.
    false The value to return if the condition is false.

    For example, this function:

    returns:

  • Charindex searches for one character expression inside a second character expression, returning the starting position of the first expression if found.

    Format: Number

    Parameter values:

    Parameter Description
    substring The substring to be searched. This parameter must be in single quotes. You can only select non-numeric data fields.
    string The string from which the substring is to be searched. You can only select non-numeric data fields.
    start

    Optional: This parameter is only used when you need to specify a starting position in the string apart from the start of the string. You can only select numeric data fields.

    Note: You must delete this parameter if you are not using it.

    For example, this function:

    returns:

  • Concatenate joins text from separate fields into a new field.

    Format: String

    Parameter values:

    Parameter Description
    field1, field 2

    The fields to add together.

    For example, this function:

    returns:

  • Concatenate Separator returns a string resulting from the joining of two or more string values in and end-to-end manner. It separates those joined string values with the separator you specify in the argument.

    Format: String

    Parameter values:

    Parameter Description
    separator

    The separator to use. You can only select non-numeric data fields.

    Note: This parameter must be in single quotes.
    field1, field2 The fields to add together

    • For example, this function:

    returns:

  • Count returns the number of the items found in the field.

    Format: Number

    Parameter values:

    Parameter Description
    field

    The field to count.

    For example, this function:

    returns:

    In this example, there are 22 'Brian Test' user roles with different user role IDs.

  • Count Distinct returns the number of unique entries within the selected field.

    Format: Number

    Parameter values:

    Parameter Description
    field

    The field that contains the value.

    For example, this function:

    returns:

    For this example, we have a list of last names and we want to know if they are unique users or if there are users with the same last name. The result shows two users with the last name Decker and 2 users with the last name Jones.

  • Date Add / Subtract adds or subtracts the specified number of days, months, or years of the selected date field.

    Format: Date/Time

    Parameter values:

    Parameter Description
    interval

    The type of interval to add. Can be one of the following values:

    • Year

    • Quarter

    • Month

    • Day of Year

    • Day Week

    • Week Day

    • Hour

    • Minute

    • Second

    • Millisecond
    number

    The value of the time/date interval to add. Both positive and negative values are allowed. You can only select numeric data fields.

    Note: To extract, add the minus sign (-). For example, -5.
    field

    The field that contains the value. You can only select date data fields.

    For example, this function:

    returns:

  • Date Diff calculates and returns the duration between two selected date fields.

    Format: Number

    Parameter values:

    Parameter Description
    interval

    The type of interval to use. Can be one of the following values:

    • Year

    • Quarter

    • Month

    • Day of Year

    • Day Week

    • Week Day

    • Hour

    • Minute

    • Second

    • Millisecond
    field1, field2

    Two fields that contain the dates. You can only select date data fields.

    For example, this function:

    returns:

  • Day returns a number that represents the day of the specified date.

    Format: Number

    Parameter values:

    Parameter Description
    field

    The field that contains the value. You can only select date data fields.

    For example, this function:

    returns:

  • Get Date returns the current database system date.

    Note: This date is derived from the operating system of the computer on which the instance of SQL Server is running.

    Format: Date/Time

    Parameter values:

    No parameters.

    For example, this function:

    returns:

  • If compares a value in the selected field.

    Note: If your equation contains text, use single quotes.

    Format: Number

    Parameter values:

    Parameter Description
    condition The field to test.
    true The value to return if condition is TRUE. You can only select numeric data fields.
    false The value to return if condition is FALSE. You can only select numeric data fields.

    For example, this function:

    returns:

  • Is Null returns the specified value IF the expression is NULL, otherwise returns the expression.

    Format: Dependent upon the selected parameters.

    Parameter values:

    Parameter Description
    expression The expression to test whether is NULL.
    replacement The value to return if expression is NULL.

    For example, this function:

    returns:

  • Length returns the length of the string in the selected field as an integer value.

    Format: Number

    Parameter values:

    Parameter Description
    field The field that contains the value.

    For example, this function:

    returns:

  • Lower converts a field to lower-case.

    Format: String

    Parameter values:

    Parameter Description
    field

    The field that contains the value. You can only select non-numeric data fields.

    For example, this function:

    returns:

  • Max returns the highest value within the selected field.

    Format: Number

    Parameter values:

    Parameter Description
    field The field that contains the value. You can only select numeric and date data fields.

    For example, this function:

    returns:

  • Min returns the lowest value within the selected field.

    Format: Number

    Parameter values:

    Parameter Description
    field The field that contains the value. You can only select numeric and date data fields.

    For example, this function:

    returns:

  • Month returns the month part for a given date (a number from 1 to 12).

    Format: Number

    Parameter values:

    Parameter Description
    field The field that contains the value. You can only select date data fields.

    for example, this function:

    returns:

  • Patindex returns the position of a pattern in a string. If the pattern is not found, 0 is returned.

    Note: The search is case-insensitive and the first position in the string is 1.

    Format: Number

    Parameter values:

    Parameter Description
    pattern

    The pattern to find. It MUST be surrounded by single quotes and %. Other wildcards can be used in pattern, such as:

    • % - Match any string of any length (including 0 length)

    • _ - Match one single character

    • [] - Match any characters in the brackets, e.g. [xyz]

    • [^] - Match any character not in the brackets, e.g. [^xyz]

    You can only select non-numeric data fields.

    field The field to be searched. You can only select non-numeric data fields.

    For example, these functions:

    1. Match any string of any length

    2. Match any characters in the brackets

    return:

    1. Match '!m'

    2. Match 'c' or 'a'

  • Replace replaces one text string with another in the selected field.

    Format: String

    Parameter values:

    Parameter Description
    field The field that contains the value. You can only select non-numeric data fields.
    old The string to be replaced. You can only select non-numeric data fields.
    new The replacement string. You can only select non-numeric data fields.

    For example, this function:

    returns:

  • Substring returns the strings between the start index and end index in the selected field.

    Format: String

    Parameter values:

    Parameter Description
    field The field that contains the value. You can only select non-numeric data fields.
    start The start position. The first position in the string is 1. You can only select numeric data fields.
    length

    The number of characters to extract. You can only select numeric data fields.

    Note: Must be a positive number.

    For example, this function:

    returns:

  • Upper converts a string to upper-case.

    Format: String

    Parameter values:

    Parameter Description
    field The field that contains the value. You can only select non-numeric data fields.

    For example, this function:

    returns:

  • Year returns the year part as an integer for the specified date.

    Format: Number

    Parameter values:

    Parameter Description
    field The field that contains the value. You can only select date data fields.

    For example, this function:

    returns: