SharePoint needs to be configured in SQL Server Integrated Mode for sql server report deployment. Both of the site collection features "Report Server Integration" and "Reporting" must be enabled on the site collection level. If these are not enabled, you will not be able to create the data connection library and report library needed to support reporting.
Step 1 - Create the report
Open Visual Studio BIDS and create a new report project. Create a new Shared Data Source that extracts data from the SharePoint list you wish to work on. Create a new dataset based on that datasource to pull out the fields you wish from that list. Develop your report locally using Visual Studio and its "preview" feature to complete and test the report.Step 2 - Set up the SharePoint site repositories
The first component you will need to add is a data connection library. Add this list type to your SharePoint site. Navigate to this library and copy the url. Remove any trailing data after the list name(in bold):https://your_sharepoint_site/data_connections/Forms/AllItems.aspx
Next, you will need a report library. Add a the report library app type to your site. Copy the URL of the report library again removing the trailing information:
https://your_sharepoint_site/reports/Forms/current.aspx
Step 3 - Configure Visual Studio Deployment and Deploy
Within Visual Studio, right click on the name of your report. Select "properties". This will bring up the Configuration manager for your report project. Here are the configuration properties required to deploy the report to SharePoint:Overwrite Datasets | True |
Overwrite Data Sources | True |
Target Dataset Folder: | https://your_sharepoint_site/data_connections |
Target Data Source Folder: | https://your_sharepoint_site/data_connections |
Target Report Folder | https://your_sharepoint_site/reports |
Target Report Part Folder: | |
TargetServerURL: | https://your_sharepoint_site |
Target Server Version: | SQL Server 2008 R2 or later |
Click the OK button once finished. Right click the project name and choose "Deploy". You will see the progress in the output window of your deployment. Now, navigate to your report library, click on the name of the report, and verify it is working. You can directly use this link on other pages in your site to refer users to this report.
Tricks and Tips
You will sometimes get a permissions error if you deploy a report to SharePoint if you did not specify credentials for the data source. Ensure that your data source has "Use Windows Authentication" selected within the data source properties.Your reports will not be viewable by other non site admins until it is "Published". Once you have the report deployed and approved by your client (if necessary), navigate to the report library, click the three dots to the right of the report name and choose "Publish a major version". This will make your report viewable by all users who have permissions to your site.
Sometimes you will want to host the report in a webpart page, rather than have the report open directly. To do this, create a new web part page in SharePoint and insert the "SQL Server Reporting Services Report Viewer" located in the "SQL Server Reporting" category. Use the report viewer web part tools to select and embed your report.
Optionally, you can enable remote errors for reports in local mode using the "Site Settings->Reporting Services->Reporting Services Site Settings" site action. Put a checkmark in the box "Enable remote errors in local mode". This will give you a more detailed error message if your report fails.