locked
Trying to add "Select All" to a parameter drop down list RRS feed

  • Question

  • User-777992925 posted

    So I have found a way of creating new datasets to pull distinct values for a few drop-down filter parameters I am using in a report.  I then added an "All" selection to one of the drop-downs by using a UNION SELECT like this...

    (Parameter is a VARCHAR)

    SELECT DISTINCT PersonName, CONVERT(VARCHAR,NameTable_ID) AS NameTable_ID FROM NameTable UNION SELECT 'All','%' ORDER BY PersonName ASC

    Which works like a charm for the one filter.  The other filter I am trying to work with in a Person Grade filter which shows the grade a person is classified as an integer between 0-16.  For this query I used...

    (Parameter is an VARCHAR)

    SELECT DISTINCT PersonGrade FROM GradeTable UNION SELECT '%' ORDER BY PersonGrade ASC

    Basically I can get the 2nd query to work but within the drop-down it shows "%" for my "Select All" choice.  I'm just trying to figure out how to do the second filter detailed in the 2nd query where the "%" gets replaced with "Select All" in the drop-down but still represents the "%" (all) choice within SQL.

    Is there any way to manually add pieces to the drop-down and rename them for the display but have them represent a different value for filtering purposes?

    Thanks.

    Thursday, April 21, 2011 3:56 PM

Answers

  • User761909039 posted

    I assume you are binding the "PersonName" column to DataTextField and DataValueField properties of the Dropdown. If that is the case, you can modify your query like you did for the first filter..

    SELECT DISTINCT PersonGrade, PersonGrade  FROM GradeTable UNION SELECT 'Select All', '%' ORDER BY PersonGrade ASC
    

    And have first column binded to DataTextField and second column binded to DataValueField in the dropdown

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 22, 2011 9:44 AM

All replies

  • User761909039 posted

    I assume you are binding the "PersonName" column to DataTextField and DataValueField properties of the Dropdown. If that is the case, you can modify your query like you did for the first filter..

    SELECT DISTINCT PersonGrade, PersonGrade  FROM GradeTable UNION SELECT 'Select All', '%' ORDER BY PersonGrade ASC
    

    And have first column binded to DataTextField and second column binded to DataValueField in the dropdown

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 22, 2011 9:44 AM
  • User1426937062 posted

    No need to add the "Select All" value manually in the paramter's value list. If you make the parameter as the "Allow Multiple Values" it will automatically add (Select All) value in the paramters value list.

    Thursday, April 28, 2011 9:08 AM