Display User’s Slicer Selections: A Macro to Automatically Create the Formulas

 
Slicer Selections Displayed via Formulas

Note the “Readout” that Displays the User’s Slicer Selections:
Now Do This for Every Pivot in Your Workbook With One Click!

A Common Trick, Now Automated

This is something we do all the time at Pivotstream – we write formulas that capture user slicer selections and then display those selections back to them.

We do this via hidden report filters:

image

(Yes, we could also do this with cube formulas, but we started out (literally years ago now) using this approach and we’ve just kinda stayed with it.  I’m not sure cube formulas would be better, but they might be.)

Why is the Readout Useful?

Why do we do this?  Well, for one thing, the Download Snapshot feature on the server does NOT download the slicers – you get a big blank white space where the slicers were, which isn’t terribly helpful.  It leaves you wondering what you had selected.

It’s also useful when there are slicers on other sheets impacting your current sheet.  And even on a single sheet, it’s often nice to have a compact readout of your selections without having to scroll (or even scan with your eyes) to see what selections you have made.


Isn’t This a Major Hassle, Adding All the Report Filters, etc?

You bet it is.  It’s a major headache.  But yesterday I wrote some macros that do it all for us Smile

The macros don’t run at all while the user is running the report.  In fact they don’t even have to be saved in the workbook (the workbook can remain an XLSX, no need to save it as XSLM or XLSB).

Summary of What the Macros Do

  1. Look at every sheet in the workbook
  2. If the sheet is visible, AND it contains a pivot, create a new sheet (a “clone” sheet), named similar to the original but with the suffix “Ctxt” – this is short for “Context,” which is what we call the readout
  3. Create a pivot on the new sheet
  4. For every slicer on the original pivot, create a report filter on the new pivot
  5. Connects all of the slicers on the original pivot to the new pivot
  6. Creates a bunch of formulas on the new sheet that ultimately converge in cell G1
  7. Hides the new sheet
  8. Moves on to the next sheet/pivot

Instructions

If you are unfamiliar with macros, go watch the first few minutes of this video – that will make these steps much easier to understand.

  1. Copy the macros below
  2. Using the Developer tab on the Excel Ribbon (you may have to unhide this tab – it is hidden by default), launch the Visual Basic window
  3. Paste the macros into a Module – either in the current workbook or in your personal.xlsb (which you may have to create
  4. Run the CloneAllVisiblePivots macro
  5. Wait for it to complete
  6. On your report sheet (named ReportSheet for instance), create a formula that references G1 on the hidden version of that sheet  (=ReportSheetCtxt!G1 for instance):

image

Last step:  reference cell G1 on the Ctxt version of the report sheet

Limitations

As always, I write my macros like it’s a prison fight – down and dirty, whatever gets the job done Smile

I know for certain that these macros fail if you have more than one pivot on a sheet, or if you have sheets with long names that differ only in the last few characters.

I’m sure they fail in other cases too, but those are the ones I am aware of.

Here They Are

Copy paste these into your Personal.XLSB or wherever you want to put them:

Sub CloneAllVisiblePivots()
    Dim oSheet As Worksheet
    Dim oPivot As PivotTable
   
    For Each oSheet In ActiveWorkbook.Sheets
        If oSheet.Visible = xlSheetVisible Then
            If oSheet.PivotTables().Count > 0 Then
                CloneCurrentPivotForContextFormulas oSheet.Name, oSheet.PivotTables(1).Name
            End If
        End If
    Next
   
End Sub

Sub CloneCurrentPivotForContextFormulas(sSheet As String, sPivot As String)
    Dim oSourceSheet As Worksheet
    Dim oDestSheet As Worksheet
    Dim oSourcePivot As PivotTable
    Dim oDestPivot As PivotTable
   
    Set oSourceSheet = ActiveWorkbook.Sheets(sSheet)
    Set oSourcePivot = oSourceSheet.PivotTables(sPivot)
   
    ‘Create and rename new sheet
    Set oDestSheet = ActiveWorkbook.Sheets.Add
    If Len(oSourceSheet.Name) <= 27 Then
        oDestSheet.Name = oSourceSheet.Name & “Ctxt”
    Else
        oDestSheet.Name = Left(oSourceSheet.Name, 27) & “Ctxt”
    End If
   
    ‘Create pivot on new sheet
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections(“PowerPivot Data”), Version:=xlPivotTableVersion14 _
        ).CreatePivotTable TableDestination:=oDestSheet.Cells(1, 1), _
        TableName:=”PivotTable1″, DefaultVersion:=xlPivotTableVersion14
    Set oDestPivot = oDestSheet.PivotTables(1)
   
    ‘Create page filters on second pivot, connect slicers, add formulas
    If oSourcePivot.Slicers().Count > 0 Then
        CreatePageFilterForEachSlicerOnClonedPivot oSourceSheet.Name, oSourcePivot.Name, oDestSheet.Name, oDestPivot.Name
        ConnectAllSlicersOnPivot1ToPivot2 oSourceSheet.Name, oSourcePivot.Name, oDestSheet.Name, oDestPivot.Name
        CreateContextFormulas (oDestSheet.Name)
    End If
   
    ‘Hide cloned sheet
    oDestSheet.Visible = xlSheetHidden
   
