Slicers - connect to two pivot tables with different data sources?

[L33]

Hi,
Can't believe this mistake in my idea took me so long to realise.

I have a list of "machines" a client has as one data source, and a list of "call outs" to those machines as another. I have pivot table pointed at each of them, and then several charts, dashboard elements etc informed by these two pivot tables.

Not all Machines will necessarily have Calls, so I have slicers pointed at the Machines pivot table (for example, that will capture all the different machine "types" even if there were zero calls). Both data sources share a huge number of 'dimension' data, but I've just realised as I'm trying to tie it together, that you can't connect slicers to pivot tables with different data sources no matter how many dimensions they might share.

Is there any hope? Is there any way I can make a slicer selection and have that control both pivot tables? (Both I and my users have Excel 2010).


Thanks in advance.

 

[L33]

Hi Blunky,
Not sure how easy it's going to be for you to use this without VBA experience, but the code I created is here. It's really nothing to do with the Slicers - it's not doing anything to them. You just need to ensure that for every Slicer there is a Page Field for it in each of the pivot tables. The code edits the Page Fields which has the affect of editing the Slicers too. If an end user creates a slicer in the usual way (ie. from the standard Excel ribbon option) then that doesn't create a page field in the pivot table it's linked to. You need to provide the user with a way to insert slicers via VBA, because that way you can ensure that any slicers created also create page fields. This has the added bonus of only allowing your end users to select fields that they ought to be slicing by.

Sub MirrorPageFields()'#####''#####''#####''#####''#####''#####''#####''#####
' This routine mirrors the page field selections made in the "DPM_Pivot" pivot table on the "DPM Pivot Table" sheet
'   in to the other pivot tables that have the same dimension fields.


''Launch this routine via a Worksheet_PivotTableUpdate event - whenever a page field is changed on the DPM_Pivot, call this routine 
'' to mirror those page field edits to the other pivot tables that must also be set up with the same page fields. 


'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Following code has been adapted from code written by Excel MVP Debra Dalgeish as described on her blog
'   http://blog.contextures.com/archives/2012/01/03/change-all-pivot-tables-with-one-selection/


' That piece is about syncing the page fields for multiple pivot tables that use the same data source.
' Slight adaptation made to make page fields sync across pivot tables with different sources.
'
' You can read about the trouble I had working all this out here (!):
'   http://www.mrexcel.com/forum/excel-questions/761687-slicers-connect-two-pivot-tables-different-data-sources.html
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


'This routine ensures that any page field changes in the DPM_Pivot are reflected in the same page fields
' in the "SR_Pivot", the "FTFcat_Pivot" and the "CauseCat_Pivot".
'
' For example,
' Model: Customer has XD1As, XD2Bs and XD3Cs. Only the XD1As and XD2Bs had any calls in a given
'   report period - the XD3Cs had none.
'
' The Slicer presented to the user is pointed at the DPM_Pivot so lists all model types.
' As the user makes selections on the Slicers, the page fields on the DPM_Pivot are updated and
'  as that happens this code is invoked which looks for the same named page field in both the SR_Pivot and the
'  FTFcat_Pivot and makes the same item changes there.
'
' If a user selects "XD3C", then these two SR-data driven pivot tables don't have that as item to select.
'  Page fields MUST have at least one option selected.
'  This code cycles through the options, and the last one is always the item value "(blank)"....
'
'    The "(blank)" option is there because these tables are driven by data in the named range "SR_DATA"
'    which needs to always include one more row (ie. a blank row!) than actual data rows present.
'
' This way, when a Model is selected in the DPM_Pivot that doesn't exist in the SR_Pivot, it'll just return an
'  entirely blank pivot table.
'


' IMPORTANT: All dimension data that appears in "SR Data" must also exist at least once in the "DPM Data"
' This is an issue that likely only comes up with poorly constructed test data - I had several entries in "Family Name" in SR Data that didn't appear in Device Per Month Data
''  but this should never happen in real life - the former is always based on a subset of serials of the latter.
'#####''#####''#####''#####''#####''#####''#####''#####
'


Dim wsMain      As Worksheet    'Sheet "DPM Pivot Table"
Dim ws          As Worksheet    'Sheet "SR Pivot Table"
Dim ptMain      As PivotTable    'Pivot table "DPM_Pivot"
Dim pt          As PivotTable    'Pivot table "SR_Pivot" and "FTFcat_Pivot"
Dim pfMain      As PivotField    'Pagefields in "DPM_Pivot"
Dim pf          As PivotField    'Pagefields in "SR_Pivot" and "FTFcat_Pivot"
Dim pi          As PivotItem
Dim bMI         As Boolean








