Define Formula Dialog Box (Report Builder 3.0)

Use the Define Formula dialog box to create a formula or a new field that contains a formula.

The dialog box has Fields and Functions tabs.

  • Use the Fields tab to locate fields within the report model that you want to include in the formula.

  • Use the Functions tab to select the functions that you want to use within your formula.

Options on the Fields Tab

  • Entities
    Lists the entities. Click an entity to display its fields in the Fields list.

  • Fields
    Lists the fields in the entities. You can drag a field to the Formula text box or double-click the field to add it to the Formula text box.

  • New Field
    Click to add a new field. The value New <entity name> Field appears in Field name.

  • Field name
    Enter a name for the new field that you are creating.

  • Formula
    Use to write and edit the formula.

  • Search
    Click to launch the Search dialog box.

  • Advanced Mode
    Click to show additional relationships between the entities. These relationships allow you to build complex queries. When you click the Advanced Mode button, three additional types of roles are displayed:

    • Reverse roles. Reverse roles show the relationship back to the entity from which you just came.

    • Lookup entities. A lookup entity is a role that contains only one field of interest.

    • Entities that inherit from the direct ancestors of the selected entity but are not a direct ancestor of the current entity.

  • Aggregate to Here
    If you double-click a field name displayed in the Formula text box, the relationship between the primary entity and the field's entity are displayed. Click to apply an aggregate to the expression anywhere along the model path.

  • Function Shortcuts
    Click to add an operator to the formula displayed in the Formula text box. The Define Formula dialog box provides you with shortcuts to the most commonly used functions; however, you can select these operators from the Functions tab as well. The following is an explanation of each shortcut.

    + Use to add two values together.

    - Use to subtract one value from another.

    * Use to multiply two values.

    / Use to divide two values.

    & Use to concatenate two values.

    ( Use to start a calculation.

    ) Use to end a calculation.

  • Expanded formula
    If you double-click a field name displayed in the Formula text box, the relationship between the primary entity and the field's entity is displayed. You can apply a filter to the expression anywhere along the model path.

    • Click No filter applied and then click Create a new filter to create a new filter for the field.

    • If the field already has a filter, click the filter and then click Edit filter.

    • To remove and existing filter, click the filter and then click Remove.

  • Save this formula as a new <entity name> fields
    Select the checkbox to indicate you want the formula to be added as a field.

Options on the Functions Tab

The following information describes each of the functions found on the Functions tab of the Define Formula dialog box.

Aggregate

To summarize a range of numeric values, use the following functions.

  • SUM
    Returns the sum of all the values within the expression.

    Syntax

    SUM(aggregate)

    SUM can be used with fields that contain numeric values only. Null values are ignored.

  • AVERAGE
    Returns the average (arithmetic mean) of all the non-null values within the expression.

    Syntax

    AVERAGE(aggregate)

    AVERAGE can be used with fields that contain numeric values only. Null values are ignored.

  • MAX
    Returns the maximum value in the expression.

    Syntax

    MAX(aggregate)

    For character columns, MAX finds the highest value in the collating sequence. Null values are ignored.

  • MIN
    Returns the minimum value in the expression.

    Syntax

    MIN(aggregate)

    For character columns, MIN finds the lowest value in the collating sequence. Null values are ignored.

  • COUNT
    Returns the number of non-null items within a group.

    Syntax

    COUNT(aggregate)

    COUNT always returns an Int data type value.

  • COUNTDISTINCT
    Returns the number of non-null distinct instances of an item within a group.

    Syntax

    COUNTDISTINCT(aggregate)

  • STDEV
    Returns the standard deviation of non-null values of an item.

    Syntax

    STDEV(aggregate)

  • STDEVP
    Returns the population standard deviation of non-null values of an item.

    Syntax

    STDEVP(aggregate)

  • VAR
    Returns the variance of non-null values of an item.

    Syntax

    VAR(aggregate)

  • VARP
    Returns the population variance of non-null values of an item.

    Syntax

    VARP(aggregate)

