How to list relationships between Slicers and Tables/Charts VBA

Yep, ANOTHER post... To be honest, I've been working on this solution for a while and was quite excited when I finally got it working.  So, now I get to share it with you!

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!

Dynamic Chart Axis with Slicer!!! - Switching between Year/Quarter/Month/Week views

OH MY GOODNESS!!!!!

I've been looking for this for so long it's ridiculous...  I've always wanted to be able to simply use a slicer to change the row values on my pivot tables/charts from Year to Quarter to Month without needing multiple tables/charts. 

This guy is brilliant and very kind to share with all of us.  Please check his article.  It's awesome.

http://tinylizard.com/dynamically-changing-chart-axis/ 

Oh man, my job is so much easier now... :)

Catching those slicer selections: A macro to do the work for you...

I've been working with PowerPivot for some time now and one thing that I've noticed on each of my solutions that I create is that I spend too much time creating the hidden sheet that "catches" each of the slicer selections that are active.  This can sometimes take hours if the project is big enough.

So, this last time, I decided to take 20 minutes out of my day do a little research on the Google and write a macro that would do exactly that for me.  Too much time is now almost no time at all and I have a bit of my work day back.  

Here is what I use. If you have suggestions on how to make it more efficient or just better practice (self-taught here folks!), please share in the comments!

Summary of Macro: 

Create a worksheet called "HiddenFilterCatch".  

Add Cubeset and CubeRankedMember formulas to new sheet (Prompting user for max number of options per slicer to catch)

Please excuse the blog formatting as I use a bit more screen real estate when coding.  Enjoy!

*Disclaimer - this worked for me in my specific scenario. You may/may not need to adjust the code to fit your situation.  :)

Option Explicit

Sub MultiplePivotSlicerCaches()

    Dim oSlicer As Slicer

    Dim oSlicercache As SlicerCache

    Dim oSh As Worksheet

    Dim SlcrName As String

    Dim Col As Integer 'Column Number

    Dim Connect As String, Comma As String, oPrn As String, cPrn As String 'Formula pieces

    Dim i As Integer 'Number of Slicers

    Dim Fmla As String 'Completed Formula

    Dim ws As Worksheet

    Dim x As Integer 'Max # of Filters determined by user

    Dim z As Variant

    Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual

    

'Add New Worksheet "HiddenFilterCatch"

    z = MsgBox("Do you have a 'HiddenFilterCatch' Worksheet already?", vbYesNo)

    If z = vbNo Then

        Set ws = Sheets.Add(After:=Sheets(Sheets.Count))

        ws.Name = "HiddenFilterCatch"

    End If

'Count Slicers in Workbook (For Trouble Shooting Code Only)

    'i = 0

    'For Each oSlicercache In ThisWorkbook.SlicerCaches

    'i = i + 1

    'Next

    

'Set Variables for "=Cubeset()"

    Col = 3 'Column Number

    Connect = "=Cubeset(" & Chr(34) & "ThisWorkBookDataModel" & Chr(34) & ","

    Comma = ","

    oPrn = "("

    cPrn = ")"

    

       

'Distribute Cubeset Formulas onto spreadsheet

    Sheets("HiddenFilterCatch").Activate

    For Each oSlicercache In ThisWorkbook.SlicerCaches

            SlcrName = oSlicercache.Name

            Fmla = Connect & SlcrName & Comma & Chr(34) & SlcrName & Chr(34) & cPrn

            Cells(9, Col).Formula = Fmla

            Col = Col + 1

    Next

    

'Set Variables for "=CubeRankedMember()"

    Connect = "=IFERROR(CUBERANKEDMEMBER(" & Chr(34) & "ThisWorkBookDataModel" & Chr(34) & ","

    

'Distribute CubeRankedMember Formulas onto spreadsheet

    Col = 3

    x = InputBox(Prompt:="What is the Maximum Number of filters you would like to show?", Title:="Maximum Number of Filters", Default:="4")

    For Each oSlicercache In ThisWorkbook.SlicerCaches

            'Add formulas for CubeRankedMembers 1 to "x"

                For i = 1 To x

                    Fmla = Connect & Cells(9, Col).Value & ",Row(HiddenFilterCatch!A" & i & "))," & Chr(34) & Chr(34) & ")"

                    Cells(9 + i, Col).Formula = Connect & Cells(9, Col).Value & ",Row(HiddenFilterCatch!A" & i & "))," & Chr(34) & Chr(34) & ")"

                Next

            'Add "More than 'x' Selected..." Text formula

                Cells(10 + x, Col).Formula = "=IF(" & Right(Fmla, Len(Fmla) - 1) & "=" & Chr(34) & Chr(34) & "," & Right(Fmla, Len(Fmla) - 1) & "," & Chr(34) & "More Than " & x & " Selected..." & Chr(34) & ")"

            'Add named Range on Cell below "More Than" formula

                Cells(11 + x, Col).Select

                With Selection

                    .Name = Right(Cells(9, Col).Value, Len(Cells(9, Col).Value) - 7)

                End With

                Col = Col + 1

    Next

    Application.ScreenUpdating = True

    Application.Calculation = xlCalculationAutomatic

    

    

