Sql – SSRS report – Casing parameter

reporting-servicessqlsql serverssrs-2008

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

enter image description here

And then set the default values to set by query and select the dataset

enter image description here

Related Topic