In this blog, I will show you how to add normal and cascading parameters to an SSRS report. I assume that you have already read my previous blog. I will continue from the end of that blog.
We have already done with SSRS report running without any parameters from our previous blog.
We are going to add two parameters. One is for customer country and the other one is for customer city. So, we will have two drop down items as parameters. When we select a value for country drop down, it will be a source for the other parameter and city drop down is going to load available cities related with selected country.
Click on design button and go to design view. On the left hand side panel, right click on Dataset folder and add a new dataset. Fill the opening window same as the picture below. We are using our existing Data Source which we added before. (Data Source has Customers and Orders tables.) Write the query and click OK.
Data Set is ready. After that, right click on Parameters folder and add a parameter. Fill out Name, and Prompt fields as “CountryParameter”. Click on Available Fields and select Get values from a query radio button. Select values same as the picture below.
For the second parameter, we need another Data Set. It is same as the previous one. The only difference is that when we write the query we will add the first parameter to the new Data Set. We need to get all cities which belong to a specific country. So, in the where clause, we should use “@CountryParameter”. You need to put @ before the parameter name to use it in Data Set.
What we did for CountryParamter, we need to do same thing for CityParameter. Right click on Parameters folder and a new parameter.
Last thing we need to do is that, implementing the city parameter into the main query. Right click on DataSet1 and select DataSet properties. Add where clause to query. (Where Customers.City=@CityParameter)
You are done! Click OK and run the project.
Tags: SQL Server, SSRS, Cascade, Parameters, Report Builder, Reports
Powered by Exsilio Solutions