Configure SQL Server 2016 Reporting Services in SharePoint Integrated Mode and Create the First Custom Report
Introduction
SQL Server Reporting Services can be installed in SharePoint 2016 by installing it as a Custom Mini Role on a separate Server. Though we can install it in a single server installation mode, upon restart, it will stop functioning. So, when planning to install SSRS in integrated mode with SharePoint 2016, we have to make sure that we create a SharePoint Server with Custom Role. Once it is created, we will install the SQL Server Reporting Services as an add on.
http://csharpcorner.mindcrackerinc.netdna-cdn.com/article/install-and-configure-sql-server-reporting-services-in-sharepoint-server-2016/Images/image001.png
*Image Source: TechNet
*
Get SQL Server Installation Files
The SQL Server installation files are required to add the SSRS add-ins to the SharePoint Server. In case the SQL Server Set up files used for SQL Installation are missing in the server, you can get it from here
Select the package as ISO and click on Download.
This will download the installation media files.
Install Reporting Services in Custom Mini Role Server
Now, we have to install SSRS bits in the Custom Mini Role SharePoint Server. In order to do that, spin up SQL Server Installation Center.
Browse for the Installation file location.
Select the radio button ‘Add features to an existing instance of SQL Server 2016’ and click on Next.
Select the features that have to be installed as part of the reporting services installation.
- Reporting Services – SharePoint
- Reporting Services Add-in for SharePoint Products.
http://csharpcorner.mindcrackerinc.netdna-cdn.com/article/install-and-configure-sql-server-reporting-services-in-sharepoint-server-2016/Images/image032.png
Select ‘Install only’ radio button from ‘Reporting Services SharePoint Integrated Mode’.
This will install two SSRS components required for the successful installation of reporting services. Click on Install to start the installation of Reporting Services in the Custom Mini Role.
This will start the installation of the reporting services components.
Finally, the reporting service installation has completed. A Server Restart is required to reflect the updates in the Custom Mini Role.
Register and start the Reporting Services SharePoint Service
This section is required to be done only if we had installed the Reporting Services in a Server that does not have SharePoint installed (where SharePoint was installed after installing Reporting Services). Currently, the demo server we are using has SharePoint installed, so it is not necessary as it has already been done automatically in the previous steps. So, in case you have installed SharePoint after Reporting Services installation, run the below steps to register Reporting Services SharePoint Service. Spin up SharePoint 2016 Management Shell as Administrator.
Run the below commands to install Reporting Services and Reporting Services Proxy Service.
Install-SPRSService
Install-SPRSServiceProxy
Install Reporting Services Add-in in SharePoint Front End Servers
The next step of setting up SSRS in the multi farm environment is to install ‘Reporting Services add-in for SharePoint’ in all the Front end SharePoint servers. If you have load balancers, it is advised to install this component in all of them. In order to install the Reporting Services add-in, make sure that you have the SQL Server set up files copied to the server. Just like we installed the reporting services in the Custom Mini Role Server, spin up SQL Server 2016 setup wizard. As you proceed through the wizard (just like we saw in Custom Mini Role above), When the feature selection window comes, select only ‘Reporting services add-in for SharePoint’. Do not select ‘Reporting Services- SharePoint’.
This will install the reporting services add-in to the front end servers.
Create SSRS Service Application
Now, we have to configure SSRS Service application in the Custom Mini Role Server (Or wherever Central Administration has been configured). This was one of the reasons why we had configured Central Administration in the Custom Mini Role server. Spin up Central Administration and from the service applications page, select ‘SQL Server Reporting Services Service Application.
Specify the Application Pool, Service Account, Data base name, and back-end data base Server that will be used by the service application. Click on OK.
This will start the provisioning of the SSRS Service Application.
Finally, the service application has been created.
Test SSRS installation
In order to ensure that SSRS has been installed and configured successfully, we can go to a document library and try to convert it into a Report library by adding the SSRS Content Types. From the Add Content Types page, add the below SSRS Content Types to the library. The fact that these Content types are visible, is a proof of successful SSRS configuration.
Once it is added, you will be able to create SSRS reports from the library by launching the Report Builder Tool.
A Sample report would look like below.
Create first Report
- Create DataSource
- Add Data Set
- Insert Bar chart
Create Data Source
Right Click Data Source and select “Add Data Source”.
It will open up the data source properties Window, where we can select the connection type. Set it as Microsoft SharePoint list.
Add the connection string as the site collection URL.
Click Test Connection to check the connection status.
Add Dataset
Once the Data Source has been created and the connection has been tested, we can create the dataset, which will act as the table from which data will be used for the creation of Bar chart.
Select Query Designer to choose SharePoint List, based on which the dataset has to be created.
Select SharePoint List by selecting the check box against the list.
You can either chose to select all the columns within the list or select only the required columns needed for the chart. The best practice is to select the required columns as more data in the report means more rendering time for the report.
Once you click OK, Query section will show CAML based query. Click OK to complete the creation of the data set.
The DataSource and the data set will come up in the left pane of the report builder, as shown below.
Create Bar Chart
Once the data set and Data Source has been created, we can add Bar chart to the report. Select Insert Chart option
Specify the type of the chart, which will be using in the report. Here, we will go with the Bar chart option.
In the Values section, specify the List Columns that should come as the Y axis in Bar chart.
The main category column that would come up in the X-Axis will be added to the Category Groups.
Thus, we have set up Bar chart. We can head over and run Bar chart report in Report Builder.
We can upload the report to the report library and view the report in the Browser, as shown below.
Summary
Thus we saw how to configure SQL Server Reporting Services in SharePoint Integrated Mode and create the first report using SSRS and SharePoint List.