Integrating SSRS with SharePoint 2016 provides the ultimate combo when it comes to providing business intelligence solutions to customers. SSRS can be installed in Native mode as well as in Integrated mode in SharePoint Server. Unlike SharePoint 2013, we have a major architectural change when planning SSRS integration with SharePoint. With SharePoint 2016 Mini Roles were introduced which was an attempt to off load SharePoint Server from some of the functionalities. We have 5 Mini Roles,
- Front-end
- Application
- Distributed cache
- Search
- Custom
When planning to install SSRS, we have to create a Server/VM that is set as Custom Role. The installation will succeed for the first time even if we install SSRS in a different Role but at a later time, when the System Restarts, we will encounter issues. So, as the best practice mandated by Microsoft, install SSRS in Custom Mini Role server.
In this article, we will see how to get started with SSRS reporting using SharePoint List data in integrated ode. Spin up the report builder by going to the Report Library.
![]()
It will open up the Report builder tool which will be used to create SSRS Reports.
Add Data Source
Right click the Data Sources folder and add the data source which will be used to fetch the data for the report.
![]()
Specify the name for the data source and select ‘Use a connection embedded in my report’. From the connection type, select ‘Microsoft SharePoint List’.
![]()
Add the connection string. It is the site URL where the source list resides.
![]()
Click on Test Connection to see if the connection can be established.
![]()
Thus, the connection was created successfully.
Add Data Set
Now, we have to add a dataset. Right click Datasets folder and add a new dataset.
![]()
Select the previously created data source and click on ‘Query Designer’.
![]()
This will list down all the lists within the site.
![]()
Specify the required fields that will be used in the report. Selecting all the fields is not advised as it effects report performance.
![]()
Click on OK. This will add the fields to the dataset and the CAML query for the same can be found in the dataset properties.
![]()
Thus, we have created the data source and the data set.
Display Report
Now, we have to show the data as a report.
![]()
We have multiple options to display the business intelligence report. Here we will use a simple table to display the data.
![]()
Add the table and drag and drop the columns from the data set on to the table.
![]()
Thus, the table is assigned with List field values.
![]()
Click on Run to process the report and display the results.
![]()
Thus, we have the first look of the SSRS report from the SharePoint 2016 List.
![]()
Report builder provides options to set the font, background colour, and offers other styling methods. We can add a bit of cosmetic changes to the report, as shown below:
![]()
Summary
Thus, we saw how to create SSRS report using SharePoint 2016 in integrated mode. We will see more of SSRS reporting in my upcoming articles.