Reporting on List Data in SharePoint

Reporting on list data within SharePoint seems to be a straightforward need that should have a straightforward solution. On first glance, you may think that Report Center (part of MOSS Enterprise) is the answer. Or maybe Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies would do it. But neither can report on SharePoint list data. It is true that KPIs (also part of MOSS Enterprise) can report on SharePoint list data, but they are very limited in this regard and won't give you what you want in terms of reporting.

Unfortunately there currently is no easy solution to this problem so this page attempts to provide you with the options that are out there and direction on which option to choose.

Types of Options
Direct to SharePoint Options

Bamboo Solutions SQL View Web Part
Enesys RS Data extension
Custom Datasets with ReportViewer
SharePoint Web Services
Web Parts
Custom UI

External Database Options

i-PMO SharePoint Data Miner
SSIS and SSRS
SSIS, SSRS, and SSAS
Other...

Comparison Matrix
Other Options

Types of Options

There are two primary types of reporting options:

Direct to SharePoint - These options report on data by querying SharePoint directly. The reports therefore show the most recent data, but are not as able to take advantage of off the shelf reporting solutions such as SQL Server Reporting Services (SSRS). There are exceptions to this, so you may want to review these options for a complete understanding.
External Database - These options involve exporting list data to an external database and then reporting on the data from the database. These can take advantage of off the shelf reporting solutions such as SSRS and even SQL Server Analysis Services (SSAS), but there are more moving parts and the delay caused by the export of the data may not meet your needs.

Before diving into the different options, you should determine what your needs are for reporting. Review the questions to the right to determine where you should focus.

Many of the options will mention SQL Server Reporting Services (SSRS) as part of the solution. In many cases any database reporting solution can be used in place of SSRS, but SSRS does have specific integration with the SharePoint UI allowing you to view SSRS reports within SharePoint. Note that the the Enesys RS Data Extension does specifically integrate with SSRS.

You may find that based on your answers, you want a hybrid approach with two or more solutions.

Question	Recommendation
Do your reports need to be up-to-the-minute? In other words, do you need to report on data that may have just changed seconds or only a few minutes ago?	If you need real time reports, you may want to focus on the solutions that go Direct to SharePoint data and do not involve a process of extracting that data into another database. However, keep in mind that the External Database options allow for some powerful reporting and may not have that much of a delay in availability of the report data.
Do your reports contain data from only one or two lists?	If so, using existing Web Parts and web part connections may suffice.
Do your reports use data joined from several lists and are those lists large?	If so, you may want to consider using one of the External Database approaches. The reports will perform better going against a database than doing joins across SharePoint lists.
Do your reports access data from SharePoint as well as other data sources?	If so, the External Database approaches may be best, but also consider the Custom Datasets with ReportViewer and Custom UI approaches.
Do your reports require user interaction?	Simple click-through reports can be done with SSRS with the External Database approaches. Otherwise you may want to use Web Parts or a Custom UI.

Direct to SharePoint Options

As mentioned above, these options report on data by querying SharePoint directly. The reports therefore show the most recent data, but many are not as able to take advantage of off the shelf reporting solutions such as SQL Server Reporting Services (SSRS).

Another advantage to this approach is that SharePoint row level security is usually honored, whereas this is generally not the case with the External Database options. A disadvantage, however, is that many of these approaches take longer to create the report and may not be very scalable for large amounts of data.

Bamboo Solutions SQL View Web Part

The Bamboo Solutions SQL View Web Part looks very promising. At a low price of $300/server (+ $60/yr for support) it can create a SQL View for one or more SharePoint lists. Basically all SharePoint data is stored in a database, but it is very hard to go direct to the database to look at the data because of the way it is stored (almost all data across all lists and sites is in a single table). This web part will create views into that data that are then accessible by SQL Server Reporting Services (SSRS) or other reporting products.

Pros	Cons
Relatively few moving parts. Performance may be the best for any of the Direct to SharePoint options. May allow for row level security (need to verify). You get the full functionality of SSRS.	This option is not vetted (higher risk). Risk that going straight against the SharePoint database is not a supported API from Microsoft's perspective. If a major version of SharePoint changes the database schema, how long will it take for Bamboo to update their web part?

There very well may be other solutions that provide this same functionality as this Bamboo Solutions web part. We need to see if there are other vendors as well.

Enesys RS Data extension

The Enesys RS Data Extension is an extension to SSRS that allows SSRS to connect to SharePoint lists as a data source. You get the full power of SSRS going straight to SharePoint list data through SharePoint's Lists web service. It is an excellent idea, but does have some shortcomings.

Pros	Cons
Very few moving parts. Should allow for row level security since it is using the SharePoint Lists web service. They have what appears to be nice editor for creating the CAML queries as well as a new SQL query syntax (big improvement over previous versions). You get the full functionality of SSRS (well, the query part is a little different than SSRS users are accustomed to).	There can be performance issues, especially if joining large lists (the join essentially occurs after the query). The costs are moderate $1200 per Report Server + $600/yr support $9200 enterprise license + $3220/yr support License Info

Back in 2007 ThreeWill used Enesys with poor performance results because we were joining large lists. It has since undergone some improvements that may help. Before choosing this option, you should perform tests against lists of similar size and complexity to your production environment.

Custom Datasets with ReportViewer

This option involves writing custom code to generate DataSets within SharePoint and provide them to the ReportViewer control. You write reports using the SSRS ReportBuilder, but this option does not involve any of the runtime services of SSRS. You must manage the storage of the report definitions yourself.

TODO - Eric, can you help provide proper details?

