How to: Display Unbound Data in a PivotGrid Control

• Apr 15, 2018
• 2 minutes to read

This example demonstrates how to add an unbound field to the PivotGridControl, to show the total sum of an order.

The PivotGridControl is bound to the Order Details data table (from the nwind sample database), which contains UnitPrice, Quantity and Discount fields. The total sum is calculated as follows: UnitPriceQuantity(1-Discount).

To solve this task, create a PivotGrid’s field and set its PivotGridField.UnboundType property to FieldUnboundColumnType.Decimal. Then, handle the PivotGridControl.CustomUnboundFieldData event and populate the field with data.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-display-unbound-data-in-a-pivotgrid-control-e2122.

MainWindow.xaml

<Window xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid" 
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" 
        Title="MainWindow" Height="395" Width="585" 
        x:Class="HowToBindToMDB.MainWindow"
        Loaded="Window_Loaded">
    <Grid>
        <dxpg:PivotGridControl CustomUnboundFieldData="pivotGridControl1_CustomUnboundFieldData"
                               HorizontalAlignment="Left" Name="pivotGridControl1"
                               VerticalAlignment="Top">
            <dxpg:PivotGridControl.Fields>
                <dxpg:PivotGridField Name="fieldOrderID" FieldName="OrderID" Area="RowArea"/>
                <dxpg:PivotGridField Name="fieldProductName" FieldName="ProductName" Area="RowArea"/>
                <dxpg:PivotGridField Name="fieldUnitPrice" FieldName="UnitPrice" Area="DataArea"/>
                <dxpg:PivotGridField Name="fieldQuantity" FieldName="Quantity" Area="DataArea"/>
                <dxpg:PivotGridField Name="fieldDiscount" FieldName="Discount" Area="DataArea"
                                     CellFormat="p"/>
                <dxpg:PivotGridField Name="fieldTotalSum" UnboundType="Decimal" Area="DataArea"
                                     Caption="Total Sum"/>
            </dxpg:PivotGridControl.Fields>
        </dxpg:PivotGridControl>
    </Grid>
</Window>

MainWindow.xaml.vb

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.OleDb
Imports System.Windows
Imports DevExpress.Xpf.PivotGrid
Imports HowToBindToMDB.NwindDataSetTableAdapters
Imports System

Namespace HowToBindToMDB
    Partial Public Class MainWindow
        Inherits Window
        Private dataTable As New NwindDataSet.OrderDetailsDataTable()
        Private dataAdapter As New OrderDetailsTableAdapter()

        Public Sub New()
            InitializeComponent()
            pivotGridControl1.DataSource = dataTable
        End Sub

        Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
            dataAdapter.Fill(dataTable)
        End Sub

        Private Sub pivotGridControl1_CustomUnboundFieldData(ByVal sender As Object, _
                                                             ByVal e As PivotCustomFieldDataEventArgs)
            If e.Field Is fieldTotalSum Then
                Dim unitPrice As Decimal = Convert.ToDecimal(e.GetListSourceColumnValue("UnitPrice"))
                Dim qty As Integer = Convert.ToInt32(e.GetListSourceColumnValue("Quantity"))
                Dim discount As Decimal = Convert.ToDecimal(e.GetListSourceColumnValue("Discount"))
                e.Value = unitPrice * qty * (1 - discount)
            End If
        End Sub
    End Class
End Namespace

MainWindow.xaml.cs

using System.Data;
using System.Data.OleDb;
using System.Windows;
using DevExpress.Xpf.PivotGrid;
using HowToBindToMDB.NwindDataSetTableAdapters;
using System;

namespace HowToBindToMDB {
    public partial class MainWindow : Window {
        NwindDataSet.OrderDetailsDataTable dataTable = new NwindDataSet.OrderDetailsDataTable();
        OrderDetailsTableAdapter dataAdapter = new OrderDetailsTableAdapter();

        public MainWindow() {
            InitializeComponent();
            pivotGridControl1.DataSource = dataTable;
        }

        private void Window_Loaded(object sender, RoutedEventArgs e) {
            dataAdapter.Fill(dataTable);
        }

        private void pivotGridControl1_CustomUnboundFieldData(object sender, 
                PivotCustomFieldDataEventArgs e) {
            if(e.Field == fieldTotalSum) {
                decimal unitPrice = Convert.ToDecimal(e.GetListSourceColumnValue("UnitPrice"));
                int qty = Convert.ToInt32(e.GetListSourceColumnValue("Quantity"));
                decimal discount = Convert.ToDecimal(e.GetListSourceColumnValue("Discount"));
                e.Value = unitPrice * qty * (1 - discount);
            }
        }
    }
}