How to: Implement Custom Summary

• Jun 27, 2019
• 2 minutes to read

This example demonstrates how to calculate a custom summary to display the distinct value count.

When the control calculates the summary value for the field whose SummaryType is PivotSummaryType.Custom, the PivotGridControl.CustomSummary event occurs. The event handler calls the e.CreateDrillDownDataSource method to retrieve the underlying data rows for the current cell, counts distinct values and returns the result to the e.CustomValue property.

Note

The complete sample project How to Create a Custom Summary Type to Display the Distinct Value Count is available in the DevExpress Examples repository.

C#

using DevExpress.XtraEditors;
using DevExpress.XtraPivotGrid;
using System;
using System.Collections;

namespace CustomSummaryDistinctCountExample
{
    public partial class Form1 : XtraForm
    {
        public Form1()
        {
            InitializeComponent();
            AddCustomSummaryField();
            pivotGridControl1.CustomSummary += PivotGridControl1_CustomSummary;
            pivotGridControl1.BestFit();
        }

        private void AddCustomSummaryField()
        {
            pivotGridControl1.Fields.Add(new PivotGridField()
            {
                Area = PivotArea.DataArea,
                AreaIndex = 1,
                Caption = "Count Distinct ",
                FieldName = "Quantity",
                Name = "fieldQuantityDistinctCount",
                SummaryType = DevExpress.Data.PivotGrid.PivotSummaryType.Custom
            });
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            excelDataSource1.FileName = "SalesPerson.xlsx";
            excelDataSource1.Fill();
        }

        private void PivotGridControl1_CustomSummary(object sender, PivotGridCustomSummaryEventArgs e)
        {
            string name = e.DataField.FieldName;

            IList list = e.CreateDrillDownDataSource();
            Hashtable ht = new Hashtable();
            for (int i = 0; i < list.Count; i++)
            {
                PivotDrillDownDataRow row = list[i] as PivotDrillDownDataRow;
                object v = row[name];
                if (v != null && v != DBNull.Value)
                    ht[v] = null;
            }
            e.CustomValue = ht.Count;
        }
    }
}

VB.NET

Imports DevExpress.XtraEditors
Imports DevExpress.XtraPivotGrid

Namespace CustomSummaryDistinctCountExample
    Partial Public Class Form1
        Inherits XtraForm

        Public Sub New()
            InitializeComponent()
            AddCustomSummaryField()
            AddHandler pivotGridControl1.CustomSummary, AddressOf PivotGridControl1_CustomSummary
            pivotGridControl1.BestFit()
        End Sub

        Private Sub AddCustomSummaryField()
            pivotGridControl1.Fields.Add(New PivotGridField() With {.Area = PivotArea.DataArea, .AreaIndex = 1, .Caption = "Count Distinct ", .FieldName = "Quantity", .Name = "fieldQuantityDistinctCount", .SummaryType = DevExpress.Data.PivotGrid.PivotSummaryType.Custom})
        End Sub

        Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
            excelDataSource1.FileName = "SalesPerson.xlsx"
            excelDataSource1.Fill()
        End Sub

        Private Sub PivotGridControl1_CustomSummary(ByVal sender As Object, ByVal e As PivotGridCustomSummaryEventArgs)
            Dim name_Renamed As String = e.DataField.FieldName

            Dim list As IList = e.CreateDrillDownDataSource()
            Dim ht As New Hashtable()
            For i As Integer = 0 To list.Count - 1
                Dim row As PivotDrillDownDataRow = TryCast(list(i), PivotDrillDownDataRow)
                Dim v As Object = row(name_Renamed)
                If v IsNot Nothing AndAlso v IsNot DBNull.Value Then
                    ht(v) = Nothing
                End If
            Next i
            e.CustomValue = ht.Count
        End Sub
    End Class
End Namespace