Interactive chart form filtering code sample

Here is another repost from my blog... Enjoy--Clint

-----

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 2007 Office Migration Planning Manager and 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.

Here is an in progress version of the Sales Pipeline database.

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 LongPrivate 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.ChartSpaceEnd 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 SelectEnd Sub 'filters the db list by monthPrivate Sub FilterByMonth(sYear As String, sVal As String) Me.Parent.Form.Filter = BuildSQL(Trim$(sYear), Trim$(sVal)) Me.Parent.Form.FilterOn = TrueEnd Sub 'filters the db list by yearPrivate Sub FilterByYear(sVal As String) Me.Parent.Form.Filter = BuildSQL(Trim$(sVal), "") If Me.Parent.Form.FilterOn = False Then Me.Parent.Form.FilterOn = TrueEnd Sub ' builds the SQL that will filter the formPrivate 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 IfEnd 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.ClearEnd 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 IfEnd 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 SelectEnd Sub <div