PivotDataFieldCollection.Add(PivotField, String, PivotDataConsolidationFunction) Method

Adds a field to the data area of a PivotTable report.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v23.2.Core.dll

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

C#

PivotDataField Add(
    PivotField field,
    string name,
    PivotDataConsolidationFunction summarizeValuesBy
)

VB.NET

Function Add(
    field As PivotField,
    name As String,
    summarizeValuesBy As PivotDataConsolidationFunction
) As PivotDataField

Parameters

Name	Type	Description
field	PivotField	A field that should be added to the data area.
name	String	A name for the data field. This value is assigned to the PivotDataField.Name property.
summarizeValuesBy	PivotDataConsolidationFunction	A summary function used to calculate values in the data field. This value is assigned to the PivotDataField.SummarizeValuesBy property.

Returns

Type	Description
PivotDataField	The data field added to the pivot table.

Remarks

Use this method overload to create a data field and specify a summary function used to calculate field values. The PivotDataField.ShowValuesWithCalculation method allows you to apply an additional calculation option to the data field and specify how summary values are displayed within the field.

C#

// Create a pivot table.
// Use the cell range "A1:E65" as a data source.
var pivotTable = worksheet.PivotTables.Add(sourceWorksheet["A1:E65"], worksheet["B2"]);

// Add the "Category" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields["Category"]);
// Add the "Product" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields["Product"]);

// Add the "Amount" field to the data area.
// Use the "Average" function to summarize values in the data field.
var dataField = pivotTable.DataFields.Add(pivotTable.Fields["Amount"], 
    "Average of AMOUNT", PivotDataConsolidationFunction.Average);
// Apply the number format to the data field.
dataField.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)";

VB.NET

' Create a pivot table.
' Use the cell range "A1:E65" as a data source.
Dim pivotTable As PivotTable = worksheet.PivotTables.Add(sourceWorksheet("A1:E65"), worksheet("B2"))

' Add the "Category" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields("Category"))
' Add the "Product" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields("Product"))

' Add the "Amount" field to the data area.
' Use the "Average" function to summarize values in the data field.
Dim dataField As PivotDataField = pivotTable.DataFields.Add(pivotTable.Fields("Amount"), "Average of AMOUNT", PivotDataConsolidationFunction.Average)
' Apply the number format to the data field.
dataField.NumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)"

The image below shows the resulting PivotTable report.

Reorder Data Fields

Use the PivotDataField.MoveDown, PivotDataField.MoveUp, PivotDataField.MoveToBeginning, and PivotDataField.MoveToEnd methods to reorder fields in the data area. The PivotLayout.DataOnRows property allows you to change the orientation of multiple data fields in the report.

Remove Data Fields

Use the PivotDataFieldCollection.Remove or PivotDataFieldCollection.RemoveAt method to remove a data field from a pivot table. The PivotDataFieldCollection.Clear method removes all fields from the data area.