How to: Display Underlying Records

• Jun 27, 2019
• 4 minutes to read

This example demonstrates how to obtain the records from the control’s underlying data source for a particular cell. Double-click a cell to invoke a form that contains a grid to show the underlying data.

Note

The complete sample project How to: Display Underlying (Drill-Down) Records is available in the DevExpress Examples repository.

The primary data source is the Northwind database contained in the SQL Server data file NW.mdf. The application can use the BindingSource component or the LinqServerModeDataSource instance to retrieve the data from the database. A LinqServerModeDataSource data source is a queryable data source, and it forces the PivotGrid to operate in server mode. Click the Server Mode toggle switch control to switch from one data source to another.

When you double-click the PivotGrid cell, the PivotGridControl.CellDoubleClick event occurs. The following CreateDrillDownDataSource method overrides are called to obtain the list of records associated with the selected cell:

• If Server Mode is not enabled, the PivotCellEventArgsBase<TField, TData, TCustomTotal>.CreateDrillDownDataSource(Int32) method returns a data source that contains the columns retrieved from the original data source as well as the columns which the PivotGrid creates.
• If Server Mode is enabled, the PivotCellEventArgsBase<TField, TData, TCustomTotal>.CreateDrillDownDataSource(Int32, List<String>) method returns a data source that contains the columns shown in the PivotGridControl and the columns explicitly specified in the method parameter.

You can also click the Get Grand Total Data button to call the PivotGridControl.CreateDrillDownDataSource method and display all data records that the PivotGridControl uses to show the summarized data.

C#

using DevExpress.XtraEditors;
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Views.Grid;
using DevExpress.XtraPivotGrid;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Windows.Forms;

namespace DrillDownDataSourceExample
{
    public partial class Form1 : DevExpress.XtraEditors.XtraForm
    {
        bool serverMode = false;
        public Form1()
        {
            InitializeComponent();
            pivotGridControl1.CellDoubleClick += PivotGridControl1_CellDoubleClick;
            // This line of code is generated by Data Source Configuration Wizard
            linqServerModeSource1.QueryableSource = new DrillDownDataSourceExample.DataClasses1DataContext().Invoices;
        }

        private void PivotGridControl1_CellDoubleClick(object sender, DevExpress.XtraPivotGrid.PivotCellEventArgs e)
        {
            PivotDrillDownDataSource drillDownDataSource;
            if (serverMode)
               drillDownDataSource = e.CreateDrillDownDataSource(25, new List<string> { "ShipName" });
            else
                drillDownDataSource = e.CreateDrillDownDataSource(25);
            XtraForm dataform = CreateDrillDownForm(drillDownDataSource);
            dataform.ShowDialog();
            dataform.Dispose();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'nWDataSet.Invoices' table. You can move, or remove it, as needed.
            this.invoicesTableAdapter.Fill(this.nWDataSet.Invoices);
            SetPivotGridDataSource();
        }

        private XtraForm CreateDrillDownForm(PivotDrillDownDataSource dataSource) {
            XtraForm form = new XtraForm();
            GridControl grid = new GridControl();
            grid.Parent = form;
            grid.Dock = DockStyle.Fill;
            grid.DataSource = dataSource;
            grid.DataSource =
            form.Bounds = new Rectangle(100, 100, 800, 400);
            GridView gridView1 = new GridView();
            grid.MainView = gridView1;
            gridView1.Columns["OrderDate"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            form.Text = string.Format("Underlying Data - {0} Records", dataSource.RowCount);
            return form;
        }

        private void toggleSwitch1_Toggled(object sender, EventArgs e)
        {
            serverMode = ((ToggleSwitch)sender).IsOn;
            SetPivotGridDataSource();
        }

        private void SetPivotGridDataSource()
        {
            if (serverMode)
                pivotGridControl1.DataSource = linqServerModeSource1;
            else
                pivotGridControl1.DataSource = invoicesBindingSource;
        }

        private void btnGrandTotal_Click(object sender, EventArgs e)
        {
            PivotDrillDownDataSource drillDownDataSource = pivotGridControl1.CreateDrillDownDataSource();
            Form dataform = CreateDrillDownForm(drillDownDataSource);
            dataform.ShowDialog();
            dataform.Dispose();
        }
    }
}

VB.NET

Imports DevExpress.XtraEditors
Imports DevExpress.XtraGrid
Imports DevExpress.XtraGrid.Views.Grid
Imports DevExpress.XtraPivotGrid
Imports System
Imports System.Collections.Generic
Imports System.Drawing
Imports System.Windows.Forms

Namespace DrillDownDataSourceExample
   Partial Public Class Form1
      Inherits DevExpress.XtraEditors.XtraForm

      Private serverMode As Boolean = False
      Public Sub New()
         InitializeComponent()
         AddHandler pivotGridControl1.CellDoubleClick, AddressOf PivotGridControl1_CellDoubleClick
         ' This line of code is generated by Data Source Configuration Wizard
         linqServerModeSource1.QueryableSource = (New DrillDownDataSourceExample.DataClasses1DataContext()).Invoices
      End Sub

      Private Sub PivotGridControl1_CellDoubleClick(ByVal sender As Object, ByVal e As DevExpress.XtraPivotGrid.PivotCellEventArgs)
         Dim drillDownDataSource As PivotDrillDownDataSource
         If serverMode Then
            drillDownDataSource = e.CreateDrillDownDataSource(25, New List(Of String) From {"ShipName"})
         Else
            drillDownDataSource = e.CreateDrillDownDataSource(25)
         End If
         Dim dataform As XtraForm = CreateDrillDownForm(drillDownDataSource)
         dataform.ShowDialog()
         dataform.Dispose()
      End Sub

      Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
         ' TODO: This line of code loads data into the 'nWDataSet.Invoices' table. You can move, or remove it, as needed.
         Me.invoicesTableAdapter.Fill(Me.nWDataSet.Invoices)
         SetPivotGridDataSource()
      End Sub

      Private Function CreateDrillDownForm(ByVal dataSource As PivotDrillDownDataSource) As XtraForm
         Dim form As New XtraForm()
         Dim grid As New GridControl()
         grid.Parent = form
         grid.Dock = DockStyle.Fill
         grid.DataSource = dataSource
         form.Bounds = New Rectangle(100, 100, 800, 400)
         grid.DataSource = form.Bounds
         Dim gridView1 As New GridView()
         grid.MainView = gridView1
         gridView1.Columns("OrderDate").DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime
         form.Text = String.Format("Underlying Data - {0} Records", dataSource.RowCount)
         Return form
      End Function

      Private Sub toggleSwitch1_Toggled(ByVal sender As Object, ByVal e As EventArgs) Handles toggleSwitch1.Toggled
         serverMode = DirectCast(sender, ToggleSwitch).IsOn
         SetPivotGridDataSource()
      End Sub

      Private Sub SetPivotGridDataSource()
         If serverMode Then
            pivotGridControl1.DataSource = linqServerModeSource1
         Else
            pivotGridControl1.DataSource = invoicesBindingSource
         End If
      End Sub

      Private Sub btnGrandTotal_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnGrandTotal.Click
         Dim drillDownDataSource As PivotDrillDownDataSource = pivotGridControl1.CreateDrillDownDataSource()
         Dim dataform As Form = CreateDrillDownForm(drillDownDataSource)
         dataform.ShowDialog()
         dataform.Dispose()
      End Sub
   End Class
End Namespace