Pivot Calculations in Windows Forms Pivot Grid
13 Jul 20216 minutes to read
Pivot calculations are defined by using the PivotComputationInfo object which holds the information required for defining calculations that appear in the pivot grid control.
Defining pivot calculations using pivot computation information
To define a pivot calculation item, the following properties of PivotComputationInfo
object are used.

FieldName 
Specifies the name of the field to be used for pivot calculation.  CalculationName  Specifies the name that is to be displayed in the pivot table if more than one calculation is included in the pivot grid.

Description 
Specifies the description content of the calculation. 
Format 
Specifies the format string to be used to formatting the calculation values.
Refer to the below code sample for adding pivot calculation item in the pivot grid control.
// Defining PivotComputationInfo
PivotComputationInfo pivotComputationInfo = new PivotComputationInfo() { CalculationName = "Amount", FieldName = "Amount" };
// Adding PivotComputationInfo to PivotCalculations
pivotGridControl1.PivotCalculations.Add(pivotComputationInfo);
' Defining PivotComputationInfo
Dim pivotComputationInfo As New PivotComputationInfo() With {.CalculationName = "Amount", .FieldName = "Amount"}
' Adding PivotComputationInfo to PivotCalculations
pivotGridControl1.PivotCalculations.Add(pivotComputationInfo)
Formatting calculation values
Using the Format property of PivotComputationInfo
object, the calculation value can be specified with the textual equivalent value.
The following table lists some of the formats used for formatting calculation values.
Format  Description 

0.00  Decimal 
C  Currency 
#,##0  Thousand Separator 
# ' degrees'  Literal String Specifier 
D  Long Date 
Refer to the following code sample to set currency format for calculation values.
PivotComputationInfo pivotComputationInfo = new PivotComputationInfo()
{
FieldName = "Amount",
Format = "C",
SummaryType = SummaryType.DecimalTotalSum
};
Dim pivotComputationInfo As PivotComputationInfo = New PivotComputationInfo() With
{
.FieldName = "Amount",
.Format = "C",
.SummaryType = SummaryType.DecimalTotalSum
}
Calculation types
Using the CalculationType property of PivotComputationInfo
object, the type of the calculation can be specified. CalculationType enumeration contains the following options to denote the calculation type:
 NoCalculation  Turn off custom calculation.
 PercentageOfGrandTotal  Displays a value as a percentage of the grand total of all the values.
 PercentageOfColumnTotal  Displays all the values in each column as a percentage of the total for the column.
 PercentageOfRowTotal  Displays all the values in each row as a percentage of the total for the row.
 PercentageOfParentColumnTotal  Calculates (value for the item) / (value for the parent item on columns).
 PercentageOfParentRowTotal  Calculates (value for the item) / (value for the parent item on rows).
 PercentageOfParentTotal  Calculates (value for the item) / (value for the parent item of the selected base field).
 Formula  Displays a calculation based on a well formed algebraic expression involving other calculations.
 PercentageOf  Displays a value as a percentage of the value of the base item in the base field.
 DifferenceFrom  Displays a value as the difference from the value of the base item in the base field.
 PercentageOfDifferenceFrom  Displays a value as the percentage difference from the value of the base item in the base field.
 RunningTotalIn  Displays the value for successive items in the base field as a running total.
 PercentageOfRunningTotalIn  Displays the value as a percentage for successive items in the field as a running total.
 RankSmallestToLargest  Displays the rank of selected values in a specific field, listing the smallest item in the field as 1, and each larger value will have a higher rank value.
 RankLargestToSmallest  Displays the rank of selected values in a specific field, listing the largest item in the field as 1, and each smaller value will have a higher rank value.
 Distinct  Displays the subtotals based on the distinct values of base item defined for calculation item.
 Index  Calculate a value as ((value in cell) x (Grand Total of Grand Totals)) / ((Grand Row Total) x (Grand Column Total))
Refer to the following code sample to denote the calculation type for pivot calculation items.
pivotGridControl1.PivotCalculations.Add(new PivotComputationInfo
{
FieldName = "Amount",
CalculationType=CalculationType.PercentageOfColumnTotal,
.SummaryType = SummaryType.DecimalTotalSum
});
pivotGridControl1.PivotCalculations.Add(New PivotComputationInfo With
{
.FieldName = "Amount",
.CalculationType = CalculationType.PercentageOfColumnTotal,
.SummaryType = SummaryType.DecimalTotalSum
})
Expression field calculations
Pivot grid allows to provide expression field for calculations, despite of inbuilt options provided for calculations such as PercentageOfGrandTotal, PercentageOfRowTotal, RankLargestToSmallest and so on. It can be achieved by setting the calculation type as “Formula” and by specifying the appropriate formula using the Formula property of PivotComputationInfo
object.
Refer to the following code sample to specify custom formula for calculations.
pivotGridControl1.PivotCalculations.Add(new PivotComputationInfo()
{
FieldName = "UnitPrice",
CalculationType = CalculationType.Formula,
Formula = "[Amount] / [Quantity]",
Format = "C"
});
pivotGridControl1.PivotCalculations.Add(New PivotComputationInfo() With
{
.FieldName = "UnitPrice",
.CalculationType = CalculationType.Formula,
.Formula = "[Amount] / [Quantity]",
.Format = "C"
})
Synchronizing pivot calculations
To synchronize the newly added or modified pivot calculation items with the pivot grid control, the SynchronizeCalculations method will be used. This method will be invoked whenever the collection of pivot calculation item gets changed.
Pivot computation information dialog
The pivot computation information dialog is used to view the information about particular pivot calculation item. It is also used to edit the header, description, value format, and calculation type of the pivot calculation item at run time. While double clicking on a calculation item in the value layout section of pivot schema designer, pivot computation information dialog containing the details of clicked pivot calculation item will be displayed.