[BI Developer] IN Clause with multi value from Report parameters.
Having trouble finding someone who addresses this issue I am having.
I am using the BI Developer in Visual Studio 2008 to create a report that hits a SQL Server database.
I have a dropdown of sales reps and it can have multiple selections.
So, this means that the Report tries to pass a string of numbers (since the SalesRepID is the value that is passed).
So, the id's could be "9,5,10". This is supposed to go into an IN clause to pull the correct records based on ID.
If a single person is selected, it is passed as an integer (or a string, but at least converted properly) and the IN clause finds the person properly as the SalesRepID is a numeric value.
But, if I choose multiple people, it says it can't convert the incoming string to an integer to be able to compare. Obviously makes sense, but a problem.
Does anyone know a way around this, be it a setting in the report? Or maybe even some SQL code that can get it to work?
The error I get is this:
An error occurred during local report processing. An error has occurred during report processing. Query execution failed for dataset 'SO_CommissionReport'. Conversion failed when converting the varchar value '142,132' to data type int.
I tried casting the field into a string, but that didn't work:
WHERE Cast(a.SalesRepID As varchar(10)) IN (@SalresRep)
Re: [BI Developer] IN Clause with multi value from Report parameters.
Never used BI developer so I don't know how the IDE works. But, have you tried using "CStr" or "Str" function instead of "Cast"?