An expression of non-boolean type specified in a context where a condition is expected SSRS

Question

 SELECT
'PERSONFULLNAME' = LASTNM --+ ', '+ FIRSTNM +' ' + COALESCE(MIDDLEINITIALNM, '')
,PERSONNUM
,EMPLOYMENTSTATUS
,HOMELABORLEVELNAME5
,ISWFCTKEMPLICENCE
,ISWFCSSELICENCE
,ISWFCMGRLICENCE
,ISWFCSCHEDULERLIC
,USERACCOUNTNM
,USERACCOUNTSTATUS
,'HCM_Cost_Center_Manager_Name' = n2.NAME
,TIMEENTRYMETHODNM
FROM dbo.VP_PERSON p
LEFT OUTER JOIN PSHCMirror.dbo.PS_JPM_PROFILE jp
  ON SUBSTRING(jp.JPM_PROFILE_ID,7,6) = SUBSTRING(p.HOMELABORLEVELNAME5,1,6)
AND jp.JPM_JP_TYPE = 'BU_DEPT'
LEFT OUTER JOIN PSHCMirror.dbo.PS_JPM_JP_ITEMS jpi
  ON jpi.JPM_PROFILE_ID = jp.JPM_PROFILE_ID
AND jpi.JPM_CAT_TYPE = 'HRIS_CNTCT2'
LEFT OUTER JOIN 
(
SELECT n1.EMPLID, n1.NAME, 'row_nbr' = ROW_NUMBER() OVER (PARTITION BY n1.EMPLID ORDER BY n1.EFFDT DESC, n1.NAME_TYPE DESC)
FROM PSHCMirror.dbo.PS_NAMES n1
) n2
ON n2.EMPLID = jpi.JPM_PERSON_ID_1
AND n2.row_nbr = 1

WHERE USERACCOUNTSTATUS = 'Active'
AND EMPLOYMENTSTATUS = 'Active'
AND (
@Manager = 'All' OR n2.NAME IN (@Manager)
)
order by 1

I have a SSRS report that trying to pass the list of names to the parameter and default set to 'all'.

Set the parameters' "default value" to use the same query as the "available values" won't work because of long list of names. (This will work if I have a small list, it will automatically checked "Select All")

I know the error come from @Manager = 'All' OR n2.NAME IN (@Manager), but I don't know any alternative ways to make it work.

Answer 1

Since @Manager is a multi-value parameter you need to use IN in conditions instead of =. Try updating that section of your WHERE clause to:

WHERE ('All' IN (@Manager) OR n2.NAME IN (@Manager))