SSRS report - Casing parameter

1
votes

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
2
sqlsql-serverreporting-servicesssrs-2008
This error occurs when dataset is comes empty that is no data is fetched from the database. Try testing your connection and query if they working correctly - Mahesh
The dataset works just fine and so do the queries, ive tested using the parameter without the case, so the drop down displays the numbers instead of the text and it parses data just fine. The issue lies with the casing. I dont understand why. Thanks for the reply though! - consorte101
What have you tried for the parameter casing can you add it in the question - Mahesh
i dont really understand the question, but i've added the main query above. The parameter is sourcing another dataset which is the one i've having the problem with. If i remove the case statement and simply do a distinct query for the parameter, it works just fine, but the the drop down for the parameter gives me numbers, which is what i'm trying to avoid with the case statement. - consorte101
In your main datset query you are matching the workorderstatusid =@status but inthis @status comes as string where as workorderstatusid is numeric type so this may be causing the problem - Mahesh

2 Answers

0
votes

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

-1
votes

Figured it out guys, needed to include this statement prior to the main query

if (@status = 'Open')
begin 
      set @status = 1
end
else if (@status = 'Closed')
Begin
      set @status = 2
End
else if (@status = 'OnHold')
Begin
      set @status = 105
End
else if (@status = 'Cancelled')
Begin
      set @status = 101
End