Just One Slice Please

We all use pivot tables, don’t we? And with the introduction of slicers, we have a much more friendly and nicer way of filtering the results than the old pivot filters.

One drawback of slicers is that it is not possible to restrict slicer selection to a single item. This can be useful in a number of situations, but is not a facility provided with facilities.

Unfortunately, Slicers suffer from a Microsoft practice that is getting more prevalent as Excel’s BI features are extended, there is little or no exposure of slicers to the VBA object model, which would allow us to control them.

In this post, I will present one way of providing this control, using some simple techniques and a bit of VBA. I will demonstrate this with a simple table of sales by sales-person over year. I added a slicer for the year, as shown in the following image.

As you are no doubt aware, we can select 1, 2 or 3 years in the slicer, and the connected pivot table will reflect that selection. In order to restrict the slicer to a single selection, I added another pivot, it only needs the Year field, and some event code that traps any change to the second pivot and restrict it to the first of any selected pivot items.

The detailed steps are as follows.

First I added another pivot table based upon the sales table, which I called ptYearControl. In this pivot, I just added the Year field to the rows area.

This pivot needs to be connected to the slicer that controls the first pivot. This is done by selecting the slicer, and then in Slicer Tools>Options>Slicer>Report Connections check the box by the second pivot name, ptYearControl in this case.

We now have both pivots connected to our slicer, and any selection in the slicer will be reflected back in both pivot tables. But we can still select multiple items from the slicer, so how do we restrict this?

Here we need some good old VBA (the language that won’t die, no matter how hard Microsoft tries). Unlike slicers, pivot tables are exposed to the object model, so we have a PivotTableUpdate event, and we will use this. The code for this event, which will reside in the code module for the worksheet that the ptYearControl pivot is within. The code is

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Const CONTROL_PIVOT As String = "ptYearControl"
Const CONTROL_FIELD As String = "Year"
Dim pi As PivotItem
Dim itemFound As Boolean

    On Error GoTo wp_exit

    Application.EnableEvents = False

    If Target.Name = CONTROL_PIVOT Then

        With Target.PivotFields(CONTROL_FIELD)

            For Each pi In .PivotItems

                If pi.Visible Then

                    If itemFound Then

                        pi.Visible = False
                    Else

                        itemFound = True
                    End If
                End If
            Next pi
        End With
    End If

wp_exit:
    Application.EnableEvents = True
End Sub

How does this work? It simply loops through all pivot items in the targeted pivot table, and hides each visible item, except the first. I use a couple of constants for the pivot table name and name of the pivot field, to make it simple to amend. Events are disabled so that the changes that we make to the pivot table in the code do not cause a cascade of events (changes triggering the event, which makes changes that trigger the event again, and so on).

That is all there is to it. The second pivot table is only there to help us control the slicer.

It would be nice if we could make it not visible, but I guess that the original designers never envisaged a pivot table that was not to be seen, but it can be moved off-stage as far as necessary to get it out of the way. Another option is to put the control pivot table, and the event code, on a separate sheet, which is probably the proper way to do it (and this worksheet can be hidden).

I thought this would work just as well with a PowerPivot pivot table, but when I tried it first did not recognise my field name. No great surprise there, the PowerPivot datamodel is a cube, and cube fields require a qualified reference; in this case I had to use a value of [tblSales].[Year].[Year]. After that, I got the error ‘unable to set the visible property of the pivotitem class’ when trying to make subsequent pivot items not visible. This is not hard to correct, and is a more elegant solution; as this is a cube we can directly set the visibility of all of the pivot items using the VisibleItemsList property, setting this to a single item array, the first visible item found. The code in this case is as follows:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Const CONTROL_PIVOT As String = "ptYearControl"
Const CONTROL_FIELD As String = "[tblSales].[Year].[Year]"
Dim pi As PivotItem
Dim itemFound As Boolean

    On Error GoTo wp_exit

    Application.EnableEvents = False

    If Target.Name = CONTROL_PIVOT Then

        With Target.PivotFields(CONTROL_FIELD)

            For Each pi In .PivotItems

                If pi.Visible Then

                    .VisibleItemsList = Array(pi.Name)
                    Exit For
                End If
            Next pi
        End With
    End If

wp_exit:
    Application.EnableEvents = True
End Sub

I have tried this quite a lot with Excel pivot tables, not so much with PowerPivot pivot tables, but it seems to work fine, and gives us that ability to restrict a slicer to a single selection. If you use this method, do report back on how it works for you, whether you come across any issues, problems, or oddities. And if you have any other way of achieving this level of control, do share it here.