SSRS parameter one label but multiple values

0
votes

I have a query that has classify codes: if 'PC' it returns 'Compatible, if Null then it returns Unclassified, if everything else returns 'Not-Compatible'. The alias column is "Classification". 

> SELECT   CASE WHEN class.code IS NULL THEN 'Unclassified'
> WHEN class.Code = 'PC' THEN 'Compatible' ELSE 'Not-Compatible'
> END Classification,  FROM classification class

Now I want to set this Classification as a parameter in my SSRS report. 

WHERE class.Code LIKE @Classification

Then in my @Classification parameterParameter Set up

So, in my report, user is prompted choose a classification, how do I get user to choose "Everything Else" that returns "Unclassified"? I can't use the alias column name "Classification" to get value. If there a way to set multiple values into the label "Unclassified"?

2
sqlsql-serverreporting-services
I don't quite understand but.... your parameter should filter on the result of the case statement which apparently can only be one of three values: Unclassified, Compatible, Not-Compatible. So that's all that should be listed in your Value column, plus the wildcard value (%) – Nick.McDermaid

2 Answers

1
votes

You can build this logic into your WHERE clause. First change the value of Non-Compatible to "NC". Then you could do something like this:

WHERE (@Classification != 'NC' and class.Code LIKE @Classification)
  OR (@Classification = 'NC' and class.Code != 'PC')
1
votes

Try modify your query to be: 

SELECT WhatEverColumnYouNeed  
FROM classification  
WHERE (CASE WHEN class.code IS NULL THEN 'Unclassified'  
            WHEN class.Code = 'PC' THEN 'Compatible'  
            ELSE 'Not-Compatible' END
      ) 
      IN (@Classification)

Then make you @Classification a multi-value parameter. Set available value to be Unclassified, Compatible, and Not-Compatible. Values same as Labels.