End Sub
Sub CreateContextFormulas(sSheet As String)
    ActiveWorkbook.Worksheets(sSheet).Activate
   
    ‘Add first row of formulas
    Cells(1, 3).FormulaR1C1 = “=IF(RC[-1]<>””(All)””,1,0)”
    Cells(1, 4).FormulaR1C1 = “=IF(SUM(C[-1])>1,””,””, “”””)”
    Cells(1, 5).FormulaR1C1 = “=IF(RC[-4]=””””,””””,(IF(RC[-3]=””(All)””,””””,RC[-4]&”” = “”&RC[-3]&RC[-1])))”
   
    ‘Now fill those three formulas down
    Range(“C1:E1″).Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range(“C1:E15″), Type:=xlFillDefault
   
    ‘Now add the formula that concats them all together
    Cells(1, 7).FormulaR1C1 = “=””Selection: “” &RC[-2]&R[1]C[-2]&R[2]C[-2]&R[3]C[-2]&R[4]C[-2]&R[5]C[-2]&R[6]C[-2]&R[7]C[-2]&R[8]C[-2]&R[9]C[-2]&R[10]C[-2]&R[11]C[-2]&R[12]C[-2]&R[13]C[-2]&R[14]C[-2]”
   
End Sub

 

Sub CreatePageFilterForEachSlicerOnClonedPivot(sSourceSheet As String, sSourcePivot As String, sDestSheet As String, sDestPivot As String)
    Dim oSlicer As Slicer
    Dim oSlicerCache As SlicerCache
    Dim sField As String
    Dim oPivot1 As PivotTable
    Dim oSheet1 As Worksheet
    Dim oPivot2 As PivotTable
    Dim oSheet2 As Worksheet
   
    Set oSheet1 = ActiveWorkbook.Sheets(sSourceSheet)
    Set oPivot1 = oSheet1.PivotTables(sSourcePivot)
    Set oSheet2 = ActiveWorkbook.Sheets(sDestSheet)
    Set oPivot2 = oSheet2.PivotTables(sDestPivot)
   
    For Each oSlicer In oPivot1.Slicers
        sField = oSlicer.SlicerCache.SourceName
        oPivot2.CubeFields(sField).Orientation = xlPageField
    Next
   
End Sub

Sub ConnectAllSlicersOnPivot1ToPivot2(sSheet1 As String, sPivot1 As String, sSheet2 As String, sPivot2 As String)
    Dim oSheet1 As Worksheet
    Dim oPivot1 As PivotTable
    Dim oSheet2 As Worksheet
    Dim oPivot2 As PivotTable
    Dim oSlicer As Slicer
   
    Set oSheet1 = ActiveWorkbook.Sheets(sSheet1)
    Set oPivot1 = oSheet1.PivotTables(sPivot1)
    Set oSheet2 = ActiveWorkbook.Sheets(sSheet2)
    Set oPivot2 = oSheet2.PivotTables(sPivot2)
   
    For Each oSlicer In oPivot1.Slicers
        oSlicer.SlicerCache.PivotTables.AddPivotTable oPivot2
    Next
   
End Sub

4 Responses to Display User’s Slicer Selections: A Macro to Automatically Create the Formulas

  1. Tracey says:

    Hi. Good post – but I’m new to PP and have a fairly basic issue with slicers. I have some info coming off our SharePoint farm that shows how long connections are held and they are non-uniform periods of seconds/minutes/hours. Because of this I’ve had to manually sort the pivot table. When I add a chart and slicers the chart takes note of the ordering – the pivot ignores it. Logic says to me that having a choice to set the slicer as alpha sort or “data source order” should have some effect, but none of the 3 options available make any difference. Any ideas? Could you write a post on this topic?

  2. Charles RIce says:

    Great post but is there a solution for when more than one item is selected from a slicer or filter? Having it say (Multiple) isn’t very informative.

  3. Arthur says:

    Nice and creative article ‘powerpivotpro’. By any chance have you had time to look at the powerpivot workbooks?

Leave a Comment or Question