Reporting Service integrated with SharePoint provides a rich reporting platform. Lets see how we can create SQL Server reporting services report from SharePoint list data:
Create a Library for Reports and add Report Content Type
To start with, Lets create a Document Library, Add Report Builder Report content type to create a new report.
- Go to Library Setting >> Advance Settings >> Select Yes for “Allow management of content types” and click on Yes.
- This Navigates to library settings again. Scroll down and Click on “Add from existing site content types” under “Content Types”
- Add “Report Builder Report” content type, and click on “OK”.
Create Report using Report Builder Tool
Go to Files Tab >> Click on drop down on “New Document” Ribbon button >> Choose “Report Builder Report”
This launches “Report Builder Client 3.0” installation for the first time. Wait for the ClickOnce installation to complete.
In the Report Builder, Click on “New Report”, choose the Table or Matrix Wizard.
Choose “Create a Dataset” option and click on “Next” button
Click on New to create new data source for the report.
Give a Name to Data source and choose connection type as “Microsoft SharePoint List” and enter the connection string as the URL of your SharePoint site. Click on Credentials Tab
Specify the relevant credential settings.
Verify the connection properties by click on “Test Connection” button
Choose the SharePoint lists you want to present as a Report.
Pick the Fields to display.
Set Grouping, Sub-total, Grand Total options
Select the style for your report
And finally, we will save our report in the document library we have created in the early step.
That’s all. Add Reporting Services Web part to desired locations and point to the report we’ve created. Here is the SharePoint reporting services from list:
Now these reports can be export to Excel, PDF, Word, etc. BTW, Its also possible to build the same report using Visual Studio. Refer: How to Create and Deploy SSRS 2012 Reports to SharePoint 2013
Here is an another related article: SQL Server 2012 Reporting Services and SharePoint 2013 integration configuration