PivotCalculatedFieldCollection.Add(String, String) Method

Creates a new calculated field with the specified name and appends it to the collection.

Namespace: DevExpress.Spreadsheet

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

NuGet Package: DevExpress.Spreadsheet.Core

NuGet Packages: DevExpress.Spreadsheet.Core, DevExpress.WindowsDesktop.Spreadsheet.Core

Declaration

C#

PivotField Add(
    string formula,
    string name
)

VB.NET

Function Add(
    formula As String,
    name As String
) As PivotField

Parameters

Name	Type	Description
formula	String	A String value that specifies the formula for the field.
name	String	A String value that specifies the custom name of the field.

Returns

Type	Description
PivotField	A PivotField object that specifies the newly created calculated field.

Remarks

Use the Add method to add a calculated field to the PivotTable report.

A formula for the calculated field should conform to the common syntax rules and contain only supported elements.

• In the formula, you can use constants and refer to other fields in the PivotTable report. The calculation will be performed on the sum of the underlying data for any fields in the formula. When you reference a field in your formula, you can enclose its name in apostrophes or omit them.
• You cannot create formulas that use a cell reference, defined name, circular references and arrays.
• You cannot use worksheet functions that require cell references or defined names as arguments.
• The formula cannot refer to the PivotTable’s subtotals, totals and Grand Total value.

After the calculated field is created, add it to the PivotTable’s data area using the PivotDataFieldCollection.Add method of the PivotTable.DataFields collection.

View Example

PivotCalculatedFieldActions.vb

Dim worksheet As Worksheet = workbook.Worksheets("Report1")
workbook.Worksheets.ActiveWorksheet = worksheet

' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Create a calculated field based on data in the "Sales" field.
Dim field As PivotField = pivotTable.CalculatedFields.Add("=Sales*10%", "Sales Tax")
' 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.
dataField.NumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)"

PivotCalculatedFieldActions.cs

Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;

// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Create a calculated field based on data in the "Sales" field.
PivotField field = pivotTable.CalculatedFields.Add("=Sales*10%", "Sales Tax");
// 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.
dataField.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)";

Related GitHub Examples

The following code snippets (auto-collected from DevExpress Examples) contain references to the Add(String, 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-t314425/CS/SpreadsheetWPFPivotTableExamples/CodeExamples/PivotCalculatedFieldActions.cs#L16

// Create a calculated field based on data in the "Sales" field.
PivotField field = pivotTable.CalculatedFields.Add("=Sales*10%", "Sales Tax");
// Add the calculated field to the data area and specify the custom field name.

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

// Create a calculated field based on data in the "Sales" field.
PivotField field = pivotTable.CalculatedFields.Add("=Sales*10%", "Sales Tax");
// Add the calculated field to the data area and specify the custom field name.

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

// Create a calculated field based on data in the "Sales" field.
PivotField field = pivotTable.CalculatedFields.Add("=Sales*10%", "Sales Tax");
// Add the calculated field to the data area and specify the custom field name.

VB.NET

wpf-spreadsheet-pivot-table-api-t314425/VB/SpreadsheetWPFPivotTableExamples/CodeExamples/PivotCalculatedFieldActions.vb#L13

' Create a calculated field based on data in the "Sales" field.
Dim field As PivotField = pivotTable.CalculatedFields.Add("=Sales*10%", "Sales Tax")
' Add the calculated field to the data area and specify the custom field name.

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

' Create a calculated field based on data in the "Sales" field.
Dim field As PivotField = pivotTable.CalculatedFields.Add("=Sales*10%", "Sales Tax")
' Add the calculated field to the data area and specify the custom field name.

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

' Create a calculated field based on data in the "Sales" field.
Dim field As PivotField = pivotTable.CalculatedFields.Add("=Sales*10%", "Sales Tax")
' Add the calculated field to the data area and specify the custom field name.