Bind Pivot Grid Fields to Calculated Expressions

• Aug 10, 2023
• 2 minutes to read

Pivot Grid uses the Binding API to bind the Pivot Grid’s fields to data. Data binding sources can be columns in a data source, calculated expressions, or window calculations.

Calculated fields display the result of calculated expressions. Each calculated field has a binding expression that can be a formula or aggregate function. In addition to the ability to retrieve values from a field in the data source, the expression allows you to specify how data is calculated (for example, aggregate it).

Expressions are computed at the data source level. This means that if you specify a field in the expression, Pivot Grid uses the field values from the data source. Use the PivotGridField.Name property to specify the field.

If you want to use aggregated field values, wrap a field in the corresponding aggregate function. The PivotGridField.SummaryType and PivotGridField.AllowRuntimeSummaryChange properties are not in effect for the field that is bound to such expression.

For example, the following expression is calculated based on underlying values of the Unit Price and Quantity fields:

[Unit Price] * [Quantity]

If you want to calculate the average sales, use the following expression:

Avg([Unit Price]) * Avg([Quantity])

Create a Calculated Field

Server mode supports ExpressionDataBinding.

Follow the steps below to create a calculated field in Server mode:

1. Create an ExpressionDataBinding descendant and pass the expression in its constructor as a parameter.
2. Assign the created object to the PivotGridField.DataBinding property.

The following code snippet shows how to bind Revenue to the expression in the markup:

ServerMode.xaml

<dxpg:PivotGridControl.Fields>
    <dxpg:PivotGridField Area="DataArea"
        DataBinding="{dxpg:ExpressionDataBinding [UnitPrice]*[Quantity]}"
        Caption="Revenue"
        CellFormat="c"
        DisplayFolder="Order" />
</dxpg:PivotGridControl.Fields>

Run Demo: Server Mode

Enable Users to Edit Expressions

You can allow users to employ the Expression Editor to change specific calculated expressions. To do this, set the PivotGridField.AllowExpressionEditor property to true. The Expression Editor… command appears in this field’s context menu as a result.

You can also call the PivotGridControl.ShowExpressionEditor method to invoke the Expression Editor.