Automatically Selecting Values in Multiple Values Dropdown Parameters RRS feed

  • Question

  • Hi,

    Is it possible to have parameter values automatically selected in a dropdown in an SSRS report?  I'm using SQL Server 2008 R2 Enterprise edition.  Basically what I have in mind is this:  The user wants to save his parameter selections in a table that will be available as a dropdown parameter of a report, call it ParamUserSelection.  ParamUserSelection is the first parameter of the report and other parameters depend on it.

    We have two other parameters called ParamCountry and ParamState.  These are dropdown text parameters that allow multiple values to be selected.  Populating the list is not a problem as I use a standard cascading parameter technique.  What I'm trying to do is the following:

    The user selects a value from ParamUserSelection, call it Selection1.  Selection1 is stored in a table and has all the values that should be selected in ParamCountry and ParamState.  In other words, it should tell these two subsequent parameters, what values should be selected in their respective dropdowns.  For example if the user selects Selection1 from ParamUserSelection, it reads the values in the database table and if it finds US and Canada for countries and CA, NY, ON for states, ParamCountry should be populated with all the countries available in the database but should have ONLY US and Canada as selected values and ParamState should be populated with all the states in the database but should have ONLY CA, NY and ON as selected values.

    If then I click on selection 2 and it has ParamCountry = US, ParamState = TX,OH in its database then again all countries and states should be in the dropdown but only US for ParamCountry and TX,OH for ParamState should be selected.

    Is this possible with SSRS?  I've tried using the Default Values tab with a dataset that returns all the selected values under Report Parameters Properties but this only works for the first time that I select ParamUserSelection.  It appears that if I change the value of ParamUserSelection again, the default values are not invoked.

    If this is possible, please tell me how to do this as I have been struggling for a day with it.

    Friday, May 2, 2014 9:37 AM

All replies

  • Anybody?
    Friday, May 2, 2014 8:26 PM
  • Hi Comedian,

    According to your description, you have a report with three parameters (ParamUserSelection, ParamCountry, ParamState). The available value lists of ParamCountry and ParamState are based on the selection of a value for ParamUserSelection. Now you want to show all countries and states in their parameter dropdown list when selecting a user selection instead of only showing the cascading values. Right?

    In Reporting Service, when we want a parameter to show the cascading values, we only need to set the corresponding dataset and field for Default Values in this parameter. In this scenario, if we want to show all countries and states in their dropdown list, we just need to set another dataset for Available Values in those parameters so they can display all countries and states. We have tested your case in our local environment. Since you have done with the cascading parameters, we just give some part of steps and screenshots for your reference:

    1. We created two tables (dbo.Selection, dbo.states) based on your information.
    2. Create one more dataset (named dataset2) in your report, put text below into your query:
      select distinct Country from states
    3. Create another dataset (named dataset3), put text below into your query:
      select distinct State from states
    4. Go to your ParamCountry, in Available Values, select dataset2 and Country for dataset and field.
    5. Go to your ParamState, in Available Values, select dataset3 and State for dataset and field.
    6. Save and preview. It looks like below:

    Report Parameters (Report Builder and SSRS)

    If you have any question, please feel free to ask.

    Best Regards,
    Simon Hou

    Tuesday, May 6, 2014 8:27 AM
  • Hi Simon,

    Thanks for your response.  Unfortunately the default values are only set the first time that the parameters are loaded.  I have done further research on this matter and have really beaten the dead horse but it appears it's like this by design and the hacks/solutions that I've found don't work 100 %.

    The thing is that if you start with selecting Selection2:

    1.  Select:  Selection2, ParamCountry List:  Canada, US   ParamCountry Selected Values:  US - Everything OK

    2.  Select:  Selection1, ParamCountry List:  Canada, US   ParamCountry Selected Values:  Canada, US  - Everything still OK

    3.  Select:  Selection2, ParamCountry List:  Canada, US   ParamCountry Selected Values:  Canada, US  - this is a problem because the selected value should only be US (like it was the first time that you selected Selection2).  The problem is that in SSRS you have no way of controlling which values are selected (with a checkmark) and that in cascading parameters the change in selection of one parameter doesn't always update the selected values of the dependent parameters.  It's like this by design but it would have been nice if Microsoft made this the default setting but allowed us to have a bit more control of which values are selected.

    Tuesday, May 6, 2014 9:46 AM