Configure Visual Studio BIDS to deploy a SharePoint list report to SharePoint (SharePoint 2013)

The ability of Visual Studio Business Intelligence Services to report against a SharePoint list and create a report based on that list is a useful way of presenting data. You can then embed this report in a report viewer web part on a web part page to display. Deploying the report to SharePoint using the built in Visual Studio BIDS tools however can be tricky as it requires coordination between SharePoint site components and the report deployment configuration.

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.