Pivot Table Filter

GcExcel enables you to filter values in pivot tables. You can either hide row fields or pages using Visible property of IPivotItem interface or use PivotFilters property of IPivotField interface, which enables you to add label, value, or date filters to the pivot table field using PivotFilterType enumeration that will be passed as a parameter in the Add method of IPivotFilters interface.

Furthermore, GcExcel provides AllowMultipleFilters property of IPivotTable interface, enabling you to add label and value filters simultaneously to a PivotField. The default value of this property is true. If you set this property to false, the filter set later will be used.

type=warning
Note: When the value of AllowMultipleFilters changes from true to false, it will go through the IPivotField. If IPivotField has both the label and value filters, GcExcel will discard both filters.

GcExcel also provides ClearLabelFilter, ClearValueFilter, and ClearAllFilters methods to delete the filters. ClearLabelFilter deletes the label filter, whereas ClearValueFilter deletes the value filter. ClearAllFilters deletes all the filters added to the field.

type=warning
Note: If you add two label filters or two value filters simultaneously using Add method, then the second filter will replace the first.

type=warning
Note: Most of the properties of the IPivotFilter interface are read-only, except for the WholeDayFilter property, which is read-write. However, any changes to the value of WholeDayFilter property will not trigger a refresh of the current pivot table. Hence, you have to call Update or Refresh methods of IPivotTable interface to update or refresh the filter results.

You can apply the following types of filters to the pivot table:

Apply Filter
Apply Label Filter
Apply Value Filter
Apply Date Filter
Apply Top 10 Filter
Apply Multiple Filters

Apply Filter

Refer to the following example code to hide row fields or pages using Visible property:

// Create a new workbook.
Workbook workbook = new Workbook();

// Add data for the pivot table.
object[,] sourceData = new object[,] {
{ "Order ID", "Product",               "Category",              "Amount", "Date",                    "Country" },
{ 1,          "Bose 785593-0050",      "Consumer Electronics",  4270,     new DateTime(2018, 1, 6),  "United States" },
{ 2,          "Canon EOS 1500D",       "Consumer Electronics",  8239,     new DateTime(2018, 1, 7),  "United Kingdom" },
{ 3,          "Haier 394L 4Star",      "Consumer Electronics",  617,      new DateTime(2018, 1, 8),  "United States" },
{ 4,          "IFB 6.5 Kg FullyAuto",  "Consumer Electronics",  8384,     new DateTime(2018, 1, 10), "Canada" },
{ 5,          "Mi LED 40inch",         "Consumer Electronics",  2626,     new DateTime(2018, 1, 10), "Germany" },
{ 6,          "Sennheiser HD 4.40-BT", "Consumer Electronics",  3610,     new DateTime(2018, 1, 11), "United States" },
{ 7,          "Iphone XR",             "Mobile",                9062,     new DateTime(2018, 1, 11), "Australia" },
{ 8,          "OnePlus 7Pro",          "Mobile",                6906,     new DateTime(2018, 1, 16), "New Zealand" },
{ 9,          "Redmi 7",               "Mobile",                2417,     new DateTime(2018, 1, 16), "France" },
{ 10,         "Samsung S9",            "Mobile",                7431,     new DateTime(2018, 1, 16), "Canada" },
{ 11,         "OnePlus 7Pro",          "Mobile",                8250,     new DateTime(2018, 1, 16), "Germany" },
{ 12,         "Redmi 7",               "Mobile",                7012,     new DateTime(2018, 1, 18), "United States" },
{ 13,         "Bose 785593-0050",      "Consumer Electronics",  1903,     new DateTime(2018, 1, 20), "Germany" },
{ 14,         "Canon EOS 1500D",       "Consumer Electronics",  2824,     new DateTime(2018, 1, 22), "Canada" },
{ 15,         "Haier 394L 4Star",      "Consumer Electronics",  6946,     new DateTime(2018, 1, 24), "France" },
};

// Access first worksheet.
IWorksheet worksheet = workbook.Worksheets[0];

