Last post Apr 28, 2011 09:08 AM by ravindra.patil
Apr 21, 2011 03:56 PM|MSBoston|LINK
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?
Apr 22, 2011 09:44 AM|V.Karthikeyan|LINK
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
Apr 28, 2011 09:08 AM|ravindra.patil|LINK
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.