Drill Down to the Underlying Data
- 6 minutes to read
A pivot grid cell value is a summary calculated against a data field for a subset of records retrieved from the PivotGrid’s underlying data source.
Consider the pivot grid shown in the picture below.
The highlighted cell value $8,467.72 is calculated as a sum of Extended Price field values for the records that meet the following criteria:
- the Country and City field values are equal to the row field values (Austria and Graz, respectively)
- the Order Year and Order Month field values are equal to the column field values (2017 and 1, respectively)
The cell’s underlying data is shown in the following picture:
To get underlying records for a particular cell, use the CreateDrillDownDataSource method. The following table shows how to access this method in different situations.
| Situation | Instruction |
|---|---|
| Click or double-click the cell | Handle the PivotGridControl.CellClick or PivotGridControl.CellDoubleClick event and call the e.CreateDrillDownDataSource method. |
| Hit test | Call the PivotGridControl.CalcHitInfo method to get a HitInfo object for a test point. If the point belongs to a cell, use the PivotGridHitInfo.CellInfo property to get the PivotCellEventArgs object. Call its CreateDrillDownDataSource method. |
| Get data for a particular cell | Call the PivotGridControl.CreateDrillDownDataSource method. |
| Get data for a particular cell asynchronously | Call the PivotGridControl.CreateDrillDownDataSourceAsync method. |
| Get data for a particular cell | Use the GetCellInfo and GetFocusedCellInfo methods of the PivotGridCells object accessible with the PivotGridControl.Cells property. Those methods return the PivotCellEventArgs object. Call its CreateDrillDownDataSource method. |
| Calculate custom summary | Handle the PivotGridControl.CustomSummary event and call the e.CreateDrillDownDataSource method. |
| Perform custom draw | Handle the PivotGridControl.CustomDrawCell event and call the CreateDrillDownDataSource method. |
| Display custom text | Handle the PivotGridControl.CustomCellDisplayText and call the e.CreateDrillDownDataSource method. |
| Display custom images | Handle the PivotGridControl.FieldValueImageIndex event and call the e.CreateDrillDownDataSource method. |
| Customize cells in printout or exported document | Handle the CustomExportCell event and call the e.CreateDrillDownDataSource method. |
Example: How to Display the Underlying Records
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 e.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 e.CreateDrillDownDataSource(int maxRowCount, List<string> customColumns 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.
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();
}
}
}