Using a SharePoint List as a Data Source in SQL Server Reporting Services 2008 R2

By: Ray Barley | Read Comments (7) | Print Ray is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert. Related Tips: More

Problem

We are constantly getting requests to produce reports that include data from SharePoint lists. We have just started using SQL Server Reporting Services (SSRS) 2008 R2 and see that there is a new Microsoft SQL Server Reporting Services SharePoint List Data Extension. Can you provide an example of how this works?

Solution

The Microsoft SQL Server Reporting Services SharePoint List Data Extension that comes with SSRS 2008 R2 allows you to specify a SharePoint site or subsite as a Data Source, then create a Dataset that is based on a SharePoint list in that site or subsite. This new data extension allows you to access list data in SharePoint Foundation 2010, SharePoint Server 2010, Windows SharePoint Services 3.0, and Office SharePoint Server 2007 sites.

In this tip I will review the following steps required to develop a report that retrieves data from a SharePoint list:

• Create a Data Source
• Create a Dataset
• Layout the report

To begin create a new Report Server project using SQL Server Business Intelligence Development Studio (BIDS) that comes with SQL Server 2008 R2. Note that the SharePoint List Data Extension is a new feature with SQL Server 2008 R2; it is not available in prior versions of SQL Server. Add a report to the project. You should see the Report Data tree view with a Data Sources and Datasets node (in addition to the other nodes) as shown below:

Create a Data Source

When building a report that uses data in a SharePoint list, the Data Source Connection String property must be set to the URL of the SharePoint site or subsite that contains the list you want to use in your report. Right click on the Data Sources node in Report Data and select Add Data Source from the popup menu. Fill in the Data Source Properties General tab as shown below (specify the URL in the Connection string for your SharePoint site or subsite):

Click on Credentials and fill in the dialog as shown below:

Create a Dataset

Once you have a Data Source that points to the URL of a SharePoint site or subsite, the next step is to create a Dataset based on the Data Source. Right click on Datasets in Report Data and select Add Dataset from the popup menu. Fill in the dialog as shown below (select the Data Source created above):

Click on the Query Designer button to display the SharePoint lists in the SharePoint site specified in the Data Source. Select a list and some fields from the list as shown below (I chose an Announcements list):

Note that you can only select fields from one list. Click OK and you will be returned to the Dataset Properties dialog which is now complete:

The Query is displayed as XML and it shows the name of the list and the fields selected.

Layout the Report

The last step is to add a Table to the report designer from the Toolbox and drag/drop fields from the dataset to the table. The report layout is shown below with the fields selected from the Announcements list:

Click Preview to show the report in the designer:

Next Steps

• You can also use Shared Data Sources and Shared Datasets, which allow multiple reports to use these items.
• Take a look at the MSDN topic Getting Data from a SharePoint List Data Source Type for additional details; one of the limitations noted is that a dataset can only retrieve data from a single list.

Related Tips: More | Become a paid author

Last Update: 7/22/2010

Share:

Share

Tweet

Free SQL Server Learning The SQL Server Security THREAT - It’s Closer Than You Think Tutorial - SQL Server Performance Tuning and Monitoring

Comments and Feedback:

Friday, September 17, 2010 - 4:54:23 PM - Pobblebonk	Read The Tip
Hi, Is there any way of loading the SharePoint data source into non R2 SQL Server environments? Txs Mat

Saturday, September 18, 2010 - 8:35:00 AM - Ray Barley

Read The Tip

You can write .NET code and use the SharePoint Lists web service which will allow you to do just about anything with a list.  There is a GetListItems method that returns items from the list based on your query parameters.  You then would implement your own Data Processing Extension in Reporting Services.  I've never done a Data Processing Extension so I don't know what kind of level of effort is involved. I think this approach is essentially what is provided in Reporting Services 2008 R2.

Saturday, September 18, 2010 - 11:19:49 AM - Pobblebonk	Read The Tip
Hi, Appreciate the response I understand there is the ability to write your own DPE however I am not a full on developer and don't have the time to spend developing something which you would think someone would have put into CodePlex by now. Txs Mat

Wednesday, March 28, 2012 - 2:41:50 AM - wingsbox	Read The Tip
how can i get a carlander data of SharePoint 2010 in reporting service? The data that I get from a list data of SharePoint 2010 is empty

Wednesday, March 28, 2012 - 9:52:40 AM - Ray Barley	Read The Tip
When you create the data source you specify the URL of the web site that has the calendar you want.  When you specify the dataset you will find the calendar in the in the list on the left hand side of the query designer.  Look at the screenshot above and you'll see an entry for BI Team Calendar.

Tuesday, April 17, 2012 - 1:09:42 PM - geeam	Read The Tip
Can I SharePoint list combine with a reporting table to combine fields from the two sources?

Tuesday, April 17, 2012 - 5:06:03 PM - Raymond Barley	Read The Tip
Try the LOOKUP functions that were added in SQL Server Reporting Service 2008 R2.  Take a look at this tip that I did on how to use them: http://www.mssqltips.com/sqlservertip/2141/sql-server-2008-r2-reporting-services-lookup-functions/

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"SQL doctor is the best SQL product on the market, by far. All of Idera's tools are great, but this is the icing on the cake!" Write, edit, and explore SQL effortlessly with SQL Prompt. SQL Servers keeping you up at night? Contact the Edgewood SQL Server consultants for a Health Check. Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy. Join the over million SQL Server Professionals who get their issues resolved daily. Valuable SQL Server web casts on Performance Tuning, Development, Administration, SSIS and more...