In a typical data grid, the data is flat and consists of many rows and columns. The data may contain cells with same values and working with the raw data may not be the best way to spot patterns and analyze trends. Many data grid controls provide rows grouping functionality which allows you to group multiple rows by columns where the corresponding grid cells have the same value.
Pivot tables go one step further and enable powerful data analysis. A Pivot table consists of rows, columns and data (value/fact) fields.
VIBlend DataGrid for Silverlight features a built-in data aggregation engine which is capable of turning any tabular data source into a wide variety of cross tab views. This is similar to the Pivot tables functionality in Excel. Creating a Pivot table with VIBlend DataGrid for Silverlight is very easy. In fact, the data binding to the data source is no different than data binding any other control to a collection. Assume that you have a table with five columns: Country, Region, City, ShipperCompany and ExtendedPrice. You need to choose the columns from the data source and add them to the BoundFields collection:
CSharp:
pivotGrid1.BoundFields.Add(new BoundField("Country", "Country"));
pivotGrid1.BoundFields.Add(new BoundField("Region", "Region"));
pivotGrid1.BoundFields.Add(new BoundField("City", "City"));
pivotGrid1.BoundFields.Add(new BoundField(
"Shipper Company ", "ShipperCompany"));
pivotGrid1.BoundFields.Add(new BoundField(
"ExtendedPrice", "ExtendedPrice"));
Using this code, once you data bind to the data source, the grid will display all table rows and the five columns: Country, Region, City, Carrier, Extended Price.
However, let’s look at a scenario where we want to show count of sales, amount of sales, and average sale amount grouped by Country, by Region and by City, and do that for each Shipper Company.
This requires only a few extra lines of code:
1. Assign the Country, Region and City fields to form the Rows hierarchy:
CSharp:
pivotGrid1.BoundPivotRows.Add(new BoundField("Country", "Country"));
pivotGrid1.BoundPivotRows.Add(new BoundField("Region", "Region"));
pivotGrid1.BoundPivotRows.Add(new BoundField("City", "City"));
2. Assign the ShipperCompany field to form the Columns hierarchy:
CSharp:
pivotGrid1.BoundPivotColumns.Add(new BoundField(
"Shipper Company", "ShipperCompany"));
3. Assign the ExtendedPrice field to form the facts/values for count of sales, sales amount, and average sale amount:
CSharp:
pivotGrid1.BoundPivotValues.Add(new BoundValueField("Count of Sales", "ExtendedPrice", PivotFieldFunction.Count));
pivotGrid1.BoundPivotValues.Add(new BoundValueField("Amount of Sales", "ExtendedPrice", PivotFieldFunction.Sum));
pivotGrid1.BoundPivotValues.Add(new BoundValueField("Avg Sale Amount", "ExtendedPrice", PivotFieldFunction.Average));
4. Specify if the value/fact columns appear attached to the rows or to the columns:
CSharp:
pivotGrid1.PivotValuesOnRows = false;
During the data binding process, the OLAP engine in VIBlend DataGrid for Silverlight will parse the table content, build the rows and columns hierarchies, and compute the pivot table cell values. The result will be the following pivot table view:
You can learn more and see a Live demo at: http://www.viblend.com
Subscribe