How To: Display Custom Summary in Totals

• Nov 13, 2018
• 4 minutes to read

The following example demonstrates how to calculate and display the difference of Income and Outlay in totals.

In this example, a custom Grand Total is implemented for the Value data field.

To show additional Grand Total rows or columns, add a field to the Data Area. Then, set the field’s PivotGridFieldBase.SummaryType property to Custom. To provide values for this field, use the PivotGridControl.CustomSummary event. You can use the PivotGridCustomSummaryEventArgsBase<T>.CreateDrillDownDataSource method to get a list of all corresponding underlying data source rows, and then you will be able to calculate summary values.

The image below shows the result.

To learn more about custom summaries, see the Custom summaries topic.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-display-the-difference-of-income-and-outlay-in-totals-t413352

Form1.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Windows.Forms;
using DevExpress.XtraPivotGrid;

namespace WindowsApplication53
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            PopulateTable();
            pivotGridControl1.RefreshData();
            pivotGridControl1.BestFit();
        }
        private void PopulateTable()
        {
            DataTable myTable = dataSet1.Tables["Data"];
            myTable.Rows.Add(new object[] { "Aaa", DateTime.Today, 7, "Income" });
            myTable.Rows.Add(new object[] { "Aaa", DateTime.Today.AddDays(1), 4, "Outlay" });
            myTable.Rows.Add(new object[] { "Bbb", DateTime.Today, 12, "Outlay" });
            myTable.Rows.Add(new object[] { "Bbb", DateTime.Today.AddDays(1), 14, "Outlay" });
            myTable.Rows.Add(new object[] { "Ccc", DateTime.Today, 11, "Income" });
            myTable.Rows.Add(new object[] { "Ccc", DateTime.Today.AddDays(1), 10, "Income" });

            myTable.Rows.Add(new object[] { "Aaa", DateTime.Today.AddYears(1), 4, "Income" });
            myTable.Rows.Add(new object[] { "Aaa", DateTime.Today.AddYears(1).AddDays(1), 2, "Income" });
            myTable.Rows.Add(new object[] { "Bbb", DateTime.Today.AddYears(1), 3, "Income" });
            myTable.Rows.Add(new object[] { "Bbb", DateTime.Today.AddDays(1).AddYears(1), 1, "Outlay" });
            myTable.Rows.Add(new object[] { "Ccc", DateTime.Today.AddYears(1), 8, "Income" });
            myTable.Rows.Add(new object[] { "Ccc", DateTime.Today.AddDays(1).AddYears(1), 22, "Outlay" });
        }

        private void pivotGridControl1_CustomSummary(object sender, PivotGridCustomSummaryEventArgs e)
        {
            if (e.DataField.Name == "fieldValue" )
            {
                if (ShouldCalculateCustomValue(fieldType, e))
                {
                    var groupedDataSource = 
                        e.CreateDrillDownDataSource().Cast<PivotDrillDownDataRow>().GroupBy(r => r[fieldType]);
                    decimal incomeSummary = GetGroupSummary(groupedDataSource, "Income", e.FieldName);
                    decimal outlaySummary = GetGroupSummary(groupedDataSource, "Outlay", e.FieldName);
                    e.CustomValue = incomeSummary - outlaySummary;
                }
                else
                    e.CustomValue = e.SummaryValue.Summary;
            }
        }

        private decimal GetGroupSummary(IEnumerable<IGrouping<object, PivotDrillDownDataRow>> groupedDataSource, 
            object target, string dataFieldName)
        {
            var targetGroup = groupedDataSource.FirstOrDefault(g => object.Equals(g.Key, target));
            if (targetGroup == null)
                return 0;
            return targetGroup.Sum(r => Convert.ToDecimal(r[dataFieldName]));
        }

        private bool ShouldCalculateCustomValue(PivotGridField groupField, PivotGridCustomSummaryEventArgs e)
        {
            if (!groupField.Visible || groupField.Area == PivotArea.FilterArea || groupField.Area == PivotArea.DataArea)
                return true;
            if (groupField.Area == PivotArea.RowArea && 
                (e.RowField == null || e.RowField.AreaIndex < groupField.AreaIndex))
                return true;
            if (groupField.Area == PivotArea.ColumnArea && 
                (e.ColumnField == null || e.ColumnField.AreaIndex < groupField.AreaIndex))
                return true;
            return false;
        }

    }
}

