[Solved] Custom Calculations

[Jonathan Boylan]

The PivotGrid is almost perfect for displaying our data, but we do need to perform a custom calculation for one of the cells in each row.

Is this possible, and if so can you give me an example.

Regards

[Rosen Vladimirov]

Hi Jonathan,

Adding calculated fields to RadPivotGrid is still in our to-do list. But there is a way to workaround this problem by creating a custom aggregate description and custom calculation function. We have implemented a simple project to demonstrate how to achieve such functionality. Note that we have added a "dummy" property called "Margin" in our "Order" class (objects in the ItemsSource of the LocalDataSourceProvider are of this type, but neither of them has value for "Margin" property). This way you will see it in RadPivotFieldList and whenever you add it to the aggregate descriptions, a new MarginAggregate description will be created (we are using PrepareDescriptionForField event of the DataProvider). The MarginAggregate is calculated based on other two properties in the source.

Hopefully this helps. Feel free to contact us if you have any problems or concerns.

All the best,
Rosen Vladimirov
the Telerik team

[Jonathan Boylan]

Not sure if I can  make your example do what I need. I'll clarify my requirement:

The pivot gid will show one primary group of columns and one or more secondary group of columns. For each of the secondary groups, I want to generate my custom calculated value referenced against a value in the primary group.

I set up my dataprovider as follows:

var dataProvider = new LocalDataSourceProvider();

// Set up the row descriptors
dataProvider.RowGroupDescriptions.Add(new PropertyGroupDescription()
    {
        PropertyName = "Name",
        GroupComparer = new GrandTotalComparer() { AggregateIndex = 0 },
        SortOrder = SortOrder.Descending
    });

dataProvider.RowGroupDescriptions.Add(new PropertyGroupDescription()
    {
        PropertyName = "Classification",
        GroupComparer = new GrandTotalComparer() { AggregateIndex = 0 },
        SortOrder = SortOrder.Descending
    });

// Set up the Column descriptors
dataProvider.ColumnGroupDescriptions.Add(new PropertyGroupDescription()
    {
        PropertyName = "Group",
        GroupComparer = new GrandTotalComparer()
    });

// Set up the aggregate descriptors
dataProvider.AggregateDescriptions.Add(new PropertyAggregateDescription()
    {
        PropertyName = "Visits",
        CustomName = "Clicks",
        StringFormat = "N0"
    });

dataProvider.AggregateDescriptions.Add(new PropertyAggregateDescription()
    {
        PropertyName = "PercentofTotal",
        CustomName = "%",
        StringFormat = "0.00"
    });

dataProvider.AggregateDescriptions.Add(new PropertyAggregateDescription()
    {
        PropertyName = "PercentofTotal",
        CustomName = "Calculated Index",
        TotalFormat = new DifferenceFrom()
            {
                Axis = PivotAxis.Columns, GroupName = "Primary", Level = 0
            }
    });

PivotGridLocalDataProvider = dataProvider;

I don't need the thrid AggregateDescription which uses a 'DifferenceFrom' calculation, but it shows how I need to set the dataprovider up with a calculation. I need to use is my own custom calculation to compare the other group's PercentofTotal value to the Primary group's PercentofTotal value. The actual calcultion is simply:

(Primarygroup.PercentofToal / Othergroup.PercentofTotal) * 100

So I think I just need a MyCustomCalculation class based on DifferenceFromBase.

Have you an example of how to do this?

Thanks in advance.

[Rosen Vladimirov]

Hello Jonathan,

The example I've sent you, is calculating the value based on the values of other two aggregated fields. From your last mail it looks like that all of your items have the value PercentOfTotal and as you have noticed - you have to create a custom TotalFormat. Unfortunately with the current release you are not able to do it, but I'll add this feature request in our internal system.
Also you may check our other calculations - PercentOf, PercentOfPrevious, etc. You can check this article for more information.

We are sorry for the caused inconvenience.

Kind regards,
Rosen Vladimirov
the Telerik team