Report Builder can't covert text to int error

Question

HERE IS THE QUERY

--DECLARE @APSDONRFOR int`

--SET @APSDONRFOR = 2 IF(@APSDONRFOR = 1) BEGIN SELECT INVESTIGATION.ID ,IR.ASSESSID ,REVIEWDATE ,INVESTIGATION.STARTDATE ,INVESTIGATION.ENDDATE ,INVESTIGATION.AllegedVictimName ,INVESTIGATION.WorkerName ,INVESTIGATION.SupervisorName ,INVESTIGATION.AAA_District ,INVESTIGATION.REGION ,DONR.CATEGORY ,DONR.DONRFUNCTION ,DONR.COMMENT ,DONR.LOI ,DONR.UMN -- ,ADLImpairmentTotal.ADLIMPTOTAL -- ,ADLUnMetNeedTotal.ADLUMNTOTAL -- ,IADLImpairmentTotal.IADLIMPTOTAL -- ,IADLUnMetNeedTotal.IADLUMNTOTAL -- ,TotalLOI.TOTALLOI -- ,TotalUnmetNeed.TOTALUMN -- ,DONRTotal.DONRTOTAL ,DONRFOR = 'INVESTIGATION' ,IR.SCREENDESIGNID FROM IncidentReview IR
Left join(SELECT CATEGORY,DONRFUNCTION,COMMENT,LOI,UMN, ASSESSID`

            FROM (Select CATEGORY ='ADL', DONRFUNCTION ='Eating', IR.ASSESSID, Comment, LOI, UMN`

FROM IncidentReview IR LEFT JOIN (SELECT ASSESSID, SCALE,COMMENT = CASE WHEN CONVERT(VARCHAR(255),ITEM) = '' THEN 'NULL' ELSE CONVERT(VARCHAR(255),ITEM) END FROM IncidentDetReview IDR Where IDR.SCALEID=30085729 )Comment on IR.ASSESSID = Comment.ASSESSID Left Join (SELECT ASSESSID, SCALE, LOI= CASE WHEN CONVERT(VARCHAR,ITEM) = '' THEN NULL ELSE CONVERT(INT,LEFT (CONVERT(VARCHAR,ITEM),1)) END /*,IRECENTDR = Row_Number() OVER(PARTITION BY IR.INCIDENTID, scaleid ORDER BY REVIEWDATE DESC, IR.ASSESSID DESC)*/ FROM IncidentDetReview IDR Where IDR.SCALEID=30085779 )LOI on IR.ASSESSID = LOI.ASSESSID Left Join (SELECT ASSESSID, SCALE, UMN=CASE WHEN CONVERT(VARCHAR,ITEM) = '' THEN NULL ELSE CONVERT(INT,LEFT (CONVERT(VARCHAR,ITEM),1)) END FROM IncidentDetReview IDR Where IDR.SCALEID=30085780 )UMN on IR.ASSESSID = UMN.ASSESSID `

        UNION ALL`

`

            Select CATEGORY ='ADL', DONRFUNCTION ='Bathing', IR.ASSESSID, Comment, LOI, UMN`

FROM IncidentReview IR Left Join (SELECT ASSESSID, SCALE, COMMENT = CASE WHEN CONVERT(VARCHAR(255),ITEM) = '' THEN 'NULL' ELSE CONVERT(VARCHAR(255),ITEM) END /*Comment=Convert(varchar(255), ITEM)*/ FROM IncidentDetReview IDR Where IDR.SCALEID=30085733)Comment on IR.ASSESSID = Comment.ASSESSID Left Join (SELECT ASSESSID, SCALE, LOI=CASE WHEN CONVERT(VARCHAR,ITEM) = '' THEN NULL ELSE CONVERT(INT,LEFT (CONVERT(VARCHAR,ITEM),1)) END FROM IncidentDetReview IDR Where IDR.SCALEID=30085781)LOI on IR.ASSESSID = LOI.ASSESSID Left Join (SELECT ASSESSID, SCALE, UMN=CASE WHEN CONVERT(VARCHAR,ITEM) = '' THEN NULL ELSE CONVERT(INT,LEFT (CONVERT(VARCHAR,ITEM),1)) END FROM IncidentDetReview IDR Where IDR.SCALEID=30085782)UMN on IR.ASSESSID = UMN.ASSESSID `

     )DONR1`

)DONR on IR.ASSESSID = DONR.ASSESSID `

