I need to be able to return data from 4000 customers. These 4000 customers unfortunately must be in the multi value parameter so I can select for example 10 different customers from the list and it will then return its data (with extra filter, so it saves time scrolling up & down). Correct me if I'm wrong, multi-value parameter only handles 1000 values at max, although I can see all 4000 values in the list, it will not return all 4000 (it does in the preview, not after it's deployed). I have tried to skim it down to 900 and it will return all 900. I do understand that it is not rational for a user to tick checkboxes more than that, but I still won't be able to return the rest of the 3000+ customers. What is the best way of doing it please?
1
-
Can you please explain the problem? your description is mixed with the issue in a way that it is hard to understand, tnxNasser Hadjloo– Nasser Hadjloo2012-05-31 10:10:30 +00:00Commented May 31, 2012 at 10:10
Add a comment
|
2 Answers
We also are experienced same problem on our Development environment sql server 2008 R2 SP1..
But we found the workaround:
<...>\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\Web.config
Inside add key:
<!-- language: xml -->
<add key="aspnet:MaxHttpCollectionKeys" value="10000" />
Restart reporting services and it should be working. Without adding aspnet:MaxHttpCollectionKeys default value is 1000.
Comments
The limit of 1000 rows comes from declaring things like this:
DECLARE @myTable MyTableType
INSERT INTO @myTable VALUES (1), VALUES (2), VALUES (3)
EXEC myProc @myTable
That's known as row-constructor syntax, and there is a limit there.
You can get around this by using single-inserts, or batching the row-constructors together. While that seems like it might be very poor for performance, SQL Server creates what is known as a 'trivial plan' - and you can read some more about it on Bob B's excellent blog - here and here.
