Obtaining Underlying Data (Drill-Down)

• Nov 13, 2018
• 3 minutes to read

Each cell displays a summary calculated against a data field for a subset of records in the PivotGrid’s underlying data source. All records from this subset have matching values in a column field(s) and row field(s), and these values are identified by column and row headers. To get the underlying records for a particular cell, use the PivotGridControl.CreateDrillDownDataSource method.

Consider the following PivotGridControl.

For the top-leftmost cell ($1,500.00), the CreateDrillDownDataSource method will return records from the PivotGrid data source that have:

• the value “Bon app’” in the ‘Customer’ field;
• the value “Carnarvon Tigers” in the ‘Product Name’ field;
• the 1995 value in the ‘Order Year’ field.

For the cell ($2,435.00) at the intersection of the first column and the fourth row, the CreateDrillDownDataSource method will return records that have:

• the value “Bon app’” in the ‘Customer’ field;
• the 1995 value in the ‘Order Year’ field.

To get information on how to access the CreateDrillDownDataSource method, refer to the Member Table: Get Underlying Data topic.

Example: How to Display Underlying Records

The following example shows how to display the records from the control’s underlying data source which correspond to a particular cell. A new form that displays these records is opened when a particular cell in the PivotGrid control is double-clicked.

In the example the PivotGridControl.CellDoubleClick event is handled. The PivotCellEventArgsBase<TField, TData, TCustomTotal>.CreateDrillDownDataSource method is called to obtain the list of records associated with the clicked cell.

The following image shows a sample PivotGrid control which is bound to the Invoices table in the nwind.mdb database:

Clicking the third cell in the 1994 column will invoke the following form:

It lists all the orders made in 1994 by Belgian customers.

C#

using DevExpress.XtraPivotGrid;

private void pivotGridControl1_CellDoubleClick(object sender, PivotCellEventArgs e) {
   // Create a new form.
   Form form = new Form();
   form.Text = "Records";
   // Place a DataGrid control on the form.
   DataGrid grid = new DataGrid();
   grid.Parent = form;
   grid.Dock = DockStyle.Fill;
   // Get the recrd set associated with the current cell and bind it to the grid.
   grid.DataSource = e.CreateDrillDownDataSource();
   form.Bounds = new Rectangle(100, 100, 500, 400);
   // Display the form.
   form.ShowDialog();
   form.Dispose();
}

VB.NET

Imports DevExpress.XtraPivotGrid

Private Sub pivotGridControl1_CellDoubleClick(ByVal sender As Object, _
  ByVal e As PivotCellEventArgs) Handles pivotGridControl1.CellDoubleClick
   ' Create a new form.
   Dim form As Form = New Form
   form.Text = "Records"
   ' Place a DataGrid control on the form.
   Dim grid As DataGrid = New DataGrid
   grid.Parent = form
   grid.Dock = DockStyle.Fill
   ' Get the recrd set associated with the current cell and bind it to the grid.
   grid.DataSource = e.CreateDrillDownDataSource()
   form.Bounds = New Rectangle(100, 100, 500, 400)
   ' Display the form.
   form.ShowDialog()
   form.Dispose()
End Sub