Conditional

To test a condition, use the following functions.

  • IF
    Returns one value if you specify a condition that evaluates to TRUE and another value if you specify a condition that evaluates to FALSE.

    Syntax

    IF(condition, value_if_true, value_if_false)

    The condition must be a value or expression that can be evaluated to TRUE or FALSE. Value_if_true represents the value returned if the condition is true. Value_if_false represents the value returned if the condition is false.

  • IN
    Determines whether an item is a member of a set.

    Syntax

    IN(item, set)

  • SWITCH
    Evaluates a list of expressions and returns a value of an expression associated with the first expression in the list that is True. Switch can have one or more conditions/value pairs.

    Syntax

    Switch(condition1, value1)

Conversion

To convert a value from one data type to another data type, use the following functions.

  • INT
    Converts a value to an integer.

    Syntax

    INT(value)

  • DECIMAL
    Converts a value to a decimal.

    Syntax

    DECIMAL(value)

  • FLOAT
    Converts a value to a float data type.

    Syntax

    FLOAT(value)

  • TEXT
    Converts a numeric value to text.

    Syntax

    TEXT(value)

Date and Time

To display the date or time, use the following functions.

  • DATE
    Returns a datetime value given a year, month, and day at 12:00:00 AM.

    Syntax

    DATE(year, month, day)

  • DATEONLY
    Returns the year, month, and day from a datetime value.

    Syntax

    DATEONLY(datetime)

  • DATETIME
    Returns a datetime given year, month, day, hour, minute, and second.

    Syntax

    DATETIME(year, month, day, hour, minute, second)

  • YEAR
    Returns a year value from a datetime.

    Syntax

    YEAR(datetime)

  • QUARTER
    Returns the calendar quarter (1-4) of the datetime.

    Syntax

    QUARTER(datetime)

  • MONTH
    Returns the month from a datetime.

    Syntax

    MONTH(datetime)

  • DAY
    Extracts the day from a datetime.

    Syntax

    DAY(datetime)

  • HOUR
    Extracts the hour from a datetime.

    Syntax

    HOUR(datetime)

  • MINUTE
    Extracts the minute from a datetime.

    Syntax

    MINUTE(datetime)

  • SECOND
    Extracts the second from a datetime.

    Syntax

    SECOND(datetime)

  • WEEK
    Returns the numeric value for the week within the calendar year.

    Syntax

    WEEK(datetime)

  • DAYOFWEEK
    Returns the day of the week, starting with Monday. Monday = 1 through Sunday = 7.

    Syntax

    DAYOFWEEK(datetime)

  • NOW
    Returns the current date and time.

    Syntax

    NOW( )

  • TODAY
    Returns the current date.

    Syntax

    TODAY( )

  • DATEDIFF
    Returns the difference between the Start datetime and the End datetime.

    Syntax

    DATEDIFF(interval, datetime, datetime)

  • DATEADD
    Returns a datetime that is the result of adding the specified number of time interval units to the original datetime.

    Syntax

    DATEADD(interval, units, datetime)

Logical

To test the logic of a condition, use the following functions.

  • AND
    Returns TRUE if all arguments are TRUE; returns FALSE if one or more arguments are FALSE.

    Syntax

    AND(logical, logical)

    The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays or references that contain logical values. If an array or reference argument contains text or empty cells, those values are ignored.

  • OR
    Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.

    Syntax

    OR(logical, logical)

    The arguments must evaluate to logical values such as TRUE or FALSE, or in arrays or references that contain logical values. If an array or reference contains text or empty cells, those values are ignored.

  • NOT
    Reverses the value of its argument. Use NOT when you want to make sure a clause is not equal to one particular value.

    Syntax

    NOT(logical)

    If the value is FALSE, NOT returns TRUE; if the value is TRUE, NOT returns FALSE.

Math

