adding union select and parameter to multiple joins 1

[lorca]

hello

I'm using sql reporting services, and i'd like a parameter for a distinct list of values (distinct d.TYPEDESC) for the user to select on the report.
I'd also like to add an 'ALL TYPES' at the top (which I've seen done as union select '-1' ?)
using my code below how would i go about adding this funcionality in ?
the values within column TYPEDESC are strings, I've tried using Report Paranmeters but got nowhere......................

many thanks
rich

SELECT     a.DURATION, a.TIMESTARTED, a.ENQUIRYID, a.LOCATION, 
                      a.SEQUENCENO, b.ENQUIRY_TYPES_ID, c.AUTHORITYID, 
                      d.TYPEDESC, d.ID
FROM         a INNER JOIN
                      b ON a.ENQUIRYID = b.ENQUIRY INNER JOIN
                      c ON a.LOCATION = c.LOCATIONID AND 
                      b.OWNINGLOCATION = c.LOCATIONID INNER JOIN
                      d ON b.ENQUIRY_TYPES_ID = d.ID
WHERE     (d.TYPEDESC = @TYPEDESC)

 

[xlbo]

You would add a new dataset to the report within the dataset manager

SELECT       distinct d.TYPEDESC as TypeDescription, d.TYPEDESC as SearchValue, 2 as ListOrder
FROM         a INNER JOIN
                      b ON a.ENQUIRYID = b.ENQUIRY INNER JOIN
                      c ON a.LOCATION = c.LOCATIONID AND 
                      b.OWNINGLOCATION = c.LOCATIONID INNER JOIN
                      d ON b.ENQUIRY_TYPES_ID = d.ID
WHERE     (d.TYPEDESC = @TYPEDESC)
union
Select 'All Types' as TypeDescription, '*' as SearchValue, 1 as ListOrder

You then create a report parameter and base it off a query - select he new dataset form the list presented and all should be good !!