0

In reporting Services, the user can choose a list with different customers. The report then generates total figures for the chosen customers. I have 2 tables in the report that incorporate the total revenue. However, when you choose all customers, the totals of the 2 tables differs. For every individual customer however, the tables match.

In the first table I use a simple query like this:

select revenue from customers where customernumber in (@CustomerNumber)

This one always shows the correct figures. However, for the second table, I have to call a stored procedure:

create procedure calculaterevenue (
   @customernumber varchar(1200), @customercategory varchar(255)=NULL 
)

In the procedure, a table valued function is used to take all the customernumbers that are in a comma-separated list and make then usable for the in operator:

and customernumber in (select value from fnStringToList',',@customernumber)

In the Dataset of the report, I have added an parameter @Customers that is 

=JOIN(Parameters!Customernumber.Value,",")

And I call my procedure with 

exec calculaterevenue (@Customers)

This works perfectly, until you select a lot of customers. I suspect the problem lies within the JOIN. Is there a maximum output for the JOIN statement?

user1261104
  • 315
  • 4
  • 14
  • 1
    What happens when call `exec calculaterevenue (@Customers)` with a lot of customers? You are asking for help debugging your code without any evidence of what you have already tried. – BIDeveloper Jun 27 '16 at 15:11
  • If I execute that in SQL Server, it works perfectly. If I try it from reporting services, it will only be correct until 29 customers, or 260 characters (29*8 characters+28 commas) – user1261104 Jun 28 '16 at 07:31
  • Amend your stored procedure to write the parameters to a table so that you can see what is being passed. The Text parameter has a 32K limit so that won't be your issue. – BIDeveloper Jun 28 '16 at 07:40
  • ... Just had a thought. Is [this](http://stackoverflow.com/questions/6418971/full-path-must-be-less-than-260-characters) your issue? – BIDeveloper Jun 28 '16 at 08:18

0 Answers0