Report Parameter Dropdown from another Dataset

Question

Report Parameter Dropdown from another Dataset

mvee

Hall of Fame

Points: 3634
More actions
November 17, 2008 at 3:37 pm

#195961

Goal: create a parameter dropdown from distinct values from another dataset
I have a pretty hefty dataset from a stored proc that takes in about 10 parameters to filter the main report data. The above parameters are passed in via a web form.
There are a few other parameters that are defaulted to show all, but are displayed in the final report.
What I want is to have these dropdown parameters show distinct values from the original filtered dataset. I don't want to have to repass in all of the parameters and rerun the code to get the final result and then grab the distinct states or distinct importdates or distinct types, etc. It just takes too long.
Is there any way to build a distinct set of values from a dataset via SSRS? Even if I have to used custom code - anything that might be fast. But I'm not a custom code writer, can anybody provide a shell to start with?
Any help is greatly appreciated.
FYI - we are using ssrs 2000 - soon to be moving to 2005.
Thank you.
-M

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

abhijeetv SSCommitted Points: 1945 More actions · Answer 1

The easiest way you can opt for is to create separate dataset/s (based on your main dataset) for the parameter dropdown list/s.

mvee Hall of Fame Points: 3634 More actions · Answer 2

but how? how can i select from the first dataset?

thanks.

abhijeetv SSCommitted Points: 1945 More actions · Answer 3

Create separate SQLs for these parameter datasets (e.g. The SQL for a parameter will have distinct values for a particular column to be used as this parameter and the same WHERE clause as your main dataset for the report.) One cannot filter a dataset to make a new dataset because the new dataset will be built prior to the later main dataset.So it has to have a separate SQL.

No idea of how to use the Custom Code for this.

Jerry Hung SSChampion Points: 12968 More actions · Answer 4

For those situations, you almost need 1 dataset per 1 dropdown parameter (from query)

You could do cascading dropdown though

e.g.

1st dropdown is Country = select name from country

2nd one is State = select name from state where country = @country (which will use the 1st dropdown's selected value)

etc...

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

mvee Hall of Fame Points: 3634 More actions · Answer 5

Thanks guys. This is exactly what I thought and what I was trying to avoid. o well. guess it's just not possible.