Search code examples
reporting-servicesssrs-2008-r2

How do I add an additional value to a SLECT DISTINCT

I have a parameter in SSRS that uses a query to retrieve a list of values.

SELECT DISTINCT LTRIM(RTRIM(user_def_fld_5)) AS user_def_fld_5 FROM imitmidx_sql AS IMITMIDX

What I need to do is add one additional option to the list this query returns. So everything in the results PLUS a text value of "Unclassified".

The main query in the tablix changes all NULL values in this column to "Unclassified". I get an error that a multi value parameter cannot also include NULLs so I need a way to either include the rows with NULL values in this column or add an additional item to the pick list. If there is a better way to accomplish this please advise. Thanks!!

Solution

  • Use UNION, eg

    SELECT DISTINCT LTRIM(RTRIM(user_def_fld_5)) AS user_def_fld_5 FROM imitmidx_sql AS IMITMIDX
UNION ALL 
SELECT 'Unclassified' as user_def_fld_5