asked
SSRS pass multiple valued parameter as varchar instead of nvarchar
Hello, I'm puzzled. I am making an SSRS report based on a view (mygroupsamount) which is a union of two tables (same columns, with possibly same rows, hence the 'union'). But it seems to make a 'union all' instead (even though my code clearly uses a union) I've tried it in SQL : this gives me 2 rows (appears to be 'union all') DECLARE @group as **NVARCHAR(MAX)** SELECT @group ='013' SELECT number1, amount1 FROM mygroupsamount WHERE group in (@group) and this gives me 1 row (appears to be 'union', as is in the views script) DECLARE @group as VARCHAR(MAX) SELECT @group ='013' SELECT number1, amount1 FROM mygroupsamount WHERE group in (@group) The SSRS report unfortunately passes a nvarchar, so I get a wrong result. Any way I can convert the multivalue parameter to a varchar(MAX)? I've tried `group in convert(varchar(MAX),@group)`, but that doesn't work with a multivalue parameter ? thx
Hmmm...my comment just disappeared when I tried to edit it.... Anyway, what data type is "group" in both tables (oh and collation please)
SSRS can only pass parameters as NVARCHAR, you will need to take care of the conversion at the DB level [MSDN Blog on SSRS NVARCHAR Params][1] [1]:
http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/06/17/possible-performance-implications-when-using-string-parameters-in-reporting-services.aspx There are 3 different work arounds in the above link
12 People are following this question.
