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!

Rolling 12 Months Chart with PowerPivot

Once again, work has provided me with a great article topic!  A request recently came across my desk to create a rolling 12 & 24 months average.

Here is how to do it:

First thing is first: You will need to have the “Last Updated” Table&Column in your Model.

 If you can connect to a SQL Server database, I create this by adding a table "Last Updated" with the following SQL as source (This also tells the users how new the data is):

Select GetDate() as ‘Last Updated'

This method is preferred to simply using the Today() formula in a linked table because your source data may not be refreshed daily.  For example, we have some financial dashboards that are only refreshed once per month on the first day of the month.

Next, create a calculated measure on the "Last updated" table with the following formula:

LastUpdatedMeasure:=sum([Last Updated])

Next, on your Fact table that you are going to use to create the rolling average (or whatever rolling metric you desire), add the following Calculated Measures to determine start and end dates for the rolling year ([last updated date] - [12 months]):

RollingYearEnd:=EOMonth([LastUpdatedMeasure],-1)

RollingYearBegin:=1+edate([RollingYearEnd],-12) - You can make this 2 years by changing "-12" to "-24".

Finally, use the following calculate formula to run whatever calculation you are looking for within that rolling year. This is the measure you will use as the “Value” on your chart or pivot table.

The formula below has multiple parts that I will explain:

=Calculate(expression, filter1, filter2..)

=Average(Column)

=Filter(TableName, TableName[Filter Criteria])

"&&" - This allows you to use an "AND" in your Filter Criteria.  Example: Event Date is >= RollingYearBegin "AND" Event Date is <= RollingYearEnd  - This ensures your data is between the start and end date. You can also include a column to qualify the event to be included by simply adding another "&&"

RollingYear_Avg:=calculate(average([ColumnToBeAveraged]),filter(FactTableName,FactTableName[EventDateColumn]>=[RollingYearBegin]&&FactTableName[EventDateColumn]<=[RollingYearEnd]&&FactTableName[QualifyingEventColumn]="Y"))

The final part of the formula &&FactTableName[QualifyingEventColumn]="Y" was required in my case because not all of the rows in my dataset qualified to be included in the average.  This is not required to make this formula work.

So, now that you are an expert in rolling year formulas, go out and impress the world!

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... :)

Pulling Table Data from PDF & Merging Multiple Sheets Into One

Okay all, this is a common problem that I've run into before... However, with updated software, the methodologies change a bit.  Here is my latest method of collecting table data from a PDF without purchasing adobe or another 3rd party software solution.

**NOTE: These methods were discovered online by searching.  I did not come up with them.  I'm just sharing, because that's how we get smarter together!

How to collect the data using PowerQuery:
http://simblais.com/simblogue/blog/2013/12/16/using-power-query-on-a-pdf-with-some-help-from-word/

**I use the "Load To..." option to drop the tables directly into a worksheet instead of the data model.

How to Consolidate the many worksheets into one sheet (I used the VBA code in the article):
http://excel.tips.net/T003005_Condensing_Multiple_Worksheets_Into_One.html

**I added the following line at the bottom of the code to show the final worksheet:  Sheets("Combined").activate

Again, these solutions are not my work, but definitely useful.  Please give these sites their due respect if you find this information useful.

How to show all dates in a powerpivot pivot table even when there is no data...

So, I've heard about this issue for a while now, but I never had to actually deal with it myself - until now, that is..

We have a system that records when a specific significant event occurs - we'll call this Event A.
Well, Event A doesn't always happen and frankly, we NEVER want it to happen.  So, when reporting month-to-month numbers, there are some times when a month shows "0" for the denominator and "0" for the numerator.  In a typical Excel pivot table, this isn't really a big deal you simply go into the properties and set the "Show items with no data on rows/columns" option to TRUE.

Unfortunately, with my particular power pivot model, it wasn't so simple.

My online investigation took me to this forum.  It was a lengthy conversation where multiple people offered the "easy" solution that ultimately didn't work.  However, there was one contributor that was spot on in understanding the issue.  It took me a while to pick through the conversation and get past the suggestions that still resulted errors to finally get at the solution I was looking for. So, I am going to attempt to explain what I did to save time:

The solution is this formula:

# of Event A:=sumx(filter(DimDateEvent,DimDateEvent[Months from Current]>0),0+Event[#ID])

I have two tables involved:

• DimDateEvent - Date dimension table that consists of only monthly data - I will only ever report Event A on a monthly basis so no need to get more granular that Monthly.
• Note - I have a column in my table to measure the number of months from current month.  The results are both positive and negative.  
• =(YEAR(TODAY())-YEAR([@[Beginning of Month]]))*12+MONTH(TODAY())-MONTH([@[Beginning of Month]])
• Event - My fact table that has the Event information.

Formulas Used in the Solution:

• Sumx(<table>, <expression>)

• Filter(<table>, <filter>)

