ssrs drop down box list parameter filter using table

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

I hope that will help you with created your own SSRS drop down box parameters.

Take care

Katie

Name: Kumar velayutham
Comment:

Nice one to learn from this post..Great Job..........

Date Posted: 26/07/2012 4:48:38 PM

Name: kalyan
Comment:

Excellent Job...It helped me alot

Date Posted: 17/08/2012 3:54:27 PM

Name: kalyan
Comment:

Hey this was really cool but i am getting this ERROR......

The Report parameter 'ClientID' has a DefaultValue or ValidValue that depends on the report parameter "ClientID". Forward dependencies are not valid.

Can you please help me....Thanks!

Date Posted: 17/08/2012 4:14:26 PM

Name: Emil
Comment:

Hi Kalyan

You might be able to solve you problem by changing order of your parameters. Bear in mind that top parameter is executed first, so if you have parameter that you use to populate another parameter (default value or valid values) then the main parameter should be "higher" in parameter list. You can click parameter and use up/down arrows.

Date Posted: 20/08/2012 6:39:17 PM

Name: raghu
Comment:

hai... i am following your posts..recently i find a problem in ssrs dropdownbox parameters...that is " The report parameter ‘userid’ has a DefaultValue or a ValidValue that depends on the report parameter “userid”. Forward dependencies are not valid."

i am using only one parameter i.e "@userid"..

can u please help me..thank u

Date Posted: 19/10/2012 5:26:21 AM

Name: Emil
Comment:

Hi Raghu,

My guess is that you use your only parameter as Default Value. So you try to set a value based on itself which is not possible. You may want to use =User.UserID instead of =Parameters!UserID.Value

If you use User.UserID it most likely will return domain as well which often needs to be removed using some functions (detect slash and remove anything before it).

Hope that helps.

Date Posted: 19/10/2012 6:48:56 AM

Name: ely&mary
Comment:

thank you

we have presentation & we need your data base , can you give us please?

Date Posted: 10/05/2013 1:02:29 PM

Name: sagar patre
Comment:

Hey emil this is sagar

I am trying to send parameters to stored procedure using dropdown but it gives error like

the report parameter accno has a defautvalue or a validvalue that depends onthe report parameter accno forward depenndencies are not valid

the stored procedure i have used is

ALTER

procedure [dbo].[sp_bankdb]

(

@accno

int

)

begin

select

* from BankDetails where accno = @accno

Order

BY accno

end

whenever i select "accno " in dropdown it should return me those records which are having that accno Plz help me out i m stuck

Date Posted: 30/05/2013 10:07:23 AM

ssrs drop down box parameter