Beyond Excel: VBA and Database Manipulation

Oct 4 2010   12:58PM GMT

Chart Drill Down

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

PivotChart Drill DownThis seems so basic to me that I was not surprised Googling “Drilldown Excel Chart” shows lots of interest out there.  But the implementation is so simple that I was very surprised Googling “Drill Down Excel Chart” yielded almost no good suggestions.  That ends today.

[kml_flashembed movie="" width="480" height="292" wmode="transparent" /]

For beginners: What is “Drilldown?” 
Drilldown is displaying underlying details for a total.  This is important because we hope charts and summaries show something we didn’t know and/or expect.  When that happens, we want to know why.  Displaying what makes up a total helps answer that question.

Doesn’t Excel Support Drilldown Automatically?
In PivotTables and Outline Reports – yes.  You can double click any calculated number in these Excel objects and Excel displays the associated rows from their source data range.  But if you double click on a Chart/Graph element, the “Format Data Point” dialog box appears.  That’s not what my users want.  The good news, though, is the very same mechanism that reveals detail beneath PivotTables makes coding drilldown for PivotCharts a snap.

What are PivotCharts?
A PivotChart is a chart over a PivotTable.  In the templates provided in this blog, we use PivotTables to summarize data in our extracted rows.  PivotTables are extremely flexible and allow the user to slice and dice data in many, many useful ways.  The only draw back to PivotTables is they show numbers, not graphs.  This is easily overcome by simply creating a chart over the PivotTable.  Charts made from PivotTable data, as opposed to simple rows of data, also allow users to slice and dice the underlying data just like the user can with a PivotTable.  The only drawback to the PivotChart is that it lacks Drill Down. 

Adding Drilldown to PivotTable Charts
This “trick” only works with PivotCharts because it relies on the PivotTable’s ShowDetail property.  As mentioned before, you can double click on any calculated result in a PivotTable and it will automatically show the associated detail rows.  If you start the Macro Recorder, double click on a PivotTable cell, stop the recorder, and then view the recorded code, you’ll see something like this:

Selection.ShowDetail = True

…where “B9” is the cell you double clicked.  The Selection.ShowDetail = True is what causes the detail to display.  Now when you create a PivotChart from a PivotTable, each PivotTable cell becomes a chart element.  So what we have to do is figure out which chart element the user double clicked and which PivotTable cell that represents.  Then all that’s left to do is use that cell’s ShowDetail property to display the data.  As it turns out, this is almost easier done than said.

Determining Which Chart Element was Clicked
Excel provides a simple routine that makes this easy – ActiveChart.GetChartElementActiveChart.GetChartElement is a method attached to every chart in Excel.  You pass to it the mouse pointer’s X and Y coordinates and it returns the Chart Element Type and two of that Chart Element Type’s properties.  Chart Element Types can be the chart’s Title, Legend, Axis, … or a Graph Element.  We are only interested in Graph Elements such as a slice in a Pie Chart, a line in a Line Chart, a bar in a Bar Chart, etc.  So if ActiveChart.GetChartElement returns anything other than a Chart Element Type of 3 (Graph Element), we know to ignore things and move on.  On the other hand, if the user clicked a Graph Element, we want to show the detail.  When Chart Element Type is 3, Arg1 is the associated PivotTable row and Arg2 is the column.  So to show the detail beneath we use:

ActiveChart.PivotLayout.PivotTable.DataBodyRange. _
            Cells(Arg2, Arg1).ShowDetail = True 

Capturing Chart Double Click and Mouse Pointer’s X and Y
Every Chart also has a Chart_MouseUp event.  Chart_MouseUp fires whenever the user clicks (and releases) the mouse on a chart.  Excel also passes a few properties to this event.  Two are important to us: X and Y.

We now have all of the pieces to the puzzle.  All that’s left to do is put it together.  Place this code in the Chart Sheet object:


Private Sub Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, _
                          ByVal x As Long, ByVal y As Long)
'   Description:Drill Down into Pivot Chart's data
'   Parameters: Button  Mouse botton that was released
'               Shift   State of SHIFT, CTRL, and ALT keys
'               x       Mouse pointer X coordinate within Chart
'               y       Mouse pointer Y coordinate within Chart
'   Example:    *none - This is an event handler
'     Date   Init Modification
'   10/04/10 CWH  Initial Programming 
    On Error GoTo ErrHandler
    Dim ElementID As Long
    Dim Arg1 As Long
    Dim Arg2 As Long
'   Pass: x, y. Receive: ElementID, Arg1, Arg2
    ActiveChart.GetChartElement x, y, ElementID, Arg1, Arg2
'   If data element clicked, show detail
    If ElementID = 3 Then
        ActiveChart.PivotLayout.PivotTable.DataBodyRange. _
            Cells(Arg2, Arg1).ShowDetail = True
        ActiveSheet.Cells(2, 2).Select
        ActiveWindow.FreezePanes = True
    End If
    If Err.Number <> 0 Then MsgBox _
        "Chart_MouseUp - Error#" & Err.Number & vbCrLf & _
        Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error Resume Next
    On Error GoTo 0
End Sub

5  Comments on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • Joergen

    Hi Craig,

    Once in a while I come across some very interesting stuff you have done. Kudo's for that.

    The article above is one of these, and I Wonder why I have not seen this beforehand, as I have been searching for material like yours.

    Your code Works with by a pop up window to ask me which details I want to see, and therebygiving me a chance to view further details in the graph.

    For some reason, I expected that your description would show me the data below. I will play around with your code, and hopefully find a way to show either or, but if you have a workaround it would be great.

    Do you have your original file, so I can see, if your file Works as mine? Or is it confidential?



    0 pointsBadges:
  • Craig Hatmaker
    Hi Joergen,

    Thanks for the kind words. The pop up is Microsoft's default behavior which does not show detail. This means the mouse click is not triggering the drilldown code. There are three main causes:

    1. The chart is not based on a PivotTable - this only works for PivotCharts (sorry)
    2. The chart is in a Chart sheet but the code is not
    3. The chart is embedded in a Worksheet in which case we need to use a different approach

    Below are more resources. The first group is for PivotCharts in a Chart sheet. The second group is the "different approach" needed for PivotCharts embedded in a worksheet. I hope this helps.

    Drilldown into a PivotChart in a Chart sheet:

    Drilldown into PivotCharts embedded in a Worksheet:
    1,860 pointsBadges:
  • Joseph456

    Hi Craig-  Thank you so much for posting this -- it was exactly what I needed!  I also looked at the Youtube and dropbox docs.  Pretty easy to implement, too.

    BTW, my Excel was intermittently crashing after multiple drilldowns until I tweaked to code in the section that checked if the drilldown tab existed.  If the tab existed, I changed the code to always delete it.  That seemed to work to stop the crashing.  The drilldown tab looked cleaner, too.  Before, it would sometimes start a couple rows down from the top.  With the change, the drilldown table always starts at the top.  Perhaps this was because some of my data was pretty small (only one row)?

    0 pointsBadges:
  • Craig Hatmaker
    Hi Joseph,
    Thanks for the tip. I've not had that problem but if it shows up, I'll know what to do thanks to you :)
    1,860 pointsBadges:
  • nvjchary
    Hi Jospeh- Can you please share the code for delteting the drilldown tab if existed. Am facing the same prob

    Hi Craig- Thanks for the code. It is amazing!
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: