PivotGridControl Class
Allows you to create a pivot table (cross-tabular format) for multi-dimensional analysis of large amounts of data.
Namespace: DevExpress.XtraPivotGrid
Assembly: DevExpress.XtraPivotGrid.v23.2.dll
NuGet Package: DevExpress.Win.PivotGrid
Declaration
[OLAPDataAccessMetadata("All", SupportedProcessingModes = "Pivot", EnableBindingToXPOServerMode = false)]
public class PivotGridControl :
BaseViewInfoControl,
IFilteringUIClient,
IFilterCriteriaBindingAware,
IComponentLoading,
ISupportAsyncScrollAnimation,
IPrintable,
IBasePrintable,
IToolTipControlClient,
IPivotGridEventsImplementor,
IPivotGridEventsImplementorBase,
ICustomizationFormOwner,
IPivotGridDataContainerCore,
IPivotGridDataOwner,
IDXManagerPopupMenu,
IBindingList,
IList,
ICollection,
IEnumerable,
ITypedList,
IPivotGrid,
IChartDataSource,
IPivotGridViewInfoDataOwner,
IPivotGridPrinterOwner,
IDataContainerBase,
IMouseWheelSupport,
ISupportXtraSerializer,
IThreadSafeAccessible,
IXtraSerializable,
IXtraSerializableLayout,
IXtraSerializableLayoutEx,
IXtraSupportDeserializeCollectionItem,
IXtraSupportDeserializeCollection,
IPivotGestureClient,
IGestureClient,
IMouseWheelScrollClient,
IOptionsLayoutProvider,
IFilteredComponent,
IFilteredComponentBase,
IStringImageProvider,
IXtraSerializableLayout2,
IXtraPartlyDeserializable,
IXtraSupportAllowProperty,
IPropertyManagerProvider,
IAccessiblePivot,
IFilterPanelOwner,
ISupportFilterCriteriaDisplayStyleRemarks
The Pivot Grid Control represents data from an underlying data source in a cross-tabulated form. It calculates summaries and summary totals against specific fields and displays the summary values within data cells. The following summary functions are supported: Sum, Average, Count, Min, Max, StdDev, StdDevp, StdVar, StdVarp.
Fields are basic blocks which an end-user can manipulate in the PivotGrid control. A field is visually represented by a box (field header) which can be dragged between the control’s areas: Column Header Area, Row Header Area, Data Area and Filter Header Area. Dragging a field between the areas lets you reorganize the data and present it in various forms. The fields positioned within these areas are called column fields, row fields, data fields and filter fields, respectively.
A field is represented by the PivotGridField class.
For column fields, the control lists their values across the top edge. Similarly, the values of the row fields are listed across the control’s left edge. Thus a cell at the intersection of a column and row is identified by a column field value(s) and row field value(s). Obviously however, multiple records in the control’s data source can have identical values in the specified column field(s) and row field(s). Consequently, a cell in the Pivot Grid Control represents multiple records and it displays a summary value calculated against these records. The summary is calculated against a data field and the summary type is specified by the PivotGridFieldBase.SummaryType property of the data field.
The control’s data source is specified by the PivotGridControl.DataSource and PivotGridControl.DataMember properties.
The Pivot Grid Control uses the Binding API to bind Pivot Grid fields to data. You can use data source columns, calculated expressions, or window calculations as data binding sources.
Example
This example demonstrates how to create the Pivot Grid fields in code and specify their location and format. The Pivot Grid’s data source is the ExcelDataSource instance, created in code.
using DevExpress.DataAccess.Excel;
using DevExpress.XtraEditors;
using DevExpress.XtraPivotGrid;
using System;
namespace WinFormsPivotGridDataFieldsExample {
public partial class Form1 : XtraForm {
public Form1() {
InitializeComponent();
this.Load += Form1_Load;
// Create the Excel Data Source.
ExcelDataSource ds = new ExcelDataSource();
ds.FileName = "SalesPerson.xlsx";
ExcelWorksheetSettings settings = new ExcelWorksheetSettings("Data");
ds.SourceOptions = new ExcelSourceOptions(settings);
ds.Fill();
// Set the pivot's data source.
pivotGridControl1.DataSource = ds;
// Create pivot grid fields.
PivotGridField fieldCategoryName = new PivotGridField() {
Area = PivotArea.RowArea,
AreaIndex = 0,
Caption = "Category Name"
};
// Bind fields to columns in the data source.
DataSourceColumnBinding categoryNameBinding = new DataSourceColumnBinding("CategoryName");
fieldCategoryName.DataBinding = categoryNameBinding;
PivotGridField fieldProductName = new PivotGridField() {
Area = PivotArea.RowArea,
AreaIndex = 1,
Caption = "Product Name"
};
DataSourceColumnBinding productNameBinding = new DataSourceColumnBinding("ProductName");
fieldProductName.DataBinding = productNameBinding;
PivotGridField fieldExtendedPrice = new PivotGridField() {
Area = PivotArea.DataArea,
AreaIndex = 0,
Caption = "Extended Price"
};
DataSourceColumnBinding extendedPriceBinding = new DataSourceColumnBinding("Extended Price");
fieldExtendedPrice.DataBinding = extendedPriceBinding;
// Specify the field format.
fieldExtendedPrice.CellFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
fieldExtendedPrice.CellFormat.FormatString = "c2";
PivotGridField fieldOrderDate1 = new PivotGridField() {
Area = PivotArea.ColumnArea,
AreaIndex = 0,
Caption = "Year"
};
DataSourceColumnBinding fieldOrderDate1Binding = new DataSourceColumnBinding("OrderDate");
fieldOrderDate1Binding.GroupInterval = PivotGroupInterval.DateYear;
fieldOrderDate1.DataBinding = fieldOrderDate1Binding;
PivotGridField fieldOrderDate2 = new PivotGridField() {
Area = PivotArea.ColumnArea,
AreaIndex = 1,
Caption = "Quarter"
};
DataSourceColumnBinding fieldOrderDate2Binding = new DataSourceColumnBinding("OrderDate");
fieldOrderDate2Binding.GroupInterval = PivotGroupInterval.DateQuarter;
fieldOrderDate2.DataBinding = fieldOrderDate2Binding;
PivotGridField fieldCountry = new PivotGridField() {
AreaIndex = 0,
Caption = "Country"
};
DataSourceColumnBinding countryBinding = new DataSourceColumnBinding("Country");
fieldCountry.DataBinding = countryBinding;
// Create a field's filter.
fieldCountry.FilterValues.Clear();
fieldCountry.FilterValues.FilterType = PivotFilterType.Included;
fieldCountry.FilterValues.Add("USA");
// Add fields to the pivot grid.
pivotGridControl1.Fields.AddRange(new PivotGridField[] {
fieldCategoryName,
fieldProductName,
fieldOrderDate1,
fieldOrderDate2,
fieldExtendedPrice,
fieldCountry});
}
private void Form1_Load(object sender, EventArgs e) {
pivotGridControl1.BestFit();
}
}
}