Integrating SSRS with SharePoint 2016 provides the ultimate combo, when it comes to providing business intelligence solutions to the customers. SSRS can be installed in Native mode as well as an 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
With Feature pack 1, we have two additional roles as well.
- Front-end with Distributed Cache
- Application with search
However, when planning to install SSRS in an 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 SQL Server Reporting Services as an add on.
![]()
Image Source: TechNet
You can read about setting up SSRS in SharePoint Server here. In this article, we will see how to create Bar charts, using SSRS. SharePoint lists the data sources that have been used to create the Table report in this article, and will be used for the Bar chart as well.
![]()
The scope of the article will be.
- Create DataSource
- Add Data Set
- Insert Bar chart
Create DataSource
Right Click DataSource 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 DataSource 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 DataSource 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.
![]()
We can implement the filters as well to Bar chart just like we add the filters to a table report.
Summary
Thus, we saw how to create Bar chart, using SQL Server Reporting Services in SharePoint Server 2016.