SSRS Multivalue parameter Condition Expected near ',' + SQL Statement

Question

Have a pretty long query that I'm needing to build an SSRS report around. The parameters in the SSRS report will need to allow for multi-value selection based of another datasets query. What I'm running in to is when using multi-select option on the parameter in SSRS I get the following error

An expression of non-Boolean type specified in a content where a condition is expected, near ','.

I'm sure that I don't have my case statement written correctly to account for multi-values parameter for the @Status in bold below. Can anyone assist me in determining how to write this to allow the parameter to use multi-values?

where

im_incident.im_id = 
    CASE 
        When @Incident_Number IS NULL
        THEN im_incident.im_id
        ELSE @Incident_Number
    END 
**AND lc_status.lc_title IN (   
    CASE 
        When @Status IS NULL
        Then lc_status.lc_title
        Else @Status
    END)**
AND (
        dateadd(mi, datediff(mi, getutcdate(),getdate()),pm_creation_date) BETWEEN IsNull(@Creation_Start_Date,'9/15/2016 00:00:00') 
        AND IsNull(@Creation_End_Date,GetDate())
    )
AND (
        dateadd(mi, datediff(mi, getutcdate(),getdate()),im_incident_resolution.im_creation_date) BETWEEN IsNull(@Resolved_Start_Date,'9/15/2016 00:00:00') 
        AND IsNull(@Resolved_End_Date,GetDate())
    )

Are you using a stored procedure or is this embedded in SSRS? — SS_DBA

Hogan Hogan · Accepted Answer · 2016-10-28T18:27:16

You have this:

AND lc_status.lc_title IN (   
CASE 
    When @Status IS NULL
    Then lc_status.lc_title
    Else @Status
END)

The following would be correct:

-- Single value
AND lc_status.lc_title =   
CASE 
    When @Status IS NULL
    Then lc_status.lc_title
    Else @Status
END

or

-- multi value
AND lc_status.lc_title IN ('title1','title2','title3')

Personally I would write it like this:

-- Single value
AND lc_status.lc_title = COALESCE(@Status,lc_status.lc_title)

SSRS Multivalue parameter Condition Expected near ',' + SQL Statement

1 Answers