SSRS: Use the Select All option in the drop down and how to query data to report according to the selections

I have written most of the bits and pieces which may need to make work with SSRS in my previous posts. Here is another common scenario in reports. You may need to select some options or alternately select all the options to view a report. Let’s see how to handle such scenario.

Below scenario, I allow user to select one or more divisions or option to select “”Select All”.

On the report heading when you view the report you also need to show which divisions you have selected, whether you have selected all divisions or one. (Selected Divisions : ...,..,..,)

You may need to handle it using the expression like below. The trick is use JOIN with “,” like below. Use the parameter label with Join which will separated by commas. So if you select only two divisions it will show on the report like

SELECTED SECTION : CS, ENG

How to show the SELECT ALL on the drop down. For that you need to allow multiple values on parameter properties.

Ok, Let’s see how the selected multiple values will be passed to Stored Procedure and bring report data. A trick to solve that problem is to use a function and pass the parameter values to this function which return and set multiple data ready to query eg; DIVISION IN (‘cs’,’eng’)

In the function below split the drop down multiple values and return to my stored procedure. So I can query the division data according to the drop down selections.