ContextQMD
Back to Devexpress

How to: Calculate Running Totals

aspnet-119876-components-pivot-grid-examples-data-shaping-how-to-calculate-running-totals.md

latest6.0 KB
Original Source

How to: Calculate Running Totals

  • Dec 17, 2020
  • 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 PivotGridFieldBase.RunningTotal property of the corresponding field to true.

aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="RunningTotal.DefaultForm" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>

</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table class="OptionsTable BottomMargin">
            <tr>
                <td>
                    <dx:ASPxCheckBox ID="cbRunningTotals" runat="server" Text="Include Previous Quarter Sales into the Values of the Next Quarter"
                        Checked="True" AutoPostBack="True" />
                    <dx:ASPxCheckBox ID="cbAllowCrossGroupRunningTotals" runat="server" Text="Allow Cross-Group Running Totals Accumulation"
                        Checked="False" AutoPostBack="True" />
                </td>
            </tr>
        </table>
        <dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" DataSourceID="NwindDataSource"
            CssClass="" Width="100%">
            <OptionsView ShowFilterHeaders="False" ShowColumnTotals="False" />
            <Fields>
                <dx:PivotGridField Area="RowArea" AreaIndex="0" FieldName="ProductName" ID="fieldProductName"
                    Caption="Product" />
                <dx:PivotGridField Area="ColumnArea" AreaIndex="0" FieldName="OrderDate" ID="fieldOrderDate"
                    Caption="Year" GroupInterval="DateYear" />
                <dx:PivotGridField Area="ColumnArea" AreaIndex="1" FieldName="OrderDate" Caption="Quarter"
                    GroupInterval="DateQuarter" ID="fieldQuarter" ValueFormat-FormatString="Qtr {0}"
                    ValueFormat-FormatType="Numeric" />
                <dx:PivotGridField Area="DataArea" AreaIndex="0" FieldName="ProductAmount" ID="fieldProductAmount"
                    Caption="Product Sales" />
            </Fields>
            <OptionsView HorizontalScrollBarMode="Auto" />
            <OptionsFilter NativeCheckBoxes="False" />
        </dx:ASPxPivotGrid>
        <asp:AccessDataSource ID="NwindDataSource" runat="server" DataFile="~/App_Data/nwind.mdb"
            SelectCommand="SELECT * FROM [CustomerReports]"></asp:AccessDataSource>
    </div>
    </form>
</body>
</html>
csharp
using System;

namespace RunningTotal {
    public partial class DefaultForm : System.Web.UI.Page {
        protected void Page_Load(object sender, EventArgs e) {
            ASPxPivotGrid1.Fields["fieldQuarter"].RunningTotal = cbRunningTotals.Checked;
            ASPxPivotGrid1.OptionsData.AllowCrossGroupVariation = cbAllowCrossGroupRunningTotals.Checked;
        }
    }
}
vb
Imports System

Namespace RunningTotal
    Partial Public Class DefaultForm
        Inherits System.Web.UI.Page

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            ASPxPivotGrid1.Fields("fieldQuarter").RunningTotal = cbRunningTotals.Checked
            ASPxPivotGrid1.OptionsData.AllowCrossGroupVariation = cbAllowCrossGroupRunningTotals.Checked
        End Sub
    End Class
End Namespace
aspx
<%@ Page Language="vb" AutoEventWireup="true" CodeBehind="Default.aspx.vb" Inherits="RunningTotal.DefaultForm" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>

</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table class="OptionsTable BottomMargin">
            <tr>
                <td>
                    <dx:ASPxCheckBox ID="cbRunningTotals" runat="server" Text="Include Previous Quarter Sales into the Values of the Next Quarter"
                        Checked="True" AutoPostBack="True" />
                    <dx:ASPxCheckBox ID="cbAllowCrossGroupRunningTotals" runat="server" Text="Allow Cross-Group Running Totals Accumulation"
                        Checked="False" AutoPostBack="True" />
                </td>
            </tr>
        </table>
        <dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" DataSourceID="NwindDataSource"
            CssClass="" Width="100%">
            <OptionsView ShowFilterHeaders="False" ShowColumnTotals="False" />
            <Fields>
                <dx:PivotGridField Area="RowArea" AreaIndex="0" FieldName="ProductName" ID="fieldProductName"
                    Caption="Product" />
                <dx:PivotGridField Area="ColumnArea" AreaIndex="0" FieldName="OrderDate" ID="fieldOrderDate"
                    Caption="Year" GroupInterval="DateYear" />
                <dx:PivotGridField Area="ColumnArea" AreaIndex="1" FieldName="OrderDate" Caption="Quarter"
                    GroupInterval="DateQuarter" ID="fieldQuarter" ValueFormat-FormatString="Qtr {0}"
                    ValueFormat-FormatType="Numeric" />
                <dx:PivotGridField Area="DataArea" AreaIndex="0" FieldName="ProductAmount" ID="fieldProductAmount"
                    Caption="Product Sales" />
            </Fields>
            <OptionsView HorizontalScrollBarMode="Auto" />
            <OptionsFilter NativeCheckBoxes="False" />
        </dx:ASPxPivotGrid>
        <asp:AccessDataSource ID="NwindDataSource" runat="server" DataFile="~/App_Data/nwind.mdb"
            SelectCommand="SELECT * FROM [CustomerReports]"></asp:AccessDataSource>
    </div>
    </form>
</body>
</html>