End Sub

Question for the Audience...

Okay all, I have a problem that I don't know how to fix.  HUGE points to one who has an appropriate solution.

The Problem: How do I default a text value slicer selection WITHOUT using VBA?  Is it even possible?

Why no VBA? Our company is a bit behind the times in upgrading us all to the same version of office.  I have Excel 2013, but the rest of the world is using 2007 (No slicers available).  Therefore, I post my files to a SharePoint site and the end users are able to use them via Excel Services (hence the no VBA).

I am searching all over for this answer right now and will gladly share the how-to if/when I figure it out.  I would gladly host a "Guest Post" if you know how to do this already.

Thanks!

(SOLVED) Slicer Selection in Chart Title without VBA!!!

So, I've been looking ALL OVER the internet to find a solution to this problem... Everywhere I look I read something like, "No, it's not possible" or "In a word, no".  Well, that's just not good enough for me.  I specialize in interim solutions and work arounds... SOoooo, after much research and trial and error, I've come up with a way to do just what we're all asking about - "How do I show the slicers selected in my Power Pivot chart?"  Since we upload our files to SharePoint and the end users are using Excel Services because they're working with Excel 2007, we can't use VBA for any of our solutions that have a data model.

Here's what we'll need (Remember, I'm using Excel 2013, NOT 2010 with the Add-In):
Data in the Data Model
Pivot Table
Pivot Chart
Slicers (Connected to the Pivot Table)

Formulas:
Cubeset(Connection, Set Expression,[Caption],[Sort Order], [Sort By])
CubeRankedMember(Set)
IfError(Value, Value if Error)

Data:  I'm using World population Data found at: http://www.census.gov/population/international/data/worldpop/table_population.php

I added a Decade column to assist me in quickly grouping the data by decade.

Now, I'm adding this table to the data model simply by:

• Highlight the dataset
• Click the PowerPivot Tab at the top
• In the Ribbon, click "Add to Data Model"

There are multiple ways to add data to the model.  Typically I will connect directly to a data source instead of adding what I already have in the current spreadsheet, but that can be a discussion for another day.

Now that we have our data in the data model, let's stop and discuss a few very important details about the following steps.  It is imperative that we create a pivot table linked to the data model and NOT just a pivot chart.  The issue with linking a chart title or axis label to a cell can be bypassed simply by generating a pivot chart from a pivot table because it is then not directly linked to the data model.  This was probably the BIGGEST issue with the whole process.. I discovered the issue when I right-clicked and selected "Pivot Chart Options" for both a chart based on pivot table and a chart based on the model.  If you do this side-by-side, you will see that the chart connected to the data model has a window titled "PivotChart Options".  However, if you do this with the chart based on the Pivot Table, you will see "PivotTable Options".  Excel sees these charts differently.  To simplify and summarize, you can do all things to a chart based on a pivot table that you could do with the chart based on a dataset in a spreadsheet.  You cannot do this with a chart based on a data model.  

Back to the DataModel:

Within the Data Model window click the following:

• Home Tab
• PivotTable > Pivot Table
• New WorkSheet > OK

Create your pivot table as you need it.  If you're following me using the WorldPop data, I'm using:

• Rows = Decade
• Values = Average of Annual Growth Rate (%)

This is what we have:

Let's Add our Slicer. On the Ribbon Click:

• Insert
• Slicer (Found in the Filters Group)

If asked, link this to your data model.  Select "Decade" from the column list under the table and click OK

Your slicer will look similar to this:

Link the slicer to the Pivot table by:

• Right-Click the Slicer
• Report Connections
• Check the Pivot Table you want the slicer to control
• Click OK

Now, Let's create our Pivot Chart:

• Click somewhere on your Pivot Table
• Under "PIVOTTABLE TOOLS" On the Ribbon, click "Pivot Chart"

• Let's choose a Clustered Column Chart
• Click OK

We now have:

I've selected a few of the slicers to demonstrate that the Slicer is working properly.

Now, to the fun part!  How do we get the slicers selected to show on the chart?

Formulas:

CUBESET(Connection, Set Expression,[Caption],[Sort Order], [Sort By])
CUBERANKEDMEMBER(connection, set_expression, rank, [caption])
IFERROR(Value, Value if Error)

When I build scorecards and dashboards I prefer to have a control sheet that is hidden that contains formulas that I don't want other's to booger up.  So I'm inserting a new sheet titled "ControlSheet" From here, the cell references will be in reference to sheet "ControlSheet"

**The formulas that follow differ slightly if you are using Excel 2010 (PowerPivot v1 and v2)- The "Connection" referred to in the Cubeset formula will always be "PowerPivot Data". "Connection" for Excel2013 is "ThisWorkbookDataModel"

In Cell A1, type the following:

• =CUBESET("ThisWorkbookDataModel",Slicer_Decade, "Decade")

I simply leave the sort options blank. This will populate Cell A1 with a linked reference to the DOS Quarter Slicer.

Next, In Cell A2, type the following:

• =IFERROR(CUBERANKEDMEMBER("ThisWorkbookDataModel",$A$1,ROW(Sheet1!A1)),"") - Remember, "Sheet1!" is the name of whatever sheet you're working in

The formula does the following:

• If no slicers are selected, an error would be returned, so "IFERROR" returns a blank value.
• Otherwise, if all slicers are selected (no filter) the value will be "ALL"
• Otherwise, if one or more slicers are selected, the first value in the list of slicers will be returned

Now, since we have  10 decades in our slicer, let's use this formula 9 times (since 10 = "ALL").

• Validate that your formula in cell A2 has the dollar signs around A1 ($A$1).
• Copy the formula down to cell A10 (or Auto-Fill).
• I typically use the "All Borders" Border around the range to indicate that the cells are being used even if they are blank.

Next, test your Slicer by Selecting Multiple Values and see what is returned by the formulas you've just created.

 Now that we are "Catching" our slicers, we can Create a label like "Filters Used: " , concatenate the values and link to our chart. 

Let's do it!

In Cell A12 on ControlSheet, Type "Filters Used - " 

In Cell A13 Copy/Paste the following formula

• =A2&IF(A3="","",", "&A3)&IF(A4="","",", "&A4)&IF(A5="","",", "&A5)&IF(A6="","",", "&A6)&IF(A7="","",", "&A7)&IF(A8="","",", "&A8)&IF(A9="","",", "&A9)&IF(A10="","",", "&A10)

This formula will always return the value in Cell A2, but will check to see if the following cells are blank before returning the value.  If it's blank, then it returns a blank value.  If it's not, it adds a comma and then the value of the cell.

In cell A14 copy/paste the following formula:

• =A12&TRIM($A$1)&": "&TRIM($A$13)

You should see:

In cell A15 concatenate your Chart title with the value in Cell A14..  I prefer to add a line between (carriage return).  My formula looks something like this:

="Avg World Population by Decade "&char(10)&A14

*Char(10) will add the carriage return we need

Now, let's return to Sheet1 and our chart...

Click on the chart title and in the formula bar type "=ControlSheet!$A$15"

You will see the this:

As you change the filters in the slicer, the title will change with it!

For the longest time I thought "Let's put the filters in the Title" was the best idea EVER!.. However, once I did it, I realized it wasn't a great Idea.  If you have multiple slicers you've incorporated into your title it becomes WAY oversized and unruly.  You see, when you link the title to the cell, ALL of the text is the same font size - you can't make the filter text smaller than the title text.  For me, the title should be the focus and the filters just there if you need them.

Let's solve this: ADD A HORIZONTAL AXIS LABEL! - Since the data in this case is very self explanatory we don't need the axis label to describe the axis.

• Click on chart
• On Ribbon, Click "Design" in PivotChart Tools
• Click Add Chart Element
• Axis Titles > Primary Horizontal
• Click on the Axis Label on the chart
• Paste "=A12&TRIM($A$1)&": "&TRIM($A$13)" into the formula Bar
• Change the font size to "8"
• Change your Chart Title by simply typing the title in "Avg World Population Growth Rate (%) By Decade"

You should now see this:

I hope you all enjoy this!  It's been a royal pain trying to work without this solution, so if you share it, please share some of the credit with me if you reference this.

Thanks!