corelibraries-403494-devexpress-pivot-grid-core-library-advanced-analytics-custom-aggregate-functions.md
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.
Review the common and platform-specific sections for information on how to implement a custom aggregate function.
The following steps are required for all platforms:
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.
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.
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;
}
}
}
}
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
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:
using DevExpress.DataAccess.ExpressionEditor;
namespace Dashboard_FirstValueAggregate {
class FirstValueAggregateFunction : ICustomAggregateFunction,
ICustomFunctionOperatorBrowsable, ICustomFunctionCategory {
public string Name => "FirstValue";
//...
public string FunctionCategory => "Aggregate";
//...
}
}
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:
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.
Call the CriteriaOperator.RegisterCustomFunction method at the application startup to register a custom function in your project.
CriteriaOperator.RegisterCustomFunction(new FirstValueAggregateFunction());
CriteriaOperator.RegisterCustomFunction(New FirstValueAggregateFunction())
To unregister a custom function, call the CriteriaOperator.UnregisterCustomFunction method.
Follow the steps below to create an expression with the custom function in the Pivot Grid control:
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.
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
<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>