cascading parameter dropdowns

[mjd3000]

Is it possible to make parameter dropdowns dependent upon each other in Report Builder? I am using SSRS 2005 and Report Builder 1.

 

[AndyH1]

Not sure if this is what you are asking, Wev'e done what I think you want in SQL Report Builder 2, In this the items in the second dropdown depend on what is selected in the first drop down (Cascading menu) is this what you mean?

Assume you have 2 dropdown list parameters Main and SubMain where the list of items in subMain depends on what's selected in Main

First you need to create a stored procedure which given a value will return the list of items you want in SubMain (such as a simple SELECT x from table where ID=@ID)

You then create a SubMain dataset.

Choose dataset properties for this. In the Query type for this you choose 'stored procedure' and enter the name of your stored procedure. In the parameters field you put the parameter name (such as @ID) and parameter value you put [@Main] (the name of the Main parameter from which you want to get the selected value).

Add a field in the dataset with Fieldname subDropDown and field source SubDropDown

Now Go to the parameter SubMain and Choose parameter properties then Available Values. in available values choose get values from a query and select the SubMain dataset. In value field and label field select SubDropDown.

If you have all this correct when you choose a value in Main the screen will refresh and the SubMain dropdown will fill with the appropriate values.

Bit hard to explain but I hope this helps, if this it what you were asking

AndyH1