CODEWITHSUNDEEP
sql-serverreporting-services
user1804925
user1804925

Reputation: 159

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

 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.

Upvotes: 3

Views: 9449

Answers (3)

Peter K
Peter K

Reputation: 61

I was getting the same error with SSIS with "Execute SQL Task" component. In my case I didn't realize that I exceeded approx. 32k of SQL script characters. In such a case use "Browse" button to load your SQL code or use "Script Component" instead.

Upvotes: 0

StevenWhite
StevenWhite

Reputation: 6024

If you want to pass the parameter into the query you have to use a Join function to convert the collection of values into a string. Then you can use a custom Split function or a Like operator to check the values in your WHERE clause.

Another option is to add a Dataset filter. For the Expression you would select the NAME column. Use "In" as the Operator". For the Value use an expression like this:

=Split(Join(Parameters!Manager.Value, ","), ",")

This converts the collection of selected names into an array that it can check.

Upvotes: 0

stubaker
stubaker

Reputation: 1958

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

Upvotes: 7

Related Questions