Drill Down to the Underlying Data

Jul 05, 2019
4 minutes to read

A pivot grid cell‘s value is a summary calculated against a data field for a subset of records retrieved from the PivotGrid’s underlying data source.

In the previous image, the pivot grid’s highlighted cell value ( $88.640.00 ) is calculated as a sum of Price field values for the records that meet the following criteria:

the Model field value is equal to the row field value ( S-Type 3.0 )
the Order Date field value is equal to the column field value ( 11/14/2002 )

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.
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 . Those methods return the PivotCellBaseEventArgs object. Call its CreateDrillDownDataSource method.
Calculate custom summary	Handle the PivotGridControl.CustomSummary event and call the e.CreateDrillDownDataSource method.
Customize cell foreground and background for display and export	Handle the PivotGridControl.CustomValueAppearance and call the e.CreateDrillDownDataSource method.
Display custom text	Handle the PivotGridControl.FieldValueDisplayText event and call the e.CreateDrillDownDataSource method..

Example: How to Obtain Underlying Data

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 with the underlying data.

Note

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

This example is based on the DevExpress MVVM Framework. When a user double-clicks a cell, the EventToCommand class invokes the bound ShowDrillDownDataCommand defined in the ViewModel.

To pass the event data as a parameter to the command, the EventToCommand.PassEventArgsToCommand property is set to true. The EventArgsToCellInfoConverter instance is assigned to the EventToCommand.EventArgsConverter property to convert the event data to the CellInfo parameter type required for the command.

The command calls the DialogService.ShowDialog method to invoke a custom window that displays the underlying data. The DialogService is a part of the DevExpress MVVM Framework. It is defined in XAML and specifies the DXDialogWindow that contains the GridControl bound to the CellInfo.DrillDownDataSource property.

ViewModel.cs
MainWindow.xaml
ViewModel.vb

csharp

using DevExpress.Mvvm;
using DevExpress.Mvvm.DataAnnotations;
using DevExpress.Mvvm.POCO;
using DevExpress.Xpf.PivotGrid;
using HowToObtainUnderlyingData.NWindDataSetTableAdapters;
using static HowToObtainUnderlyingData.NWindDataSet;

namespace HowToObtainUnderlyingData
{
    [POCOViewModel]
    public class ViewModel {
        SalesPersonTableAdapter salesPersonDataAdapter = new SalesPersonTableAdapter();
        public SalesPersonDataTable DataSource { get; } = new SalesPersonDataTable();

        protected ViewModel() {
            salesPersonDataAdapter.Fill(DataSource);
        }

        public void ShowDrillDownData(CellInfo cellInfo) {
            this.GetService<IDialogService>().ShowDialog(MessageButton.OK, "Drill Down Results", cellInfo);
        }
    }
}

xaml

<Window
    x:Class="HowToObtainUnderlyingData.MainWindow"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:dx="http://schemas.devexpress.com/winfx/2008/xaml/core"
    xmlns:dxg="http://schemas.devexpress.com/winfx/2008/xaml/grid"
    xmlns:dxmvvm="http://schemas.devexpress.com/winfx/2008/xaml/mvvm"
    xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid"
    xmlns:local="clr-namespace:HowToObtainUnderlyingData"
    Width="776"
    Height="419"
    DataContext="{dxmvvm:ViewModelSource Type=local:ViewModel}"
    Title="MainWindow">

    <dxmvvm:Interaction.Behaviors>
        <dx:DialogService Name="DrillDownTemplate">
            <dx:DialogService.DialogStyle>
                <Style TargetType="dx:DXDialogWindow">
                    <Setter Property="Height" Value="400" />
                    <Setter Property="Width" Value="600" />
                    <Setter Property="ShowIcon" Value="False" />
                    <Setter Property="ShowInTaskbar" Value="False" />
                    <Setter Property="WindowStyle" Value="ToolWindow" />
                </Style>
            </dx:DialogService.DialogStyle>
            <dx:DialogService.ViewTemplate>
                <DataTemplate>
                    <dxg:GridControl ItemsSource="{Binding DrillDownDataSource}">
                        <dxg:GridControl.Columns>
                            <dxg:GridColumn FieldName="OrderID" />
                            <dxg:GridColumn FieldName="OrderDate" />
                            <dxg:GridColumn FieldName="ProductName" />
                            <dxg:GridColumn FieldName="Extended Price" Header="Price" />
                        </dxg:GridControl.Columns>
                    </dxg:GridControl>
                </DataTemplate>
            </dx:DialogService.ViewTemplate>
        </dx:DialogService>
    </dxmvvm:Interaction.Behaviors>

    <Grid>
        <dxpg:PivotGridControl x:Name="pivot" DataSource="{Binding DataSource}" RowTreeWidth="170">
            <dxpg:PivotGridControl.InputBindings>
                <KeyBinding
                    Key="Enter"
                    Command="{Binding ShowDrillDownDataCommand}"
                    CommandParameter="{Binding SelectedCellInfo, ElementName=pivot}" />
            </dxpg:PivotGridControl.InputBindings>
            <dxmvvm:Interaction.Triggers>
                <dxmvvm:EventToCommand
                    Command="{Binding ShowDrillDownDataCommand}"
                    EventName="MouseDoubleClick"
                    PassEventArgsToCommand="True">
                    <dxmvvm:EventToCommand.EventArgsConverter>
                        <dxpg:EventArgsToCellInfoConverter />
                    </dxmvvm:EventToCommand.EventArgsConverter>
                </dxmvvm:EventToCommand>
            </dxmvvm:Interaction.Triggers>
            <dxpg:PivotGridControl.Fields>
                <dxpg:PivotGridField
                    Area="RowArea"
                    FieldName="Country"
                    Name="fieldCountry" />
                <dxpg:PivotGridField
                    Area="RowArea"
                    Caption="Customer"
                    FieldName="Sales Person"
                    Name="fieldCustomer" />
                <dxpg:PivotGridField
                    Area="ColumnArea"
                    Caption="Year"
                    FieldName="OrderDate"
                    GroupInterval="DateYear"
                    Name="fieldYear" />
                <dxpg:PivotGridField
                    Area="ColumnArea"
                    Caption="Product Category"
                    FieldName="CategoryName"
                    Name="fieldCategoryName" />
                <dxpg:PivotGridField
                    Area="FilterArea"
                    Caption="Product Name"
                    FieldName="ProductName"
                    Name="fieldProductName" />
                <dxpg:PivotGridField
                    Area="DataArea"
                    CellFormat="c0"
                    FieldName="Extended Price"
                    Name="fieldExtendedPrice" />
            </dxpg:PivotGridControl.Fields>
        </dxpg:PivotGridControl>
    </Grid>

</Window>

Imports DevExpress.Mvvm
Imports DevExpress.Mvvm.DataAnnotations
Imports DevExpress.Mvvm.POCO
Imports DevExpress.Xpf.PivotGrid
Imports HowToObtainUnderlyingData.NWindDataSetTableAdapters
Imports HowToObtainUnderlyingData.NWindDataSet

Namespace HowToObtainUnderlyingData
    <POCOViewModel>
    Public Class ViewModel
        Private salesPersonDataAdapter As New SalesPersonTableAdapter()
        Public ReadOnly Property DataSource() As New SalesPersonDataTable()

        Protected Sub New()
            salesPersonDataAdapter.Fill(DataSource)
        End Sub

        Public Sub ShowDrillDownData(ByVal cellInfo As CellInfo)
            Me.GetService(Of IDialogService)().ShowDialog(MessageButton.OK, "Drill Down Results", cellInfo)
        End Sub
    End Class
End Namespace