Bind Pivot Grid Fields to Calculated Expressions

• Aug 05, 2022
• 4 minutes to read

This topic describes how to use the Binding API to create calculated fields.

Calculated fields display the result of calculated expressions. Each calculated field has a binding expression that can be a formula or an aggregate function. The expression allows you to not only obtain values from a field in the data source, but specify exactly how to calculate the data (for example, aggregate it).

Run Demo: Calculated Fields

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

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

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

[Unit Price] * [Quantity]

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

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

Create a Calculated Field in Visual Studio Designer

Follow the steps below to create a calculated field in the Pivot Grid:

1. Add a new data field in any of the following ways:

   • Click ‘Run Designer’ in the PivotGrid’s smart tag menu. In the Pivot Grid Designer window, select the Fields page and click or to add a new field.

   • Use one of the ‘Add Field to…’ options in the PivotGrid’s smart tag menu to add a new field to the required area directly. Then, you can specify its properties in the property grid or in the field’s smart tag menu.

2. Set the field’s PivotGridFieldBase.DataBinding property to Expression.

3. Specify the expression. Click the Expression property’s ellipsis button and enter the expression in the invoked Expression Editor.

   

   For information about expression operators and functions, refer to the following article: Pivot Grid Expression Syntax.

The following example shows how to create a calculated field that checks if the cell’s value is not a total value and belongs to the Beverages category.

The example uses the following expression:

Not IsTotal([fieldProductName]) And FirstValue([fieldCategoryName]) = 'Beverages'

The following image shows the field settings:

The Is Beverages field displays true if the cell value from the Extended Price column belongs to the Beverages category and is not the total value; otherwise, false.

Create a Calculated Field in Code

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 PivotGridFieldBase.DataBinding property.

The following code snippet shows how to bind fieldSalesPerson to the expression:

C#

PivotGridFieldBase fieldSalesPerson = pivotGridControl1.Fields.Add();
fieldSalesPerson.DataBinding = new ExpressionDataBinding(string.Format(
    "Concat([{0}], ' ', [{1}], ' (', [{2}], ')')", fieldFirstName.Name, fieldLastName.Name, fieldEmployeeID.Name));

VB

Dim fieldSalesPerson As PivotGridFieldBase = pivotGridControl1.Fields.Add()
fieldSalesPerson.DataBinding = New ExpressionDataBinding(String.Format(
    "Concat([{0}], ' ', [{1}], ' (', [{2}], ')')", fieldFirstName.Name, fieldLastName.Name, fieldEmployeeID.Name))

Run Demo: Calculated Fields

Enable Users to Edit Expressions

You can allow users to edit or create new custom expressions in the Expression Editor at runtime. To accomplish this, set the PivotGridFieldOptionsEx.ShowExpressionEditorMenu property to true to add the Expression Editor command to the field’s context menu or call the ShowExpressionEditor method to invoke the Expression Editor.

Note

You cannot use the Expression Editor dialog in OLAP mode.

The following code snippet shows how to invoke the Expression editor for pivotGridField2 on a mouse click:

C#

private void pivotGridControl1_MouseClick(object sender, MouseEventArgs e) {
        PivotGridHitInfo info = pivotGridControl1.CalcHitInfo(new Point(e.X, e.Y));
        if (info != null && info.HeaderField == pivotGridField2) {
            pivotGridControl1.ShowExpressionEditor(pivotGridField2);
        }
    }

VB

Private Sub pivotGridControl1_MouseClick(ByVal sender As Object, ByVal e As MouseEventArgs)
        Dim info As PivotGridHitInfo = pivotGridControl1.CalcHitInfo(New Point(e.X, e.Y))
        If info IsNot Nothing AndAlso info.HeaderField = pivotGridField2 Then
            pivotGridControl1.ShowExpressionEditor(pivotGridField2)
        End If
End Sub

Use the PivotGridOptionsBehavior.ExpressionEditorMode property to specify the Expression Editor’s version.