New to Telerik UI for WinForms? Download free 30-day trial

Calculated items

A calculated item is a new item in a row or column field in which the values are the result of a custom calculation. In this case, the calculated item’s formula references one or more items in the same field. By using Calculated Items you are able to extend RadPivotGrid with additional items that are not part of the data source.

Defining Calculated Item

With RadPivotGrid you are able to create different Groups that will be shown in Rows and Columns. But in some cases you may need to show additional items for specific group. In this case you may use Calculated Items. Calculated Items are added to a group description and they have access to different items from the same group. For example, lets say we want to calculate the average sales made by some of the sales people, but not all of them. First we have to create a concrete class that implements the abstract CalculatedItem class. For this purpose the new class must implement GetValue method. In our scenario we'll show the average sales of four of the sales people:

Calculated Item

public class MenAverageSales : CalculatedItem
{
    protected override AggregateValue GetValue(IAggregateSummaryValues aggregateSummaryValues)
    {
        AggregateValue[] aggregateValues = {
        aggregateSummaryValues.GetAggregateValue("Andrew Fuller"),
        aggregateSummaryValues.GetAggregateValue("Michael Suyama"),
        aggregateSummaryValues.GetAggregateValue("Robert King"),
        aggregateSummaryValues.GetAggregateValue("Steven Buchanan")
    };
        if (aggregateValues.ContainsError())
        {
            return AggregateValue.ErrorAggregateValue;
        }
        double average = aggregateValues.Average(av => av.ConvertOrDefault<double>());
        return new DoubleAggregateValue(average);
    }
}

As you can see the calculated item will show the average sales of four people. Now we just have to add it to the PropertyGroupDescription. In our case this will be the Salesperson group:

PropertyGroupDescription salesPersonGroupDescription = new PropertyGroupDescription();
salesPersonGroupDescription.PropertyName = "Salesperson";
MenAverageSales calculatedItem = new MenAverageSales();
calculatedItem.GroupName = "Average Sales (Men)";
salesPersonGroupDescription.CalculatedItems.Add(calculatedItem);
localProvider.ColumnGroupDescriptions.Add(salesPersonGroupDescription);

Figure 1: RadPivotGrid Calculated Item

WinForms RadPivotGrid Calculated Item

Add Calculated Items at Run-time

Calculated items can be added only to Group Descriptions. If you are using RadPivotFieldList the users can remove the group for which you've added calculated items and this way the calculated items will be removed as well. Adding the same group in rows or columns will not show the calculated items anymore. In order to add them again you have to use PrepareDescriptionForField event of LocalDataSourceProvider and add the calculated items to the description:

PrepareDescriptionForField Event

void localProvider_PrepareDescriptionForField(object sender, PrepareDescriptionForFieldEventArgs e)
{
    if (e.DescriptionType == DataProviderDescriptionType.Group && e.FieldInfo.DisplayName == "Salesperson")
    {
        var description = e.Description as Telerik.Pivot.Core.PropertyGroupDescription;
        if (description != null)
        {
            var calculatedItem = new MenAverageSales();
            calculatedItem.GroupName = "Average Sales (Men)";
            description.CalculatedItems.Add(calculatedItem);
        }
    }
}

Solve Order

If you have calculated items in both rows and columns group descriptions, you have to define which of them will be used for the intersected cells. That's why each Calculated Item has SolveOrder property - when a cell is an intersection between two calculated items the one with higher solve order will be used.

SolveOrder Property

PropertyGroupDescription salesPersonGroupDescription = new PropertyGroupDescription();
salesPersonGroupDescription.PropertyName = "Salesperson";
MenAverageSales menAvgSalesCalculatedItem  = new MenAverageSales();
menAvgSalesCalculatedItem .GroupName = "Average Sales (Men)";
menAvgSalesCalculatedItem .SolveOrder = 1;
salesPersonGroupDescription.CalculatedItems.Add(menAvgSalesCalculatedItem);
PropertyGroupDescription countryGroupDescription = new PropertyGroupDescription();
countryGroupDescription.PropertyName = "Country";
CA caCalculatedItem = new CA();
caCalculatedItem.GroupName = "CA";
caCalculatedItem.SolveOrder = 2;
countryGroupDescription.CalculatedItems.Add(caCalculatedItem);
localProvider.ColumnGroupDescriptions.Add(salesPersonGroupDescription);
localProvider.RowGroupDescriptions.Add(countryGroupDescription);

Here is the result:

Figure 1: SolveOrder Example 1

WinForms RadPivotGrid SolveOrder Example 1

As you can see the intersected cell between the two calculated items has value 500 as the CA calculated item has higher solve order. If we change the solve order of Men Average Sales to a higher value, for example 5, here is how RadPivotGrid will look like:

Figure 2: SolveOrder Example 2

WinForms RadPivotGrid SolveOrder Example 2