On Error Resume Next
Set ptMain = Sheets("DPM Pivot Table").PivotTables("DPM_Pivot")




Application.EnableEvents = False
Application.ScreenUpdating = False








'SR_Pivot
'Replicate page field settings of DPM Pivot in to the SR Pivot Table...
Set ws = Sheets("SR Pivot Table")
For Each pt In ws.PivotTables
    For Each pfMain In ptMain.PageFields
        pt.ManualUpdate = True




        Set pf = pt.PivotFields(pfMain.Name)
        bMI = pfMain.EnableMultiplePageItems




        With pf
            .ClearAllFilters




            Select Case bMI
                Case False
                    .CurrentPage = pfMain.CurrentPage.Value
                Case True
                    .CurrentPage = "(All)"




                    For Each pi In pfMain.PivotItems
                        .PivotItems(pi.Name).Visible = pi.Visible
                    Next pi




                    .EnableMultiplePageItems = bMI
            End Select
        End With




        bMI = False




        Set pf = Nothing
        pt.ManualUpdate = False
    Next pfMain
Next pt




Application.EnableEvents = True
Application.ScreenUpdating = True




End Sub

The code I used to insert a new slicer is here:

Sub InsertASlicer()

Application.EnableEvents = False
Application.ScreenUpdating = False


Dim SlicerSelect As String
Dim SlicerCacheTitle As String
Dim i As Integer


Call ProtectionOFF


''Get new slicer name from "Data Control" field
'' which itself is fed by a list in named range "DimensionsPickList" on the sheet "Dimension Fields"
SlicerSelect = Range("DataControl_SlicerToCreate")


''Add "Slicer_" at the front and replace all spaces with underscores
SlicerCacheTitle = "Slicer_" & Replace(SlicerSelect, " ", "_", , , vbTextCompare)
SlicerCacheTitle = Replace(SlicerCacheTitle, "(", "_", , , vbTextCompare)
SlicerCacheTitle = Replace(SlicerCacheTitle, ")", "", , , vbTextCompare)




On Error GoTo Err_Handle




'''*********~~~~~~~~~~~~~~~~**************
''Go to the DPM Pivot Table sheet
Sheets("DPM Pivot Table").Visible = xlSheetVisible
Sheets("DPM Pivot Table").Select
'''*********~~~~~~~~~~~~~~~~**************


''Create the slicer for the main DPM table
ActiveWorkbook.SlicerCaches.Add(ActiveSheet.PivotTables("DPM_Pivot"), _
                                SlicerSelect).Slicers.Add ActiveSheet, , SlicerSelect, _
                                SlicerSelect, 248.25, 152.25, 129.75, 135


''' Add it as a PageField to SR pivot
With ActiveSheet.PivotTables("SR_Pivot").PivotFields(SlicerSelect)
    .Orientation = xlPageField
    .EnableMultiplePageItems = True
    .Position = 1
End With


''Select and cut the new slicer
ActiveSheet.Shapes.Range(Array(SlicerSelect)).Select
Selection.Cut




''Back to "Data Control" sheet
Sheets("Data Control").Select
Range("DataControl_SlicerToCreate").Offset(0, 2).Select    'two columns along from selector field
ActiveSheet.Paste




Sheets("SR Pivot Table").Visible = xlSheetHidden
Sheets("FTF Pivot Table").Visible = xlSheetHidden
Sheets("DPM Pivot Table").Visible = xlSheetHidden
Sheets("AgeBracket Pivot Table").Visible = xlSheetHidden


Sheets("Data Control").Select


SlicerSelect = ""


Call ProtectionON


Application.EnableEvents = True
Application.ScreenUpdating = True


End


Err_Handle:
Sheets("DPM Pivot Table").Visible = xlSheetHidden
Sheets("SR Pivot Table").Visible = xlSheetHidden


MsgBox ("Error - is the slicer already active?")
Sheets("Data Control").Select


Call ProtectionON


Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub

I am in no way a VBA expert myself, and managed to piece this together. There will be all sorts of redundancies and ways of doing things in here that aren't quite the "correct" way, so don't pick up any bad habits before your course!

 

[Blunky]

Thanks heaps for sharing your code. It will definitely help as I try to work this one out.

Thanks again!