LEFT JOIN (SELECT ASSESSID,SCALE,ADLIMPTOTAL = CASE WHEN CONVERT(VARCHAR,ITEM)= '' THEN NULL ELSE CONVERT(INT,ITEM) END FROM INCIDENTDETREVIEW IDR WHERE IDR.SCALEID = 30085743 )ADLImpairmentTotal ON IR.ASSESSID = ADLImpairmentTotal.ASSESSID

LEFT JOIN ( SELECT ID = INCIDENT.INCIDENTID ,STARTDATE = CAST(INCIDENT.STARTDATE AS DATE) ,ENDDATE = CAST(INCIDENT.ENDDATE AS DATE) ,INCIDENTReportDate = CAST(INCIDENT.REPORTDATE AS DATE) ,REGION = CASE WHEN (INCIDENT.INCIDENTQUEUE LIKE 'A%' OR INCIDENT.REGION LIKE 'A%') THEN 'ATLANTA REGION' WHEN (INCIDENT.INCIDENTQUEUE LIKE 'CEN%' OR

INCIDENT.REGION LIKE 'CEN%' OR INCIDENT.INCIDENTQUEUE LIKE 'CSRA' OR INCIDENT.REGION LIKE 'CSRA%') THEN 'CENTRAL SAVANNAH RIVER REGION' WHEN (INCIDENT.INCIDENTQUEUE LIKE 'COASTAL%' OR INCIDENT.REGION LIKE 'COASTAL%') THEN 'COASTAL GEORGIA REGION' WHEN (INCIDENT.INCIDENTQUEUE LIKE 'G%' OR .REGION LIKE 'G%') THEN 'GEORGIA MOUNTAIN REGION' WHEN (INCIDENT.INCIDENTQUEUE LIKE 'HEART%' OR INCIDENT.REGION LIKE 'HEART%') THEN 'HEART OF GEORGIA REGION'enter code here WHEN (INCIDENT.INCIDENTQUEUE LIKE 'MIDDLE%' OR .REGION LIKE 'MIDDLE%') THEN 'MIDDLE GEORGIA REGION' `

                                                    WHEN (INCIDENT.INCIDENTQUEUE LIKE 'NORTHEAST%' OR `

INCIDENT.REGION LIKE 'NORTHEAST%' OR INCIDENT.INCIDENTQUEUE LIKE 'NE%' OR INCIDENT.REGION LIKE 'NE%') THEN 'NORTHEAST GEORGIA REGION' WHEN (INCIDENT.INCIDENTQUEUE LIKE 'NORTHWEST%' OR INCIDENT.REGION LIKE 'NORTHWEST%' OR INCIDENT.INCIDENTQUEUE LIKE 'NW%' OR INCIDENT.REGION LIKE 'NW%') THEN 'NORTHWEST GEORGIA REGION' WHEN (INCIDENT.INCIDENTQUEUE LIKE 'River%' OR INCIDENT.REGION LIKE 'River%') THEN 'RIVER VALLEY GEORGIA REGION' WHEN (INCIDENT.INCIDENTQUEUE LIKE 'SOUTHERN%' OR INCIDENT.REGION LIKE 'SOUTHERN%') THEN 'SOUTHERN GEORGIA REGION' WHEN (INCIDENT.INCIDENTQUEUE LIKE 'SE%' OR INCIDENT.REGION LIKE 'SE%' OR INCIDENT.INCIDENTQUEUE LIKE 'SOUTHEAST%' OR INCIDENT.REGION LIKE enter code here'SOUTHEAST%') THEN 'SOUTHEAST GEORGIA REGION' WHEN (INCIDENT.INCIDENTQUEUE LIKE 'SOUTHWEST%' OR INCIDENT.REGION LIKE 'SOUTHWEST%' OR INCIDENT.INCIDENTQUEUE LIKE 'SW%' OR INCIDENT.REGION LIKE 'SW%') THEN 'SOUTHWEST GEORGIA REGION' WHEN (INCIDENT.INCIDENTQUEUE LIKE 'THREE%' OR INCIDENT.REGION LIKE 'THREE%') THEN 'THREE RIVERS REGION' ELSE 'REGION UNAVAILABLE' END ,AAA = Incident.IncidentQueue ,AAA_Clean = CASE WHEN (Incident.Region = '' OR Incident.Region = NULL) AND (Incident.IncidentQueue = '' OR Incident.IncidentQueue = NULL ) THEN 'Missing' WHEN (Incident.IncidentQueue = '' OR Incident.IncidentQueue = NULL )Then Incident.Region /* AND (Incident.Region <> NULL OR Incident.Region <> '' )*/ ELSE Incident.IncidentQueue

