Skip to main content
Row

PivotFilterCollection.Add(PivotField, PivotFilterType, FilterValue) Method

Applies a label or date filter to the specified PivotTable field.

Namespace: DevExpress.Spreadsheet

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

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

PivotFilter Add(
    PivotField field,
    PivotFilterType filterType,
    FilterValue value
)

Parameters

Name Type Description
field PivotField

A PivotField object that specifies a row or column field that should be filtered.

filterType PivotFilterType

A PivotFilterType enumeration member specifying the type of filter to be applied to the field.

value FilterValue

A FilterValue object that specifies the filter criteria value.

Returns

Type Description
PivotFilter

A PivotFilter object that represents the applied filter.

Remarks

Use the Add method to filter item labels of a specific row or column field. For example, you can display item labels that contain (PivotFilterType.CaptionContains), begin (PivotFilterType.CaptionBeginsWith) or end (PivotFilterType.CaptionEndsWith) with the specified filter criteria value, or greater than (PivotFilterType.CaptionGreaterThan), less than (PivotFilterType.CaptionLessThan) or equal to (PivotFilterType.CaptionEqual) a criteria value.

The following example demonstrates how to apply a label filter to the “Region” field to display sales data only for the Southern region.

View Example

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

' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Access the "Region" field.
Dim field As PivotField = pivotTable.Fields(0)
' Filter the "Region" field by text to display sales data for the "South" region.  
pivotTable.Filters.Add(field, PivotFilterType.CaptionEqual, "South")

Tip

To perform more versatile filtering, you can use the wildcard characters. The asterisk * matches any number of characters, while the question mark ? represents a single character. To filter labels containing a specific character, such as the asterisk, question mark or tilde, put the tilde (~) before it.

If the a row or column field you wish to filter contains date items, use the current Add method to construct a filter criteria showing dates that are before (PivotFilterType.DateOlderThan, PivotFilterType.DateOlderThanOrEqual), after (PivotFilterType.DateNewerThan, PivotFilterType.DateNewerThanOrEqual), equal (PivotFilterType.DateEqual) or not equal (PivotFilterType.DateNotEqual) to the specified date.

// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "Date" field by its name in the collection.
PivotField field = pivotTable.Fields["Date"];
// Filter the "Date" field to display sales that occurred after June 1, 2014.
pivotTable.Filters.Add(field, PivotFilterType.DateNewerThanOrEqual, new DateTime(2014, 6, 1));

The following code snippets (auto-collected from DevExpress Examples) contain references to the Add(PivotField, PivotFilterType, FilterValue) 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.

See Also