Reporting on List Data in SharePoint using Enesys

Overview

Microsoft, at this point and time, does not support reporting against SharePoint list data. On of the ways you can address this is through using a Third Party Tool from Enesys.

Development Environment Technologies

• Visual Studio 2003/2005
• Business Intelligence (BI) Studio (not sure if this is necessary)
• Report Designer (in SharePoint) (Requires SQL Server 2005)

Integration/Production Technologies

• SQL Server 2000/2005
• SharePoint (WSS v3 or MOSS 2007)
• SQL Server Reporting Services (SSRS)
• SSRS Add-in for SharePoint

3rd Party Tools

• Enesys RS Data Extension

Architecture Diagram

Architecture

SQL Server Reporting Services (SSRS) to design and render the reports. SSRS is very useful for turning a data set into a functional and visually appealing report. However, SSRS does not have built in support for pulling data out of SharePoint lists.

Enter the Enesys RS Data Extension... The RS Data Extension adds the ability for SSRS to pull data from 1 or more SharePoint lists. It also provides the ability to join, merge, union multiple SharePoint lists into a single data set for SSRS to use.

Examples from RS Data Extension document:

Configuration

RS Data Extension is installed on the same server hosting the SSRS Report Manager. It is also installed in the development environment used to design/build SSRS reports

Infrastructure Diagram

Licensing

• SSRS is available for free as part of an existing SQL Server license
• (As of early 2007) Enesys RS Data Extension can be installed on one SSRS server and on an unlimited number of development workstations for the purpose of building reports.

Links

• Enesys Wiki Page
• Enesys Home - http://www.enesyssoftware.com/
• Enesys RS Data Extension - http://www.enesyssoftware.com/Default.aspx?tabid=72
• Enesys Software article on "Joining SharePoint Lists" - http://www.enesyssoftware.com/Products/EnesysRSDataExtension/JoiningSharePointlists/tabid/68/Default.aspx

  Why Enesys Note the following link is what lead me to Enesys RS Data Extension. This blog also provides source code to represent a similar solution.

• Teun Duynstee's Blog - http://www.teuntostring.net/blog/2006/03/update-reporting-over-sharepoint-lists.html
• CAML Query Tools - See U2U CAML Query Builder (very helpful)

  Primer on SSRS The following series of 4 articles cover a lot of ground and do a good job walking through how to use SQL Server Reporting Services (SSRS); especially parts 1 and 2.

• Beginning SQL Server 2005 Reporting Services Part 1 - http://www.simple-talk.com/sql/learn-sql-server/beginning-sql-server-2005-reporting-services-part-1/
• Beginning SQL Server 2005 Reporting Services Part 2 - http://www.simple-talk.com/sql/learn-sql-server/beginning-sql-server-2005-reporting-services-part-2/
• Beginning SQL Server 2005 Reporting Services Part 3 - http://www.simple-talk.com/sql/learn-sql-server/beginning-sql-server-2005-reporting-services-part-3-the-chart-control/
• Beginning SQL Server 2005 Reporting Services Part 4 - http://www.simple-talk.com/sql/learn-sql-server/beginning-sql-server-reporting-services-part-4/
• SSRS Add-in for SharePoint (requires SQL Server 2005 SP2) - http://www.microsoft.com/downloads/details.aspx?FamilyId=1e53f882-0c16-4847-b331-132274ae8c84&DisplayLang=en

Key Links The items marked with an asterisk represent the most valuable links

Q&A

Q: What options did we explore to produce reports from SharePoint lists data? (How did we get here?)

A: We researched different ways to access SharePoint list data and provide the data to Reporting Services. Here is a sampling of our finding. We have chosen to focus on #1 because it provided a true solution for a reasonable amount of money.

• enesys - RS Data Extension - This creates a data extension of SharePoint lists that makes it available to SQL Server Reporting Services.
  • This one holds the most promise for the short term. The cost is <$200 per server (based on early 2007 prices).
• Microsoft SQL Server 2005 Reporting Services Add-In for SharePoint - This one is for showing and designing reports within SharePoint, but it appears to be focused on data that lives outside of SharePoint lists.
• Custom code solution - This one would likely take examples from blogs that spawned the enesys RS Data Extension. We would basically get SharePoint lists into ADO.NET DataTables and relate those within a DataSet. Then do Reporting Services off of the DataSet.
  • This one may be doable, but is not a very low cost solution.
• Office 2007 Web Components - This one holds promise, but will not be available for a couple of months. There is a Pivot Table Web Part that can display in a web page or export to Excel. There are basically 7 web parts that might come in handy.
  • Note that there is an Office 2003 Web Component pack, but it does not work with 2007. We may want to investigate this to really understand if the Office 2007 pack will provide us what we need. There is no documentation yet on the Office 2007 Web Component pack yet according to Microsoft.

Q: What is CAML?

A: Per Microsoft, "Collaborative Application Markup Language (CAML) is the XML-based language that is used to build and customize Web sites based on SharePoint Team Services from Microsoft®."