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.

Basic Analysis

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.

Control Pivot

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.

Set Slicer Control

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.

Single Slice

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.

Advertisements
This entry was posted in Pivots, Power Pivot and tagged , , , , , . Bookmark the permalink.

16 Responses to Just One Slice Please

  1. Ken Puls says:

    Nice stuff, Bob!

  2. Pingback: (Excel) Just One Slice Please | Bob’s BI Ramblings | juno7273

  3. andypope says:

    Bob, only thing I would add would be a IF test of pt.name against Target.name and only execute the For each Loop once when the ptYearControl table updates.

    • wessexbob says:

      Good point Andy. It doesn’t actually cause any problems, but it is better coding, and a bit more efficient. I have amended the code in the post.

      In reality, the extra pivot is not absolutely necessary, I could have directed the changes at the Sales pivot table, but it just seems better and more flexible to me to have a separate, dedicated control pivot table.

  4. Fred says:

    Hello Bob, This sounded promising, though I’m trying to limit Name instead of Year.
    However, I re-created your spreadsheet using powerpivot tables without success. I’m using Excel 2013 64bit and wondering if the coding is still applicable or if it needs to be tweaked.

    • wessexbob says:

      Fred, I have just tried that and it works fine with 64-bit Excel 2013 for me.

      Did you do the following three things:
      – change the constant CONTROL_FIELD in the procedure declaratives to reflect the other field name,
      – make sure that the constant CONTROL_PIVOT was referring to the controlling pivot table
      – connect both pivots to the slicer?

  5. Fred says:

    Bob, In re-creating your original scenario, I formatted the sales data as a table, named it ‘Sales’ & added it to the data model. I named the main pivot table ‘ptSales’ & the control pivot ‘ptYearControl’ & connected the slicer to both pivots.I copied & pasted your script to my ‘test worksheet’ code module. Went back to my slicer & selected two dates……& they ‘both’ appear instead of just one in my main table.
    So, somewhere I’ve screwed up & done something stupid. Maybe I’ll try it again in a new clean file.

  6. Fred says:

    Yep, works on a clean file, hmm.

  7. Fred says:

    Mate, I’m still having problems. I’ve had a million tries at re-creating your PowerPivot scenario without success (well, at least 30). Put in some MsgBox’s to see each line progress…..nothing. Finally worked out how to view a quick watch window for the ‘constant’ selections & they show “Out of context”. So it appears that the code is falling over at the start and isn’t running at all.
    This is driving me nuts! Is there a way to send you my file off-line?

    • wessexbob says:

      I’ve got an old gmail account you could use Fred, xlDynamic (domain) gmail (dot separator) com. Give me a personal email I can respond to f necessary, and I will keep checking it for a couple of days.

  8. Fred says:

    I guess I stuffed up your email address or you’ve been too busy, no matter Bob, I understand.
    I kept playing around with your PowerPivot code and ended up re-typing each row. When I left off the ‘tbl’ from the Control_Field string (just to see what it would do) …. Bingo! it works. Copied it over to my Dashboard file, changed the Constants and it works, added a second set of constants and coding, and it works….. You bloody ripper mate!

  9. Olimpia says:

    Hi Bob! I hope you are doing great! thank you for the article, it is very helpful! However, I have a lot of slicers connected with my pivot and I need couple of them to be only-one-choice-possible. Do you have any ideas how to do it? I created two separate pivots for each slicer and I tried to connect it in one workbook. I stopped here:

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Const CONTROL_PIVOT As String = "ptViewControl"
    Const CONTROL_FIELD As String = "View"
    Const CONTROL_PIVOT2 As String = "ptStatusControl"
    Const CONTROL_FIELD2 As String = "STATUS"
    
    Dim pi As PivotItem
    Dim itemFound As Boolean
    
        On Error GoTo wp_exit
    
        Application.EnableEvents = False
    
        If (Target.Name = CONTROL_PIVOT Or Target.Name = CONTROL_PIVOT2) Then
    
            With (Target.PivotFields(CONTROL_FIELD) Or Target.PivotFields(CONTROL_FIELD2))
    
                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

    Thanks for your help Bob! 🙂

  10. wessexbob says:

    Hi Olimpia,

    your VBA code is a tad askew in the OR statements.

    This mod should do it

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Const CONTROL_PIVOT1 As String = "ptViewControl"
    Const CONTROL_FIELD1 As String = "View"
    Const CONTROL_PIVOT2 As String = "ptStatusControl"
    Const CONTROL_FIELD2 As String = "STATUS"
    Dim CONTROL_FIELD As String
    Dim pi As PivotItem
    Dim itemFound As Boolean
    
        On Error GoTo wp_exit
    
        If Target.Name = CONTROL_PIVOT1 Then
        
            CONTROL_FIELD = CONTROL_FIELD1
        ElseIf Target.Name = CONTROL_PIVOT2 Then
        
            CONTROL_FIELD = CONTROL_FIELD2
        Else
        
            Exit Sub
        End If
    
        Application.EnableEvents = False
        
        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
    
    wp_exit:
        Application.EnableEvents = True
    End Sub
  11. Giacomo says:

    Hello
    thanks for this post, very useful. One thing though, look like the “clear all” button in the slicer still does its job. How to prevent this to happen?
    Thanks

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s