Sunday, November 6, 2011

Sharepoint Access Services Reporting Flexibility

I have been struggling with offering my users a flexible and powerful reporting environment in their Access Services application. In a desktop MS Access database, to give users the ability to select more than one item to apply to a single filtering criteria you can either create a custom interface/fuction with VBA or use a multi-select combobox (or listbox) with VBA. Unfortunately, VBA is not available in Access Services. Microsoft has replaced VBA with expanded Macro capabilities that, while pretty powerful, is not as flexible or powerful as VBA. Nor is a multi-select combobox available in the same way in a web database. I had to think outside the box.

Here's what I came up with:

In our database we have a table with contacts and a related table with lists that the contacts belong to. I created a list lookup table with the values the users can select to filter their records with the fields: SelectList(s) (Y/N), Lists, ListID. There are 55 possible lists in the lookup table. Then I designed a report selection form, placed a subform on it with checkboxes for each list selection. After selecting the list(s) the user desires and clicking "Select Lists" a macro is run that populates TempVars with the for selected list(s) ListID number.

I designed a Query that links the Contacts table to an interim ContactLists table that then links to the SelectMultiLists table. I run a datamacro that sets the selected records to YES for the TempVars ListID. I included the SelectList field in the query and set it's criteria to "True" so that only the related records that the user has checked in the subform appear in the results.

Back on the Report Selection form, after the user clicks the yes/no option to select their criteria, they select the report (I have 4 different report formats including labels and a spreadsheet layout appropriate for export to Excel) they want to run and a macro is run with additional If/Else/Endif structure that additionally filters the report selected.

In this way I was able to offer reports on multiple lists and have included 3 additional criteria (state, district, active).

I reviewed the templates Microsoft offers for Web Databases, and the Northwinds Web template and I believe what I have designed has more flexibility/options for reporting all in Sharepoint (don't have to move to the client).

Would love to hear your feedback if you have worked with Access Services and can offer better or additional reporting suggestions.