Interactive chart/form filtering code sample
One of the most enjoyable design challenges I have had over the last couple of years was redesigning how users can filter inside Access and Excel. The new filter dropdown menu and context menus were lots of fun to explore different designs and think creatively about how users interact with data. I wanted to make filtering simple for everyone to use but powerful enough to answer most hard questions. One of my key goals was to build a system where users could create 90% of WHERE clauses without using the query designer. It was really fun to watch users be successful use the features in the usability lab.
Recently, I have been hunting the web and rich applications looking for different ways to visualize and interact with data. I noticed in the Microsoft Access 2003 Conversion Toolkit that a chart was used to filter tabular data. I thought it would be interesting to create a view that showed trends and data but yet was also useful. The Sales Pipeline template is an example of an application where trend-lines excite users. With a little code and a chart sub-form you can sprinkle a dab of visualization with a pinch of interactivity.
Clicking on a year, month label, or bar in the chart above will filter the Est Closed Date to the year or month selected. For example if you click on Jun you will see all sales opportunities in the month of June. Clicking on the 2007 label will show all the sales opportunities with an expected closed date in 2007. It is even smart enough to maintain filters on other columns as you filter. for example, if you filter where category = hot and click on the April month you will see all records where category = hot and in the month of April.
You can download a working sample of the Sales Pipeline database https://clintc.officeisp.net/Blogs/2006/43%20-%20Chart%20Filtering/Sales%20pipeline_chart_filter_demo.zip.
Using the new split-form you can resize the height of the chart and datasheet with the chart growing or shrinking to fill the view.
Add the Chart
Step 1. Open the Open Opportunities List and select all the controls. Remove them from the stack so that you can size the form window smaller. Move all the controls in the detail section up into a cramp space. Turn on the split form resize bar by selecting the form setting the Split Form Splitter Bar = true.
Step 2. Create a new chart called Filter Chart Sub-form. I won’t go into the details of polishing the chart and the chart settings. Add the chart to the form covering all the controls in the detail section. I found it useful to select the sub-form and setting Top = 0. Select the sub-form and set the Anchoring property to stretch down and across.
Step 3. The next step is to make sure the chart is always updated when the user adds, updates, or deletes records. I added three simple embedded macros on the AfterUpdate, AfterInsert, AfterDelConfirm events to refresh the chart.
Make the Chart Interactive
Step 1. Add reference to c:\program files\Microsoft Office\Office12\ offowc.dll.
Step 2. Add the following code to the chart sub form. The code should be fairly straight forward. You use an API call to change the cursor from an arrow to a hand when the user hovers over the bars, month, or year labels.
When the user clicks on a bar or label it builds an approriate filter string. The first iteration of the demo removed all filters and applied just the new filter. So if the user had filtered by category and applied a filter to Expected Closed Date they would be viewing all categories again. I fixed this bug using the ClearFilterFromSelectedField runcommand to remove just the current column filter instead of the entire form filter. This is a new command in Access 2007.
You will need to modify the variables sFilterField and sFilterControl in the BuildSQL function to point to the field name and control name you want to filter in your own app. I haven't tried it but there shouldn't be any reason why this technique doesn't work in report browse as well. If anyone out there wants to modify the buildSQL routine to support filtering on strings--send me the updated sample and I will post it to the community following this blog.
Anyways, here is the code:
Option Explicit
Private Const HandCursor = 32649&
Private Declare Function SetCursor Lib "user32" (ByVal hCursor As Long) As Long
Private Declare Function LoadCursor Lib "user32" Alias "LoadCursorA" (ByVal hInstance As Long, ByVal lpCursorName As Long) As Long
Dim WithEvents oChart As ChartSpace
Private Sub Form_Load()
Set oChart = Me.ChartSpace
End Sub
Private Sub Form_Click()
Dim sVal As String
Dim sYear As String
Dim iSeperator As Integer
Select Case Me.ChartSpace.SelectionType
Case chSelectionPoint
sVal = Me.ChartSpace.Selection.GetValue(chDimCategories)
iSeperator = InStr(1, sVal, "-") 'make sure we only filter for year or month and year
If iSeperator = 0 Then 'selection is a year
FilterByYear sVal
ElseIf InStr(iSeperator + 1, sVal, "-") = 0 Then
'selection is a month and year
sYear = Trim$(Mid$(sVal, 1, iSeperator - 1))
sVal = Trim$(Mid$(sVal, iSeperator + 1))
FilterByMonth sYear, sVal
Else
Exit Sub 'selection is a day or somthing of a lower level that we don't filter for
End If
Case chSelectionCategoryLabel
'set the source object to the proper form
sVal = Me.ChartSpace.Selection.Caption
Select Case Me.ChartSpace.Selection.Level 'check to see what level was selected on the x axis
Case 0 'year
FilterByYear sVal
Case 1 'month
sYear = Me.ChartSpace.Selection.ParentLabel.Caption
FilterByMonth sYear, sVal
Case Else
Exit Sub 'selection is a day or somthing of a lower level that we don't filter for
End Select
End Select
End Sub
'filters the db list by month
Private Sub FilterByMonth(sYear As String, sVal As String)
Me.Parent.Form.Filter = BuildSQL(Trim$(sYear), Trim$(sVal))
Me.Parent.Form.FilterOn = True
End Sub
'filters the db list by year
Private Sub FilterByYear(sVal As String)
Me.Parent.Form.Filter = BuildSQL(Trim$(sVal), "")
If Me.Parent.Form.FilterOn = False Then Me.Parent.Form.FilterOn = True
End Sub
' builds the SQL that will filter the form
Private Function BuildSQL(sYear As String, sMonth As String) As String
Dim sFilterField As String
Dim sFilterControl As String
sFilterField = "[Est Closed Date]"
sFilterControl = "Est Closed Date"
If sMonth <> "" Then 'passed in year and month
BuildSQL = "(" & sFilterField & " >= #" & FormatDateTime(sMonth & " - " & sYear) & "#) AND (" & sFilterField & " <= #" & DateAdd("m", 1, FormatDateTime(sMonth & " - " & sYear)) - 1 & "#)"
Else 'just passed in the year
BuildSQL = "(" & sFilterField & " >= #1/1/" & sYear & "#) AND (" & sFilterField & " <= #12/31/" & sYear & "#)"
End If
'Clear the filter from the field the chart is filtered on.
'If you want to clear the entire filter, just set Me.Parent.Filter = "".
RemoveFilterFromField Me.Parent, sFilterControl
' If there is still something left of the filter
If Me.Parent.Filter <> "" Then
' Append
BuildSQL = BuildSQL & " AND (" & Me.Parent.Filter & ")"
End If
End Function
Private Sub RemoveFilterFromField(frm As Form, strControlName As String)
frm.SetFocus
frm.Controls(strControlName).SetFocus
On Error Resume Next
DoCmd.RunCommand acCmdRemoveFilterFromCurrentColumn ' This command is new to Access 2007.
Debug.Assert Err.Number = 0 Or Err.Number = 2046 ' 2046 is thrown when there was no filter on the column
Err.Clear
End Sub
Private Sub Form_CommandBeforeExecute(ByVal Command As Variant, ByVal Cancel As Object)
' Cancel the ability to drill into days and hours.
If Command = chCommandDrill Then
Cancel = True
End If
End Sub
Private Sub oChart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Select Case TypeName(Me.ChartSpace.RangeFromPoint(x, y))
Case "ChCategoryLabel", "ChPoint"
'display hand cursor if pointer is over a label or bar
SetCursor LoadCursor(0, HandCursor)
Case Else
'display the normal cursor
Screen.MousePointer = 0
End Select
End Sub