Custom Totals

• Jul 05, 2018
• 3 minutes to read

Automatic totals are calculated using a summary function which is specified by the corresponding data field. Custom totals can be calculated using any summary function.

The main differences between Automatic and Custom Totals are listed in the following table:

Feature	Automatic Totals	Custom Totals
Association with fields	Automatic totals are not associated with fields. They are calculated for all the field values which have nested field values.	Custom totals are associated with column or row fields. They are only calculated for the values of associated fields, provided that these values have nested field values. Custom totals replace automatic totals for these field values.
Collection of total objects	The Pivot Grid doesn’t provide a collection of automatic totals.	The custom totals can be accessed via the PivotGridField.CustomTotals property.
Number of Totals for individual field values	The number of automatic totals displayed for a specific field value matches the number of data fields.	The number of custom totals displayed for a specific field value is determined by the number of items in the PivotGridField.CustomTotals collection.
Summary type	The type of summary function used to calculate an automatic total, is determined by the corresponding data field’s PivotGridField.SummaryType property.	The type of summary function used to calculate a custom total is specified by the custom total object (specifically by its PivotGridCustomTotal.SummaryType property).

Creating and Displaying Custom Totals

Custom totals are represented by PivotGridCustomTotal objects. These objects are stored within a field’s PivotGridField.CustomTotals collection. To display custom totals for a column or row field, do the following:

• Set the field’s PivotGridField.TotalsVisibility property to FieldTotalsVisibility.CustomTotals;
• Create required PivotGridCustomTotal objects and add them to the PivotGridField.CustomTotals collection.

Custom totals (like automatic totals) are calculated for field values which have nested field values. They are not calculated for the values of the innermost column and row fields. If a field is not a column field or row field, custom totals are not calculated for it.

Example: How to Display Custom Totals

The following example demonstrates how to add custom totals for a particular pivot grid field.

In this example, four different totals are added for the Category Name row field: Average, Sum, Min and Max. For this, they should be added to the PivotGridField.CustomTotals property and the PivotGridField.TotalsVisibility property should be set to FieldTotalsVisibility.CustomTotals.

Note

A complete sample project is available at: https://github.com/DevExpress-Examples/how-to-add-custom-totals-e2135

MainWindow.xaml

<Window x:Class="HowToBindToMDB.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid"
        Title="MainWindow" Height="350" Width="525"  Loaded="Window_Loaded">
    <Grid>
        <dxpg:PivotGridControl HorizontalAlignment="Left" Name="pivotGridControl1" 
                               VerticalAlignment="Top" RowTotalsLocation="Far">
            <dxpg:PivotGridControl.Fields>
                <dxpg:PivotGridField Name="fieldCategoryName" FieldName="CategoryName" 
                                     Area="RowArea" Caption="Category"
                                     TotalsVisibility="CustomTotals">
                    <dxpg:PivotGridField.CustomTotals>
                        <dxpg:PivotGridCustomTotal SummaryType="Average" />
                        <dxpg:PivotGridCustomTotal SummaryType="Sum" />
                        <dxpg:PivotGridCustomTotal SummaryType="Max" />
                        <dxpg:PivotGridCustomTotal SummaryType="Min" />
                    </dxpg:PivotGridField.CustomTotals>
                </dxpg:PivotGridField>
                <dxpg:PivotGridField Name="fieldProductName" FieldName="ProductName" Area="RowArea"
                                     Caption="Product Name" />
                <dxpg:PivotGridField Name="fieldOrderYear" FieldName="OrderDate" Area="ColumnArea"
                                     Caption="Order Year" GroupInterval="DateYear" />
                <dxpg:PivotGridField Name="fieldExtendedPrice" FieldName="Extended Price"
                                     Area="DataArea" CellFormat="c0" />
            </dxpg:PivotGridControl.Fields>
        </dxpg:PivotGridControl>
    </Grid>
</Window>

MainWindow.xaml.cs

using System.Data;
using System.Data.OleDb;
using System.Windows;
using DevExpress.Xpf.PivotGrid;
using HowToBindToMDB.NwindDataSetTableAdapters;
using System;

namespace HowToBindToMDB {
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window {
        NwindDataSet.SalesPersonDataTable salesPersonDataTable = new NwindDataSet.SalesPersonDataTable();
        SalesPersonTableAdapter salesPersonDataAdapter = new SalesPersonTableAdapter();

        public MainWindow() {
            InitializeComponent();
            pivotGridControl1.DataSource = salesPersonDataTable;
        }

        private void Window_Loaded(object sender, RoutedEventArgs e) {
            salesPersonDataAdapter.Fill(salesPersonDataTable);
            object[] filter = new object[15];
            Array.Copy(fieldProductName.GetUniqueValues(), filter, filter.Length);
            fieldProductName.FilterValues.ValuesIncluded = filter;
        }
    }
}

MainWindow.xaml.vb

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.OleDb
Imports System.Windows
Imports DevExpress.Xpf.PivotGrid
Imports HowToBindToMDB.NwindDataSetTableAdapters
Imports System

Namespace HowToBindToMDB
    ''' <summary>
    ''' Interaction logic for MainWindow.xaml
    ''' </summary>
    Partial Public Class MainWindow
        Inherits Window
        Private salesPersonDataTable As New NwindDataSet.SalesPersonDataTable()
        Private salesPersonDataAdapter As New SalesPersonTableAdapter()

        Public Sub New()
            InitializeComponent()
            pivotGridControl1.DataSource = salesPersonDataTable
        End Sub

        Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
            salesPersonDataAdapter.Fill(salesPersonDataTable)
            Dim filter(14) As Object
            Array.Copy(fieldProductName.GetUniqueValues(), filter, filter.Length)
            fieldProductName.FilterValues.ValuesIncluded = filter
        End Sub
    End Class
End Namespace