END ,TripleA = COALESCE(Incident.IncidentQueue,Incident.Region) `

                         ,HisParticipant.participantid`

,HisParticipant.entityid ,Hisparticipant.entityname ,HisParticipant.contacttype ,HPNParticipantID = HisParticipantName.participantid ,AllegedVictimName = HisParticipantName.FirstName + ' ' + HisParticipantName.LastName ,Workers.APSWorkerType ,AAA_District = Case When (Contact.District IS NULL OR Contact.District = '') Then 'MISSING' ELSE Contact.District END ,County = HisParticipantAddress.County ,Contact.contactid ,WorkerName = Contact.FirstName + ' ' + Contact.LastName ,SupervisorName = CASE WHEN (CONVERT (VARCHAR,Supercontact.FirstName) IS NULL OR CONVERT(VARCHAR,Supercontact.FirstName) = '') AND (CONVERT (VARCHAR,Supercontact.LastName) = '' OR CONVERT (VARCHAR,Supercontact.LastName) IS NULL) THEN 'MISSING' ELSE CONVERT(VARCHAR,Supercontact.FirstName) + ' ' + CONVERT(VARCHAR,Supercontact.LastName) END `

          From INCIDENT `

Left Join HISParticipant ON Incident.IncidentID = HISParticipant.EntityID AND HISParticipant.EntityName = 'Incident' AND HISParticipant.ContactType = 'AllegedVictim' Left Join HISParticipantName ON HisParticipant.Participantid = HisParticipantName.participantID AND HISParticipantName.Active = 'True' AND HisParticipant.PrimaryYN= 'True' AND Display = 'True' Left Join HISParticipantAddress ON HisParticipant.ParticipantID = HisParticipantAddress.ParticipantID AND HISParticipantAddress.PrimaryYN = 'True' AND HISParticipantAddress.Active = 'True' AND HISParticipantAddress.Display = 'True' Left Join Workers ` ON Incident.MemberID = WORKERS.MEMBERID AND Workers.Unit = 'APS' /*And workers.Active =

'True'*/ Left Join (SELECT * FROM CONTACT WHERE DISTRICT LIKE 'DISTRICT%')Contact ON Workers.ContactID = Contact.ContactID Left Join SUPERVISORS ON Workers.MEMBERID = Supervisors.MEMBERID AND supervisors.PRIMARYSUPER = 'TRUE' AND (SUPERVISORS.ENDDATE IS NULL OR SUPERVISORS.ENDDATE = '')AND SUPERVISORS.ACTIVE = 'TRUE' LEFT JOIN WORKERS SUPERWORK ON Supervisors.SUPERVISOR = SUPERWORK.MEMBERID LEFT JOIN CONTACT SUPERCONTACT ON SUPERWORK.CONTACTID = SUPERCONTACT.CONTACTID AND SUPERWORK.Active = 'TRUE' `

          --Where Incident.FundCode = 'APS'`

--Where IR.SCREENDESIGNID = 2105 and Incident.FundCode = 'APS' --AND IR.REVIEWDATE BETWEEN (:Startdate) AND (:EndDate) ) INVESTIGATION ON IR.INCIDENTID = INVESTIGATION.ID WHERE IR.SCREENDESIGNID = 2105 AND INVESTIGATION.STARTDATE BETWEEN '1/1/2015' AND '1/31/2020' --AND -- IR.REVIEWDATE BETWEEN @STARTDATE AND @ENDDATE --AND -- INVESTIGATION.REGION IN (@REGION) --AND -- INVESTIGATION.AAA_District IN (@DISTRICT) --AND -- INVESTIGATION.SupervisorName IN (@SUPERVISOR) --AND -- INVESTIGATION.WorkerName IN (@WorkeR) `

