How to: Create the Fields and Bind the PivotGridControl to a Data Source

• Jul 02, 2019
• 3 minutes to read

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.

C#

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();
        }
    }
}

VB.NET

Imports DevExpress.DataAccess.Excel
Imports DevExpress.XtraEditors
Imports DevExpress.XtraPivotGrid
Imports System

Namespace WinFormsPivotGridDataFieldsExample
    Partial Public Class Form1
        Inherits XtraForm

        Public Sub New()
            InitializeComponent()
            AddHandler Me.Load, AddressOf Form1_Load
            ' Create the Excel Data Source.
            Dim ds As New ExcelDataSource()
            ds.FileName = "SalesPerson.xlsx"
            Dim settings As New ExcelWorksheetSettings("Data")
            ds.SourceOptions = New ExcelSourceOptions(settings)
            ds.Fill()
            ' Set the pivot's data source.
            pivotGridControl1.DataSource = ds
            ' Create pivot grid fields.
            Dim fieldCategoryName As New PivotGridField() With {
                .Area = PivotArea.RowArea,
                .AreaIndex = 0,
                .Caption = "Category Name",
                .FieldName = "CategoryName"
            }
            Dim fieldProductName As New PivotGridField() With {
                .Area = PivotArea.RowArea,
                .AreaIndex = 1,
                .Caption = "Product Name",
                .FieldName = "ProductName"
            }
            Dim fieldExtendedPrice As New PivotGridField() With {
                .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"

            Dim fieldOrderDate1 As New PivotGridField() With {
                .Area = PivotArea.ColumnArea,
                .AreaIndex = 0,
                .Caption = "Year",
                .GroupInterval = PivotGroupInterval.DateYear,
                .FieldName = "OrderDate"
            }
            Dim fieldOrderDate2 As New PivotGridField() With {
                .Area = PivotArea.ColumnArea,
                .AreaIndex = 1,
                .Caption = "Quarter",
                .GroupInterval = PivotGroupInterval.DateQuarter,
                .FieldName = "OrderDate"
            }
            Dim fieldCountry As New PivotGridField() With {
                .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})
        End Sub

        Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
            pivotGridControl1.BestFit()
        End Sub
    End Class
End Namespace