Access Sharepoint Lists in SSRS Reports (SQL Server 2012)

There are many cases where we need to display SharePoint List records in SSRS reports. To achieve this, earlier we used to follow different hard paths like trying to access SharePoint Database, using some 3rd party tools to integrate SharePoint lists, etc.


But, in SQL Server 2012 reporting / Report Builder 3.0, this was made straight forward be adding an option 'Microsoft SharePoint List' in Data source type selection. follow below simple steps to get some basic idea on creating a Dataset for a SharePoint List.


1. Click on Create New Data Source and select 'Microsoft SharePoint List' from Type Selection Dropdown

2. Now, prove the SharePoint Portal URL in Connection String. Also, provide the Credentials as per your need and Click on Ok.

3. Now we have a dataset pointing to the SharePoint Portal. We are left with creating a Dataset. Click on Create Dataset and select SharePoint Portal as Data source.

4. Now, click on Query Designer and you will be able to see the SharePoint Lists / Libraries available in provided SharePoint portal.

5. Select the List you needed and also the required fields from it. In section 3 in above image, you can filter the SharePoint List data. Once set up, click on OK.


Now we have a dataset which fetches data from SharePoint list and you can build any type of report using this data set.


Below are few important notes to be considered in this aspect.

• Default view data is fetched  from the SharePoint list when we connect it as a Data Source
• If we need to join multiple lists, easy way is to create a list in SharePoint using Lookup relations and then directly creating a dataset for the new list.