PivotCalculatedFieldCollection.Add(String, String) Method

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

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v24.1.Core.dll

NuGet Package: DevExpress.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	The formula used to calculate field values.
name	String	The field name.

Returns

Type	Description
PivotField	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. Calculations are performed on the sum of the underlying data for any field in the formula. When you reference a field in your formula, you can enclose the field name in apostrophes or omit them.
• You cannot create formulas that use a cell reference, defined name, circular references, or 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.
• The formula should use culture-dependent separators. Use the DocumentOptions.Culture property to specify the workbook culture.

After the calculated field is created, call the PivotTable.DataFields.Add method to add the field to the PivotTable’s data area.

View Example

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]* "" - ""??_);_(@_)";

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]* "" - ""??_);_(@_)"

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#

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.

wpf-spreadsheet-pivot-table-api-examples/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/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.

VB.NET

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.

wpf-spreadsheet-pivot-table-api-examples/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/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.