Calculated Fields

In this article you will find out how to extend RadPivotGrid's generated report by adding Calculated Fields.

Calculated Fields Description

If your data analysis requires results that are not available using just the data source fields and RadPivotGrid's built-in calculations, you can insert a calculated field that uses a custom formula to derive the results you need. A calculated field is a new data field in which the values are the result of a custom calculation formula. You can display the calculated field along with another data field or on its own. A calculated field is really a custom summary calculation, so in almost all cases, the calculated field references one or more fields in the source data.

Define Calculated Field

All Calculated Fields that you have added to your QueryableDataProvider will be visible in RadPivotFieldList, so the user will be able to add and remove them at runtime. In order to add a calculated field to QueryableDataProvider you have to add it to its CalculatedFields collection. So first you have to create a concrete class that implements the abstract class CalculatedField. This requires the implementation of two methods - CalculatedValue and RequiredFields. CalculateValue is the method in which you have to define your calculation formula and create a new AggregateValue that will be shown in RadPivotGrid. In common scenario a Calculated Field reference one or more fields in the data source. The RequiredFields method should return IEnumerable of the required fields. That's why we have added a new class called RequiredField. It's purpose is to describe a field, required for a calculated field. RequiredField can be created for property from the data object or for another calculated field. Name property of CalculatedField class identifies its unique name that will be shown in the UI.

The first task is to decide what is the calculation formula that you want to use. For example, you can show the commission that will be paid to all salespeople. Commission will be paid only to those who have more sold for more than $15 000. The price of the sold items is kept by the ExtendedPrice property from the source. So the new class will look like this:

public class CommissionCalculatedField : CalculatedField 
{ 
    private RequiredField extendPriceField; 
 
    public CommissionCalculatedField() 
    { 
        this.Name = "Commission"; 
        this.extendPriceField = RequiredField.ForProperty("ExtendedPrice"); 
    } 
 
    protected override IEnumerable<RequiredField> RequiredFields() 
    { 
        yield return this.extendPriceField; 
    } 
 
    protected override AggregateValue CalculateValue(IAggregateValues aggregateValues) 
    { 
        var aggregateValue = aggregateValues.GetAggregateValue(this.extendPriceField); 
        if (aggregateValue.IsError()) 
        { 
            return aggregateValue; 
        } 
 
        double extendedPrice = aggregateValue.ConvertOrDefault<double>(); 
        if (extendedPrice > 15000) 
        { 
            return new DoubleAggregateValue(extendedPrice * 0.1); 
        } 
 
        return null; 
    } 
}

Public Class CommissionCalculatedField 
    Inherits CalculatedField 
 
    Private extendPriceField As RequiredField 
 
    Public Sub New() 
        Me.Name = "Commission" 
        Me.extendPriceField = RequiredField.ForProperty("ExtendedPrice") 
    End Sub 
 
    Protected Overrides Iterator Function RequiredFields() As IEnumerable(Of RequiredField) 
        Yield Me.extendPriceField 
    End Function 
 
    Protected Overrides Function CalculateValue(ByVal aggregateValues As IAggregateValues) As AggregateValue 
        Dim aggregateValue = aggregateValues.GetAggregateValue(Me.extendPriceField) 
        If aggregateValue.IsError() Then 
            Return aggregateValue 
        End If 
 
        Dim extendedPrice As Double = aggregateValue.ConvertOrDefault(Of Double)() 
        If extendedPrice > 15000 Then 
            Return New DoubleAggregateValue(extendedPrice * 0.1) 
        End If 
 
        Return Nothing 
    End Function

Now it is time to add a new instance of this class to the CalculatedFields collection of QueryableDataProvider:

<pivot:QueryableDataProvider.CalculatedFields> 
    <local:CommissionCalculatedField Name="Commission" /> 
</pivot:QueryableDataProvider.CalculatedFields>

var queryableCalculatedField = new CommissionCalculatedField(); 
queryableCalculatedField.Name = "Commission"; 
dataProvider.CalculatedFields.Add(queryableCalculatedField);

Dim queryableCalculatedField = New CommissionCalculatedField() 
queryableCalculatedField.Name = "Commission" 
dataProvider.CalculatedFields.Add(queryableCalculatedField)

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

This way the Commission field will be visible in a special folder inside RadPivotFieldList called Calculated Fields.

Rad Pivot Grid Features Local Calc Fields 01

In order to use the calculated field in the generated report, you have to add it to QueryableDataProvider's AggregateDescriptions collection:

<pivot:QueryableDataProvider.AggregateDescriptions> 
    <pivot:QueryableCalculatedAggregateDescription CalculatedFieldName="Commission" /> 
</pivot:QueryableDataProvider.AggregateDescriptions>

var queryableCalculatedAggregate = new QueryableCalculatedAggregateDescription(); 
queryableCalculatedAggregate.CalculatedFieldName = "Commission"; 
dataProvider.AggregateDescriptions.Add(queryableCalculatedAggregate);

Dim queryableCalculatedAggregate = New QueryableCalculatedAggregateDescription() 
queryableCalculatedAggregate.CalculatedFieldName = "Commission" 
dataProvider.AggregateDescriptions.Add(queryableCalculatedAggregate)

The result will look like this:

Rad Pivot Grid Features Local Calc Fields 02

Calculated Fields