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.

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.

So lately, I've been tasked with working with several databases where the number of tables and views within the databases number into the low thousands.  As you can imagine when working with any kind of long list, the amount of time spent scrolling and searching for the objects you are interested in proves to be a pain point after a while.  In SQL Server Management Studio (SSMS), you do have the ability to set filters, but they aren't persistent and they are limited in value.  For my purposes, the filters helped, but not nearly as much as I would have liked.  So, my first thought was that I wished there was a way that I could create a persistent "folder" structure.  After a quick search of the web, I wasn't able to find anything out there that was what I was looking for, but I did find code samples on creating SSMS add-ins.  I was pretty much resigned to trying to write an add-in myself to do what I wanted to do in SSMS.

After discussing my thoughts about this with a co-worker, he made me aware of a free add-in (SQL Treeo) created by Jakob Dvorak that does a lot of what I was looking for. It allows me to create persistent "virtual" folders as a hierarchy to organize the database objects into a far, far more manageable interface.  As noted in his forum, the add-in is an alpha version and still a work in progress.  I've used it now for a month and give it positive feedback.  There are some bugs and there are a couple modifications that would be nice to have, but all up, it is a solid piece of development and well worth a try (Note: currently it is only available for SQL Server 2008 R2 client).  If you're working with hundreds of tables within a database, it may just make you more productive (as it has for me) and reduce your frustration with constantly having to scroll up and down to find a table or view.