CODEWITHSUNDEEP
sqlsql-serverreporting-services
Ming Huang
Ming Huang

Reputation: 1370

SSRS parameter one label but multiple values

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"?

Upvotes: 0

Views: 1557

Answers (2)

William Xu
William Xu

Reputation: 261

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.

Upvotes: 1

StevenWhite
StevenWhite

Reputation: 6024

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')

Upvotes: 1

Related Questions