Export (0) Print
Expand All

Define Formula Dialog Box (Model Designer)

Use to create a new field that contains a formula. For examples of valid formulas, see Using Functions (Report Builder 1.0).

Fields

Use this tab to locate fields within the report model that you want to include in the expression. Double-click the field to add it to the expression.

Functions

Use this tab to select the function that you want to use within your expression. Double-click the function to add it to the expression. For a description of each function, see the following section.

Field name

Enter a name for the expression that you are creating.

Formula

Use to write and edit the formula.

Search

Click to launch the Search dialog box.

Aggregate to Here

If you double-click a field name displayed in the Formula 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.

No filter applied

If you double-click a field name displayed in the Formula box, the relationship between the primary entity and the field's entity are displayed. You can apply a filter to the expression anywhere along the model path. Click to apply a filter to the expression and then click Create a new filter. The Filter Data dialog box opens.

Function Shortcuts

Click to add an operator to the formula displayed in the Formula 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.

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)

DAYOFYEAR

Returns the day of the year of the datetime. January 1st = 1 through December 31st = 366, assuming a leap year.

Syntax

DAYOFYEAR(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.

+ 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

Comparison Operators

To compare two values and return a logical value of either TRUE or FALSE, use the following operators.

= 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 within your report, 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()

Community Additions

ADD
Show:
© 2014 Microsoft