Slicers are sup
er cool! By extending the power of filtering to apply to multiple PivotTables (or PivotCharts) they have really increas ed the reporting and data delivery power of Excel 2010. Not only do they look cool and are fun to interact with, they are also extremely functional. The fact that you can indeed apply a slicer value to multiple PivotTable objects was a big step forward for Excel based dashboarding and reporting. I've used them in nearly every Excel 2010 report that I've written since the product release.
One thing that they are not so good at doing is interacting with objects that are based on a different data source. I know, you’re going to say “But THAT’S how they are designed!“ Sure, it’s true that they are, but as data developers we are often asked to do the impossible (or the improbable)
Often, I've had to tell users that "this isn't possible". Connecting a slicer to an object created from a different data source was one of those things. I finally ran into a situation that I couldn't just brush off as "impossible". I was working on some Excel reporting that used PivotTables based on a TSQL query. I needed to create a slicer based on one of the data sets and have it interact with the other.
I decided that I was going to solve this problem, and solve it I did. It DOES require the use of VBA, which I normally try to avoid, but in this case, it was the right solution. Let's take a look at the anatomy of this solution.
The Players
First, we have to take note of some of the objects that we are going to be interacting with.
- ActiveWorkbook – The currently open Excel workbook.
- Slicer – A slicer object embedded in the worksheet. It will have a name (typically SlicerN where N is a number designating which order it was placed)
- PivotTable –A PivotTable object embedded in a report (see naming above)
- PivotField – A specific field in the PivotTable
The Order of Operations
In order to complete this solution, some understand of what needs to be done is required. Here is the basic order of operations required to make this happen:
- Identify all of the correct objects in play
- Collect a list of selected values in the slicer object
- Find the correct field to filter on in the PivotTable. This field NEEDS to be what’s known as a PAGE type field. Most commonly, this is a field in the FILTERS section of the PivotTable.
- Pass the list of selected values to the PivotTable object.
Only a few Limitations:
- OLAP data sources can accept multiple slicer values. Non-OLAP data sources can only accept ONE slicer value
- As this solution works with strings, the values in the slicer NEED to match the values in the PivotObject Creating them based on the same field should ensure this to be the case.
- This does require a macro-enable workbook, which can cause an extra layer of security to be used in collaboration scenarios.
- Slicers with extremely large number of filter options may cause performance issues.
The code
Ok, this is where we get a little technical. Here is the basic code needed to pass a SINGLE slicer value to a PivotTable. Modification would need to be made — and are shown in comments – to allow for multiple values to be passed. REMEMBER. THIS IS IMPORTANT! Multiple values can ONLY be passed if the data source is from an OLAP database. This is from the Excel Object Model documentation. If you try to pass multiple values to a non-OLAP source object, a run-time error will occur.
' Author: Josh Fennessy (http://www.joshuafennessy.com)
'
' This short method will allow a value from a selected slicer to be
' passed to a PivotObject that is connected to a different data source
' Since this is based on string matching, data would need to be indentical
' from the two sources.
'
' Use cases for this could be PivotObject based on different T-SQL queries or
' cubes/perspectives in the same SSAS database.
' We want this code to fire with an object connected to our slicer
' is updated. This means that the slicer was likely interacted with.
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'find the right object in the workbook. AKA: The Source
If Target.Name = "PivotTable0" Then
Dim objSlicerItem As SlicerItem
Dim ptSummary As PivotTable
Dim ptField As PivotField
Dim objField As PivotField
Dim strValues() As String
'Dim intIndex as Integer
'get a reference to the PivotTable to be filtered AKA: The Target
Set ptSummary = Worksheets("Sheet1").PivotTables("Sheet1")
'locate the correct field (by name) to be filtered
'this field MUST be a "page" type -- on a normal PivotTable this
'is the FILTERS section
For Each ptField In ptSummary.PageFields
If ptField.Name = "Field1" Then
Set objField = ptField
End If
Next
'intIndex = 1
'loop through each item in the slicer and check to see if it's selected
'if so, add it to the current pages property of the field selected above
For Each objSlicerItem In ActiveWorkbook.SlicerCaches.Item(1).SlicerItems
If objSlicerItem.Selected = True Then
''NOTE: The following commented code is for use with PivotTables connected to an OLAP source
'ReDim Preserve strValues(1 To intIndex)
'strValues(intIndex) = objSlicerItem.Value
'intIndex = intIndex + 1
'this is for setting a single filter value -- required for non-olap sources
objField.CurrentPage = objSlicerItem.Value
End If
Next
''NOTE: If working with a PivotTable connected to an OLAP source
'' You can set the CurrentPageList property to a string array
'' of values. The EnableMultiplePageItems property also
'' needs to be set to TRUE.
'' This means that when working with non-OLAP sources, only single
'' select filters are enabled. Important to note when working with
'' slicers.
'objField.EnableMultiplePageItems = True
''use the following command to clear all filters before setting new values
''important when setting multi-value filters.
'objField.ClearAllFilters
'objField.CurrentPageList = strValues()
End If
End Sub