END

IF (@APSDONRFOR = 2)

BEGIN

SELECT RECENTREVIEWEDCASE.CASENO ,RECENTREVIEWEDCASE.ASSESSID ,RECENTREVIEWEDCASE.REVIEWDATE ,STARTDATE ,ENDDATE ,AllegedVictimName ,WorkerName ,SupervisorName ,AAA_District ,REGION ,CATEGORY ,DONRFUNCTION ,COMMENT ,LOI ,UMN -- ,ADLIMPTOTAL -- ,ADLUMNTOTAL -- ,IADLIMPTOTAL -- ,IADLUMNTOTAL -- ,TOTALLOI -- ,TOTALUMN -- ,DONRTOTAL ,DONRFOR = 'CASE' ,SCREENDESIGNID FROM (SELECT * FROM ( SELECT DISTINCT AR.CASENO,AR.ASSESSID ,AR.SCREENDESIGNID,REVIEWDATE = CAST(AR.REVIEWDATE AS DATE) ,RECENTREVIEW = ROW_NUMBER () OVER (PARTITION BY CASENO ORDER BY REVIEWDATE DESC,ASSESSID DESC) FROM ASSESSMENTREVIEW AR WHERE AR.FUNDCODE = 'APS' AND AR.STATUS = 'COMPLETE' )RECENTASSESSMENT WHERE RECENTREVIEW = 1 )RECENTREVIEWEDCASE --AND CASENO = 273274

