**Pivot table filters are filters that are applied to Pivot table fields. Pivot table slicers are drawing objects that map to a Pivot table fields value filter. These two features are supported from EPPlus 5.4.** ## Pivot table filters EPPlus supports the following filters for Pivot tables: - **Item filters** - Filters on individual items in row/column or page fields. - **Caption filters (label filters)** - Filters for text on row and column fields. - **Date, numeric and string filters** - Filters using various operators such as Equals, NotBetween, GreaterThan, etc. - **Top 10 filters** - Filter using top or bottom criterias for percent, count or value. - **Dynamic filters** - Filter using various date- and average criterias. The following code is from Sample 13 (link below) ```csharp ExcelRangeBase range = await LoadFromDatabase(connectionString, ws); var tbl = ws.Tables.Add(range, "ptFilter"); tbl.TableStyle = OfficeOpenXml.Table.TableStyles.Medium23; var pt1=ws.PivotTables.Add(ws.Cells["J1"], tbl, "PivotTable1"); var rowField = pt1.RowFields.Add(pt1.Fields["CompanyName"]); var dataField = pt1.DataFields.Add(pt1.Fields["OrderValue"]); //First deselect a company in the items list. To do so we first need to refresh the items from the range. rowField.Items.Refresh(); //Refresh the items from the range. rowField.Items.GetByValue("Sporer, Mertz and Jaskolski").Hidden=true; //Add a caption filter on Company Name between A and D rowField.Filters.AddCaptionFilter(ePivotTableCaptionFilterType.CaptionBetween, "A", "D"); //Add a value filter where OrderValue >= 100 rowField.Filters.AddValueFilter(ePivotTableValueFilterType.ValueGreaterThanOrEqual, dataField, 100); //Add a second pivot table with some different filters. var pt2 = ws.PivotTables.Add(ws.Cells["M1"], tbl, "PivotTable2"); var rowField1 = pt2.RowFields.Add(pt2.Fields["Currency"]); var rowField2 = pt2.RowFields.Add(pt2.Fields["OrderDate"]); rowField2.Format = "yyyy-MM-dd"; var dataField1 = pt2.DataFields.Add(pt2.Fields["OrderValue"]); var dataField2 = pt2.DataFields.Add(pt2.Fields["OrderId"]); dataField2.Function = DataFieldFunctions.CountNums; ``` ```vbnet Dim range As ExcelRangeBase = Await LoadFromDatabase(connectionString, ws) Dim tbl = ws.Tables.Add(range, "ptFilter") tbl.TableStyle = OfficeOpenXml.Table.TableStyles.Medium23 Dim pt1 = ws.PivotTables.Add(ws.Cells("J1"), tbl, "PivotTable1") Dim rowField = pt1.RowFields.Add(pt1.Fields("CompanyName")) Dim dataField = pt1.DataFields.Add(pt1.Fields("OrderValue")) 'First deselect a company in the items list. To do so we first need to refresh the items from the range. rowField.Items.Refresh() 'Refresh the items from the range. rowField.Items.GetByValue("Sporer, Mertz and Jaskolski").Hidden = True 'Add a caption filter on Company Name between A and D rowField.Filters.AddCaptionFilter(ePivotTableCaptionFilterType.CaptionBetween, "A", "D") 'Add a value filter where OrderValue >= 100 rowField.Filters.AddValueFilter(ePivotTableValueFilterType.ValueGreaterThanOrEqual, dataField, 100) 'Add a second pivot table with some different filters. Dim pt2 = ws.PivotTables.Add(ws.Cells("M1"), tbl, "PivotTable2") Dim rowField1 = pt2.RowFields.Add(pt2.Fields("Currency")) Dim rowField2 = pt2.RowFields.Add(pt2.Fields("OrderDate")) rowField2.Format = "yyyy-MM-dd" Dim dataField1 = pt2.DataFields.Add(pt2.Fields("OrderValue")) Dim dataField2 = pt2.DataFields.Add(pt2.Fields("OrderId")) dataField2.Function = DataFieldFunctions.CountNums ``` See [Sample 4.2-C\#]() or [Sample 4.2-VB]() ## Pivot table slicers Pivot table slicers are drawing objects that map to a Pivot table fields value filter. The slicer can be connected to any item filter in a Pivot table. ```csharp var pivotTable = wsPivot.PivotTables.Add(wsPivot.Cells["A1"], wsSource.Cells[wsSource.Dimension.Address], "PivotTable1"); pivotTable.RowFields.Add(pivotTable.Fields["Company Name"]); pivotTable.DataFields.Add(pivotTable.Fields["Order Value"]); pivotTable.DataFields.Add(pivotTable.Fields["Tax"]); pivotTable.DataFields.Add(pivotTable.Fields["Freight"]); pivotTable.DataOnRows = false; var slicer1 = pivotTable.Fields["Name"].AddSlicer(); slicer1.SetPosition(0, 0, 10, 0); slicer1.SetSize(400, 208); slicer1.Style = eSlicerStyle.Light4; slicer1.Cache.Data.Items.GetByValue("Brown Kutch").Hidden = true; slicer1.Cache.Data.Items.GetByValue("Tierra Ratke").Hidden = true; slicer1.Cache.Data.Items.GetByValue("Jamarcus Schimmel").Hidden = true; //Add a column with two columns and start showing the item 3. slicer1.ColumnCount = 2; //Use two columns on this slicer slicer1.StartItem = 3; //First visible item is 3 slicer1.Cache.Data.CrossFilter = eCrossFilter.ShowItemsWithNoData; slicer1.Cache.Data.SortOrder = eSortOrder.Descending; ``` ```vbnet Dim pivotTable = wsPivot.PivotTables.Add(wsPivot.Cells("A1"), wsSource.Cells(wsSource.Dimension.Address), "PivotTable1") pivotTable.RowFields.Add(pivotTable.Fields("Company Name")) pivotTable.DataFields.Add(pivotTable.Fields("Order Value")) pivotTable.DataFields.Add(pivotTable.Fields("Tax")) pivotTable.DataFields.Add(pivotTable.Fields("Freight")) pivotTable.DataOnRows = False Dim slicer1 = pivotTable.Fields("Name").AddSlicer() slicer1.SetPosition(0, 0, 10, 0) slicer1.SetSize(400, 208) slicer1.Style = eSlicerStyle.Light4 slicer1.Cache.Data.Items.GetByValue("Brown Kutch").Hidden = True slicer1.Cache.Data.Items.GetByValue("Tierra Ratke").Hidden = True slicer1.Cache.Data.Items.GetByValue("Jamarcus Schimmel").Hidden = True 'Add a column with two columns and start showing the item 3. slicer1.ColumnCount = 2 'Use two columns on this slicer slicer1.StartItem = 3 'First visible item is 3 slicer1.Cache.Data.CrossFilter = eCrossFilter.ShowItemsWithNoData slicer1.Cache.Data.SortOrder = eSortOrder.Descending ``` A slicer can also be connected to multiple Pivot tables if the Pivot table share the same cache. In the sample below we add three slicers, one connected to both Pivot tables and the other two just connected to one Pivot table each. ```csharp var wsSource = p.Workbook.Worksheets["PivotTableSourceData"]; var wsPivot = p.Workbook.Worksheets.Add("OneSlicerToTwoPivotTables"); var pivotTable1 = wsPivot.PivotTables.Add(wsPivot.Cells["D15"], wsSource.Cells[wsSource.Dimension.Address], "PivotTable1"); pivotTable1.RowFields.Add(pivotTable1.Fields["Company Name"]); pivotTable1.DataFields.Add(pivotTable1.Fields["Order Value"]); pivotTable1.DataFields.Add(pivotTable1.Fields["Tax"]); pivotTable1.DataFields.Add(pivotTable1.Fields["Freight"]); pivotTable1.DataOnRows = false; //To connect a slicer to multiple pivot tables the tables need to use the same pivot table cache, so we use pivotTable1's cache as source to pivotTable2... var pivotTable2 = wsPivot.PivotTables.Add(wsPivot.Cells["H15"], pivotTable1.CacheDefinition, "PivotTable2"); pivotTable2.RowFields.Add(pivotTable2.Fields["Country"]); pivotTable2.DataFields.Add(pivotTable2.Fields["Order Value"]); pivotTable2.DataFields.Add(pivotTable2.Fields["Tax"]); pivotTable2.DataFields.Add(pivotTable2.Fields["Freight"]); pivotTable2.DataOnRows = false; var slicer1 = pivotTable1.Fields["Country"].AddSlicer(); slicer1.Caption = "Country - Both"; //Now add the second pivot table to the slicer cache. This require that the pivot tables share the same cache. slicer1.Cache.PivotTables.Add(pivotTable2); slicer1.SetPosition(0, 0, 0, 0); slicer1.Style = eSlicerStyle.Light4; var slicer2 = pivotTable1.Fields["Company Name"].AddSlicer(); slicer2.Caption = "Company Name - PivotTable1"; slicer2.ChangeCellAnchor(eEditAs.Absolute); slicer2.SetPosition(0, 192); slicer2.SetSize(256, 260); var slicer3 = pivotTable2.Fields["Order date"].AddSlicer(); slicer3.Caption = "Order date - PivotTable2"; slicer3.ChangeCellAnchor(eEditAs.Absolute); slicer3.SetPosition(0, 448); slicer3.SetSize(256, 260); ``` ```vbnet Dim wsSource = p.Workbook.Worksheets("PivotTableSourceData") Dim wsPivot = p.Workbook.Worksheets.Add("OneSlicerToTwoPivotTables") Dim pivotTable1 = wsPivot.PivotTables.Add(wsPivot.Cells("D15"), wsSource.Cells(wsSource.Dimension.Address), "PivotTable1") pivotTable1.RowFields.Add(pivotTable1.Fields("Company Name")) pivotTable1.DataFields.Add(pivotTable1.Fields("Order Value")) pivotTable1.DataFields.Add(pivotTable1.Fields("Tax")) pivotTable1.DataFields.Add(pivotTable1.Fields("Freight")) pivotTable1.DataOnRows = False 'To connect a slicer to multiple pivot tables the tables need to use the same pivot table cache, so we use pivotTable1's cache as source to pivotTable2... Dim pivotTable2 = wsPivot.PivotTables.Add(wsPivot.Cells("H15"), pivotTable1.CacheDefinition, "PivotTable2") pivotTable2.RowFields.Add(pivotTable2.Fields("Country")) pivotTable2.DataFields.Add(pivotTable2.Fields("Order Value")) pivotTable2.DataFields.Add(pivotTable2.Fields("Tax")) pivotTable2.DataFields.Add(pivotTable2.Fields("Freight")) pivotTable2.DataOnRows = False Dim slicer1 = pivotTable1.Fields("Country").AddSlicer() slicer1.Caption = "Country - Both" 'Now add the second pivot table to the slicer cache. This require that the pivot tables share the same cache. slicer1.Cache.PivotTables.Add(pivotTable2) slicer1.SetPosition(0, 0, 0, 0) slicer1.Style = eSlicerStyle.Light4 Dim slicer2 = pivotTable1.Fields("Company Name").AddSlicer() slicer2.Caption = "Company Name - PivotTable1" slicer2.ChangeCellAnchor(eEditAs.Absolute) slicer2.SetPosition(0, 192) slicer2.SetSize(256, 260) Dim slicer3 = pivotTable2.Fields("Order date").AddSlicer() slicer3.Caption = "Order date - PivotTable2" slicer3.ChangeCellAnchor(eEditAs.Absolute) slicer3.SetPosition(0, 448) slicer3.SetSize(256, 260) ``` ### See also - [Pivot Table Sample 7.2]() or [Pivot Table Sample 7.2-VB]() - [Slicer Sample 7.3-C\#]() or [Slicer Sample 7.3-VB]() - [Pivot tables]()