Use Slicer to select value fields for Pivot table or PowerPivot!

posted Mar 30, 2016, 5:20 AM by Krisztina Szabó   [ updated Mar 31, 2016, 2:50 AM ]

Igor:
What is this?
Freddy: Schwartzwalder Kirschtorte.
The Monster: [off-screen] MMMMMMM!
Freddy: Oh, do you like it? I'm not partial to desserts myself, but this is excellent.


by The FrankensTeam

This post is not about formulas. No charts. And it contains VBA.
Sorry for that... it's a unique case, but I'm sure it can be useful for many readers... So, in the same time we publish it in Hungarian, I take the opportunity to make it available for those who do not speak my native language. :-)

In our example file and also on the below picture you can see a small pivot table showing monthly trend of Profit. We would like to give the possibility to the users to see the trend of another measure. Using slicer it's not necessary to open the Field List pane and look for the measure there - they can easily choose it by one click.


If you choose more measures on the slicer, all will appear under Value fields in the pivot.
Another advantage of this soultion is that you can limit the measures appearing on the slicer: for example you can offer only a sub-set of measures where the above pivot table with monthly trend can be interesting for analyze.

Obviously slicers are for filtering a field and not for choosing field names... So to build up this solution, we will need a short and simple VBA code and an additional pivot table. Let's see how to do it step by step.


First: the original data table, base of the above pivot. On the below picture you can see all the measure names highlighted by blue:


Step 0:

Create your pivot table. You will need the name of this particular pivot. You can find it under Pivottable Tools / Analyze on the left hand side. In the example the name is PivotTable1.
You will also need the name of the worksheet where this pivot table sits. In the example it is named Pivot.

Step 1:

On a blank, new worksheet create a list of measure names (not necessary to include all the measures) and insert a pivot table on this list. Put measure into Row lables:
Name the sheet Slicer.


Step 2:

Define Name choice with reference to the first cell under Row Labels. (Scope of the name should be workbook.)

Step 3:

Insert a slicer for measure, and move it to the worksheet where your original pivot table is. (Pivot sheet)

Step 4:

Open VBA Editor (Alt+F11). Find your workbook under Project explorer, and open the code module of the worksheet where the measure pivot table can be found - in the example it is the Slicer sheet.



Step 5 Code for normal pivot tables:

Follow this step if you use normal pivot table. In case of PowerPivot, go to Step 5 Code for PowerPivot!
Put this VBA code into the code module of the Slicer worksheet.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
   
    Dim ptMain As PivotTable
    Dim pfMeasure As PivotField
    Dim i As Long
  
    On Error GoTo Errorhandler
   
    Set ptMain = Worksheets("Pivot").PivotTables("PivotTable1")
   
    For Each pfMeasure In ptMain.DataFields
        pfMeasure.Orientation = xlHidden
    Next
   
    i = 0
    Do While [choice].Offset(i, 0).Value <> ""
        ptMain.AddDataField ptMain.PivotFields([choice].Offset(i, 0).Value)
        i = i + 1
    Loop
   
    Exit Sub
   
Errorhandler:
Debug.Print Now(), Err.Description
   
End Sub


If you would like to use the solution in your own file, you only have to change the worksheet name "Pivot", the pivot table name "PivotTable1" and the named range [choice] - pay attention this latest appears in two rows!

Step 5 Code for PowerPivot:

Put this VBA code into the code module of the Slicer worksheet.


Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
   
    Dim ptMain As PivotTable
    Dim pfMeasure As CubeField
    Dim i As Long
   
    On Error GoTo Errorhandler

    Set ptMain = ThisWorkbook.Worksheets("Pivot").PivotTables("PivotTable1")

    For Each pfMeasure In ptMain.CubeFields
        If pfMeasure.Orientation = xlDataField Then
            pfMeasure.Orientation = xlHidden
        End If
    Next
   
    i = 0
    Do While [choice].Offset(i, 0).Value <> ""
        ptMain.AddDataField ptMain.CubeFields("[Measures].[" & [choice].Offset(i, 0).Value & "]")
        i = i + 1
    Loop
   
    Exit Sub
   
Errorhandler:
Debug.Print Now(), Err.Description
   
End Sub

Instead of PivotFields we use CubeFields, and a different string expression for AddDataField method.

Leave a comment






ċ
Measure_slicer_test_en.xlsb
(27k)
Krisztina Szabó,
Mar 30, 2016, 5:20 AM
Comments