I'm having an issue with an SSRS report in Report builder 3. I'm attempting to use a parameter which is being cased to show text instead of the int datatype of the field. Report Builder spits out an error every time i try to preview the report. I've tried casting and converting the data type, but still get the same error. Any insight would be much appreciated. Below is the query that the parameter is sourcing and the error message.
–Query
select distinct case convert(varchar(10),workorderstatusid)
when '1' then 'Open'
when '2' then 'Closed'
when '105' then 'OnHold'
when '101' then 'Cancelled'
end as 'Status'
from tasks
–Error message
Cannot read the next data row for the dataset DataSet1. (rsErrorReadingNextDataRow)
----------------------------
An error has occurred during report processing. (rsProcessingAborted)
— Dataset 1 – main query
select wo_num as 'Word Order ID',isnull(dept,'Unassigned') as 'Department',
task as 'Summary', isnull(descript, 'No Description') as 'Notes',
respons as 'Assigned Technician', duedate as 'Due Date',completed as 'Date Completed',
isnull(status,'Incomplete') as 'Status'
from tasks
where (workorderstatusid =@status)
and (dept=@department)
order by wo_num asc
Best Answer
First add the parameter and set it's Type as Text and multiple values checked as below
And then set the default values to set by query and select the dataset