none
SSRS: Need to set a default string "Select values" in the parameter selection list

    Question

  • Hi,

    I have report with a parameter. I have an option to choose multiple parameters.But I have to keep "select values" in the list control while opening the report. Or else is it to possible to keep (select ALL) option as default.?

    Please suggest.

    Now it shows like below.

    





    Friday, July 18, 2014 10:02 AM

Answers

  • Hi Julie,

    If I understand correctly, you want to display “select any value” text before selecting values in the multiple parameter drop-down list. In Reporting Services, by default, the textbox displays”<Select a Value>” text before selecting values in a single parameter drop-down list. While it displays nothing in a multiple parameter. This is by design.

    To work around this issue, we can specify the Available Values from the batchId field in the following dataset:
    select 'select any value' as batchId
    union all
    select distinct  CAST(batchId AS VARCHAR(1)) from  dbo.Table1 
    Then specify the 'select any value' as the Default Values for the multiple parameter. In this way, we should change the values in the drop-down list when we render the report.

    Hope this helps.

    Thanks,
    Katherine Xiong

    If you have any feedback on our support, please click here.


    Katherine Xiong
    TechNet Community Support

    Monday, July 21, 2014 8:30 AM
    Moderator
  • I'm using "Get values from a query " option. It didn't work for me.

    Then expand your query to get this fix value as well, like

    SELECT 'SelectAll' AS FieldName
    UNION ALL
    SELECT FieldName
    FROM <yourQuery>


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 18, 2014 11:43 AM

All replies

  • Hi,

    Yes you can use <Select ALL>.

    Follow below steps.

    1.Go to Parameter.

    2. In defaultvalues

    3. in Query write .

    Select * from Table

    where id = @id OR id ="SelectAll"

    Thanks

    Friday, July 18, 2014 10:09 AM
  • Use a dataset to populate the available values for the parameter and as default values; select the same dataset you have used as the parameter available values.

    Friday, July 18, 2014 10:13 AM
  • Hi,

    I'm using "Get values from a query " option. It didn't work for me. I need to show as text to the user that " select any value" something like that

    Friday, July 18, 2014 10:32 AM
  • Use a dataset to populate the available values for the parameter and as default values; select the same dataset you have used as the parameter available values.

    I have done this way only..But it is not showing up any text in the list control..it is blank there still..Where do I give a specific text like "select any value"
    Friday, July 18, 2014 10:34 AM
  • If you select from a query; it should by default show up as follows.

    Hope this is what you are looking for.

    Friday, July 18, 2014 10:46 AM
  • Sorry Avijith,

    This is what I did. I will get values display when I select them.. But I need a text "select any value " to be displayed before selecting any value.

    Friday, July 18, 2014 11:01 AM
  • Have you tried my solution.??

    or

    you can simply write "select any value" in available values.

    Thanks

    Friday, July 18, 2014 11:36 AM
  • I'm using "Get values from a query " option. It didn't work for me.

    Then expand your query to get this fix value as well, like

    SELECT 'SelectAll' AS FieldName
    UNION ALL
    SELECT FieldName
    FROM <yourQuery>


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 18, 2014 11:43 AM
  • I'm using "Get values from a query " option. It didn't work for me.

    Then expand your query to get this fix value as well, like

    SELECT 'SelectAll' AS FieldName
    UNION ALL
    SELECT FieldName
    FROM <yourQuery>

    Heyy I tried this but the text came as  another option like below.  I need a text "select any value " to be displayed before selecting any value.

    select 'select values' as batchId
    union all
    select distinct  CAST(batchId AS VARCHAR(1)) from   dbo.Table1 

    Friday, July 18, 2014 12:12 PM

  • you can simply write "select any value" in available values.


    Heyy, I cannot give like this..I have to take values from query
    Friday, July 18, 2014 12:13 PM
  • SSRS multi valued parameters are not designed to display any message (as a dropdown displays "<Select a Value>"). The only option is to set the default value on report start-up.

    You can try to contact Microsoft; in case there is something they can do.

    Saturday, July 19, 2014 9:20 PM
  • Hi,

    If YOu want to use Multi-Select option for parameter then "Select all" will be come by Default If you have more than one values in Parameter.
    You can add your Parameter value with combination of this "Select ALL".
    Then your Final Parameter will be ;

        <Select ALL>
        <Select any Value>
        <1>
        <2>

    Note : You can not customized in SSRS here to only show Three values if Multiselect is chosen.


    In case of any Queries please let me know.

    Thanks
    Monday, July 21, 2014 6:14 AM
  • Hi Julie,

    If I understand correctly, you want to display “select any value” text before selecting values in the multiple parameter drop-down list. In Reporting Services, by default, the textbox displays”<Select a Value>” text before selecting values in a single parameter drop-down list. While it displays nothing in a multiple parameter. This is by design.

    To work around this issue, we can specify the Available Values from the batchId field in the following dataset:
    select 'select any value' as batchId
    union all
    select distinct  CAST(batchId AS VARCHAR(1)) from  dbo.Table1 
    Then specify the 'select any value' as the Default Values for the multiple parameter. In this way, we should change the values in the drop-down list when we render the report.

    Hope this helps.

    Thanks,
    Katherine Xiong

    If you have any feedback on our support, please click here.


    Katherine Xiong
    TechNet Community Support

    Monday, July 21, 2014 8:30 AM
    Moderator
  • Hi Katherine,

    That is right..I was not knowing that "select a value" option is not available in the parameter on multiple values list. It won't be much useful as you have suggested to alter the query.. I got this suggestion from OLAF earlier.

    Thanks for the reply..

    Monday, July 21, 2014 10:00 AM