Tutorial 2 - Populate the Pivot Grid with Data and Configure Field Layout

• Sep 12, 2022
• 4 minutes to read

The tutorial shows how to display data from the connected data source in the Pivot Grid. In the previous tutorial, you connected the Pivot Grid to the Northwind SQLite Database. This tutorial explains how to create Pivot Grid fields, bind them to data from the connected data source, and configure the Pivot Grid layout.

Create Pivot Grid Fields and Bind them to Data

Open the Pivot Grid’s smart tag menu and click Run Designer to invoke the PivotGrid Designer.

The Field List pane in the Fields page displays data source columns that you can bind to Pivot Grid fields. Click Show Field List to display all available fields.

Click Retrieve Fields to create Pivot Grid Fields for all available data source columns. The created Pivot Grid fields are bound to the corresponding data source columns.

Note

You can also drag and drop a data source column from the Field List to the PivotGrid Fields pane to create the field for a specific data source column.

The following field properties are set automatically:

PivotGridFieldBase.DataBinding

Gets or sets an object that specifies the Pivot Grid field’s source data.

DataSourceColumnBindingBase.ColumnName

Gets or sets the name of the data source column.

PivotGridFieldBase.Area

Gets or sets the area in which the field is displayed.

PivotGridFieldBase.AreaIndex

Gets or sets the field’s index from among the other fields displayed within the same area.

PivotGridFieldBase.Caption

Gets or sets the field’s display caption.

After you have retrieved fields, the Pivot Grid looks as follows:

Arrange Pivot Grid Fields

You can place Pivot Grid Fields into the following four areas:

Column Area

Lists field values along the control’s top edge. Field values are column headers.

Row Area

Lists field values along the control’s left edge. Field values are row headers.

Data Area

Holds fields whose values take part in summary calculations. The control displays these summary values in data cells.

Filter Area

Displays headers of filter fields. Filter fields allow users to filter the entire Pivot Grid to display data for the predefined values from the filter dropdown list.

To arrange fields within areas and display data in the Pivot Grid, specify the field’s Area and AreaIndex properties. You can do it in the following ways:

• Specify Area and AreaIndex properties of each field in the Properties pane of the PivotGrid Designer Fields page.

• Drag and drop Pivot Grid fields to different areas on the form.

Arrange Pivot Grid fields as shown in the image above:

“Order Date” Field

Area: ColumnArea

AreaIndex: 0

“Extended Price” Field

Area: DataArea

AreaIndex: 0

“Category Name” Field

Area: RowArea

AreaIndex: 0

“Product Name” Field

Area: RowArea

AreaIndex: 1

Group Field Values

Run the application to see the arranged Pivot Grid fields.

When you drop multiple fields onto the same area, the Pivot Grid combines them into the hierarchy on an axis. The image above displays the Pivot Grid, where the product sales are grouped by category (the Category → Product hierarchy). Users can browse data summarized by categories and view details for each product within the Pivot Grid.

The Order Date field displays detailed sales by day. The Pivot Grid allows you to combine unique field values into groups. Set the field’s GroupInterval property to DateYear in the Properties pane of the Order Date field to display product sales grouped by years.

The image below displays product sales per year:

Adjust Pivot Grid Columns

Call the PivotGridControl.BestFit() method to resize all Pivot Grid columns to fit their contents.

Form1.cs

using System.Windows.Forms;

namespace WinPivot_GettingStarted {
    public partial class Form1 : Form {
        public Form1() {
            InitializeComponent();
            // This line of code is generated by Data Source Configuration Wizard
            // Fill the SqlDataSource
            sqlDataSource1.Fill();
            pivotGridControl1.BestFit();
        }
    }
}

Form1.vb

Imports System.Windows.Forms

Namespace WinPivot_GettingStarted
    Partial Public Class Form1
        Inherits Form

        Public Sub New()
            InitializeComponent()
            ' This line of code is generated by Data Source Configuration Wizard
            ' Fill the SqlDataSource
            sqlDataSource1.Fill()
            pivotGridControl1.BestFit()
        End Sub
    End Class
End Namespace

Result

The following image illustrates the resulting UI:

Related Documentation

The following help topics contain information about the functionality used in the tutorial:

• Pivot Grid Fields
• Populate Pivot Grid Fields with Data
• Hierarchical Value Presentation
• Grouping

Next Step

In the next tutorial, you analyze data with basic features of the Pivot Grid. You add the percentage variance between annual sales and sort displayed data.

Tutorial 3 - Configure Displayed Data for Analysis