Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Expand]WinForms Controls
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Collapse]Reporting
 [Expand]Product Information
 [Collapse]Creating Reports in Visual Studio
  [Expand]Adding a Report to Your .NET Application
  [Expand]Getting Started with DevExpress Reporting
  [Collapse]Detailed Guide to DevExpress Reporting
   [Expand]Storing and Distributing Reports
   [Expand]Providing Data to Reports
   [Collapse]Shaping Report Data
    [Expand]Data Filtering
    [Expand]Grouping and Sorting
    [Expand]Shaping Data using Binding Expressions
    [Expand]Shaping Data using Legacy Data Bindings
    [Collapse]Using Calculated Fields
      Calculated Fields Overview
      Calculating an Aggregate Function
      Calculating a Weighted Average Function
      Creating a Calculated Field (Runtime Sample)
    [Expand]Using Report Parameters
   [Expand]Report Navigation, Interactivity and Appearance
   [Expand]Reporting API
  [Expand]Detailed Guide to Visual Studio Report Designer
 [Expand]Creating End-User Reporting Applications
 [Expand]API Reference
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Calculated Fields Overview

This document describes how calculated fields can be used in your report. Calculated fields are primarily used in data-aware reports when using both standard data binding and mail merge. 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.

This document consists of the following sections.

Expanded Calculated Fields Overview

Calculated fields are represented by instances of the CalculatedField class residing in the CalculatedFieldCollection. Their collection is accessed via a report's XtraReport.CalculatedFields property.

At design time, they are easily managed via the Field List. For details, refer to Using Calculated Fields.

The value of a calculated field is obtained by evaluating its expression, which is specified by its CalculatedField.Expression property's text. The Expression Editor can be used to visually construct a calculated field's expression. In the Field List, it is invoked by right-clicking a calculated field and choosing the Edit Expression... item in the invoked popup menu.

Construct the required expression in the invoked Expression Editor.

This expression can be built on data fields (which are obtained from the data table defined by the CalculatedField.DataSource and CalculatedField.DataMember property values), report parameters, and different date-time, logical, math and string functions surrounding them.

Finally, you can group and sort your report data based on the calculated field values. For an example of this task, refer to Grouping Data by Days of the Week.

For tasks that for some reason cannot be solved using calculated fields, the XRControl.EvaluateBinding event was introduced, which allows you to perform custom calculations over data obtained from a control's data source.

Expanded Expression Syntax

A data field is inserted into the expression's text using its name in [square brackets], and parameters are inserted using the "Parameters." prefix before their names.

A calculated field's expression can evaluate the values of other calculated fields if you make sure to avoid circular references.

Note

When creating calculated fields, avoid dots in their names, because XtraReports uses them to address data source members.

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 CalculatedField.FieldType property. All available types are listed in the FieldType enumeration.

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 (if the CalculatedField.FieldType property is set to None). 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 XRPictureBox 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 (e.g., [].Count()).

  • <Aggregate> - Specifies one of the available aggregate functions listed in the Aggregate enumeration.

  • <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 Criteria Language Syntax and Expression Operators, Functions, and Constants.

Expanded Advanced Approach to Calculated Fields

For tasks that cannot be solved using the standard set of functions provided by the Expression Editor, you can evaluate your own functions in the CalculatedField.GetValue event handler (or in its scripting counterpart).

See Grouping Data by Days of the Week (Runtime Sample) for a code example.

Expanded Examples

Expanded See Also

How would you rate this topic?​​​​​​​