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

By:   |   Updated: 2010-07-22   |   Comments (39)   |   Related: > SharePoint


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:

The Microsoft SQL Server Reporting Services SharePoint List Data Extension that comes with SSRS 2008 R2

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):

the Data Source Connection String property must be set to the URL of the SharePoint site or the subsite

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

Click on Credentials and fill in the dialog

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):

create a Dataset based on the Data Source

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):

Click on the Query Designer button to display the SharePoint lists in the SharePoint site specified in the Data Source

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:

you will be returned to the Dataset Properties dialog

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:

add a Table to the report designer from the Toolbox

Click Preview to show the report in the designer:

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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2010-07-22

Comments For This Article




Sunday, February 26, 2017 - 2:11:58 PM - Vijju Back To Top (46707)

 Very useful post.  I have a  similiar requirment in SSRS where I have to use Excel (xlsx) data which is in sharepoint 2013. I have added Excel sheet to Sharepoint list and trying to access it from SSDT using Sharepoint list as datasource type but I have been getting the error "Access enied" for million times.Please help to fix it.

 

Wednesday, April 8, 2015 - 8:12:13 AM - kishor Back To Top (36855)

thanks it helps me

Friday, March 13, 2015 - 9:34:10 AM - Raymond Barley Back To Top (36517)

Generating a report from 2 different XLS files in a SharePoint list - I can't thnik of an easy way to do this.  Maybe you could combine the XLS files into a single XLS file, import the combined XLS file into a custom list, then run your report based on what I did in this tip.

A better approach might be to import the XLS files into tables in a SQL Server database then report from it.

Friday, March 13, 2015 - 6:00:24 AM - subha Back To Top (36515)

 

Hi Ray,

 

Thaks for the post and it would be great if can you let me know , if there is any way to generate a report from 2 differents xl that are reciting in the sharepoint list.

 

I have got a requirment : where i need to generate a report from 2 differents xls that are residing in a list. 

to be more clear let me put like this: there are 2 different xls which will be residing in sharepoint list and we need to generate a report with the inputs of the 2 xls.

 

Please suggest me the best approchs in solving  my recuirnment.

It would be of great help if you could help me out in delivering my requirnment.

 

Thanks in Advance.

Subha

Friday, May 23, 2014 - 3:18:13 PM - Dawn Back To Top (30910)

Thank you SO MUCH for posting this! It's been a great help to me!

 

And I have to say you are awesome for answering everyone's questions in the comments section. As long as I've been searching through technical posts, I've never seen an author do that.

Sunday, December 1, 2013 - 11:56:09 PM - Avahita Back To Top (27645)

Thanks for your explanation, but my problem was the type of  report server project.I followed the steps that I described in my previous post  but about the list with folder does not return the correct result!!!!!!

Sunday, December 1, 2013 - 9:08:04 AM - Ray Barley Back To Top (27644)

Integration Services project is not a report; it's something you use to retrieve data from various data sources, transform the data, and store the data in a different place.  This is typically called ETL - extract, transform and load.  It is used in data warehousing applications.

Thursday, November 28, 2013 - 1:43:50 AM - Avahita Back To Top (27634)

I'm confused about difference netween "integration service project" and  "report server project". your comment about "integration service project " (Advanced Editor; try setting IncludeFolders to True and possibly also IsRecursive to True.)

but I follow this steps
1.create "report server project"
2.create rdl report with datasource of sharepoint site
3.create dataset of sharepoint list in that site with this query:
◦
<RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ListName>[mylistname]</ListName>
 
  <ViewFields>

    <FieldRef Name="_x0646__x0648__x0639__x0020__x06" />
    <FieldRef Name="Title" />
    <FieldRef Name="_x0627__x0648__x0644__x0648__x060" />
    <FieldRef Name="_x0632__x0645__x0627__x0646__x00" />
    <FieldRef Name="_x0648__x0627__x06af__x0630__x06" />
    <FieldRef Name="_x0644__x064a__x0646__x06a9__x00" />
    <FieldRef Name="_x062a__x0648__x0636__x064a__x06" />
    <FieldRef Name="_x067e__x0627__x0633__x062e__x00" />
    <FieldRef Name="_x0648__x0636__x0639__x064a__x06" />
    <FieldRef Name="_x062a__x0627__x0631__x06cc__x06" />
    <FieldRef Name="_x0645__x062f__x062a__x0020__x06" />
    <FieldRef Name="_x062a__x0648__x0642__x0641__x00" />
    <FieldRef Name="_x0634__x0631__x062d__x0020__x06" />
    <FieldRef Name="LinkTitleNoMenu" />
    <FieldRef Name="Author" />
    <FieldRef Name="Editor" />
    <FieldRef Name="Modified" />
    <FieldRef Name="Created" />
    <FieldRef Name="_x0646__x0648__x0639__x0020__x060" />
    <FieldRef Name="_x062a__x0627__x0631__x06cc__x060" />
    <FieldRef Name="ID" />
    <FieldRef Name="ContentType" />
    <FieldRef Name="Attachments" />
    <FieldRef Name="FolderChildCount" />
    <FieldRef Name="LinkTitle" />

  </ViewFields>

  <Query>
  
  <ViewAttributes Scope='RecursiveAll' />