Pros	Cons
Flexible solution as you can pull data from any source you choose (SharePoint, databases, web services) and put them together into a single DataSet. You get much of the capability of SSRS.	Requires custom coding; likely for each report (certainly for each dataset). May have to code a somewhat generic report parameter UI. There may be in issue printing: browser-based printing through the RSClientPrint ActiveX control is not available for client report definitions that run in the ReportViewer Web server control. The print control is part of the report server feature set.

The printing issue may be a show stopper for some.

SharePoint Web Services

This option involves using SSRS to retrieve data directly from SharePoint through SharePoint's Lists web service. SSRS can not only query data from databases, but it can also query data from web services. This option simply involves querying the data from the SharePoint Lists web service. In some ways this is similar to the
Enesys RS Data Extension, but unlike Enesys this does not appear to allow joining of lists.

Pros	Cons
Very few moving parts. Should allow for row level security since it is using the SharePoint Lists web service. You get the full functionality of SSRS (well, sort of - the querying part is limited due to the nature of how the datasets are built).	This option appear to be very limited unless you are just reporting on data from lists without performing an joins.

Take note of the primary limitation on this one. It may make sense in some scenarios, but if you have lookups that are used in reports you may want to test this out before jumping in with both feet.

Web Parts

Web parts or web controls can be used to data viewed as a report. These section discusses existing web part; see Custom UI below for developing custom web parts.

TODO

Lists Web Part
Data View / Data Form Web Part
- Tech-Ed Presentation including Data View / Data Form web part
Content Query Web Part (CQWP)
3rd Party Web Parts
- CorasWorks
- CorasWorks DIT
- Others...
Business Data Catalog (BDC) Web Parts

Custom UI

This option involves creating user interfaces in SharePoint (ASPX pages or web parts) that display data in a report format. This option is very powerful, but likely the most costly per report if you have several reports.

Pros	Cons
Very flexible in how to display the report as you can use any user interface you choose including 3rd party controls. Very flexible in how data is gathered as you can pull data from any source you choose (SharePoint, databases, web services).	Fairly costly per report since there is no framework such as SSRS to draw upon. Tends to be more costly to provide print, paging, and export functionality.

ThreeWill has used this option with Telerik, Dundas, and other 3rd party controls in the past. This is a good approach if you need highly customizable user interfaces.

External Database Options

As mentioned above, these options involve exporting list data to an external database and then reporting on the data from the database. These can take advantage of off the shelf reporting solutions such as SSRS and even analysis services (SSAS), but there are more moving parts and the delay caused by the export of the data may not meet your needs.

i-PMO SharePoint Data Miner

The SharePoint Data Miner is installed into a SharePoint farm as a feature and generates a real-time Reporting database for SharePoint content. This approach provides the full benefits of report from SQL, with indexing, commonly known tools and query editors. It addresses all shortcomings with reporting from SharePoint with only one downside.

Pros

Cons

Tightly integerated with SharePoint.
Allows for Indexing content.
Uses standard T-SQL for Report Queries, so data can be grouped, summed, joined and aggregated using standard T-SQL techniques.
You get the full functionality of SSRS (or any other reporting tool you choose to use).
Provides for additional custom constraints on list data (uniqueness of fields, list field relationships etc).
Free 30 day trial period
Licensing is per SharePoint Production Farm.

SharePoint item security is not adhered to. If necessary you need to re-secure the information using either SQL authorisation techniques or at the report level.

SSIS and SSRS

This option involves using SQL Server Integration Services (SSIS) to pull data out of SharePoint and into a SQL Server database. Then using SQL Server Reporting Services (SSRS) to report on that data.

Pros	Cons
Simplifies report creation (per report costs are low) Very scalable and has good performance Allows for numerous tools to be used to create the reports (SSRS is only one option)	Lots of moving parts (requires knowledge of SSIS and SSRS as well as another database) Requires writing a SSIS process (this has a few gotchas) There is a delay between the update of SharePoint data and when the data is ready in the report due to the period SSIS export process

SSIS, SSRS, and SSAS

This is the same as the above option, except that with the above option you can put your data into a data warehouse (maybe using a Star Schema). Using SQL Server Analysis Services (SSAS) you can put the data in a cube for analysis of a very large set of data.

Pros	Cons
Simplifies report creation (per report costs are low) Extremely scalable and the highest performing option Allows for numerous tools to be used to create the reports (SSRS is only one option) You can use very powerful tools such as the Dundas OLAP Services	Lots of moving parts (requires knowledge of SSIS, SSRS, and SSAS, as well as another database and a SSAS cube) Requires writing a SSIS process (this has a few gotchas) There is a delay between the update of SharePoint data and when the data is ready in the report due to the period SSIS export process as well as the cubing process

Other...

There are several other options here such as using Crystal Reports instead of SSRS. There are really a plethora of other options for reporting on data that is in a database. Some may have integration with SharePoint by allowing you to run the report viewer within SharePoint and maybe even manage the reports within SharePoint (SSRS does both).

Comparison Matrix

Option	Capability	Complexity	Performance	License Cost	Maintenance Cost
Bamboo Solutions SQL View Web Part			?		?
Enesys RS Data Extension
i-PMO's SharePoint Data Miner				?	?
Custom Datasets with ReportViewer		-			-
SharePoint Web Services
Web Parts - Lists
Web Parts - Data View / Data Form	?
Web Parts - Content Query	?
Web Parts - 3rd Party	Depends
Web Parts - BDC	?
Custom UI
SSIS and SSRS
SSIS, SSRS, and SSAS