Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba
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="http://www.youtube.com/v/-Uu2WqDxLdk" 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:
Range("B9").Select 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.GetChartElement. ActiveChart.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
ErrHandler: 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