# Define Formula Dialog Box (Report Builder 1.0)

Use to create a new field that contains a formula.

## Options

**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.**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 text values.

/ Use to divide two values.

& Use to concatenate two values.

( Use to start a calculation.

) Use to end a calculation.

## Right-Click Options

To see these menu options, right-click formulas in the Formula box or in the expanded formula area.

**Expand**

Click to display the formula and its relationship to the context entity. You need to do this when you want to filter or indicate the aggregation level.**Rename**

Click to rename the formula displayed in the Formula dialog box.**Aggregate to Here**

Click the field name to change the aggregation level. This option is available only after you expand the formula.**No filter applied**

If you double-click a field name displayed in the Formula box, the relationship between the entity on which the formula is defined and the field itself is 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.

## Functions

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 non-null values in a set of values.**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 of all non-null values in a set of values.**Syntax**AVERAGE(aggregate)

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

**MAX**

Returns the maximum of all non-null values in a set of values.**Syntax**MAX(aggregate)

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

**MIN**

Returns the minimum of all non-null values in a set of values.**Syntax**MIN(aggregate)

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

**COUNT**

Returns the number of all non-null values in a set of values.**Syntax**COUNT(aggregate)

COUNT always returns an Int data type value.

**COUNTDISTINCT**

Returns the number of all non-null values in a set of values.**Syntax**COUNTDISTINCT(aggregate)

**STDev**

Returns the standard deviation of of all non-null values in a set of values.**Syntax**STDEV(aggregate)

**STDevP**

Returns the population standard deviation of all non-null values in a set of values.**Syntax**STDEVP(aggregate)

**VAR**

Returns the variance of all non-null values in a set of values.**Syntax**VAR(aggregate)

**VARP**

Returns the population variance of all non-null values in a set of values.**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 evaluate 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 a value is present in a set of values.**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 date/time value representing the specified year, month, and day at 12:00:00 AM.**Syntax**DATE(year, month, day)

**DATEONLY**

Returns a date/time value representing the specified year, month, and day.**Syntax**DATEONLY(datetime)

**DATETIME**

Returns a date/time value representing the specified 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 (1-12) from a datetime.**Syntax**MONTH(datetime)

**DAY**

Extracts the day from a datetime.**Syntax**DAY(datetime)

**HOUR**

Extracts the hour (0-23) 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.**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.**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.**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()