Form1.vb

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq
Imports System.Windows.Forms
Imports DevExpress.XtraPivotGrid

Namespace WindowsApplication53
    Partial Public Class Form1
        Inherits Form

        Public Sub New()
            InitializeComponent()
        End Sub
        Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
            PopulateTable()
            pivotGridControl1.RefreshData()
            pivotGridControl1.BestFit()
        End Sub
        Private Sub PopulateTable()
            Dim myTable As DataTable = dataSet1.Tables("Data")
            myTable.Rows.Add(New Object() { "Aaa", Date.Today, 7, "Income" })
            myTable.Rows.Add(New Object() { "Aaa", Date.Today.AddDays(1), 4, "Outlay" })
            myTable.Rows.Add(New Object() { "Bbb", Date.Today, 12, "Outlay" })
            myTable.Rows.Add(New Object() { "Bbb", Date.Today.AddDays(1), 14, "Outlay" })
            myTable.Rows.Add(New Object() { "Ccc", Date.Today, 11, "Income" })
            myTable.Rows.Add(New Object() { "Ccc", Date.Today.AddDays(1), 10, "Income" })

            myTable.Rows.Add(New Object() { "Aaa", Date.Today.AddYears(1), 4, "Income" })
            myTable.Rows.Add(New Object() { "Aaa", Date.Today.AddYears(1).AddDays(1), 2, "Income" })
            myTable.Rows.Add(New Object() { "Bbb", Date.Today.AddYears(1), 3, "Income" })
            myTable.Rows.Add(New Object() { "Bbb", Date.Today.AddDays(1).AddYears(1), 1, "Outlay" })
            myTable.Rows.Add(New Object() { "Ccc", Date.Today.AddYears(1), 8, "Income" })
            myTable.Rows.Add(New Object() { "Ccc", Date.Today.AddDays(1).AddYears(1), 22, "Outlay" })
        End Sub

        Private Sub pivotGridControl1_CustomSummary(ByVal sender As Object,
                                    ByVal e As PivotGridCustomSummaryEventArgs) Handles pivotGridControl1.CustomSummary
            If e.DataField.Name = "fieldValue" Then
                If ShouldCalculateCustomValue(fieldType, e) Then
                    Dim groupedDataSource =
                        e.CreateDrillDownDataSource().Cast(Of PivotDrillDownDataRow)().GroupBy(Function(r) r(fieldType))
                    Dim incomeSummary As Decimal = GetGroupSummary(groupedDataSource, "Income", e.FieldName)
                    Dim outlaySummary As Decimal = GetGroupSummary(groupedDataSource, "Outlay", e.FieldName)
                    e.CustomValue = incomeSummary - outlaySummary
                Else
                    e.CustomValue = e.SummaryValue.Summary
                End If
            End If
        End Sub

        Private Function GetGroupSummary(ByVal groupedDataSource As IEnumerable(Of IGrouping(Of Object, PivotDrillDownDataRow)),
                                         ByVal target As Object, ByVal dataFieldName As String) As Decimal
            Dim targetGroup = groupedDataSource.FirstOrDefault(Function(g) Object.Equals(g.Key, target))
            If targetGroup Is Nothing Then
                Return 0
            End If
            Return targetGroup.Sum(Function(r) Convert.ToDecimal(r(dataFieldName)))
        End Function

        Private Function ShouldCalculateCustomValue(ByVal groupField As PivotGridField,
                                                    ByVal e As PivotGridCustomSummaryEventArgs) As Boolean
            If (Not groupField.Visible) OrElse groupField.Area = PivotArea.FilterArea OrElse
                groupField.Area = PivotArea.DataArea Then
                Return True
            End If
            If groupField.Area = PivotArea.RowArea AndAlso
                (e.RowField Is Nothing OrElse e.RowField.AreaIndex < groupField.AreaIndex) Then
                Return True
            End If
            If groupField.Area = PivotArea.ColumnArea AndAlso
                (e.ColumnField Is Nothing OrElse e.ColumnField.AreaIndex < groupField.AreaIndex) Then
                Return True
            End If
            Return False
        End Function

    End Class
End Namespace