Implement Filters In SSRS Report Based On SharePoint 2016 List Data

Integrating SSRS with SharePoint 2016 provides the ultimate combo, when it comes to providing the 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, which are-

Front-end
Application
Distributed cache
Search
Custom

When planning to install SSRS, we have to create a Server/VM, which 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 the issues. The best practice mandated by Microsoft, install SSRS in Custom Mini Role Server.

You can see how to create the main source dataset and create a SSRS Table report, using dataset here.

In this article, we will see how to apply a filter to the SSRS report. In order to apply the filter to the report, we have to add an extra dataset , which will serve as the source for the filter. Though we have the main dataset that serves as the source for the report, we cannot use it to provide the filter values, as we will face the error, given – “Forward dependencies are not valid”.

Let’s create a new dataset for the filter. Right click datasets and select ‘Add Dataset’.

Specify the filter name and select the data source. Click on Query Designer to select the list columns to be added to the dataset.

Select the column based on which the filter should be implemented. Here, it is the ‘Product Name’.

The CAML Query for the above operation will come up in the dataset properties page. Click OK to create the dataset.

Thus, we have two datasets, which are-

Product - The main dataset for the report.
FilterDS - The dataset for the filter.

Add Filter Parameter

Now, we have to add the filter parameter, which will be added to the report. Click on parameters and select Add Parameter.

Specify the report filter name and the data type of the filter.

Select “Allow multiple values” to enable the multiple selection in the filter drop down, so that the report can be filtered by multiple values.

From the Available Values tab, select the dataset, which will serve as the value source for the filter.

From ‘Default Values’ tab, select the dataset to assign the default values on form load.

Assign Filter to Dataset

Now, filter the dataset and filter parameter has been specified. We have to assign the filter to the main report dataset.

Click Add and in the Expression field, specify the SharePoint list column, which has to be filtered upon.

In the Value field, set the filter drop down values.

From the parameters tab, select Product parameter. By default, it will add Product.Value(0), which means only the first index value of the parameter will be used for filtering.

Remove the (0) part to use the entire values from the filter parameter to filter.

Let’s run the report to see the filtering in action. By default, it will show all the records, as all the values in the filter parameter are selected by default.

From the Product filter, select specific values and click on View report. This will filter the report, based on the selected values.

Summary - Thus, we saw how to create a filter dataset, based on SharePoint 2016 List data and implement the filter in the SSRS report.