<View Scope='Recursive'></View>
  </Query>

</RSSharePointList>

◦this query results all items in folders of list in preview tab but when my report upload to sharepoint report library in sharepoint site result only folders and items in root folder of list
4.upload the report to report library in sharepoint site.

Wednesday, November 27, 2013 - 2:36:39 PM - Raymond Barley Back To Top (27629)

You can configure some custom properties of the SharePoint List source in the Advanced Editor; try setting IncludeFolders to True and possibly also IsRecursive to True.

Download the documentation here: go to http://msdn.microsoft.com/en-us/library/hh368261.aspx and click the link to download the Word document Extracting and Loading SharePoint Data in SQL Server Integration Services.

Wednesday, November 27, 2013 - 3:03:52 AM - Avahita Back To Top (27623)

How to retrieve all items from all folder  in a list? because my report return all data in root folder only. that is very strange !!! my report in designer in vs2008 retrive all items without folder but when i upload in my report library in sharepoint site retrive items in folders.

Tuesday, July 2, 2013 - 8:24:23 AM - Bob Armstrong Back To Top (25674)

All,

This works.

                         DataSet1                                  DataSet2                          DataSet2            
=Lookup(Fields!Employee_Name.Value, Fields!Employee_Name,Fields!Department_Individual.Value, "DataSet2")

 

Tuesday, July 2, 2013 - 7:03:43 AM - Bob Armstrong Back To Top (25671)

Nael,

I'm still trying to figure it out using this link:

http://rtmgroupq8.com/en/creating-ssrs-reports-from-two-sharepoint-lists-or-more/

 

Tuesday, July 2, 2013 - 4:35:57 AM - Nael Back To Top (25669)

Please how to create report from 2 sharepoint 2010 lists

Monday, July 1, 2013 - 4:51:11 PM - Rnauman Back To Top (25662)

In your full example above, is there anyway to Filter the Query on Created Date, let say [Today] - 30 (this is the Sharepoint way to do this)

Monday, July 1, 2013 - 8:25:20 AM - Raymond Barley Back To Top (25646)

