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
If you want to create useful reports without spending too much time, you should definitely use SSRS reports. In this blog I will show you how to create reports using Report Builder. I will use Report Builder 3.0 version. You can use SQL Server Business Intelligence Development Studio as well.
After you run the Report Builder, select Table or Matrix Wizard under New Report tab. I will create a dataset, but you can use an existing dataset. Click on next and select the database which you want to connect.
Click OK and go to Design a query window. I am using Nortwind database. Under database view expand tables and select tables which you want to show in your report. I selected Customers and Orders table. You can use your own query instead of using the tool.
Then click on next button and go to Arrange fields window. At this point you are starting to select the fields for your report. Drag and drop the fields that you want to show in your report.
Click on next and finish the wizard. You can click on Run button and see what your report looks like.
You can play with the report settings and use expressions to make your report more flexible. I will show how to add dependable and individual parameters to your reports in my next blog.
Tags: SSRS, Reports, SQL Server, Report Builder
Powered by Exsilio Solutions