Developing Business Intelligence Apps for SharePoint

Chapter 20. Slicers

In earlier versions of Microsoft Excel, you can use report filters to filter data in a PivotTable report, but it is not easy to see the current filtering state when you filter on multiple items. In Microsoft Excel 2010, you can use slicers to filter the data. Slicers provide buttons that you can click in order to filter PivotTable data. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to see exactly what is shown in a filtered PivotTable report.

When you use a regular PivotTable report filter to filter on multiple items, the filter indicates only that multiple items are filtered, and you have to open a drop-down list to find the filtering details. However, a slicer clearly labels the filter that is applied and provides details so that you can easily understand the data that is displayed in the filtered PivotTable report.

Slicers are typically associated with the PivotTable in which they are created. However, you can also create standalone slicers that are referenced from Online Analytical Processing (OLAP) cube functions or that can be associated with any PivotTable at a later time.

One of the limitations of report filters was that they had a 1:1 relationship with the PivotTable they were filtering. If you wanted to apply a filter to multiple PivotTables, you were out of luck; you’d have to re-create that filter for each PivotTable. Now, you can connect slicers to PivotTables, PivotCharts, and/or CUBE functions ...