Custom Summaries

Oct 31, 2022
3 minutes to read

This topic describes how to create custom summaries.

Custom Summaries Overview

Pivot Grid allows you to calculate common summary functions for data fields. You can create custom summaries to execute complex calculations.

Custom summaries can do the following:

Use predefined and custom aggregate functions to calculate summary values.
Involve multiple fields in a summary calculation.
Calculate a summary for records that match certain criteria.

Create a Custom Summary in Optimized and Server Modes

Do the following to create a custom summary for a data field:

Create a Pivot Grid field.
Create an ExpressionDataBinding object, and specify a calculated summary expression in the object’s constructor.
Assign the created ExpressionDataBinding object to the field’s DataBinding property.
Add the field to the Data Area.

Refer to the following article for information on how to create calculated expressions: Calculated Fields.

Tip

If a custom summary calculation requires data from multiple data sources, bind the Pivot Grid to a FederationDataSource that combines data from two or more tables into one data set. The custom summary expression can then use fields defined in the FederationDataSource.

Example

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
Form1.vb

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; 
        }      
    }
}

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

Create a Custom Summary in Legacy and LegacyOptimized Modes

Use the PivotGridControl.CustomSummary event to create custom summaries in Legacy or LegacyOptimized mode.

Custom Summaries

Custom Summaries