Have you ever created a monstrosity of a dashboard that has MANY slicers and MANY charts? Well, I have, and it gets to be unruly quite fast.
OR, have you ever had to take over a dashboard developed by someone who is no longer around to make modifications or support the file? Again, I have and it can be a bit daunting to have to figure out what they were thinking and their organizational strategies.
One of the biggest issues that I have had is knowing which slicers are connected to which charts. Yes, I know you can look at each and every slicer, go to "Report Connections" and see what is checked, but how do you then quickly look at the other related slicers that should be connected to the same charts as the first?
Sure you could grab a couple of snapshots or write them down, but I must admit, that doesn't work for me. Or to quote a viral YouTube sensation "Ain't nobody got time for that!"
So, after much complaining and digging through the VBA object library and looking around the internet (where I found the incomplete start for this code), I have developed the following:
Option Explicit
Sub MultiplePivotSlicerCaches()
Dim oSlicer As Slicer
Dim oSlicercache As SlicerCache
Dim oPT As PivotTable
Dim oSh As Worksheet
Dim Row As Integer
Dim ObjChart As Chart
Dim x As String
'Create New Worksheet & Table Structure
Sheets.Add.Name = "HIDDENSlicerConnections"
Sheets("HIDDENSlicerConnections").Cells(1, 1).Value = "Slicer Name"
Sheets("HIDDENSlicerConnections").Cells(1, 2).Value = "Pivot Parent Name"
Sheets("HIDDENSlicerConnections").Cells(1, 3).Value = "Pivot Name"
Sheets("HIDDENSlicerConnections").Cells(1, 4).Value = "Chart Title"
'Add Slicer Cache Name and Pivot Table Name
Row = 2
For Each oSlicercache In ThisWorkbook.SlicerCaches
For Each oPT In oSlicercache.PivotTables
oPT.Parent.Activate
Sheets("HIDDENSlicerConnections").Cells(Row, 1).Value = oSlicercache.Name
Sheets("HIDDENSlicerConnections").Cells(Row, 2).Value = oPT.Parent.Name
Sheets("HIDDENSlicerConnections").Cells(Row, 3).Value = oPT.Name
If InStr(oPT.Name, "Chart") < 1 Then GoTo Skip
Sheets("HIDDENSlicerConnections").Cells(Row, 4).Value = oPT.PivotChart.Chart.ChartTitle.Caption
Skip:
Row = Row + 1
Next
Next
End Sub
This code will create a table on a new spreadsheet called "HIDDENSlicerConnections" and list out every slicer and the chart/table that it is connected to.
The first file that I used this on, I discovered that with all of the filters and charts and tables, I had a combined 238 connections in my file. Again, "Ain't nobody got time for that!" - Okay, I'm done quoting....maybe.
Hold on, this is a cool macro, but it's no more than a raw data puke! We need to make sense of it.
Next step is to create a pivot table based on the dataset. Highlight the whole dataset, insert > PivotTable. I prefer to keep the table on the same sheet as the dataset so choose a location and click OK.
Now, to organize the data. Do the following:
Row = Pivot Name
Row = Chart Title
Column = Slicer Name
Value = Pivot Parent Name: COUNT()
This will now create a table that shows the chart/table name down the left and Slicer name across the top and show a "1" for each connection that exists. SWEET! No more snapshots and no more writing! I'm down with that.
Edit: To use the Macro, follow these steps:
- Open your file > Press Alt + F11 to Open the VBA Editor
- In the left-hand pane, find the VBAProject([YOUR FILE NAME HERE]).
- Right-click the "Microsoft Excel Objects" Folder and Select Insert > Module
- Copy/Paste the code into the module.
- Click the "Play button" or Press F5 on the Keyboard to run the Macro.
If you are deploying your dashboard to SharePoint using Excel Services, your files cannot use Macros. Simply continue to save as a .xlsx file and all will be well. The Macro may not save, but you've already run it, and you can always find again right here on the blog!
No comments:
Post a Comment