I did a tip a while back showing how to call a SharePoint web service by using a SQL Server CLR function.  With CLR you can write .NET code then create SQL Server functions, stored proc, triggers, etc. that execute your .NET code.  The example doesn't do what you want, it's based on SharePoint 2007, but the concept will work.  You have to figure out how to get what you want using the SharePoint web service (I think the Lists web service is what you want - see http://msdn.microsoft.com/en-us/library/lists(v=office.12).aspx)

 

Monday, July 1, 2013 - 6:29:49 AM - Bob Armstrong Back To Top (25643)

Ray, As far as I can see.  We can only access the List and not the view of a List.

Trying to come up with a solution.

I'll let you know if I can figure out a work a round to this madness.

Friday, June 28, 2013 - 8:20:01 PM - Ray Barley Back To Top (25625)

I don't see any way to do that; I added a view to a calendar list and I don't see the view I added when I setup the dataset query.

Friday, June 28, 2013 - 8:22:07 AM - Bob Armstrong Back To Top (25612)

I can access a SharePoint 2010 list in SSRS.  Is there a way to access a view from one of those lists?

Thursday, April 11, 2013 - 5:58:07 AM - Ray Barley Back To Top (23300)

If you're saying you get no data when you run the report after you've deployed it, that may be a security issue; i.e. the deployed report isn't using the credentials of the caller, or you need kerberos, or something else.  A simple way to test is to use stored credentials in the connection and see if that solves the problem.

Take a look here in the credentials section: http://msdn.microsoft.com/en-us/library/ee633650.aspx

 

Wednesday, April 10, 2013 - 4:48:54 PM - george hardy Back To Top (23290)

thanks for the reply.

well, that may not be it after all.  i build the query, pulling the document name, version, modified date, etc.....in the query editor, it pulls the info from the library fine when i click "run Query", but going info run report, the fields are all blank.   man, i wish MS would have just bought crystal reports :S

Wednesday, April 10, 2013 - 4:17:34 PM - Raymond Barley Back To Top (23289)

I don't see any way to do that; I added a view to a calendar list and I don't see the view I added when I setup the dataset query.

Wednesday, April 10, 2013 - 3:54:08 PM - george hardy Back To Top (23287)

so what if i wanted the report to his a view other than the default view set for the list?

Thursday, February 28, 2013 - 5:46:34 PM - Raymond Barley Back To Top (22492)

If you want to render the calendar view in SharePoint then you can create an external content type that points to your SQL Server data then use the external content type in a calendar list.

You can create an external content type by using SharePoint designer as well as by writing .NET code.

I don't think this has been covered on mssqltips so you can search (e.g. google) for sharepoint 2010 external content type calendar view and you will get some places to start.

Thursday, February 28, 2013 - 12:42:46 PM - Matt Smith Back To Top (22483)

Hi Ray,

I'm wondering if you can offer some suggestions on the best (better) tools to use to generate a Calendar View (Weeks and Days) simliar to Outlook from data in SQL Server.  Reporting Services?  SilverLight?  Any other suggestions?

Thanks.

 

Wednesday, December 5, 2012 - 2:00:04 PM - Ray Barley Back To Top (20770)

Check the guidance in the Credentials section of this link: http://msdn.microsoft.com/en-us/library/ee633650.aspx

Wednesday, December 5, 2012 - 12:13:06 PM - AJ Back To Top (20768)

I can view my report just fine in Preview but when I select "Run", I get an error stating: "An error occurred when accessing the specified Sharepoint list.

I'm using Windows Authentication (integrated mode).  I get this error both on my local development machine and on the production server.

Any ideas on how to fix this problem?

Monday, December 3, 2012 - 6:48:41 AM - Raymond Barley Back To Top (20700)

I can think of two possibilities.  First maybe you can create a view on the SharePoint list; the view would only include the date range that you want.  Second when you set the properties for the SharePoint List Source component you can specify a CAML query.

Here's a decent link on CAML: http://sharepointmagazine.net/articles/writing-caml-queries-for-retrieving-list-items-from-a-sharepoint-list

Sunday, December 2, 2012 - 5:05:37 PM - Raghu Ariga Back To Top (20694)

I have tried accessing Calendar list from SharePoint as data source into SSRS report. I was unable to get previous years items, like if start date is 2011 i was not getting the item in the datasource on the SSRS report. Is there any way we could include items with start date as previous year. Thanks Ray.

Thursday, November 29, 2012 - 9:27:01 AM - Ray Barley Back To Top (20629)

You can configure some custom properties of the SharePoint List source in the Advanced Editor; try setting IncludeFolders to True and possibly also IsRecursive to True.

Download the documentation here: go to http://msdn.microsoft.com/en-us/library/hh368261.aspx and click the link to download the Word document Extracting and Loading SharePoint Data in SQL Server Integration Services.

Thursday, November 29, 2012 - 2:18:13 AM - Rohit Back To Top (20620)

this is not accessing data inside the folders.

Friday, August 3, 2012 - 3:37:06 PM - Ray Barley Back To Top (18918)

Take a look at this tip: http://www.mssqltips.com/sqlservertip/1733/accessing-sharepoint-lists-with-sql-server-integration-services-ssis-2005/

It walks through an example of using a SharePoint List Source that Microsoft put out on codeplex. 

Friday, August 3, 2012 - 2:55:11 PM - Franckb Back To Top (18916)

Is it possible to use SharePoint site with SSIS instead ?  I want to use the content as a lookup source in ETL and not sure of how to create the Datasource.

I've tried with a davwwwroot link but I've got trouble with authentification.  Any idea ?

 

 

Tuesday, April 17, 2012 - 5:06:03 PM - Raymond Barley Back To Top (16980)

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/

Tuesday, April 17, 2012 - 1:09:42 PM - geeam Back To Top (16979)

Can I SharePoint list combine with a reporting table to combine fields from the two sources?

 

Wednesday, March 28, 2012 - 9:52:40 AM - Ray Barley Back To Top (16672)

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.

Wednesday, March 28, 2012 - 2:41:50 AM - wingsbox Back To Top (16658)

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

Saturday, September 18, 2010 - 11:19:49 AM - Pobblebonk Back To Top (10180)
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

Saturday, September 18, 2010 - 8:35:00 AM - Ray Barley Back To Top (10179)
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.

 

Friday, September 17, 2010 - 4:54:23 PM - Pobblebonk Back To Top (10177)
Hi,

Is there any way of loading the SharePoint data source into non R2 SQL Server environments?

Txs
Mat














get free sql tips
agree to terms