How to Create and apply filter using multi-value parameter in a SSRS report using Report Builder

In this post, I will explain how we can create a multi-value filter and apply it on SharePoint list using Report Builder.

Before we start just have a look at the SharePoint list first. This list contains school applications received from different countries for different schools.

Screen1

Our target is to create a report that should display number of applications received from each country filtered on School type/school applied for i.e. Junior school, senior school etc. It should look something like this – Screen2

 

I will skip the steps that shows how to enable Report Builder in your ‘Reports” list, create and connect to the data source (which is the SharePoint list in this case). For details, you can refer to my post here – Create a Pie Chart

Let’s jump straight to designing a query and so on.

Firstly, create a report without any filter

  1. In “Design a query” screen, choose the SharePoint List on which you want to generate a report. The list I will use for this demo is a list called ‘Statements’ and click ‘Next’. Just note that we will keep the “Applied filters” section empty as it doesn’t allow us to setup filter correctly if a filter parameter is multi-value. Click ‘Next’.

Screen3

2) Choose the ‘Chart Type’ as ‘Column’ and click ‘Next’.

3) Under ‘Categories’, drag the field name ‘Country’. Also, drag the ‘Country’ field under ‘Values’ and set the formula to ‘Count’ as shown below. Click Next.

Screen4

4) Choose any Style. I have chosen ‘Ocean’ and click ‘Finish’.

5) Feel free to drag and expand the size of the chart. At this stage, you can Run the report and should be able to see the actual data coming but it’s without the filter yet.

Create a multi-value parameter

6) Now, to add the School filter, lets first create a parameter which would allow multiple values.

7) In your report builder in design mode, on the left hand side look for the ‘Parameters’ folder under ‘Report Data’. Right click on it and click on ‘Add Parameter’.

Screen5

8) Under ‘General Section’, enter ‘School’ for ‘Name’ and ‘Prompt’ fields. Under ‘Data type’, check ‘Allow multiple values’.

Screen6

9) Don’t click on ‘OK’ yet. Under ‘Available values’, select ‘Specify values’ and add these four values – Junior School, Junior Secondary school, Middle School and Senior School. Now, click ‘OK’.

Screen7

Create a filter

10) We have created a parameter to be used in a filter, we now need to create a filter. For this, right click on Dataset1 under ‘Datasets’ and then click on ‘Dataset properties’.

Screen8

Expression – Select the field (on which you want to apply filter) from the list of fields. As I want to apply filter on School field, name of the field in my SharePoint list is ‘School_applied_for’.

Operator – Select ‘ IN’ as we want to use a multi-value parameter as a filter.

Value – The filter value should the multi-value parameter we just created in the above section which is ‘School’. Click on the expression builder icon(fx), Select ‘Parameters’ under ‘Category’ and double-click on ‘School’. Just make sure that value for expression is without (0) i.e. (in this case) it should be =Parameters!School.Value and not =Parameters!School.Value(0).

Screen10

Click ‘OK’. It should look as shown below –

Screen9

Again, click on ‘OK’.

11) You are done. Now, it’s time to run and test the report and then, don’t forget to ‘Save’. It should look as shown below –

Screen11

If this helped you then please don’t forget to leave any comments.

Leave a Comment

Your email address will not be published. Required fields are marked *