Understanding Formulas (Report Builder)
Formulas allow you to combine, aggregate, filter, and evaluate both numeric and text data. You can create formulas and save them as new fields; or, you can modify the formulas of existing fields.
All formulas are defined within the context of an entity. This means that the formula returns a single value for each item within the entity. For example, suppose you create a formula within the Customer entity. The formula returns a single value for each Customer. When you create your own fields with formulas, you will need to create them in the context of an entity as well.
A formula can contain any or all of the following: references, functions, operators, and constants.
References
A reference is a field name. This can be an existing field name within the entity, or a calculated field name that you have created and added to the Fields list. The reference tells Report Builder where to look for the values, or data, you want to use within a formula. You can refer to fields within your context entity and to fields in other entities within one formula or use the value from one field in several formulas.
When you use references, Report Builder runs the formula against each value within the field. For example, suppose a field contains the yearly sales total for the past five years. This field contains five values, each representing the sales total for a given year. If your formula contains a reference to this field, the formula calculates the new value using each individual value.
Functions
Functions are predefined formulas that perform calculations by using specific values, called arguments, specified in a particular order. For examples of how supported functions can be used in Report Builder, see Using Functions (Report Builder).
Arguments can be literal values or fields, or combinations of both. When fields are used in formulas, the field name represents each instance of the field. If the argument is a literal value, you may need to indicate that the argument is a literal value using specific characters. For more information about using literals in Report Builder, see Working with Literals in Formulas and Filters (Report Builder).
Operators
Operators specify the type of calculation that you want to perform on the values of a formula. Report Builder includes three different types of calculation operators: arithmetic, comparison, and text. Operators are indicated using symbols, such as the plus sign (+), in Report Builder.
Arithmetic Operators
Arithmetic operators perform basic mathematical operations such as addition, subtraction or multiplication, combine numbers, and produce numeric results. For more information about Arithmetic operators, see Operator Functions (Report Builder).
Comparison Operators
You can compare two values using Comparison operators. When two values are compared by using these operators, the result is a logical value, either TRUE or FALSE. For more information about Comparison operators, see Operator Functions (Report Builder).
Text Concatenation Operator
Use the ampersand (&) to join, or concatenate, one or more text strings to produce a single piece of text. For more information about the Text Concatenation operator, see Text Functions (Report Builder).
Constants
A constant is a value that is not calculated and, therefore, does not change. Report Builder uses the following constants: True, False, and Empty. These constants are used to evaluate Boolean fields. For example, suppose you have a field called IsDiscontinued. The only valid values for this field are True, False, or Empty (" ").
See Also
Concepts
Working with Formulas (Report Builder)
Creating a New Field (Report Builder)
Using Functions (Report Builder)
Working with Literals in Formulas and Filters (Report Builder)