Calculated fields allow you to pre-process a report's input data, based on a certain expression. So, using calculated fields allows you to apply complex expressions to one or more data fields that are obtained from your report's underlying data source. Moreover, you can both group and sort your report data based on a calculated field's value.
To create a calculated field, switch to the Field List, right-click any item inside the data source and select Add Calculated Field.
Right-click the calculated field in the Field List and select Edit Expression. Alternatively, you can select the calculated field, and in the Property Grid, click the Expression property's ellipsis button.
In the invoked Expression Editor, construct the required expression. You can use data fields, report parameters, predefined constants as well as various date-time, logical, math and string functions. See the next document section for more information about expression syntax.
Note
The Expression Editor displays only those data fields that are obtained from a data source specified by the calculated field's Data Source and Data Member property values.
The report's Calculated Fields property provides access to the calculated field collection.
You can click the Add Calculated Field button in the Toolbar's Home tab to invoke the Calculated Field Collection Editor.
You can drag the calculated field from the Field List onto the required band like an ordinary data field.
You can also group and sort your report data based on the calculated field values.
A data field is inserted into the expression's text using its name in [square brackets], and parameters are inserted using a question mark before their names.
A calculated field's expression can evaluate the values of other calculated fields if you make sure to avoid circular references.
Date-time constants must be wrapped in hashtags (#) (e.g., [OrderDate] >= #1/1/2009#). To represent a null reference (one that does not refer to any object), use a question mark (e.g., [Region] != ?). To denote strings, use apostrophes ('), otherwise an error will occur.
To embed an apostrophe into an expression's text, it should be preceded by another apostrophe (e.g., 'It''s sample text').
The type of a value returned by a calculated field is defined by its Field Type property.
If a calculated field expression involves the use of different types, it is necessary to convert them to the same type (e.g., Max(ToDecimal([Quantity]),[UnitPrice]))
Although a value that is returned by a calculated field is usually converted to a string (to be displayed in a text-aware report control), it can return a value of any kind. For example, if a database field contains an image, you can set a calculated field's expression to "=...", after which this calculated field can be bound to the Picture Box control.
To construct a valid aggregate expression, use the following format, which consists of four parts.
[<Collection>][<Condition>].<Aggregate>(<Expression>)
•<Collection> - Specifies a collection against which an aggregated value should be calculated. It can be the relationship name in a case of a master-detail relationship, or the name of a collection property exposed by the target class. For example, [CategoriesProducts][[CategoryId]>5].Count(). Empty brackets [] indicate the root collection.
•<Condition> - Specifies a condition defining which records should participate in calculating an aggregate function. To obtain an aggregated value against all records, delete this logical clause along with square brackets (for example, [].Count()).
•<Aggregate> - Specifies one of the available aggregate functions.
•<Expression> - Specifies an expression evaluating values to be used to perform calculation. For example, [][[CategoryID] > 5].Sum([UnitPrice]*[Quantity]). The Count function does not require field values to count the records, so leave the round brackets empty for this function.
You can refer to the currently processed group using the Parent Relationship Traversal Operator ('^'). This allows you to calculate aggregates within groups using expressions like the following: [][[^.CategoryID] == [CategoryID]].Sum([UnitPrice]).
For more information, see Expression Language.
The following tutorials demonstrate the use of calculated fields in various environments:
•Calculate an Aggregate Function
•Calculate a Weighted Average Function