SharePoint Saturday Atlanta

As promised, here are my slides from my SPS Atlanta presentation: Survival Skills for the SharePoint SCA and Site Owner

SPS Atlanta was a great conference and I hope to return next year.


Create Full Featured SharePoint Reports with MS Access

Sometimes, I have customer requirements for “real” reports based on SharePoint list data.  By “real” reports, I mean reports with headers, footers, page numbers, etc..  While Excel can be used to create great visualizations, that just isn’t what my customers want – they want a report.

In a perfect world, we could all use SQL Server Reporting Services (SSRS) to generate reports.  The problem is that SSRS is not available to Office 365 SharePoint customers and isn’t available to many “on premises” SharePoint users.  If you need to create reports against SharePoint data and SSRS isn’t an option, MS Access can be the solution.

(Please note that when I mention MS Access, I am referring to traditional Access desktop databases and not Access Services.  Access Services will be the subject of a future article)

The key to using MS Access with SharePoint is using linked tables.  An Access linked table is an Access table whose data lives outside of the Access database.  The data could reside in other Access databases, SQL Server, Oracle, ODBC data sources and a wide variety of other places including SharePoint lists.

To create a linked table, click on the External Data ribbon.  Then select “More” on the left side of the ribbon group with the other import options.  You’ll be prompted for the URL of the site and then for the specific lists.  Make sure you select “Link to the data source” and click on “Next”.  Select the lists you want to use and click on “OK.

You’ll see new Access tables created with a special icon indicating that it is a linked table.  It is important to remember that you can update most of the list columns from Access (assuming you have permissions to update the list).

At this point, you can create your reports.  A good way to get started is to use the report wizard to create you first report – you find it on the “Create” ribbon.  Create a report using the wizard and take a look to see how it is constructed.  Adding and removing columns is reasonably intuitive.  Use the MS Access help feature for info on how to format reports and additional information on report features.   Even now, I frequently start with a report created by the report wizard – the wizard gets me close to where I want to go a lot of the time.  I then customize what the wizard has provided to get me the rest of the way.

Please note that you can combine data from multiple SharePoint lists and other data sources by creating an MS Access query.  Then you can create a report against the query.  Once again, see Access help for more information on creating queries.