// Add values to the range.
worksheet.Range["G1:L16"].Value = sourceData;

// Set column width.
worksheet.Range["G:L"].ColumnWidth = 15;

// Create pivot cache.
var pivotcache = workbook.PivotCaches.Create(worksheet.Range["G1:L16"]);

// Add data to the pivot table.
var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["A1"], "pivottable1");

// Set number format.
worksheet.Range["J1:J16"].NumberFormat = "$#,##0.00";

// Configure pivot table's fields.
var field_product = pivottable.PivotFields[1];
field_product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields[3];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

var field_Country = pivottable.PivotFields[5];
field_Country.Orientation = PivotFieldOrientation.PageField;

// Add row field filter.
field_product.PivotItems["Bose 785593-0050"].Visible = false;
field_product.PivotItems["Haier 394L 4Star"].Visible = false;
field_product.PivotItems["Iphone XR"].Visible = false;

// Add page filter.
field_Country.PivotItems["United States"].Visible = false;
field_Country.PivotItems["Canada"].Visible = false;

worksheet.Range["A:B"].EntireColumn.AutoFit();
            
// Save WorkBook.
workbook.Save("FilterPivotTable.xlsx");

Apply Label Filter

Refer to the example code to filter products based on the item names containing "mi":

// Configure pivot table's fields.
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.ColumnField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

// Filter for products where the product name contains 'mi'.
field_Product.PivotFilters.Add(PivotFilterType.CaptionContains, "mi");

worksheet.Range["A:D"].EntireColumn.AutoFit();

// Save the workbook.
workbook.Save("PivotLabelFilter.xlsx");

Apply Value Filter

Refer to the example code to filter products based on value:

// Configure pivot table's fields.
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.ColumnField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

// Filter for products with sales volume greater than 7000.
field_Product.PivotFilters.Add(PivotFilterType.ValueGreaterThan, 7000, options: new PivotFilterOptions { AppliedDataField = 0 });

worksheet.Range["A:D"].EntireColumn.AutoFit();

// Save the workbook.
workbook.Save("PivotValueFilter.xlsx");

type=warning
Note: You must set AppliedDataField property of PivotFilterOptions class with the value filter to specify which DataField to filter by.

Apply Date Filter

Refer to the following example code to filter products dated between two specified dates:

// Configure pivot table's fields.
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.ColumnField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.RowField;

var field_Date = pivottable.PivotFields["Date"];
field_Date.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

// Filter for products between two dates.
field_Date.PivotFilters.Add(PivotFilterType.DateBetween, new DateTime(2018, 1, 1), new DateTime(2018, 1, 15));

worksheet.Range["A:D"].EntireColumn.AutoFit();

// Save the workbook.
workbook.Save("PivotDateFilter.xlsx");

Apply Top 10 Filter

Refer to the example code to filter the top twelve products based on value:

// Configure pivot table's fields.
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.ColumnField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

// Filter for top 12 products.
field_Product.PivotFilters.Add(PivotFilterType.Count, 12, options: new PivotFilterOptions { IsTop10Type = true });

worksheet.Range["A:D"].EntireColumn.AutoFit();

// Save the workbook.
workbook.Save("PivotTopTenFilter.xlsx");

Apply Multiple Filters

Refer to the following example code to filter products using both lable and value filters:

// Configure pivot table's fields.
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.ColumnField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

// Allow adding label filter and value filter to a field at the same time.
pivottable.AllowMultipleFilters = true;

// Filter for products with sales volume greater than 7000.
field_Product.PivotFilters.Add(PivotFilterType.ValueGreaterThan, 7000, options: new PivotFilterOptions { AppliedDataField = 0 });

// Filter for products where the product name contains 'mi'.
field_Product.PivotFilters.Add(PivotFilterType.CaptionContains, "mi");

worksheet.Range["A:D"].EntireColumn.AutoFit();

// Save the workbook.
workbook.Save("MultipleFilter.xlsx");