PivotDataFieldCollection.Add(PivotField, String) 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
)

VB.NET

Function Add(
    field As PivotField,
    name As String
) 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.

Returns

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

Remarks

The following example demonstrates how to create a simple PivotTable report.

View Example

PivotFieldActions.vb

Dim sourceWorksheet As Worksheet = workbook.Worksheets("Data1")
Dim worksheet As Worksheet = workbook.Worksheets.Add()
workbook.Worksheets.ActiveWorksheet = worksheet

' Create a pivot table.
Dim pivotTable As PivotTable = worksheet.PivotTables.Add(sourceWorksheet("A1:D41"), worksheet("B2"))

' Add the "Product" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields("Product"))
' Add the "Category" field to the column axis area.
pivotTable.ColumnFields.Add(pivotTable.Fields("Category"))
' Add the "Sales" field to the data area and specify the custom field name.
Dim dataField As PivotDataField = pivotTable.DataFields.Add(pivotTable.Fields("Sales"), "Sales(Sum)")
' Specify the number format for the "Sales" field.
dataField.NumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)"
' Add the "Region" field to the filter area.
pivotTable.PageFields.Add(pivotTable.Fields("Region"))

PivotFieldActions.cs

Worksheet sourceWorksheet = workbook.Worksheets["Data1"];
Worksheet worksheet = workbook.Worksheets.Add();
workbook.Worksheets.ActiveWorksheet = worksheet;

// Create a pivot table.
PivotTable pivotTable = worksheet.PivotTables.Add(sourceWorksheet["A1:D41"], worksheet["B2"]);

// Add the "Product" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields["Product"]);
// Add the "Category" field to the column axis area.
pivotTable.ColumnFields.Add(pivotTable.Fields["Category"]);
// Add the "Sales" field to the data area and specify the custom field name.
PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Sales"], "Sales(Sum)");
// Specify the number format for the "Sales" field.
dataField.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)";
// Add the "Region" field to the filter area.
pivotTable.PageFields.Add(pivotTable.Fields["Region"]);

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.

Change Summary Function

Use the PivotDataField.SummarizeValuesBy property to change the default summary function used to calculate values in a data field. 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.

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.

Related GitHub Examples

The following code snippets (auto-collected from DevExpress Examples) contain references to the Add(PivotField, String) method.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

wpf-spreadsheet-pivot-table-api-examples/CS/SpreadsheetWPFPivotTableExamples/CodeExamples/PivotCalculatedFieldActions.cs#L18

// Add the calculated field to the data area and specify the custom field name.
PivotDataField dataField = pivotTable.DataFields.Add(field, "Total Tax");
// Specify the number format for the data field.

winforms-spreadsheet-pivot-table-api/CS/SpreadsheetPivotTableExamples/CodeExamples/PivotCalculatedFieldActions.cs#L18

// Add the calculated field to the data area and specify the custom field name.
PivotDataField dataField = pivotTable.DataFields.Add(field, "Total Tax");
// Specify the number format for the data field.

spreadsheet-document-api-pivot-table-examples/CS/SpreadsheetDocServerPivotAPI/CodeExamples/PivotCalculatedFieldActions.cs#L18

// Add the calculated field to the data area and specify the custom field name.
PivotDataField dataField = pivotTable.DataFields.Add(field, "Total Tax");
// Specify the number format for the data field.

VB.NET

wpf-spreadsheet-pivot-table-api-examples/VB/SpreadsheetWPFPivotTableExamples/CodeExamples/PivotCalculatedFieldActions.vb#L15

' Add the calculated field to the data area and specify the custom field name.
Dim dataField As PivotDataField = pivotTable.DataFields.Add(field, "Total Tax")
' Specify the number format for the data field.

winforms-spreadsheet-pivot-table-api/VB/SpreadsheetPivotTableExamples/CodeExamples/PivotCalculatedFieldActions.vb#L15

' Add the calculated field to the data area and specify the custom field name.
Dim dataField As PivotDataField = pivotTable.DataFields.Add(field, "Total Tax")
' Specify the number format for the data field.

spreadsheet-document-api-pivot-table-examples/VB/SpreadsheetDocServerPivotAPI/CodeExamples/PivotCalculatedFieldActions.vb#L15

' Add the calculated field to the data area and specify the custom field name.
Dim dataField As PivotDataField = pivotTable.DataFields.Add(field, "Total Tax")
' Specify the number format for the data field.