Bind Pivot Grid Fields to Calculated Expressions

• Aug 10, 2023
• 3 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

Important

You cannot bind the Pivot Grid to data at design time in .NET 5+ projects.

Optimized mode supports ExpressionDataBinding.

Follow the steps below to create a calculated field:

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

Run Demo

CalculatedFields.xaml

<dxpg:PivotGridControl.Fields>
    <dxpg:PivotGridField x:Name="fieldOrderCountBonus"
                            DataBinding="{dxpg:ExpressionDataBinding 'Iif(Count() &gt; 50 and Sum([fieldExtendedPrice]) / Count() &gt; 500, 
                                15.0 * Sum([fieldExtendedPrice]) / Sum([fieldQuantity]) , 0)'}"
                            Area="DataArea"
                            AreaIndex="4"
                            AllowedAreas="DataArea"
                            Caption="Order Count Bonus"
                            CellFormat="c2"
                            ValueTemplate="{StaticResource ResourceKey=UnboundFieldTemplate}" />
</dxpg:PivotGridControl.Fields>

Use Custom Functions in Expression

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

Refer to the following article for more information about custom functions: Custom Aggregate Functions.

The example below shows how create a Count Distinct field and bind it to an expression that contains a custom function. The expression counts distinct values (the number of orders with equal product quantities).

View Example: How to Create a Custom Summary to Display the Distinct Value Count

MainView.xaml

<dx:ThemedWindow x:Class="DXPivotGrid_CustomSummary.MainWindow" mc:Ignorable="d" Title="MainWindow" Height="450" Width="800" 
    xmlns:dx="http://schemas.devexpress.com/winfx/2008/xaml/core" 
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" 
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008" 
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 
    xmlns:local="clr-namespace:DXPivotGrid_CustomSummary" 
    xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid">
    <Grid>

        <dxpg:PivotGridControl Loaded="PivotGridControl1_Loaded" Name="pivotGridControl1" 
            DataProcessingEngine="Optimized">
            <dxpg:PivotGridControl.Fields>
            <!-- ... -->
                <dxpg:PivotGridField Name="fieldQuantityDistinctCount"  Area="DataArea" AreaIndex="1" 
                     Caption="Count Distinct">
                    <dxpg:PivotGridField.DataBinding>
                        <dxpg:ExpressionDataBinding Expression="DistinctCount([OrderID])"/>
                    </dxpg:PivotGridField.DataBinding>
                </dxpg:PivotGridField>
            </dxpg:PivotGridControl.Fields>
        </dxpg:PivotGridControl>

    </Grid>
</dx:ThemedWindow>

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.