Any Way You Slice It

For the last year, I’ve been doing sessions on PowerPivot for Excel at many SQL Saturday events in Florida.  How does that relate to SharePoint you may ask?  Excel documents can be published to SharePoint if you have the Enterprise edition of SharePoint and if you have PowerPivot for SharePoint installed on your server as well, you can manipulate the Pivot tables and charts to drill down through information as well as change the filter or slicers on the chart.  What are slicers?  Glad you asked.

Prior to Microsoft Office Excel 2010, Pivot Tables and Pivot Charts only had Report Filters.  As the name implies a Report Filter allowed the user to select one of the dimension fields to filter the data displayed in the chart or table.  As shown in the figure to the right, it would show the name of the selected field in one cell with a dropdown list in the next field of the possible filter values.

By default all of the data is selected.  However, using a tree structure, you can see the possible values for the field.  In this case, there are only three continents listed, each of which is also selected.  If you first make sure the checkbox ‘Select Multiple Items’ is selected, you can choose to deselect any of the continents by unchecking the corresponding checkbox.  For example, you could deselect Asia and Europe to show only data for North America.  (Note, you can also toggle the All checkbox and then just select North America as well.).

When you click OK, the dropdown rolls back up and you see the name of the selected filter value.  Or do you?  If you select more than one item, and close the dropdown, you see instead of the selected values, a placeholder that simply says: (Multiple Items) as shown in the following figure.

This is not very helpful when viewing the pivot chart or table to understand what is really going on.  Of course you could always open the dropdown to examine the filter (note the drop down arrow shows a funnel to visually indicate that a filter is in effect).  However, that requires extra keystrokes.

Another problem with the Report Filter feature is that you cannot select one of the dimension fields that you are already using in the chart or table to define a filter.  So if you are using Continent Name as one of your vertical or horizontal dimensions, you cannot filter the report by Continent Name.

Finally if you had several pivot charts or pivot tables in the same worksheet or even in the same workbook, there was no way to tie them all together with a single filter that you could change in one place and affect all of the charts and tables.  Rather you had to add a report filter definition for each chart and table and if you changed the value that you were filtering on, you can to remember to change each of the other report filters for their corresponding chart or table. 

Enter Microsoft Office Excel 2010 and a new feature called slicers.  In some ways a slicer is like a report filter in that it limits the data included in the current table or chart.  For example the following figure shows a slicer for Continent Name as displayed for a pivot table.

In the figure to the right, you see that all three continents are shaded indicating that they are all selected.  However, you can easily select or deselect a continent by simply clicking on its name.  By simply clicking on the name of a continent, you select it and deselect any or all other continents.  That is great when you want to look at one value from your filter criteria at a time, but what if you want to look at more than one?

Great question, you can click the name of the first filter value then press and hold the shift key while you click on the name of the last filter value you want to include.  This action includes all of the values between and including the two values you selected, a contiguous selection.  Since the field values are listed alphabetically, this is great if the values you want to include follow one after the other, but what if they don’t?

Then you can click on the name of the first filter you want and press and hold the ctrl key while you click on the name of each of the subsequent filter values you want to include.  This action allows you to select non-contiguous values, but the downside is that you want to select each value that you want.

If you want to get fancy, you can begin with a contiguous selection with the shift key and then add to it by switching to the ctrl key to add outliers.

But the real benefit is that the slicer always shows all of the values that you have included in your filtered pivot table or chart by highlighting the value.  Furthermore, if you have multiple slicers like the following figure that shows countries and continents and you select a continent, countries not in the selected continent are listed separately and dimmed to indicate that the raw data includes records with those values, but the other filter criteria have eliminated those records as possible values.

Slicers also remove the constraint of using the same field as a dimension in the pivot table or chart as well as a filter criteria in the slicer. 

Finally, if you want to use the same slicer that you defined on the worksheet that contains your pivot table for your pivot chart on a different worksheet (or even in the same worksheet), you can click anywhere within the pivot chart (or a second pivot table) and then using the Options ribbon in the PivotTable Tools group, select the bottom portion of the Insert Slicer button as shown in the figure to the left.


Interestingly, the corresponding option for the pivot chart (remember to click on the chart first) is the Analyze ribbon in the PivotChart Tools.  (Seems like someone was not talking to someone else during the development process.)  Anyway, from the resulting dropdown select Slicer Connection.  If you choose Insert Slicer you can just adding a slicer specifically for that table or chart.  The resulting dialog will show all of the slicers defined in the current workbook with the field name and the worksheet where they are defined.  The figure below shows an example of the Slicer Connections dialog.

Simply click the checkbox of the desired slicer to reuse it for the current table or chart.  You can even include slicers defined on multiple worksheets.  Now when you change the slicer on the original worksheet where you defined it, it will change the filter for the data in that table or chart and all other tables or charts that you have connected to it.

In future weeks, I’ll explore more features in PowerPivot and SharePoint mixed with an occasion opinion piece for something that is bugging me.  See you later.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s