Creating Window Calculations

• Oct 25, 2021
• 2 minutes to read

Follow the steps below to create a PivotGrid field that uses a window function to calculate its data.

1. Switch to the Optimized Mode.
2. Create a PivotGridField instance and specify the field’s caption, location, name, and format.

3. Create a data binding class instance.

   You can choose a predefined binding class instance:

   WinForms	WPF	ASP.NET	Description
   DevExpress.XtraPivotGrid.RunningTotalBinding	DevExpress.Xpf.PivotGrid.RunningTotalBinding	DevExpress.Web.ASPxPivotGrid.RunningTotalBinding	A cumulative total for the specified source binding across a window.
   DevExpress.XtraPivotGrid.MovingCalculationBinding	DevExpress.Xpf.PivotGrid.MovingCalculationBinding	DevExpress.Web.ASPxPivotGrid.MovingCalculationBinding	Uses neighbouring values to calculate a total.
   DevExpress.XtraPivotGrid.DifferenceBinding	DevExpress.Xpf.PivotGrid.DifferenceBinding	DevExpress.Web.ASPxPivotGrid.DifferenceBinding	Calculates the difference between values across a window.
   DevExpress.XtraPivotGrid.PercentOfTotalBinding	DevExpress.Xpf.PivotGrid.PercentOfTotalBinding	DevExpress.Web.ASPxPivotGrid.PercentOfTotalBinding	A percentage of the total for the specified source binding across a window.
   DevExpress.XtraPivotGrid.RankBinding	DevExpress.Xpf.PivotGrid.RankBinding	DevExpress.Web.ASPxPivotGrid.RankBinding	Assigns a rank to each row in a window.

   You can also use the WindowExpressionBinding type to specify a custom string as an expression that uses window functions:

   WinForms	WPF	ASP.NET	Description
   DevExpress.XtraPivotGrid.WindowExpressionBinding	DevExpress.Xpf.PivotGrid.WindowExpressionBinding	DevExpress.Web.ASPxPivotGrid.WindowExpressionBinding	A custom calculation with window functions.

4. Specify the partitioning criteria to define a window and the calculation direction for certain calculation types.

5. Assign the data binding instance to the PivotGridField.DataBinding property.
6. Add the field to the PivotGridControl.Fields collection.

Note

The complete sample project is available in the DevExpress Demo Center: Code Examples - Optimized Mode - Field Calculation Bindings module in the XtraPivotGrid MainDemo.

C#

pivotGridControl.OptionsData.DataProcessingEngine = PivotDataProcessingEngine.Optimized;

PivotGridField fieldProductAmount = new PivotGridField() {
    Area = PivotArea.DataArea,
    Caption = "Product Sales",
    Name = "fProductAmount"
};

PivotGridField fieldProductAmountRunningTotal = new PivotGridField {
    Area = PivotArea.DataArea,
    Caption = "Running Total",
    Name = "fRunningTotal"
};

DataSourceColumnBinding productAmountBinding = new DataSourceColumnBinding("ProductAmount");
//Bind a field to a column in the data source.
fieldProductAmount.DataBinding = productAmountBinding;
//Calculate a running summary of the column.
fieldProductAmountRunningTotal.DataBinding = new RunningTotalBinding() {
    Source = productAmountBinding,
    PartitioningCriteria = CalculationPartitioningCriteria.ColumnValue,
    SummaryType = PivotSummaryType.Sum
};

pivotGridControl.Fields.AddRange(new PivotGridField[] { fieldProductAmount, fieldProductAmountRunningTotal });

VB.NET

pivotGridControl.OptionsData.DataProcessingEngine = PivotDataProcessingEngine.Optimized

Dim fieldProductAmount As New PivotGridField()
fieldProductAmount.Area = PivotArea.DataArea
fieldProductAmount.Caption = "Product Sales"
fieldProductAmount.Name = "fProductAmount"

Dim fieldProductAmountRunningTotal As New PivotGridField()
fieldProductAmountRunningTotal.Area = PivotArea.DataArea
fieldProductAmountRunningTotal.Caption = "Running Total"
fieldProductAmountRunningTotal.Name = "fRunningTotal"

Dim productAmountBinding As New DataSourceColumnBinding("ProductAmount")
'Bind a field to a column in the data source.
fieldProductAmount.DataBinding = productAmountBinding
'Calculate a running summary on the column.
Dim runningTotalBinding As New RunningTotalBinding()
runningTotalBinding.Source = productAmountBinding
runningTotalBinding.PartitioningCriteria = CalculationPartitioningCriteria.ColumnValue
runningTotalBinding.SummaryType = PivotSummaryType.Sum
fieldProductAmountRunningTotal.DataBinding = runningTotalBinding

pivotGridControl.Fields.AddRange(New PivotGridField() { fieldProductAmount, fieldProductAmountRunningTotal})