ssrs drop down box parameter

In this step by step SSRS tutorial I will show you how to create a drop down box parameter in SSRS based on dataset (sql query) and how to filter main dataset using your drop down box parameter. For the purpose of this SSRS tutorial I will use SQL Server reporting services 2008 R2 but this should work with SSRS 2008 and 2012. The steps should also be similar in SSRS 2005.
I have an existing report with a sparkline graph which shows me my website visits for the last 14 weeks. I would like to filter it by country using a drop down box parameter in SSRS.
First I need to create a new dataset Country that will contain two fields ID and Country, and will extract data from country table. I need ID for my main query so I can filter the data using Foreign Key (ID) and not actual country name which would require additional join. Country Field will be used to display the value in the drop down box (ID will be hidden and used behind the scenes).
I add new data set Country which will be used in Country Parameter


Emil's Review: Katie please add a screenshot with Country data set here so it is easier to follow the tutorial.
Next I need to add new parameter country. Below I add new Parameter in Report Data pane.

In Report Parameter Properties in General section I typed Parameter Name, Prompt (which is what user will see) and Data Type and in my case the parameter will hold ID hence I chosen Integer as data type.


In Available Values tab in Report Parameter Properties I Select Get values from a query. This means that the parameter will appear as drop down box (Get values from a query) and I chose Country dataset which I created earlier to "populate" my drop down box parameter. My Value is ID so when I refer to this parameter I will get ID (not name) and Label Field is Country which is actual name of the country that users will see.


Click ok.
Now let's filter dataset that is used to provide data fro the trend chart.

To the TrendDayOfWeek dataset I add SQL filter WHERE using CountryID field and new parameter @Country.
So the new filter is WHERE CountryID = @Country. And click OK.

Below is the report Preview to see the result select value from drop down box .

Report Preview