3
votes

On my SSRS report I have a multi-value parameter which contains 250+ uniqueidentifier data type values. This works fine with a small selection of values in the parameter dropdown, but when user chooses (select all), they get this error:

An error occurred during local report processing.
String or binary data would be truncated. 

Each uniqueidentifier field is 36 characters long, which means 250 of them added together result in a 9000 character string. This is what causes the truncation to occur.

What approach should I take to handle this situation?

Edit:

Couple snapshots of the stored procedure:

ALTER PROCEDURE [dbo].[spReport]
     @StartDate as datetime
    ,@EndDate as datetime
    ,@LocationId uniqueidentifier
    ,@UserIds uniqueidentifier

@UserIds is the multi-value parameter. It is used in the where clause of the query:

WHERE (U.UserId IN (@UserIds) OR @UserIds IS NULL)
3
this is my most wild deed while being on SO. i upvoted your question just because of your nickname - Andrey Chernukha
Does the error occur when you select "all" on the report, or is the issue in your query? - David Lawson
Can you share what your query looks like? Does it by any chance call a stored procedure with an nvarchar(xxx) (with xxx < 9000) perhaps? In addition, you could try using SQL profiler to snatch the query being executed and try to execute it manually. - Jeroen
@David it is an issue with the report, however if I executed the query on its own using a parameter that size, it would also fail. - Dr. Greenthumb
You can't use an SSRS multi-value parameter with a stored procedure like that. You'll need to join the values in the report, pass them as a varchar(max), and then split them in the stored procedure: stackoverflow.com/a/9862901/124386 - Richard Deeming

3 Answers

1
votes

SSRS does have a limit on the size of multi-value parameters. I can't remember what it is off the top of my head, but I think you are well beyond it. (SSRS converts the multi-value parameter to a comma separated string and replaces the occurances of the variable name in the query with the string.)

So as mentioned in the comments, you've got two problems:

  1. SP's can't take multi-value parameters directly from SSRS. You'll need to do some manipulation.
  2. Your overall parameter length. This may require a little bit of creativity to solve. Some options:

    • Can you supply either a separate parameter or a special value in your existing parameter for <All Users> and then check for this in the SP, returning all values in that case. If the query is directly in SSRS (instead of a SP) something like this would work:

      ...WHERE ( U.UserId in ( @UserIds) OR '<All Users>' in ( @UserIds ) )
      ...
      
    • Can you filter the number of items in your parameter, based on earlier parameters? Such as have the user select a date range and/or department, and only return UIDs that match that range?
1
votes

You can't use an SSRS multi-value parameter with a stored procedure like that. You'll need to join the values in the report, pass them as a varchar(max), and then split them in the stored procedure:
https://stackoverflow.com/a/9862901/124386
http://www.codeulike.com/2012/03/ssrs-multi-value-parameters-with-less.html

0
votes

Another approach is to create a user defined table type and use that instead of a varchar to pass in the selected values.