Post reply

Report Parameter Dropdown from another Dataset

  • 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

  • November 18, 2008 at 5:31 am

    #899819

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

  • November 18, 2008 at 5:53 pm

    #900228

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

    thanks.

  • November 20, 2008 at 5:58 am

    #900988

    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.

  • November 20, 2008 at 8:10 am

    #901075

    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

  • November 20, 2008 at 9:14 am

    #901114

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

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

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