Skip to main content
All docs
V24.1

Pivot Grid Custom Aggregate Functions

  • 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.

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

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:

expression editor command in ui

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:

using DevExpress.DataAccess.ExpressionEditor;

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

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

custom-aggregates-in-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.

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

First Value Custom Function Example

View Example

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

Web Forms Pivot Grid

First Value Custom Function Example Web

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>