How to create cascading parameters in Reporting services (SSRS)

What is SSRS?

SSRS stands for SQL Server Reporting Services. It is a reporting tool developed by Microsoft that comes free with the SQL Server. It produces formatted reports with the tables of data, graph, and reports. Reports are hosted on a server and configured to run using parameters supplied by users. When we run the reports, the current data appears from the database, XML file or other data source. It provides the security features that controls who can see which reports.

What are cascading parameters?

The concept of cascading parameters is a list of values for one parameter which depends on the values chosen for a previous parameter. Cascaded Parameters help the user when a parameter has a long list of values. The user can filter parameters based on the previous parameter.

What is a cascading report?

Cascading parameters provide a way of managing large amounts of data in a paginated report. You can define a set of related parameters so that the list of values for one parameter depends on the value chosen in another parameter.

We will see how to create a sequence of cascading drop-down lists we are selecting an option from one filter to the next and so on.

Following are the points that needs to be taken care while creating Cascading Drop Down Lists-

1. Understanding the database structure
2. Planning a report (To identify which drop-down lists and which data sets you need to create)
3. Creating Drop Down Lists Parameters
4. Filtering Datasets

Below is the query which we will be using from ‘SSRS_Demo_Data’ table which includes Region, Country, and other information. We are going to copy the below query and create our report.

Note-Before we create a report, we need Shared Data Source or Embedded Data Source to create the report and, in our case, we are going to use the shared data source because, our all the reports are pointing to the same database.

Step 1 – To create the new report right-click on the report and add the new item and rename it as ‘CascadeParameter’ as shown in below the screenshot.

Step 2 – Now, Empty report will be created next part we will be using embedded data source or shared data source. So, right-click on the data source and add data source and configure it as shown below.

Here, we have used the shared data source reference as our shared data source is pointing to the same database as our table is and we can also rename the ‘DataSource2’ as per the requirement so that it will be more readable.

Step 3 – We must create our Datasets for all the detail columns or table where we see the information. This will be the main dataset that we will be creating. We can give suitable name to that dataset then select the data source and paste the query as below.

Here, we have added the parameters as you can see in the above screenshot where region column is @RegionName we can give any parameter name it will not really matter and country in @CountryName and we can also add other parameter for example- State.

Step 4 – Right click on the design Insert and bring a table so that we can insert some column as you can see in the below screenshot. We can make it bold, italic and can change the font, background, etc.

Now, our report is ready we can go ahead and preview it as below-

If we put Asia in region and then will try to put country name as Pak and India it is not going to return me anything because, this is taken as single parameter so for that we just need to put India as a single parameter then, it will return me the records from India as you can see below.

Step 5 –We have to make these parameters as multi value parameter so that we can get the result for both the country name and to implement this we have go to the parameters properties on the country parameter and select ‘Allow multiple values’ it will now accept more than one value.

Step 6 – To make this process automated as dropdown list so that we don’t have to type region name or country name every time for that click on the dataset and create the values for the region first and then for the country.

Here, we have to click on the region parameter properties and go the available values and select ‘get values from query’ and configure it as below.

Now, we can see the region name is in the drop-down list and can select any region from the drop-down list. Now let’s make the country as a drop down as well but we want to make it drop down in a way- if we select Asia or Europe then the countries belong to that region must be shown in the drop-down.

Step 7- In this step Right-click on the data set and rename it as ‘DSET_Country’ and as we are selecting the country, we need to select only the country belong to that region as below-

Step 8- Click on the region parameter properties and go the available values and select ‘get values from query’ and configure it as below-

Here, in our case we can see that the country name is greyed out. Because we have not selected the values from Region once we select the values. For example- Europe then it will start showing us the countries from specific region.

So, that’s how we can create the cascade parameter in SQL server reporting services.

For more such blogs click here

Thanks for reading!! Hope you enjoyed reading this blog.