Custom Aggregation

RadPivotGrid is designed to aggregate data and it provides an API for assigning special aggregation functions performing custom calculations. For the purpose of this example we will use a LocalDataSourceProvider object and we will bind the pivot control to the Orders table from the Northwind database as explained here.

Figure 1: Using the Built-in Sum function

Figure 2: Using the Custom Sqrt Of Sum function

Defining a Custom Aggregation Function

Our new function will be almost the same as the already available AggregateFunctions.Sum, with the important difference that it will calculate not the sum of the items, but the sum of their square roots.

Custom Function Logic

• C#
• VB.NET

public class SqrtSumAggregateFunction : Telerik.Pivot.Core.Aggregates.AggregateFunction
{
    public override string DisplayName
    {
        get { return "Sqrt Of Sum"; }
    }
    protected override AggregateValue CreateAggregate(Type dataType)
    {
        return new SqrtSumAggregate();
    }
    public override string GetStringFormat(Type dataType, string format)
    {
        if (format == null)
        {
            return "G";
        }
        return format;
    }
    public override string ToString()
    {
        return "Sqrt Of Sum";
    }
    public override bool Equals(object obj)
    {
        return obj is SqrtSumAggregateFunction;
    }
    protected override void CloneCore(Telerik.Pivot.Core.Cloneable source)
    {
    }
    protected override Telerik.Pivot.Core.Cloneable CreateInstanceCore()
    {
        return new SqrtSumAggregateFunction();
    }
    protected override AggregateValue CreateAggregate(IAggregateContext context)
    {
        return this.CreateAggregate(context.DataType);
    }
}

Imports System.Globalization
Imports Telerik.Pivot.Core
Imports Telerik.Pivot.Core.Aggregates
Public Class SqrtSumAggregateFunction
    Inherits Telerik.Pivot.Core.Aggregates.AggregateFunction
    Public Overrides ReadOnly Property DisplayName() As String
        Get
            Return "Sqrt Of Sum"
        End Get
    End Property
    Protected Overrides Function CreateAggregate(dataType As Type) As AggregateValue
        Return New SqrtSumAggregate()
    End Function
    Public Overrides Function GetStringFormat(dataType As Type, format As String) As String
        If format Is Nothing Then
            Return "G"
        End If
        Return format
    End Function
    Public Overrides Function ToString() As String
        Return "Sqrt Of Sum"
    End Function
    Public Overrides Function Equals(obj As Object) As Boolean
        Return TypeOf obj Is SqrtSumAggregateFunction
    End Function
    Protected Overrides Sub CloneCore(source As Telerik.Pivot.Core.Cloneable)
    End Sub
    Protected Overrides Function CreateInstanceCore() As Telerik.Pivot.Core.Cloneable
        Return New SqrtSumAggregateFunction()
    End Function
    Protected Overrides Function CreateAggregate(context As IAggregateContext) As AggregateValue
        Return Me.CreateAggregate(context.DataType)
    End Function
End Class

Defining a Custom Aggregate Value

We also need to create a custom aggregate value class which will be used by our newly defined function to perform the actual calculation.

Custom Aggregate Logic

• C#
• VB.NET

public sealed class SqrtSumAggregate : AggregateValue, IConvertibleAggregateValue<double>
{
    private double sum;
    private bool hasError = false;
    protected override object GetValueOverride()
    {
        return Math.Round(Math.Sqrt(this.sum), 2);
    }
    protected override void AccumulateOverride(object value)
    {
        this.sum += Convert.ToDouble(value, CultureInfo.InvariantCulture);
    }
    protected override void MergeOverride(AggregateValue childAggregate)
    {
        var sumAggregate = childAggregate as SqrtSumAggregate;
        if (sumAggregate != null)
        {
            this.sum += sumAggregate.sum;
        }
        else
        {
            double doubleValue;
            if (AggregateValueExtensions.TryConvertValue<double>(childAggregate, out doubleValue))
            {
                this.sum += doubleValue;
            }
            else
            {
                this.sum = 0;
                this.hasError = true;
            }
        }
    }
    bool IConvertibleAggregateValue<double>.TryConvertValue(out double value)
    {
        if (this.hasError)
        {
            value = 0;
            return false;
        }
        value = (double)this.sum;
        return true;
    }
}

Public NotInheritable Class SqrtSumAggregate
    Inherits AggregateValue
    Implements IConvertibleAggregateValue(Of Double)
    Private sum As Double
    Private hasError As Boolean = False
    Protected Overrides Function GetValueOverride() As Object
        Return Math.Round(Math.Sqrt(Me.sum), 2)
    End Function
    Protected Overrides Sub AccumulateOverride(value As Object)
        Me.sum += Convert.ToDouble(value, CultureInfo.InvariantCulture)
    End Sub
    Protected Overrides Sub MergeOverride(childAggregate As AggregateValue)
        Dim sumAggregate = TryCast(childAggregate, SqrtSumAggregate)
        If sumAggregate IsNot Nothing Then
            Me.sum += sumAggregate.sum
        Else
            Dim doubleValue As Double
            If AggregateValueExtensions.TryConvertValue(Of Double)(childAggregate, doubleValue) Then
                Me.sum += doubleValue
            Else
                Me.sum = 0
                Me.hasError = True
            End If
        End If
    End Sub
    Private Function IConvertibleAggregateValue_TryConvertValue(ByRef value As Double) As Boolean Implements IConvertibleAggregateValue(Of Double).TryConvertValue
        If Me.hasError Then
            value = 0
            Return False
        End If
        value = CDbl(Me.sum)
        Return True
    End Function
End Class

Assigning the Custom Function

We can make use of our new function by assigning it to the AggregateFunction property of a PropertyAggregateDescription object before setting it to our pivot.

The Custom Dialogs article demonstrates how the custom function can be added to the Aggregate Options Dialog.

Using the Custom Aggregate Function

• C#
• VB.NET

LocalDataSourceProvider dataProvider = new LocalDataSourceProvider();
dataProvider.ItemsSource = dataset.Orders;
dataProvider.AggregateDescriptions.Add(new PropertyAggregateDescription() { PropertyName = "Freight", AggregateFunction = new SqrtSumAggregateFunction() });
this.radPivotGrid1.DataProvider = dataProvider;

Dim dataProvider As New LocalDataSourceProvider()
dataProvider.ItemsSource = dataset.Orders
dataProvider.AggregateDescriptions.Add(New PropertyAggregateDescription() With { _
.PropertyName = "Freight", _
.AggregateFunction = New SqrtSumAggregateFunction() _
})
Me.RadPivotGrid1.DataProvider = dataProvider

If you add calculated fields in code behind, you have to set the ItemsSource of LocalDataSourceProvider after you have added all calculated fields or to wrap the code between (including setting the ItemsSource ) BeginInit/EndInit methods (or inside using DeferRefresh() { ... } section ).

Figure 2: Calculated Field in RadPivotGrid