Pivot Grid Custom Aggregate Functions

• Aug 24, 2023
• 6 minutes to read

You can use predefined and custom aggregate functions to create aggregated expressions. Assign an aggregated expression to a field in the Data Area to calculate summaries based on this expression. The Pivot Grid supports custom aggregate functions in Optimized mode only.

Create a Custom Aggregate Function

Review the common and platform-specific sections for information on how to implement a custom aggregate function.

Common Steps

The following steps are required for all platforms:

1. Define a class that implements the ICustomAggregateFunction interface to create an aggregate. The aggregate accepts a collection of values, uses an expression or several expressions to evaluate the values, and returns the result.

2. Implement the ICustomFunctionOperatorBrowsable interface to validate a custom function and supply additional information (a function’s category, function’s description, parameter count, and so on) on the custom function for the Expression Editor.

3. Define a class that implements the ICustomAggregateFunctionContext<TInput, TOutput> interface. The class implements the logic of the custom function.

The following code creates the custom FirstValue(fieldName) aggregate function. This function returns the first value of the specified target field.

FirstValueFunction.cs

using DevExpress.Data.Filtering;
using DevExpress.DataProcessing.Criteria;
using System;
using System.Linq;

namespace FirstValueAggregate {
    class FirstValueAggregateFunction : ICustomAggregateFunction, ICustomFunctionOperatorBrowsable {
        public string Name => "FirstValue";

        public int MinOperandCount => 1;

        public int MaxOperandCount => 1;

        public string Description => @"Displays the first value of the field";

        public FunctionCategory Category => DevExpress.Data.Filtering.FunctionCategory.Text;

        public object Evaluate(params object[] operands) {
            throw new NotImplementedException();
        }
        public Type GetAggregationContextType(Type inputType) {
            return typeof(FirstValueAggregateState<>).MakeGenericType(inputType);
        }
        public bool IsValidOperandCount(int count) {
            return count <= MaxOperandCount && count >= MinOperandCount;
        }
        public bool IsValidOperandType(int operandIndex, int operandCount, Type type) {
            return IsValidOperandCount(operandCount) && operandIndex == 0;
        }
        public Type ResultType(params Type[] operands) {
            return operands.FirstOrDefault();
        }
    }

    class FirstValueAggregateState<TInput> : ICustomAggregateFunctionContext<TInput, TInput> {
        bool isSet = false;
        TInput firstValue;
        public TInput GetResult() {
            return isSet ? firstValue : default(TInput);
        }
        public void Process(TInput value) {
            if(!isSet) {
                firstValue = value;
                isSet = true;
            }
        }
    }
}

FirstValueFunction.vb

Imports DevExpress.Data.Filtering
Imports DevExpress.DataProcessing.Criteria
Imports System
Imports System.Linq

Namespace FirstValueAggregate
    Friend Class FirstValueAggregateFunction
        Implements ICustomAggregateFunction, ICustomFunctionOperatorBrowsable
        Public ReadOnly Property Name As String Implements ICustomFunctionOperator.Name
            Get
                Return "FirstValue"
            End Get
        End Property
        Public ReadOnly Property MinOperandCount As Integer Implements ICustomFunctionOperatorBrowsable.MinOperandCount
            Get
                Return 1
            End Get
        End Property
        Public ReadOnly Property MaxOperandCount As Integer Implements ICustomFunctionOperatorBrowsable.MaxOperandCount
            Get
                Return 1
            End Get
        End Property
        Public ReadOnly Property Description As String Implements ICustomFunctionOperatorBrowsable.Description
            Get
                Return "Displays the first value of the field"
            End Get
        End Property
        Public ReadOnly Property Category As FunctionCategory Implements ICustomFunctionOperatorBrowsable.Category
            Get
                Return FunctionCategory.Text
            End Get
        End Property
        Public Function Evaluate(ParamArray operands As Object()) As Object Implements ICustomFunctionOperator.Evaluate
            Throw New NotImplementedException()
        End Function
        Public Function GetAggregationContextType(ByVal inputType As Type) As Type Implements ICustomAggregateFunction.GetAggregationContextType
            Return GetType(FirstValueAggregateState(Of )).MakeGenericType(inputType)
        End Function
        Public Function IsValidOperandCount(ByVal count As Integer) As Boolean Implements ICustomFunctionOperatorBrowsable.IsValidOperandCount
            Return count <= MaxOperandCount AndAlso count >= MinOperandCount
        End Function
        Public Function IsValidOperandType(ByVal operandIndex As Integer, ByVal operandCount As Integer, ByVal type As Type) As Boolean Implements ICustomFunctionOperatorBrowsable.IsValidOperandType
            Return IsValidOperandCount(operandCount) AndAlso operandIndex = 0
        End Function
        Public Function ResultType(ParamArray operands As Type()) As Type Implements ICustomFunctionOperator.ResultType
            Return operands.FirstOrDefault()
        End Function
    End Class

    Friend Class FirstValueAggregateState(Of TInput)
        Implements ICustomAggregateFunctionContext(Of TInput, TInput)
        Private isSet As Boolean = False
        Private firstValue As TInput
        Public Function GetResult() As TInput Implements ICustomAggregateFunctionContext(Of TInput, TInput).GetResult
            Return If(isSet, firstValue, Nothing)
        End Function
        Public Sub Process(ByVal value As TInput) Implements ICustomAggregateFunctionContext(Of TInput, TInput).Process
            If Not isSet Then
                firstValue = value
                isSet = True
            End If
        End Sub
    End Class

