How to create calculated field using another calculated field
Environment
| Product Version | Product | Author |
|---|---|---|
| 2022.2.622 | PivotGrid for WinForms | Dinko Krastev |
Description
An example demonstrating how to get the value of a calculated field in another calculated field.
Solution
The RequiredField static class exposes two methods to get fields in RadPivotGrid: RequiredField.ForProperty() and RequiredField.ForCalculatedField(). Using the second method we can get another calculated field value.
Sample Implementation
- First we are going to populate our RadPivotGrid with sample data.
public partial class RadForm1 : Telerik.WinControls.UI.RadForm
{
public RadForm1()
{
InitializeComponent();
LocalDataSourceProvider dataProvider = new LocalDataSourceProvider();
this.radPivotGrid1.DataProvider = dataProvider;
dataProvider.BeginInit();
dataProvider.RowGroupDescriptions.Add(new PropertyGroupDescription() { PropertyName = "Name" });
dataProvider.ColumnGroupDescriptions.Add(new DateTimeGroupDescription() { PropertyName = "Date", Step = DateTimeStep.Month });
dataProvider.AggregateDescriptions.Add(new PropertyAggregateDescription() { PropertyName = "Quantity" });
dataProvider.EndInit();
dataProvider.ItemsSource = GeneratePivotData();
}
private static IList<Product> GeneratePivotData()
{
IList<Product> PivotData = new List<Product>()
{
new Product() { Name = "Pen", Date = new DateTime(2012,1,1,0,0,0), Price = 10.40, Quantity = 148 },
new Product() { Name = "Pen", Date = new DateTime(2012,2,1,0,0,0), Price = 10.99, Quantity = 122 },
new Product() { Name = "Pen", Date = new DateTime(2012,3,1,0,0,0), Price = 11.24, Quantity = 80 },
new Product() { Name = "Pen", Date = new DateTime(2012,4,1,0,0,0), Price = 11.24, Quantity = 90 },
new Product() { Name = "Pen", Date = new DateTime(2012,5,1,0,0,0), Price = 11.14, Quantity = 140 },
new Product() { Name = "Pen", Date = new DateTime(2012,6,1,0,0,0), Price = 10.89, Quantity = 162 },
new Product() { Name = "Pen", Date = new DateTime(2012,7,1,0,0,0), Price = 10.89, Quantity = 181 },
new Product() { Name = "Pen", Date = new DateTime(2012,8,1,0,0,0), Price = 10.88, Quantity = 180 },
new Product() { Name = "Pen", Date = new DateTime(2012,9,1,0,0,0), Price = 11.00, Quantity = 116 },
new Product() { Name = "Pen", Date = new DateTime(2012,10,1,0,0,0), Price = 10.99, Quantity = 128 },
new Product() { Name = "Pen", Date = new DateTime(2012,11,1,0,0,0), Price = 10.95, Quantity = 145 },
new Product() { Name = "Pen", Date = new DateTime(2012,12,1,0,0,0), Price = 10.45, Quantity = 189 },
new Product() { Name = "Pencil", Date = new DateTime(2012,1,1,0,0,0), Price = 5.22, Quantity = 100 },
new Product() { Name = "Pencil", Date = new DateTime(2012,2,1,0,0,0), Price = 5.99, Quantity = 85 },
new Product() { Name = "Pencil", Date = new DateTime(2012,3,1,0,0,0), Price = 6.04, Quantity = 80 },
new Product() { Name = "Pencil", Date = new DateTime(2012,4,1,0,0,0), Price = 6.28, Quantity = 72 },
new Product() { Name = "Pencil", Date = new DateTime(2012,5,1,0,0,0), Price = 6.12, Quantity = 99 },
new Product() { Name = "Pencil", Date = new DateTime(2012,6,1,0,0,0), Price = 6.59, Quantity = 40 },
new Product() { Name = "Pencil", Date = new DateTime(2012,7,1,0,0,0), Price = 6.29, Quantity = 68 },
new Product() { Name = "Pencil", Date = new DateTime(2012,8,1,0,0,0), Price = 5.99, Quantity = 95 },
new Product() { Name = "Pencil", Date = new DateTime(2012,9,1,0,0,0), Price = 5.89, Quantity = 120 },
new Product() { Name = "Pencil", Date = new DateTime(2012,10,1,0,0,0), Price = 5.99, Quantity = 105 },
new Product() { Name = "Pencil", Date = new DateTime(2012,11,1,0,0,0), Price = 5.96, Quantity = 111 },
new Product() { Name = "Pencil", Date = new DateTime(2012,12,1,0,0,0), Price = 5.99, Quantity = 108 },
new Product() { Name = "Notebook", Date = new DateTime(2012,1,1,0,0,0), Price = 22.86, Quantity = 88 },
new Product() { Name = "Notebook", Date = new DateTime(2012,2,1,0,0,0), Price = 23.02, Quantity = 95},
new Product() { Name = "Notebook", Date = new DateTime(2012,3,1,0,0,0), Price = 23.22, Quantity = 102 },
new Product() { Name = "Notebook", Date = new DateTime(2012,4,1,0,0,0), Price = 21.99, Quantity = 95},
new Product() { Name = "Notebook", Date = new DateTime(2012,5,1,0,0,0), Price = 22.45, Quantity = 84},
new Product() { Name = "Notebook", Date = new DateTime(2012,6,1,0,0,0), Price = 22.56, Quantity = 96 },
new Product() { Name = "Notebook", Date = new DateTime(2012,7,1,0,0,0), Price = 22.88, Quantity = 88},
new Product() { Name = "Notebook", Date = new DateTime(2012,8,1,0,0,0), Price = 22.42, Quantity = 99},
new Product() { Name = "Notebook", Date = new DateTime(2012,9,1,0,0,0), Price = 22.56, Quantity = 111},
new Product() { Name = "Notebook", Date = new DateTime(2012,10,1,0,0,0), Price = 22.18, Quantity = 102 },
new Product() { Name = "Notebook", Date = new DateTime(2012,11,1,0,0,0), Price = 22.93, Quantity = 105 },
new Product() { Name = "Notebook", Date = new DateTime(2012,12,1,0,0,0), Price = 22.89, Quantity = 122 },
};
return PivotData;
}
}
public class Product
{
public string Name { get; set; }
public int Quantity { get; set; }
public double Price { get; set; }
public DateTime Date { get; set; }
}
Public Partial Class RadForm1
Inherits Telerik.WinControls.UI.RadForm
Public Sub New()
InitializeComponent()
Dim dataProvider As LocalDataSourceProvider = New LocalDataSourceProvider()
Me.radPivotGrid1.DataProvider = dataProvider
dataProvider.BeginInit()
dataProvider.RowGroupDescriptions.Add(New PropertyGroupDescription() With {
.PropertyName = "Name"
})
dataProvider.ColumnGroupDescriptions.Add(New DateTimeGroupDescription() With {
.PropertyName = "Date",
.[Step] = DateTimeStep.Month
})
dataProvider.AggregateDescriptions.Add(New PropertyAggregateDescription() With {
.PropertyName = "Quantity"
})
dataProvider.EndInit()
dataProvider.ItemsSource = GeneratePivotData()
End Sub
Private Shared Function GeneratePivotData() As IList(Of Product)
Dim PivotData As IList(Of Product) = New List(Of Product)() From {
New Product() With {
.Name = "Pen",
.Date = New DateTime(2012, 1, 1, 0, 0, 0),
.Price = 10.40,
.Quantity = 148
},
New Product() With {
.Name = "Pen",
.Date = New DateTime(2012, 2, 1, 0, 0, 0),
.Price = 10.99,
.Quantity = 122
},
New Product() With {
.Name = "Pen",
.Date = New DateTime(2012, 3, 1, 0, 0, 0),
.Price = 11.24,
.Quantity = 80
},
New Product() With {
.Name = "Pen",
.Date = New DateTime(2012, 4, 1, 0, 0, 0),
.Price = 11.24,
.Quantity = 90
},
New Product() With {
.Name = "Pen",
.Date = New DateTime(2012, 5, 1, 0, 0, 0),
.Price = 11.14,
.Quantity = 140
},
New Product() With {
.Name = "Pen",
.Date = New DateTime(2012, 6, 1, 0, 0, 0),
.Price = 10.89,
.Quantity = 162
},
New Product() With {
.Name = "Pen",
.Date = New DateTime(2012, 7, 1, 0, 0, 0),
.Price = 10.89,
.Quantity = 181
},
New Product() With {
.Name = "Pen",
.Date = New DateTime(2012, 8, 1, 0, 0, 0),
.Price = 10.88,
.Quantity = 180
},
New Product() With {
.Name = "Pen",
.Date = New DateTime(2012, 9, 1, 0, 0, 0),
.Price = 11.00,
.Quantity = 116
},
New Product() With {
.Name = "Pen",
.Date = New DateTime(2012, 10, 1, 0, 0, 0),
.Price = 10.99,
.Quantity = 128
},
New Product() With {
.Name = "Pen",
.Date = New DateTime(2012, 11, 1, 0, 0, 0),
.Price = 10.95,
.Quantity = 145
},
New Product() With {
.Name = "Pen",
.Date = New DateTime(2012, 12, 1, 0, 0, 0),
.Price = 10.45,
.Quantity = 189
},
New Product() With {
.Name = "Pencil",
.Date = New DateTime(2012, 1, 1, 0, 0, 0),
.Price = 5.22,
.Quantity = 100
},
New Product() With {
.Name = "Pencil",
.Date = New DateTime(2012, 2, 1, 0, 0, 0),
.Price = 5.99,
.Quantity = 85
},
New Product() With {
.Name = "Pencil",
.Date = New DateTime(2012, 3, 1, 0, 0, 0),
.Price = 6.04,
.Quantity = 80
},
New Product() With {
.Name = "Pencil",
.Date = New DateTime(2012, 4, 1, 0, 0, 0),
.Price = 6.28,
.Quantity = 72
},
New Product() With {
.Name = "Pencil",
.Date = New DateTime(2012, 5, 1, 0, 0, 0),
.Price = 6.12,
.Quantity = 99
},
New Product() With {
.Name = "Pencil",
.Date = New DateTime(2012, 6, 1, 0, 0, 0),
.Price = 6.59,
.Quantity = 40
},
New Product() With {
.Name = "Pencil",
.Date = New DateTime(2012, 7, 1, 0, 0, 0),
.Price = 6.29,
.Quantity = 68
},
New Product() With {
.Name = "Pencil",
.Date = New DateTime(2012, 8, 1, 0, 0, 0),
.Price = 5.99,
.Quantity = 95
},
New Product() With {
.Name = "Pencil",
.Date = New DateTime(2012, 9, 1, 0, 0, 0),
.Price = 5.89,
.Quantity = 120
},
New Product() With {
.Name = "Pencil",
.Date = New DateTime(2012, 10, 1, 0, 0, 0),
.Price = 5.99,
.Quantity = 105
},
New Product() With {
.Name = "Pencil",
.Date = New DateTime(2012, 11, 1, 0, 0, 0),
.Price = 5.96,
.Quantity = 111
},
New Product() With {
.Name = "Pencil",
.Date = New DateTime(2012, 12, 1, 0, 0, 0),
.Price = 5.99,
.Quantity = 108
},
New Product() With {
.Name = "Notebook",
.Date = New DateTime(2012, 1, 1, 0, 0, 0),
.Price = 22.86,
.Quantity = 88
},
New Product() With {
.Name = "Notebook",
.Date = New DateTime(2012, 2, 1, 0, 0, 0),
.Price = 23.02,
.Quantity = 95
},
New Product() With {
.Name = "Notebook",
.Date = New DateTime(2012, 3, 1, 0, 0, 0),
.Price = 23.22,
.Quantity = 102
},
New Product() With {
.Name = "Notebook",
.Date = New DateTime(2012, 4, 1, 0, 0, 0),
.Price = 21.99,
.Quantity = 95
},
New Product() With {
.Name = "Notebook",
.Date = New DateTime(2012, 5, 1, 0, 0, 0),
.Price = 22.45,
.Quantity = 84
},
New Product() With {
.Name = "Notebook",
.Date = New DateTime(2012, 6, 1, 0, 0, 0),
.Price = 22.56,
.Quantity = 96
},
New Product() With {
.Name = "Notebook",
.Date = New DateTime(2012, 7, 1, 0, 0, 0),
.Price = 22.88,
.Quantity = 88
},
New Product() With {
.Name = "Notebook",
.Date = New DateTime(2012, 8, 1, 0, 0, 0),
.Price = 22.42,
.Quantity = 99
},
New Product() With {
.Name = "Notebook",
.Date = New DateTime(2012, 9, 1, 0, 0, 0),
.Price = 22.56,
.Quantity = 111
},
New Product() With {
.Name = "Notebook",
.Date = New DateTime(2012, 10, 1, 0, 0, 0),
.Price = 22.18,
.Quantity = 102
},
New Product() With {
.Name = "Notebook",
.Date = New DateTime(2012, 11, 1, 0, 0, 0),
.Price = 22.93,
.Quantity = 105
},
New Product() With {
.Name = "Notebook",
.Date = New DateTime(2012, 12, 1, 0, 0, 0),
.Price = 22.89,
.Quantity = 122
}
}
Return PivotData
End Function
End Class
Public Class Product
Public Property Name As String
Public Property Quantity As Integer
Public Property Price As Double
Public Property Date As DateTime
End Class
- Next step is to create our custom-calculated fields.
public class CommissionCalculatedField : CalculatedField
{
private RequiredField sellPriceCalculatedField;
public CommissionCalculatedField()
{
this.Name = "Commision";
this.sellPriceCalculatedField = RequiredField.ForCalculatedField("SellPrice");
}
protected override IEnumerable<RequiredField> RequiredFields()
{
yield return this.sellPriceCalculatedField;
}
protected override AggregateValue CalculateValue(IAggregateValues aggregateValues)
{
var quantity = aggregateValues.GetAggregateValue(sellPriceCalculatedField);
if (quantity.IsError())
{
return quantity;
}
double extendedPrice = quantity.ConvertOrDefault<double>();
return new DoubleAggregateValue(extendedPrice * 0.1);
}
}
public class SellPriceCalculatedField : CalculatedField
{
private RequiredField sellPriceCalculatedField;
public SellPriceCalculatedField()
{
this.Name = "SellPrice";
this.sellPriceCalculatedField = RequiredField.ForProperty("Price");
}
protected override IEnumerable<RequiredField> RequiredFields()
{
yield return this.sellPriceCalculatedField;
}
protected override AggregateValue CalculateValue(IAggregateValues aggregateValues)
{
var aggregateValue = aggregateValues.GetAggregateValue(this.sellPriceCalculatedField);
if (aggregateValue.IsError())
{
return aggregateValue;
}
double extendedPrice = aggregateValue.ConvertOrDefault<double>();
return new DoubleAggregateValue(extendedPrice * 1.5);
}
}
Public Class CommissionCalculatedField
Inherits CalculatedField
Private sellPriceCalculatedField As RequiredField
Public Sub New()
Me.Name = "Commision"
Me.sellPriceCalculatedField = RequiredField.ForCalculatedField("SellPrice")
End Sub
Protected Overrides Iterator Function RequiredFields() As IEnumerable(Of RequiredField)
Yield Me.sellPriceCalculatedField
End Function
Protected Overrides Function CalculateValue(ByVal aggregateValues As IAggregateValues) As AggregateValue
Dim quantity = aggregateValues.GetAggregateValue(sellPriceCalculatedField)
If quantity.IsError() Then
Return quantity
End If
Dim extendedPrice As Double = quantity.ConvertOrDefault(Of Double)()
Return New DoubleAggregateValue(extendedPrice * 0.1)
End Function
End Class
Public Class SellPriceCalculatedField
Inherits CalculatedField
Private sellPriceCalculatedField As RequiredField
Public Sub New()
Me.Name = "SellPrice"
Me.sellPriceCalculatedField = RequiredField.ForProperty("Price")
End Sub
Protected Overrides Iterator Function RequiredFields() As IEnumerable(Of RequiredField)
Yield Me.sellPriceCalculatedField
End Function
Protected Overrides Function CalculateValue(ByVal aggregateValues As IAggregateValues) As AggregateValue
Dim aggregateValue = aggregateValues.GetAggregateValue(Me.sellPriceCalculatedField)
If aggregateValue.IsError() Then
Return aggregateValue
End If
Dim extendedPrice As Double = aggregateValue.ConvertOrDefault(Of Double)()
Return New DoubleAggregateValue(extendedPrice * 1.5)
End Function
End Class
- What's left is to add the above-calculated fields to the RadPivotGrid control.
public RadForm1()
{
InitializeComponent();
LocalDataSourceProvider dataProvider = new LocalDataSourceProvider();
this.radPivotGrid1.DataProvider = dataProvider;
dataProvider.BeginInit();
dataProvider.RowGroupDescriptions.Add(new PropertyGroupDescription() { PropertyName = "Name" });
dataProvider.ColumnGroupDescriptions.Add(new DateTimeGroupDescription() { PropertyName = "Date", Step = DateTimeStep.Month });
dataProvider.AggregateDescriptions.Add(new PropertyAggregateDescription() { PropertyName = "Quantity" });
dataProvider.EndInit();
using (radPivotGrid1.PivotGridElement.DeferRefresh())
{
SellPriceCalculatedField sellPriceCalculatedField = new SellPriceCalculatedField();
sellPriceCalculatedField.Name = "SellPrice";
((LocalDataSourceProvider)this.radPivotGrid1.DataProvider).CalculatedFields.Add(sellPriceCalculatedField);
CommissionCalculatedField calculatedField = new CommissionCalculatedField();
calculatedField.Name = "Commision";
((LocalDataSourceProvider)this.radPivotGrid1.DataProvider).CalculatedFields.Add(calculatedField);
}
dataProvider.ItemsSource = GeneratePivotData();
}
Public Sub New()
InitializeComponent()
Dim dataProvider As LocalDataSourceProvider = New LocalDataSourceProvider()
Me.radPivotGrid1.DataProvider = dataProvider
dataProvider.BeginInit()
dataProvider.RowGroupDescriptions.Add(New PropertyGroupDescription() With {
.PropertyName = "Name"
})
dataProvider.ColumnGroupDescriptions.Add(New DateTimeGroupDescription() With {
.PropertyName = "Date",
.[Step] = DateTimeStep.Month
})
dataProvider.AggregateDescriptions.Add(New PropertyAggregateDescription() With {
.PropertyName = "Quantity"
})
dataProvider.EndInit()
Using radPivotGrid1.PivotGridElement.DeferRefresh()
Dim sellPriceCalculatedField As SellPriceCalculatedField = New SellPriceCalculatedField()
sellPriceCalculatedField.Name = "SellPrice"
(CType(Me.radPivotGrid1.DataProvider, LocalDataSourceProvider)).CalculatedFields.Add(sellPriceCalculatedField)
Dim calculatedField As CommissionCalculatedField = New CommissionCalculatedField()
calculatedField.Name = "Commision"
(CType(Me.radPivotGrid1.DataProvider, LocalDataSourceProvider)).CalculatedFields.Add(calculatedField)
End Using
dataProvider.ItemsSource = GeneratePivotData()
End Sub