Obtaining Underlying Data

• Apr 15, 2018
• 3 minutes to read

The Pivot Grid converts lengthy table data into compact reports that display summarized data. Cells display summaries calculated against data field(s) for a subset of the records in the pivot grid’s data source. All records from this subset have matching values in a column field(s) and row field(s). These values are identified by column and row headers.

To get the list of data source records for an individual cell, use the PivotGridControl.CreateDrillDownDataSource method.

Example: How to Obtain Underlying Data

The PivotGridControl supports the drill-down capability, which enables you to retrieve a list of records that can be used to calculate a particular summary. To obtain drill-down data, use the PivotGridControl‘s PivotCellBaseEventArgs.CreateDrillDownDataSource method. Its parameters completely identify a summary cell.

In this example, an end-user can view records from the control’s underlying data source, associated with a summary cell, by double-clicking it. The obtained data is displayed by the DXGrid within a popup window.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-obtain-underlying-data-e2173.

ViewModel.cs

using DevExpress.Mvvm;
using DevExpress.Mvvm.DataAnnotations;
using DevExpress.Mvvm.POCO;
using DevExpress.Xpf.PivotGrid;
using HowtoObtainUnderlyingData.NwindDataSetTableAdapters;

namespace HowtoObtainUnderlyingData {
    [POCOViewModel]
    public class ViewModel {
        NwindDataSet.SalesPersonDataTable salesPersonDataTable = new NwindDataSet.SalesPersonDataTable();
        SalesPersonTableAdapter salesPersonDataAdapter = new SalesPersonTableAdapter();

        public NwindDataSet.SalesPersonDataTable DataSource { get { return salesPersonDataTable; } }

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

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

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"
        xmlns:dxmvvm="http://schemas.devexpress.com/winfx/2008/xaml/mvvm"
        xmlns:local="clr-namespace:HowtoObtainUnderlyingData"
        xmlns:dx="http://schemas.devexpress.com/winfx/2008/xaml/core"
        xmlns:dxg="http://schemas.devexpress.com/winfx/2008/xaml/grid"
        x:Class="HowtoObtainUnderlyingData.MainWindow"   
        Title="MainWindow" Height="419" Width="776"
        DataContext="{dxmvvm:ViewModelSource Type=local:ViewModel}">
    <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 DataSource="{Binding DataSource}" x:Name="pivot">
            <dxpg:PivotGridControl.InputBindings>
                <KeyBinding Command="{Binding ShowDrillDownDataCommand}" CommandParameter="{Binding SelectedCellInfo, ElementName=pivot}" Key="Enter"/>
            </dxpg:PivotGridControl.InputBindings>
            <dxmvvm:Interaction.Triggers>
                <dxmvvm:EventToCommand PassEventArgsToCommand="True" Command="{Binding ShowDrillDownDataCommand}" EventName="MouseDoubleClick">
                    <dxmvvm:EventToCommand.EventArgsConverter>
                        <dxpg:EventArgsToCellInfoConverter />
                    </dxmvvm:EventToCommand.EventArgsConverter>
                </dxmvvm:EventToCommand>
            </dxmvvm:Interaction.Triggers>
            <dxpg:PivotGridControl.Fields>
                <dxpg:PivotGridField Name="fieldCountry" FieldName="Country" Area="RowArea" />
                <dxpg:PivotGridField Name="fieldCustomer" FieldName="Sales Person" Area="RowArea"
                                     Caption="Customer" />
                <dxpg:PivotGridField Name="fieldYear" FieldName="OrderDate" Area="ColumnArea" 
                                     Caption="Year" GroupInterval="DateYear" />
                <dxpg:PivotGridField Name="fieldCategoryName" FieldName="CategoryName" 
                                     Area="ColumnArea" Caption="Product Category" />
                <dxpg:PivotGridField Name="fieldProductName" FieldName="ProductName"
                                     Area="FilterArea" Caption="Product Name" />
                <dxpg:PivotGridField Name="fieldExtendedPrice" FieldName="Extended Price"
                                     Area="DataArea" CellFormat="c0" />
            </dxpg:PivotGridControl.Fields>
        </dxpg:PivotGridControl>
    </Grid>
</Window>

MainWindow.xaml.cs

using System.Windows;

namespace HowtoObtainUnderlyingData {
    public partial class MainWindow : Window {
        public MainWindow() {
            InitializeComponent();
        }
    }
}

Application.xaml

<Application x:Class="HowtoObtainUnderlyingData.App"
             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             StartupUri="MainWindow.xaml">
    <Application.Resources>

    </Application.Resources>
</Application>

ViewModel.vb

Imports DevExpress.Mvvm
Imports DevExpress.Mvvm.DataAnnotations
Imports DevExpress.Mvvm.POCO
Imports DevExpress.Xpf.PivotGrid
Imports NwindDataSetTableAdapters

Namespace HowtoObtainUnderlyingData
    <POCOViewModel>
    Public Class ViewModel
        Private salesPersonDataTable As New NwindDataSet.SalesPersonDataTable()
        Private salesPersonDataAdapter As New SalesPersonTableAdapter()

        Public ReadOnly Property DataSource() As NwindDataSet.SalesPersonDataTable
            Get
                Return salesPersonDataTable
            End Get
        End Property

        Protected Sub New()
            salesPersonDataAdapter.Fill(salesPersonDataTable)
        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

MainWindow.xaml.vb

Imports System.Windows

Namespace HowtoObtainUnderlyingData
    Partial Public Class MainWindow
        Inherits Window

        Public Sub New()
            InitializeComponent()
        End Sub
    End Class
End Namespace

Application.xaml.vb

Imports System
Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data
Imports System.Linq
Imports System.Windows

Namespace HowtoObtainUnderlyingData
    ''' <summary>
    ''' Interaction logic for App.xaml
    ''' </summary>
    Partial Public Class App
        Inherits Application

    End Class
End Namespace