How to: Calculate Cumulative Values in the Pivot Grid Control

Aug 01, 2019
2 minutes to read

This example demonstrates how to include previous cell values in values of the next cell. To calculate cumulative values, set the PivotGridField.RunningTotal property of the corresponding field to true.

The PivotGridControl.AllowCrossGroupVariation property allows you to specify whether running totals are calculated independently within individual groups or for the entire Pivot Grid.

In this example, you can use the corresponding check boxes to control the Pivot Grid behavior.

MainWindow.xaml.vb
MainWindow.xaml
MainWindow.xaml.cs

Imports System.Windows
Imports DevExpress.Xpf.Editors

Namespace exWpfPivotRunningTotals
    Partial Public Class MainWindow
        Inherits Window

        Public Sub New()
            InitializeComponent()
        End Sub

        Private Sub ceRunningTotals_EditValueChanged(ByVal sender As Object,
                                                     ByVal e As EditValueChangedEventArgs)
            fieldOrderQuarter.RunningTotal = CBool(ceRunningTotals.IsChecked)
        End Sub

        Private Sub ceAllowCrossGroupRunningTotals_EditValueChanged(ByVal sender As Object,
                                                    ByVal e As EditValueChangedEventArgs)
            pivot.AllowCrossGroupVariation = CBool(ceAllowCrossGroupRunningTotals.IsChecked)
        End Sub
    End Class
End Namespace

xaml

<Window xmlns:dxe="http://schemas.devexpress.com/winfx/2008/xaml/editors"  
        xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid"  
        x:Class="exWpfPivotRunningTotals.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="Running Totals" Height="500" Width="800" 
        xmlns:dx="http://schemas.devexpress.com/winfx/2008/xaml/core" 
        xmlns:my="clr-namespace:exWpfPivotRunningTotals.Data.nwindDataSetTableAdapters" 
        xmlns:my1="clr-namespace:exWpfPivotRunningTotals.Data">
    <Window.Resources>
        <dx:TypedSimpleSource x:Key="TypedSimpleSource" AdapterType="my:SalesPersonTableAdapter" 
                              ContextType="my1:nwindDataSet" Path="SalesPerson">
            <dx:DesignDataManager.DesignData>
                <dx:DesignDataSettings RowCount="5" />
            </dx:DesignDataManager.DesignData>
        </dx:TypedSimpleSource>
    </Window.Resources>
    <Grid>
        <Grid>
            <StackPanel x:Name="stackPanel">
                <dxe:CheckEdit x:Name="ceRunningTotals" IsChecked="False" Content="Enable Running Totals" 
                               ToolTip="Include previous quarter sales into the values of the next quarter." 
                               Margin="5,0" EditValueChanged="ceRunningTotals_EditValueChanged"/>
                <dxe:CheckEdit x:Name="ceAllowCrossGroupRunningTotals" IsChecked="False" 
                               Content="Allow Cross-Group Variation" 
                               ToolTip="Allow cross-group running totals accumulation." 
                               Margin="5,0,5,5" EditValueChanged="ceAllowCrossGroupRunningTotals_EditValueChanged" />
            </StackPanel>
            <dxpg:PivotGridControl x:Name="pivot" Margin="0,50,0,0" 
                                   DataSource="{Binding Path=Data, Source={StaticResource TypedSimpleSource}}">
                <dxpg:PivotGridControl.Fields>
                    <dxpg:PivotGridField Area="ColumnArea" x:Name="fieldOrderYear" Caption="Year" 
                                         FieldName="OrderDate" AreaIndex="0" GroupInterval="DateYear"/>
                    <dxpg:PivotGridField Area="ColumnArea" x:Name="fieldOrderQuarter" Caption="Quarter" 
                                         FieldName="OrderDate" AreaIndex="1" GroupInterval="DateQuarter" 
                                         ValueFormat="Qtr {0}"/>
                    <dxpg:PivotGridField Area="RowArea" x:Name="fieldProduct" Caption="Product" 
                                         FieldName="ProductName" AreaIndex="1"/>
                    <dxpg:PivotGridField Area="DataArea" x:Name="fieldProductSales" Caption="Product Sales" 
                                         FieldName="Extended Price" AreaIndex="0"/>
                </dxpg:PivotGridControl.Fields>
            </dxpg:PivotGridControl>
        </Grid>
    </Grid>
</Window>

csharp

using System.Windows;
using DevExpress.Xpf.Editors;

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

        private void ceRunningTotals_EditValueChanged(object sender, 
                    EditValueChangedEventArgs e) {
            fieldOrderQuarter.RunningTotal = (bool)ceRunningTotals.IsChecked;
        }

        private void ceAllowCrossGroupRunningTotals_EditValueChanged(object sender, 
                    EditValueChangedEventArgs e) {
            pivot.AllowCrossGroupVariation = (bool)ceAllowCrossGroupRunningTotals.IsChecked;
        }
    }
}