SSRS (SQL Server Reporting Services) is a server-side report building tool. 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.
Once SSRS installation is completed and the required Content Types have been added to the Report Library we get the below templates on clicking ‘New Document’. This indicates that the environment set up for SSRS is successful. Report Builder Report is the design tool that will be used to create the SSRS reports.
However, on clicking Report Builder Report we might face the below warning message, if we are running it for the first time. This is because SQL Server Reporting Services requires Report Builder Tool to be installed on the server. This is not installed along with SSRS installation. In order to get it installed, Click on ‘Get Report Builder’.
This will navigate to the Microsoft site from where we can download the SQL Server 2016 Report Builder.
Click on Next.
Accept the license agreement.
Proceed to the Next step.
Click on install which will start off the report builder installation in SharePoint Server.
Wait for a couple of minutes for the installation to complete.
Click on ‘Finish’. Thus Report Builder has been successfully installed on the server.
It will show a warning message. Click on Allow.
This will spin up the report builder.
It will connect to the report server to create and process the report.
Finally, the report builder is up and we can create fully fledged SSRS reports using Report Builder.
Summary - Thus, we saw how to set up Report Builder Tool using which we can create SSRS reports in SharePoint Server 2016.