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.v20.1.dll

Declaration

[OLAPDataAccessMetadata("All", SupportedProcessingModes = "Pivot", EnableBindingToXPOServerMode = false)]
public class PivotGridControl :
    BaseViewInfoControl,
    IFilteringUIClient,
    IFilterCriteriaBindingAware,
    IComponentLoading,
    ISupportAsyncScrollAnimation,
    ISupportLookAndFeel,
    IPrintable,
    IBasePrintable,
    IToolTipControlClient,
    IPivotGridEventsImplementor,
    IPivotGridEventsImplementorBase,
    ICustomizationFormOwner,
    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,
    IPropertyManagerProvider,
    IFilterPanelOwner,
    ISupportFilterCriteriaDisplayStyle
<OLAPDataAccessMetadata("All", SupportedProcessingModes:="Pivot", EnableBindingToXPOServerMode:=False)>
Public Class PivotGridControl
    Inherits BaseViewInfoControl
    Implements IFilteringUIClient,
               IFilterCriteriaBindingAware,
               IComponentLoading,
               ISupportAsyncScrollAnimation,
               ISupportLookAndFeel,
               IPrintable,
               IBasePrintable,
               IToolTipControlClient,
               IPivotGridEventsImplementor,
               IPivotGridEventsImplementorBase,
               ICustomizationFormOwner,
               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,
               IPropertyManagerProvider,
               IFilterPanelOwner,
               ISupportFilterCriteriaDisplayStyle

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).

Examples

This example demonstrates how to create the pivot grid fields in code and specify their location and format. The PivotGridControl's data source is the ExcelDataSource instance, created in code.

PivotGridControl-Fields-DataSource

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",
                FieldName = "CategoryName"
            };
            PivotGridField fieldProductName = new PivotGridField()
            {
                Area = PivotArea.RowArea,
                AreaIndex = 1,
                Caption = "Product Name",
                FieldName = "ProductName"
            };
            PivotGridField fieldExtendedPrice = new PivotGridField()
            {
                Area = PivotArea.DataArea,
                AreaIndex = 0,
                Caption = "Extended Price",
                FieldName = "Extended Price",
            };
            // 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",
                GroupInterval = PivotGroupInterval.DateYear,
                FieldName = "OrderDate",
            };
            PivotGridField fieldOrderDate2 = new PivotGridField()
            {
                Area = PivotArea.ColumnArea,
                AreaIndex = 1,
                Caption = "Quarter",
                GroupInterval = PivotGroupInterval.DateQuarter,
                FieldName = "OrderDate"
            };
            PivotGridField fieldCountry = new PivotGridField()
            {
                AreaIndex = 0,
                Caption = "Country",
                FieldName = "Country"
            };
            // 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();
        }
    }
}

Implements

Inheritance

Object
MarshalByRefObject
Component
Control
EditorContainer
DevExpress.XtraPivotGrid.ViewInfo.BaseControl
DevExpress.XtraPivotGrid.ViewInfo.BaseViewInfoControl
PivotGridControl
See Also