WinForms Specific

In the WinForms Pivot Grid, you can enable users to create expressions that use custom functions in the UI. For this, create a field and drop it in the Pivot Grid’s Data Area. Set the field’s PivotGridFieldOptionsEx.ShowExpressionEditorMenu property to true to enable the Expression Editor menu for this field.

The following command appears in the field’s context menu:

You can additionally implement the ICustomFunctionCategory interface to define a category under which the Expression Editor should display the custom function. Otherwise, the custom function is displayed in the “String” function’s category.

The following code snippet adds the FirstValue function in the “Aggregate” function’s category:

C#

using DevExpress.DataAccess.ExpressionEditor;

namespace Dashboard_FirstValueAggregate {
    class FirstValueAggregateFunction : ICustomAggregateFunction, 
        ICustomFunctionOperatorBrowsable, ICustomFunctionCategory {
        public string Name => "FirstValue";
        //...
        public string FunctionCategory => "Aggregate";
        //...
    }
}

VB.NET

Imports DevExpress.DataAccess.ExpressionEditor

Namespace Dashboard_FirstValueAggregate
    Friend Class FirstValueAggregateFunction
        Implements ICustomAggregateFunction, ICustomFunctionOperatorBrowsable, ICustomFunctionCategory
        Public ReadOnly Property Name() As String Implements DevExpress.Data.Filtering.ICustomFunctionOperator.Name
            Get
                Return "FirstValue"
            End Get
        End Property
        '...
         Private ReadOnly Property ICustomFunctionCategory_FunctionCategory As String Implements ICustomFunctionCategory.FunctionCategory
            Get
                Return "Aggregate"
            End Get
        End Property
        '...
    End Class
End Namespace

After you registered the function, it appears in the Expression Editor:

Web Specific

You can implement a custom function in the ASPxPivotGrid control at runtime only. Users are not able to create expressions with custom functions in the UI.

Register a Custom Aggregate Function

Call the CriteriaOperator.RegisterCustomFunction method at the application startup to register a custom function in your project.

C#

CriteriaOperator.RegisterCustomFunction(new FirstValueAggregateFunction());

VB.NET

CriteriaOperator.RegisterCustomFunction(New FirstValueAggregateFunction())

To unregister a custom function, call the CriteriaOperator.UnregisterCustomFunction method.

Use a Function in the Pivot Grid

Follow the steps below to create an expression with the custom function in the Pivot Grid control:

1. Set the PivotGridOptionsData.DataProcessingEngine property to PivotDataProcessingEngine.Optimized.
2. Create a new field and drop it in the Data Area.
3. Assign the ExpressionDataBinding object to the field’s PivotGridFieldBase.DataBinding property.
4. Specify the expression that uses the created custom function.

The following code snippets add the FirstValue([ProductName]) expression to the First Product Sold field. The expression returns the first product sold by Sales Persons in each product category.

WinForms Pivot Grid

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

Web Forms Pivot Grid

View Example

<dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" ClientIDMode="AutoID" DataSourceID="SqlDataSource1" 
    OptionsView-ShowColumnGrandTotals ="False" OptionsView-ShowRowGrandTotals="False"
    OptionsView-ShowRowTotals="False" Theme="Metropolis">
    <Fields>
    <!-- 20... -->
        <dx:PivotGridField ID="FirstSoldValue" Area="DataArea" AreaIndex="0" FieldName="FirstSoldProduct" 
            Caption ="First Sold Product" Options-ShowGrandTotal ="false " Options-ShowTotals ="False">
            <DataBindingSerializable>
                <dx:ExpressionDataBinding Expression= "FirstValue([ProductName])" />
            </DataBindingSerializable>
        </dx:PivotGridField>
    </Fields>
    <OptionsData DataProcessingEngine="Optimized" />
</dx:ASPxPivotGrid>

---