none
In 2008 SSRS how to have a multi-value parameter to default to 'Select All'

    Question

  • I have a SSRS 2008 report that has a paramater (Sales Rep) that allows multiiple values to be selected.  I want to have a default value for the parameter of 'Select All'.  how can I do that?

    thanks,

    clayp


    tcpowell

    Friday, March 30, 2012 4:17 PM

Answers

  • I found the answer. I just needed to have the parameter default values set to 'get values from a query' and use the same dataset as the 'Available Values'.

    Then in the query for the dataset for the parameter I needed to have the following where clause:

    where (column_name IS NOT NULL)

    and it works.  It selects all the values in the list as default when I preview the report.

    Thanks for all your help! 


    tcpowell

    • Marked as answer by tcpowell Monday, April 02, 2012 3:08 PM
    Monday, April 02, 2012 3:08 PM

All replies

  • Easy. 

    One way I know is that you can create a dataset that the condition is all from the table you want, then on default tab, choose the dataset to match the same value on the Available tab, then, it's done. 

    Of course, if your original dataset is good, you can use the same one. 
    • Edited by Chjquest Friday, March 30, 2012 4:27 PM
    Friday, March 30, 2012 4:24 PM
  • I created another dataset as you requested (using a query to obtain the values from the same table) and then in the parameter properties default tab I selected get values froma query and chose the new dataset and the value.  When I previewed the report it still prompted me to select a value and when I open the list box the parameter's check boxes were still un-checked.

    Also, on the first parameter on the report I have a similar configuration but with customer_id.  In the default tab I specify a value (single customer_id) and when I preview the report it shows the single customer_id and it's check box has been checked.

    It just will not allow for it on this one parameter.


    tcpowell

    Friday, March 30, 2012 6:17 PM
  • try setting BOTH available values and default values of that parameter to the new dataset you created. good luck

    Think out of the box

    Friday, March 30, 2012 6:32 PM
  • Ya, i_h is right. In your case, you "must" set the Available Value first. It is the base to provide the choices to Default value. Without Available Value, default value has nothing to choose/select. 

    Friday, March 30, 2012 6:44 PM
  • Well this is really strange.  I have set the parameter default on two of the four parameters to a Specific Value and they work.  However, on the other two (same datatype etc,) the default value (Specific Value) does not work.  I also, tried using the Obtain vlaues from a query for the default and it does not work either.

    This is strange that it works on two parameters and not two others.  I have reviewed each parameter and the datasets and I don't see any difference.  So I cam going to set up another parameter and use the dataset that has a parameter associated with it that works and see if that might give me a clue as to what is going on.


    tcpowell

    Friday, March 30, 2012 7:25 PM
  • Check your value in the default value matched the value source in the Available value. This is a very frequent and common mistake. 


    Available Value is ID, 


    You need to put ID in the Default Value

    Friday, March 30, 2012 7:31 PM
  • I have done that and it still is not working.  I am about to go into a two hour conference call and will be out of pocket for the week-end.

    Thanks for your help.  I would like to continue this on Monday.

    Have a great week-end!


    tcpowell

    Friday, March 30, 2012 7:44 PM
  • Hi there,

    Here is a step-by-step on how to add the value ALL to your available values, and then use that as the default.  This doesn't have the issues inherent with forcing the default value to multiple values.

    http://dataqueen.unlimitedviz.com/2011/06/how-to-default-to-all-in-an-ssrs-multi-select-parameter/

    Cheers,

    Martina


    Martina White

    Friday, March 30, 2012 7:56 PM
  • Martina,

    thanks, I would however for this application need to have the default to all the selections in the selection list or to have it chose the "(Select All)" selection in the list.  Can you hlep me on this?

    thanks!!


    tcpowell

    Monday, April 02, 2012 2:26 PM
  • Can you provide more details on what is not working? Also, please try the suggestions from this blog post - http://shahfaisalmuhammed.blogspot.com/2012/01/ssrsparameter-default-values-not.html

    Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

    Monday, April 02, 2012 2:30 PM
  • I found the answer. I just needed to have the parameter default values set to 'get values from a query' and use the same dataset as the 'Available Values'.

    Then in the query for the dataset for the parameter I needed to have the following where clause:

    where (column_name IS NOT NULL)

    and it works.  It selects all the values in the list as default when I preview the report.

    Thanks for all your help! 


    tcpowell

    • Marked as answer by tcpowell Monday, April 02, 2012 3:08 PM
    Monday, April 02, 2012 3:08 PM
  • Just wanted to say thanks - I was having the same problem, and your solution resolved it. Appreciate this very much.
    Saturday, May 18, 2013 4:25 AM
  • You can use same dataset for both available value and default value.

    This will work. let's try...


    Paramesh G

    Tuesday, June 25, 2013 8:44 AM