Bind Pivot Grid Fields to Calculated Expressions

• Aug 05, 2022
• 5 minutes to read

This topic describes how to use the Binding API to create calculated fields when the Pivot Grid uses the Optimized data processing engine.

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.

   The following expression is used as an example:

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

   

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

As a result, 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

Optimized mode supports ExpressionDataBinding.

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

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

The following example shows how to use ExpressionDataBinding to display sales by country for different years:

Run Demo: Filter Sales by Year and Country

C#

pivotGridControl.OptionsData.DataProcessingEngine = PivotDataProcessingEngine.Optimized;
PivotGridField field = new PivotGridField {
    Name = "SalesByYearByCountry",
    Area = PivotArea.FilterArea,
    DataBinding = new ExpressionDataBinding("Aggr(Sum([ExtendedPrice]), GetYear([OrderDate]), [Country])")
};
pivotGridControl.Fields.Add(field);

VB

pivotGridControl.OptionsData.DataProcessingEngine = PivotDataProcessingEngine.Optimized
Dim field As PivotGridField = New PivotGridField With {
    .Name = "SalesByYearByCountry",
    .Area = PivotArea.FilterArea,
    .DataBinding = New ExpressionDataBinding("Aggr(Sum([ExtendedPrice]), GetYear([OrderDate]), [Country])")
}
pivotGridControl.Fields.Add(field)

Use Custom Functions in Expression

You can create functions with custom logic to build an expression that executes complex calculations for Pivot Grid’s field.

Refer to the following articles for more information about custom functions:

• Custom Functions
• Custom Aggregate Functions

The following example specifies a custom summary for the First Product Sold field. The custom summary’s expression (FirstValue([ProductName])) uses a custom aggregate function (FirstValue) to return the first product sold by a sales person in each product category.

View Example

Form1.cs

using DevExpress.XtraPivotGrid;
using System.Windows.Forms;

namespace WinPivot_CustomFunctions {
    public partial class Form1 : Form {
        public Form1() {
            InitializeComponent();     
            //  ...
            pivotGridControl1.OptionsData.DataProcessingEngine = PivotDataProcessingEngine.Optimized;
            PivotGridField pivotGridField1 = new PivotGridField() {
                Area = PivotArea.DataArea,
                AreaIndex = 0,
                Caption = "First Product Sold",
                FieldName = "FirstProductSold"
            };
            pivotGridControl1.Fields.Add(pivotGridField1);
            pivotGridField1.DataBinding = new ExpressionDataBinding() { 
                Expression = "FirstValue([ProductName])" };
            pivotGridField1.Options.ShowExpressionEditorMenu = true;
            pivotGridField1.Options.ShowGrandTotal = false; 
        }      
    }
}

Form1.vb

Imports System.Windows.Forms
Imports DevExpress.XtraPivotGrid

Namespace WinPivot_CustomFunctions
    Partial Public Class Form1
        Inherits Form
        Public Sub New()
            InitializeComponent()
            '  ...
            pivotGridControl1.OptionsData.DataProcessingEngine = PivotDataProcessingEngine.Optimized
            Dim pivotGridField1 As New PivotGridField() With {
                .Area = PivotArea.DataArea,
                .AreaIndex = 0,
                .Caption = "First Product Sold",
                .FieldName = "FirstProductSold"
            }
            pivotGridControl1.Fields.Add(pivotGridField1)
            pivotGridField1.DataBinding = New ExpressionDataBinding() With {.Expression = "FirstValue([ProductName])"}
            pivotGridField1.Options.ShowExpressionEditorMenu = True
            pivotGridField1.Options.ShowGrandTotal = False
        End Sub
    End Class
End Namespace

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.