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.v17.2.dll
Declaration
[ToolboxTabName("DX.17.2: Data & Analytics")]
[Docking(DockingBehavior.Ask)]
[OLAPDataAccessMetadata("All", SupportedProcessingModes = "Pivot", EnableBindingToXPOServerMode = false)]
[ToolboxBitmap(typeof(PivotGridControl), "Bitmaps256.PivotGridControl.bmp")]
public class PivotGridControl :
BaseViewInfoControl,
IFilteringUIClient,
IFilterCriteriaBindingAware,
IComponentLoading,
ISupportLookAndFeel,
IToolTipControlClient,
IPivotGridEventsImplementor,
IPivotGridEventsImplementorBase,
ICustomizationFormOwner,
IPivotGridDataOwner,
IDXManagerPopupMenu,
IBindingList,
IList,
ICollection,
IEnumerable,
ITypedList,
IPivotGrid,
IChartDataSource,
IPivotGridViewInfoDataOwner,
IPivotGridPrinterOwner,
IPrintable,
IBasePrintable,
IDataContainerBase,
IMouseWheelSupport,
ISupportXtraSerializer,
IThreadSafeAccessible,
IXtraSerializable,
IXtraSerializableLayout,
IXtraSerializableLayoutEx,
IXtraSupportDeserializeCollectionItem,
IXtraSupportDeserializeCollection,
IPivotGestureClient,
IGestureClient,
IMouseWheelScrollClient,
IOptionsLayoutProvider,
IFilteredComponent,
IFilteredComponentBaseRelated API Members
The following members accept/return PivotGridControl objects:
| Library | Related API Members |
|---|---|
| WinForms Controls | FilterPopupExcelQueryFilterCriteriaEventArgs.PivotGrid |
| PivotGridField.PivotGrid | |
| PivotGridStyleFormatCondition.PivotGrid | |
| Dashboard | DashboardItemControlEventArgs.PivotGridControl |
| eXpressApp Framework | PivotGridListEditor.PivotGridControl |
Remarks
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. Two types of fields are supported: bound fields (which get their data from the data source) and unbound fields (which should be populated manually via the PivotGridControl.CustomUnboundFieldData event).
Example
The following example demonstrates how to bind the PivotGridControl to a view in the Northwind database. The control will be used to analyze sales per customer, country, product category and year.
The connection to a data source is established via the OleDbConnection component. The OleDbDataAdapter component is used to fill the DataSet with records from the SalesPerson view.
The Pivot Grid Control is bound to a table in the dataset using the PivotGridControl.DataSource property. The created Pivot Grid Control fields represent datasource fields. They are positioned within appropriate areas to analyze the data in the required way.
using DevExpress.LookAndFeel;
using DevExpress.XtraPivotGrid;
using System.Data.OleDb;
// Create a connection object.
OleDbConnection connection =
new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\..\\NWIND.MDB");
// Create a data adapter.
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM SalesPerson", connection);
// Create and fill a dataset.
DataSet sourceDataSet = new DataSet();
adapter.Fill(sourceDataSet, "SalesPerson");
// Assign the data source to the PivotGrid control.
pivotGridControl1.DataSource = sourceDataSet.Tables["SalesPerson"];
// Create a row Pivot Grid Control field bound to the Country datasource field.
PivotGridField fieldCountry = new PivotGridField("Country", PivotArea.RowArea);
// Create a row Pivot Grid Control field bound to the Sales Person datasource field.
PivotGridField fieldCustomer = new PivotGridField("Sales Person", PivotArea.RowArea);
fieldCustomer.Caption = "Customer";
// Create a column Pivot Grid Control field bound to the OrderDate datasource field.
PivotGridField fieldYear = new PivotGridField("OrderDate", PivotArea.ColumnArea);
fieldYear.Caption = "Year";
// Group field values by years.
fieldYear.GroupInterval = PivotGroupInterval.DateYear;
// Create a column Pivot Grid Control field bound to the CategoryName datasource field.
PivotGridField fieldCategoryName = new PivotGridField("CategoryName", PivotArea.ColumnArea);
fieldCategoryName.Caption = "Product Category";
// Create a filter Pivot Grid Control field bound to the ProductName datasource field.
PivotGridField fieldProductName = new PivotGridField("ProductName", PivotArea.FilterArea);
fieldProductName.Caption = "Product Name";
// Create a data Pivot Grid Control field bound to the 'Extended Price' datasource field.
PivotGridField fieldExtendedPrice = new PivotGridField("Extended Price", PivotArea.DataArea);
fieldExtendedPrice.CellFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
// Specify the formatting setting to format summary values as integer currency amount.
fieldExtendedPrice.CellFormat.FormatString = "c0";
// Add the fields to the control's field collection.
pivotGridControl1.Fields.AddRange(new PivotGridField[] {fieldCountry, fieldCustomer,
fieldCategoryName, fieldProductName, fieldYear, fieldExtendedPrice});
// Arrange the row fields within the Row Header Area.
fieldCountry.AreaIndex = 0;
fieldCustomer.AreaIndex = 1;
// Arrange the column fields within the Column Header Area.
fieldCategoryName.AreaIndex = 0;
fieldYear.AreaIndex = 1;
// Customize the control's look-and-feel via the Default LookAndFeel object.
UserLookAndFeel.Default.Style = LookAndFeelStyle.Skin;
UserLookAndFeel.Default.SkinName = "Visual Studio 2013 Blue";
Related GitHub Examples
The following code snippets (auto-collected from DevExpress Examples) contain references to the PivotGridControl class.
Note
The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.