INNER JOIN (SELECT DISTINCT ID = O.CASENO ,O.FUNDCODE ,STARTDATE = CAST(O.OPENDATE AS DATE) ,ENDDATE = CAST(O.CLOSEDATE AS DATE) ,O.DISPOSITION ,'AllegedVictimName' = C.FIRSTNAME + ' ' + C.LASTNAME ,'WorkerName' = WC.FIRSTNAME + ' ' + WC.LASTNAME ,'SupervisorName' = SC.FIRSTNAME + ' ' + SC.LASTNAME ,AAA_District = CASE WHEN (SC.DISTRICT IS NULL OR SC.DISTRICT = '') THEN 'MISSING' ELSE SC.DISTRICT END ,REGION = R.REGION FROM OPENCLOSE O LEFT JOIN DEMOGRAPHICS D ON O.CASENO = D.CASENO
LEFT JOIN CONTACT C ON D.CONTACTID = C.CONTACTID LEFT JOIN WORKERS W ON O.PRIMARYWORKERID = W.MEMBERID LEFT JOIN CONTACT WC ON W.CONTACTID = WC.CONTACTID LEFT JOIN ( SELECT SUPERVISOR, MEMBERID FROM SUPERVISORS WHERE PRIMARYSUPER = 'TRUE' AND (SUPERVISORS.ENDDATE IS NULL OR SUPERVISORS.ENDDATE = '')AND SUPERVISORS.ACTIVE = 'TRUE' )S ON W.MEMBERID = S.MEMBERID LEFT JOIN WORKERS SW ON S.SUPERVISOR = SW.MEMBERID LEFT JOIN CONTACT SC ON SW.CONTACTID = SC.CONTACTID LEFT JOIN (Select DISTINCT HP.Region, HP.District, HP.County, HP.StateAbrev From HISPlace HP Where HP.StateAbrev='GA' and HP.Active='True' )R ON C.RESCOUNTY = R.County

        WHERE O.FUNDCODE = 'APS'`

) ONGCASE ON RECENTREVIEWEDCASE.CASENO = ONGCASE.ID AND (RECENTREVIEWEDCASE.ScreenDesignID = 2081 OR RECENTREVIEWEDCASE.ScreenDesignID = 1854) `

LEFT JOIN (

SELECT CATEGORY,DONRFUNCTION,COMMENT,LOI,UMN,CASENO FROM (

SELECT SCREENDESIGNID,REVIEW,REVIEWDATE,CATEGORY,DONRFUNCTION,ASSESSID,COMMENT,LOI,UMN,CASENO FROM(SELECT * FROM (SELECT Category='ADL', DONRFunction='Transfer', AR.ASSESSID, Comment, LOI,UMN,CASENO,STATUS,FUNDCODE,REVIEW,REVIEWDATE,SCREENDESIGNID ,RECENT = ROW_NUMBER () OVER (PARTITION BY AR.CASENO ORDER BY AR.REVIEWDATE DESC,AR.ASSESSID DESC) FROM ASSESSMENTREVIEW AR LEFT JOIN (SELECT ASSESSID, SCALE,LOI = CASE WHEN CONVERT(VARCHAR,ITEM) = '' THEN NULL ELSE LEFT (CONVERT(VARCHAR,ITEM),2) END FROM ASSESSMENTDetReview ADR Where ADR.SCALEID = 30062203)LOI ON AR.ASSESSID = LOI.ASSESSID LEFT JOIN (SELECT ASSESSID, SCALE,UMN = CASE WHEN CONVERT(VARCHAR,ITEM) = '' THEN NULL ELSE LEFT (CONVERT(VARCHAR,ITEM),2)END FROM ASSESSMENTDetReview ADR Where ADR.SCALEID = 30062204)UMN ON AR.ASSESSID = UMN.ASSESSID LEFT JOIN (SELECT ASSESSID, SCALE,COMMENT = CASE WHEN CONVERT(VARCHAR(255),ITEM) = '' THEN NULL ELSE CONVERT(VARCHAR(255),ITEM)END FROM ASSESSMENTDetReview ADR Where ADR.SCALEID = 30062149 )COMMENT ON AR.ASSESSID = COMMENT.ASSESSID WHERE AR.ScreenDesignID = 1854 AND AR.FUNDCODE = 'APS' ) E WHERE E.RECENT = 1 )EE

----SCORE FROM SCREENDESIGNID 2081

        UNION ALL`

`

SELECT SCREENDESIGNID,REVIEW,REVIEWDATE,CATEGORY,DONRFUNCTION,ASSESSID,COMMENT,LOI,UMN,CASENO FROM (SELECT * FROM ( SELECT Category='ADL', DONRFunction='EATING', AR.ASSESSID, Comment, LOI,UMN,CASENO,STATUS,FUNDCODE,REVIEW,REVIEWDATE,SCREENDESIGNID ,RECENT = ROW_NUMBER () OVER (PARTITION BY AR.CASENO ORDER BY AR.REVIEWDATE DESC,AR.ASSESSID DESC) FROM ASSESSMENTREVIEW AR LEFT JOIN (SELECT ASSESSID,SCALE,LOI = CASE WHEN CONVERT(VARCHAR,ITEM) = '' THEN NULL ELSE LEFT(CONVERT(VARCHAR,ITEM),1)END FROM ASSESSMENTDETREVIEW ADR WHERE ADR.SCALEID = 30083264 )LOI ON AR.ASSESSID = LOI.ASSESSID LEFT JOIN (SELECT ASSESSID,SCALE,UMN = CASE WHEN CONVERT(VARCHAR,ITEM) = '' THEN NULL ELSE LEFT(CONVERT(VARCHAR,ITEM),1)END FROM ASSESSMENTDETREVIEW ADR WHERE ADR.SCALEID = 30083265 )UMN ON AR.ASSESSID = UMN.ASSESSID LEFT JOIN (SELECT ASSESSID,SCALE,COMMENT = CASE WHEN CONVERT(VARCHAR,ITEM) = '' THEN NULL ELSE CONVERT(VARCHAR(255),ITEM)END FROM ASSESSMENTDETREVIEW ADR WHERE ADR.SCALEID = 30083214 )COMMENT ON AR.ASSESSID = COMMENT.ASSESSID WHERE AR.ScreenDesignID = 2081 AND AR.FUNDCODE = 'APS' ) E WHERE E.RECENT = 1 )EE1 --WHERE EE1.CASENO IN (123894,253179,233200)

)CDONR --WHERE (SCREENDESIGNID = 2081 OR SCREENDESIGNID = 1854) --AND FUNDCODE = 'APS'

)CASEDONR ON ONGCASE.ID = CASEDONR.CASENO AND (SCREENDESIGNID = 2081 OR SCREENDESIGNID = 1854)

INNER JOIN ( -- DONRTOTALS FROM BOTTOM OF THE SCREEN 2081 SELECT CASENO ,ADLIMPTOTAL - -,ADLUMNTOTAL,IADLIMPTOTAL,IADLUMNTOTAL,TOTALLOI,TOTALUMN,DONRTOTAL, FROM( SELECT AR.CASENO ,AR.ASSESSID ,ADLIMPTOTAL -- ,ADLUMNTOTAL -- ,IADLIMPTOTAL -- ,IADLUMNTOTAL -- ,TOTALLOI -- ,TOTALUMN -- ,DONRTOTAL FROM ASSESSMENTREVIEW AR INNER JOIN( SELECT * FROM (SELECT REVIEW,CASENO,AR.ASSESSID,SCALE,ADLIMPTOTAL = CASE WHEN CONVERT(VARCHAR,ITEM) = '' THEN NULL ELSE CONVERT(VARCHAR,ITEM) END

                                                                   ,RECENTDR = Row_Number() 

OVER(PARTITION BY AR.CASENO ORDER BY AR.REVIEWDATE DESC, AR.ASSESSID DESC) FROM ASSESSMENTREVIEW AR JOIN ASSESSMENTDETREVIEW ADR ON AR.ASSESSID = ADR.ASSESSID WHERE SCREENDESIGNID = 2081 AND SCALEID = 30083228 AND STATUS = 'Complete' AND FUNDCODE = 'APS' ` )DR WHERE RECENTDR = 1 --and caseno = 273274

)ADLImpairmentTotal ON AR.ASSESSID = ADLImpairmentTotal.ASSESSID `

             UNION ALL  -- DONRTOTALS FROM BOTTOM OF THE SCREEN 1854`

`

            SELECT AR.CASENO`

,AR.ASSESSID ,ADLImpairmentTotal1.ADLIMPTOTAL -- ,ADLUMNTOTAL -- ,IADLIMPTOTAL -- ,IADLUMNTOTAL -- ,TOTALLOI -- ,TOTALUMN -- ,DONRTOTAL FROM ASSESSMENTREVIEW AR INNER JOIN(SELECT * FROM (SELECT CASENO,AR.ASSESSID,SCALE,ADLIMPTOTAL = CASE WHEN CONVERT(VARCHAR,ITEM) = '' THEN NULL ELSE CONVERT(VARCHAR,ITEM) END ,RECENTDR = Row_Number() OVER(PARTITION BY AR.CASENO ORDER BY AR.REVIEWDATE DESC, AR.ASSESSID DESC) FROM ASSESSMENTREVIEW AR JOIN ASSESSMENTDETREVIEW ADR ON AR.ASSESSID = ADR.ASSESSID WHERE SCREENDESIGNID = 1854 AND SCALEID = 30062158 AND STATUS = 'Complete'AND FUNDCODE = 'APS')DR WHERE RECENTDR = 1 )ADLImpairmentTotal1 ON AR.ASSESSID = ADLImpairmentTotal1.ASSESSID `

      )DONRTOTAL1`

)DONRTOTAL ON CASEDONR.CASENO = DONRTOTAL.CASENO WHERE (RECENTREVIEWEDCASE.ScreenDesignID = 2081 OR RECENTREVIEWEDCASE.ScreenDesignID = 1854) AND RECENTREVIEWEDCASE.REVIEWDATE BETWEEN '1/1/2015' AND '1/31/2020'--BETWEEN @STARTDATE AND @ENDDATE -- AND -- ONGCASE.REGION IN (@REGION) -- AND -- ONGCASE.AAA_DISTRICT IN (@DISTRICT) -- AND -- ONGCASE.SupervisorName IN(@SUPERVISOR) -- AND -- ONGCASE.WorkerName IN (@WorkeR) `

END

1. enter code here

enter code here

Answer 1

conversion of text data type to int is not possible. check here

CONVERT(INT,LEFT (CONVERT(VARCHAR,ITEM),1)) this may work only if LEFT function on ITEM column is resulting in an integer value. Check the data in ITEM Column.