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 http://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