SSRS :Failed to convert nvarchar to int

Question

Hi I have a dataset as follows

SELECT WEEKNAME AS WEEKSTART, CUSIP_BASE AS CUSIP,

case

when FK_BBGFTPStatus = 5 then 'Failure'

when FK_BBGFTPStatus = 8 then 'Failure'

when FK_BBGFTPStatus = 9 then 'Failure'

when FK_BBGFTPStatus = 3 then 'Success' end 'RESULT'

FROM Glossary

WHERE (FK_BBGFTPStatus = 5 OR

              FK_BBGFTPStatus = 8 OR
              FK_BBGFTPStatus = 9 OR
              FK_BBGFTPStatus = 3) AND (WEEKNAME BETWEEN @WeekStart AND @EndDate) and convert(varchar(10),FK_BBGFTPStatus) in (@Status)

i have a report parameter as follows

Name:Status Datatype:string Prompt:status multivalue Nonqueried

Label Value

Success 3

Failure '5','8','9'

it works fine for succes because it is just one number i am getting an error when i check the checbox in the dropdownbox for failure

can you please help mee with this issue

Answer 1

The easiest way is to add a cast to put it in int.

I add this at the beginning (before the select):

DECLARE @tmp_Status integer;
set @tmp_Status = CAST(FK_BBGFTPStatus as int);

And then use everywhere the var @tmp_Status.