- 3 minutes to read
A calculated field is a field that calculates its value with an expression that may include existing database fields. A calculated field allows you to create new data that is not stored in the database, or select a value from the database fields based on certain criteria.
To add a calculated field in the Report Designer, invoke the Field List window, right-click any field, and select Add Calculated Field:
A new calculated field is created with the calculatedField1 default name.
You can select Edit Calculated Fields in the context menu to invoke the Calculated Field Collection Editor:
The field type can be set explicitly. The specified field type determines how a calculated field is processed in expressions. The field type affects the field drag-and-drop behavior in the Field List dialog.
The data source and data member settings are not automatically updated when you change the report data source. You should change them manually. For this, in the report’s Properties window, click the ellipsis next to the Calculated Fields property to invoke the Calculated Field Collection Editor and specify the data source and data member settings.
A calculated field evaluates its expression to calculate its value. Use the CalculatedField.Expression property to specify the field’s expression. To specify an expression in the Report Designer, invoke the Field List window, right-click the required field, and select Edit Expression to invoke the Expression Editor, which allows you to visually construct an expression:
In an expression, you can use data fields, report parameters, predefined constants, and various date-time, logical, math, and string functions. The Expression Editor displays only data fields from a data source specified by the CalculatedField.DataSource and CalculatedField.DataMember property values.
You can group and sort data in the report based on the calculated field values. Review the following topic, which contains an example of how to use a calculated field to group data: Group Data by Days of the Week.
If you cannot use calculated fields to accomplish your task, use the XRControl.EvaluateBinding event.
A data field is defined by its name in square brackets:
A parameter has a question mark prefix (“?“) before its name:
The expression of a calculated field can include other calculated fields if you avoid cyclic references.
Date-time constants must be wrapped in hash symbols (#):
[OrderDate] >= #1/1/2020#
Use a question mark to specify a null reference that does not refer to any object:
[Region] != ?
Use apostrophes (‘) to denote strings. To insert an apostrophe in the text of an expression, precede it with another apostrophe:
'It''s sample text'
The type of a calculated field’s value is defined by the CalculatedField.FieldType property. If a calculated field expression involves different value types, use a function to convert them to the same type:
For more information on expression syntax, review the following help topic: Expression Language.
Calculated Field Evaluation
Review the following help topic for the code sample: Group Data by Days of the Week (Runtime Sample).
The following help topics describe how to use calculated fields to complete various tasks:
- How to Use an Aggregate Function in Calculated Fields
- Sort Data by a Custom Field
- Group Data by a Custom Field
- Group Data by Days of the Week