XtraPivotGrid is a life saver if you need to provide in-depth analyses and data mining features to your end users. If you are not familiar with this control then please read about it here http://www.devexpress.com/Products/NET/WinForms/XtraPivotGrid/ and here http://www.devexpress.com/Products/NET/WinForms/XtraPivotGrid/QuickStart.xml
From drilling down through your OLAP data source, live filtering, charts integration, and countless customization features, this control has it all. Those who use it can't get enough of it, and those who learn about it try to find a way to include it in their applications.
One piece was missing however. It's the support for data editing. First of all, what does it really mean to edit a summary value? Sure, one can drill down to an individual cell and modify it there, but what if the value is an aggregate of two or more? What should be done then? Well, it all depends on your data and your aggregates. Creating a universal approach that will cover all possible data editing scenarios seemed impractical, and that is not what our customers wanted. "Just let us modify cell values and we'll take care of updating the data source," we were told.
Unleashing the power of XtraEditors library
It is only natural to reuse the editors library that is used in our other product suites. (XtraGrid, XtraBars etc...). And editor embedding in the pivot grid is as easy as choosing from a repository of available editors.
But first things first. Let's create a pivot grid and bind it to some mock data.
public class Fact {
public string Name {
get;
set;
}
public string Item {
get;
set;
}
public decimal Amount {
get;
set;
}
}
public class Facts : List<Fact> {
public static Facts GetMockData() {
return new Facts() {
new Fact() { Name = "Joe Smith", Item = "Apples", Amount = 1000 },
new Fact() { Name = "Joe Smith", Item = "Apples", Amount = 2000 },
new Fact() { Name = "Joe Smith", Item = "Oranges", Amount = 200 },
new Fact() { Name = "Joe Smith", Item = "Red Bull", Amount = 4000 },
new Fact() { Name = "Ann Pixy", Item = "Apples", Amount = 6000 },
new Fact() { Name = "Ann Pixy", Item = "Oranges", Amount = 2000 },
new Fact() { Name = "Ann Pixy", Item = "Red Bull", Amount = 13000 },
new Fact() { Name = "Ann Pixy", Item = "Coke", Amount = 1000 },
new Fact() { Name = "David Cane", Item = "Apples", Amount = 1000 },
new Fact() { Name = "David Cane", Item = "Oranges", Amount = 32000 },
new Fact() { Name = "David Cane", Item = "Red Bull", Amount = 3000 },
new Fact() { Name = "David Cane", Item = "Coke", Amount = 500 },
};
}
}
Using the pivot grid control smart tag, I'll first choose an object data source that I want to bind to. In this case I will select the Fact class. Using the smart tag again, I'll now choose the Run Designer and click on Retrieve Fields to get all the fields built for me automatically.
See the new FieldEdit property? That's the property that will enable me to embed an in-place editor. In this case I will chose a simple calc edit.
After binding the pivot grid control to mock data like so:
this.pivotGridControl1.DataSource = Facts.GetMockData();
This is what we get:
Editing the value
Editing the value is done via the EditorValueChanged event. But let's go back to our data source first. Specifically, the Joe Smith - Apples cell. Notice that the value is 3000 which is correct because I have 2 Joe Smith - Apples records in my facts table (one with the Amount of 1000 and another with the Amount of 2000) and my aggregate operation is "summary". So what should happen when I edit it? It is easy for the Ann Pixy - Red Bull because there is only one fact item. We have a couple of options here. 1) Don't allow editing in this case. 2) Show a drilled down data with only Joe Smith - Apples records or 3) back-propagate the total to each individual fact.
Option number 3 covers the updates for single and multiple records so I will use that.
private void pivotGridControl1_EditorValueChanged(object sender, DevExpress.XtraPivotGrid.EditorValueChangedEventArgs e) {
if (e.DataField == fieldAmount) {
BackProp(e.CreateDrillDownDataSource(),
(decimal)e.Value,
(decimal)e.Editor.EditValue);
}
}
// Simply distribute the amount proportionally.
private void BackProp(PivotDrillDownDataSource dataSource,
decimal oldTotal, decimal newTotal) {
for (int i = 0; i < dataSource.RowCount; i++) {
decimal value = (decimal)dataSource [ i ] [ fieldAmount ];
value = (value / oldTotal) * newTotal;
dataSource [ i ] [ fieldAmount ] = value;
}
}
Side effects
Of course having support for embedded editors means support for data validation (handled in the ValidatingEditor event). But the coolest feature here is that I can now reuse the available editors for display purposes instead of having to custom draw them. For example you might want to display a progress bar or a track bar for some fields. And all you have to do is choose it for your FieldEdit.
Cheers
Azret