Calculated Measure used in Solution:

• Event[ID#] - this is simply Counta(Event[ID])

Normally, when creating a pivot table, the source data is the table with your event information and you "Look up" the remaining details.  However, for this solution, we are going to use our DimDateEvent Table as the data and "Look Up" the event information.

So, lets walk through the formula:

=Sumx(

Sumx - allows you to apply an expression to the data being added.

=Sumx(Filter(DimDateEvent,DimDateEvent[Months from Current]>0)

Filter - provides the DimDateEvent table filtering it to show only last month and every month before it.

=Sumx(Filter(DimDateEvent,DimDateEvent[Months from Current]>0),0+Event[#ID])

I didn't want the table to show blanks, so I showed 0+ (this guarantees a value of "0") and then the result of the calculated measure Event[#ID] or in other words, the formula "Counta(Event[ID])".

So now we have completed the formula:

=sumx(filter(DimDateEvent,DimDateEvent[Months from Current]>0),0+Event[#ID])

Here is what it looks like in a pivot table: 

Note: No real data is being used here... The fiscal Year resets because I'm not showing the entire pivot table there is a Group A, Group B, Group C

No Demo File will be provided for this example.

Enjoy!

Show two labels on a single chart series

Just learned how to add a denominator at the base of a column on a column chart that shows an average (average is simply one example, but this could be used in multiple ways):

Note: I removed all identifying info from the image on purpose... 

Anyway, here's how to do it:

Create a chart for Average times and add the data lables above the columns.  

Now, add the second series that shows the count.  You will now have two Columns showing.  

Right-click the count series and set the overlap property to 100% and change the Count series' color to match the Average series' color.  The columns now overlap and look the same.  

Last, set the data label position for the count to show inside base. 

Anyway, thought I'd share.

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

SPEED UP YOUR VBA RunTime!

WOW!  I just found a nice little help that improved my vba run time from 30 minutes to less than 1 minute!

So, I had a bit of VBA that I wrote to clean up and group a transaction level data set from a 3rd party report.  I noticed that on average it was taking up to FOUR seconds per row to clean, identify, re-organize, validate for duplicate entries, and write to a new table.  Essentially, 600 rows would take at least 30 minutes for the vba code to complete its task.  I have NEVER run into this issue before - Turning off Screen Updating has resolved any issues with speed I've had up until this point.

30 minutes just won't do.

So, I went hunting for solutions.  But, before I can solve the problem, I had to FIND the problem.

What is the problem?  Well, I found through the use of breakpoints where the lag in time was.  It was taking up to 3 seconds to simply place a batch of values that were stored in variables into empty cells. 

example line: 

Sheets("Converted").Cells(Rowc, 1).Value = Location

The issue was that Excel was recalculating the sheet every time a new value was placed in a cell.  So, how do we stop that? 

At the beginning of your vba code (probably right along with your line to deactivate screen updating) insert the following line:

Application.Calculation = xlCalculationManual

Then, at the bottom right before you end sub, insert the line:

Application.Calculation = xlCalculationAutomatic

BINGO!  Problem solved.

Find n-th space from the right...

Another quick post:

Have you ever had the wonderful pleasure of needing to pull all characters to the right of the 2nd or 3rd space within the string?  I know I have..

Solution:
*Disclaimer*: I have no idea where I found this beauty, but again, I FOUND it because someone else was willing to share - and sharing is the whole point of this blog...  I'm posting this just as much for myself as for anyone else. :)

Assuming your string is in cell C2:

=FIND(CHAR(1),SUBSTITUTE(C2," ",CHAR(1),LEN(C2)-LEN(SUBSTITUTE(C2," ",""))-n))

For n:
("n" determines the number of spaces)
0=1
1=2 etc...

Update On Progress While Macro Runs..

Okay, I know it's been FOREVER since I've posted.  Long story short, I don't make money on the blog, but I do make money working....

Anyway, I found this AWESOME little tidbit for those of us who use VBA.

Situation:
I have a macro that takes a large chunk of data from a 3rd party .csv file, cleans it, and organizes it into a format that is usable.  The problem I have is that it takes a long time for it to run and I have no idea if it's hung up somehow or just slow.  If any of you have ever used a loop before, when the code takes a long time to run, it's possible you screwed up and it will loop forever!  It's nice to have an update on progress.

My Online Search Criteria: "excel vba msgbox update during code"

Solution:
I did NOT come up with this solution on my own, but I did find it on the stackoverflow site when I searched it out:  http://stackoverflow.com/questions/5459734/using-messagebox-without-pausing-the-application-vba

Anyway, here is the answer I liked:

Application.StatusBar = "Please be patient..."
Application.StatusBar = iDone & " of " & iTotal & " items done."

Using the Application Status Bar (Bottom left corner of your Excel window) is fantastic!  You can simply use a counter to display the number of the record you're on.

Well, that's it for today.  Hoping to get back into this as things slow down.