Excel-like Computation Types in PivotGrid

The PivotGrid control now supports providing specific computation types for PivotCalculations. This can be achieved by setting the property “CalculationType.” CalculationType is an enumerator defined in the PivotComputationInfo class which is used to specify the type of calculation. Earlier versions of our PivotGrid control had only a limited number of computation types compared to Excel. We now provide support for all computation types available in Excel.

CalculationType

Description

NoCalculation

Remove custom calculations and restore original values (Default value). Display pivot values as default value.

PercentageOfGrandTotal

Displays a value cell as a percentage of Grand Total of all value cells of Pivot Engine.

PercentageOfColumnTotal

Displays all value cells in each column as a percentage of their corresponding column totals.

PercentageOfRowTotal

Displays all value cells in each row as a percentage of their corresponding row totals.

PercentageOfParentColumnTotal

Displays a value cell as a percentage of parent column item values.

PercentageOfParentRowTotal

Displays a value cell as a percentage of parent row item values.

PercentageOfParentTotal

Displays a value cell as a percentage of base field (parent row/column total).

Index

Displays a value cell as an index value based on PivotEngine generation.

Formula

Displays a calculation based on a well-formed algebraic expression involving other calculations.

PercentageOf

Displays values as a percentage of the value of the base item in the base field.

DifferenceFrom

Displays values as the difference from the value of the base item in the base field.

PercentageOfDifferenceFrom

Displays values 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

Calculates the value for successive items in the base field that are displayed as a running total as a percentage.

RankSmallestToLargest

Displays the rank of selected values in a specific field, listing the smallest item in the field as 1, and each larger value as 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 as a higher rank value.

By default, CalculationType will be “NoCalculation.” To change the CalculationType, we can refer to the following code samples:

Through XAML

[XAML]

<Grid>

       <!--Adding PivotGrid Control-->

       <syncfusion:PivotGridControl x:Name="pivotGrid1" VerticalAlignment="Top" Grid.Row="2" Grid.Column="0" ItemSource="{Binding ProductSalesData}">

 

       <syncfusion:PivotComputationInfo CalculationName = "Total" Description = "Summation of values" FieldName = "Amount" Format = "C" SummaryType="DoubleTotalSum" CalculationType="PercentageOfParentColumnTotal"/>

 

    </syncfusion:PivotGridControl>

</Grid>


Through Code Behind:

[C#]

protected void Window_Loaded(object sender, RoutedEventArgs e)

 {

      Syncfusion.Windows.Controls.PivotGrid.PivotGridControl PivotGrid1 = new Syncfusion.Windows.Controls.PivotGrid.PivotGridControl();

 

      PivotComputationInfo m_PivotComputationInfo = new PivotComputationInfo()

      {

          CalculationName = "Amount",

          FieldName = "Amount",

          CalculationType = CalculationType.PercentageOfParentColumnTotal

      };

      // Adding PivotComputationInfo to PivotCalculations

      this.pivotGrid1.PivotCalculations.Add(m_PivotComputationInfo);

  }

[VB]

 

Protected Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)

    Dim PivotGrid1 As New Syncfusion.Windows.Controls.PivotGrid.PivotGridControl()

 

    Dim m_PivotComputationInfo As New PivotComputationInfo() With {.CalculationName = "Amount", .FieldName = "Amount", .CalculationType = CalculationType.PercentageOfParentColumnTotal}

    ' Adding PivotComputationInfo to PivotCalculations

    Me.pivotGrid1.PivotCalculations.Add(m_PivotComputationInfo)

End Sub


clip_image002

PivotValues as PercentageOfParentColumnTotal calculation

Loading