To manipulate numeric values, use the following functions.

  • MOD
    Returns the remainder after a number is divided by a divisor. The divisor cannot be 0.

    Syntax

    MOD(number, divisor)

  • TRUNC
    Truncates a number by the number of digits specified. If the number is positive, the number is truncated to the right of the decimal. If the number is negative, the number is truncated to the left of the decimal.

    Syntax

    TRUNC(number, digits)

  • ROUND
    Rounds a number to a specified number of digits.

    Syntax

    ROUND(number, digits)

    If the number of digits is greater than 0 (zero), then the number is rounded to the specified number of decimal places. If the number of digits is 0, then the number is rounded to the nearest integer. If the number is less than 0, then the number is rounded to the left of the decimal point.

Operators

Arithmetic Operators

To perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numerical results, use the following operators. Function Shortcuts contains the most commonly used arithmetic operators.

  • + Add
    Use to add two or more items together.

    Syntax

    value + value

  • - Subtract
    Use to deduct an item from another item.

    Syntax

    value- value

  • * Multiply
    Use to multiply items.

    Syntax

    value* value

  • / Divide
    Use to divide items. The divisor cannot be 0.

    Syntax

    value/divisor

  • - Negate
    Change the sign of the value.

    Syntax

    -value

  • ^ Exponentiation
    Use to raise a value to a power (exponentiation).

    Syntax

    value^power

  • = Equal to
    Use to equate two values. True when value1 equals value2.

    Syntax

    value1= value2

  • <> Not Equal to
    Use to indicate that two values do not equal each other. True when value1 does not equal value2.

    Syntax

    value1 <> value2

  • > Greater Than
    Use to indicate that one value is greater than another value. True when value1 is greater than value2.

    Syntax

    value1 > value2

  • >= Greater Than or Equal
    Use to indicate that one value is greater than or equals another value. True when value1 is greater than or equal to value2.

    Syntax

    value1 >= value2

  • < Less Than
    Use to indicate that one value is less than another value. True when value1 is less than value2.

    Syntax

    value1 < value2

  • <= Less Than or Equal
    Use to indicate that one value is less than or equals another value. True when value1 is less than or equal to value2.

    Syntax

    value1 <= value2

Text

To manipulate text, use the following functions.

  • CONCAT (&)
    Combines two strings together into one. The second string is appended to the first string.

    Syntax

    string & string

  • FIND
    Position of the first instance of a string.

    Syntax

    FIND(string, substring)

  • LEFT
    Returns the left most characters of a string. If you specify a value less than zero for the length argument within the function, the behavior is undefined.

    Syntax

    LEFT(string, length)

  • LENGTH
    Returns the number of characters in a string.

    Syntax

    LENGTH(string)

  • LOWER
    Converts a string from uppercase characters to lowercase.

    Syntax

    LOWER(string)

  • LTRIM
    Returns a string with the leading spaces removed.

    Syntax

    LTRIM(string)

  • REPLACE
    Returns a string with all the instances of a sub-string replaced by another sub-string.

    Syntax

    REPLACE(find, replace, string)

  • RIGHT
    Returns the right most characters of a string. If you specify a value less than zero for the length argument within the function, the behavior is undefined.

    Syntax

    RIGHT(string, length)

  • RTRIM
    Returns a string with the trailing spaces removed.

    Syntax

    RTRIM(string)

  • TEXT
    Converts a numeric value to a string.

    Syntax

    TEXT(value)

  • SUBSTRING
    Returns a sub-string from within a string. If you specify a value less than zero for the length argument within the function, the behavior is undefined.

    Syntax

    SUBSTRING(string, start, length)

  • UPPER
    Converts a string from lowercase characters to uppercase.

    Syntax

    UPPER(string)

Information

To return global information about users, use the following functions.

  • GETUSERID
    Returns the ID that the user used to access the data.

    Syntax

    GETUSERID()

  • GETUSERCULTURE
    Returns the user's language or locale.

    Syntax

    GETUSERCULTURE()