Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

MultiValue parameter, select all no data Expand / Collapse
Posted Thursday, December 20, 2012 2:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 26, 2013 2:51 PM
Points: 29, Visits: 64
I have an SSRS 2005 report (DB = SQL 2005), running on SSRS 2008 report server. There are total 6 datasets (and no stored procedures). I have four parameters, out of which one is a date field, one is a text box and the other two are multi value parameters (@PC, @CT). Out of the 6 datasets, 2 serve as providing the available values for the multi select parameters and other 4 feed the data for the report.
Number of available values for @CT = 480 and that of @PC = 987.
When I select all, the report runs fine in BIDS, but no data in report manager.
I updated the queries populating the available values for parameters as
select 'All' union select column from table.
Report query:
where ( @PC = 'All'
or M.PC IN (@PC)
and ( @CT = 'All'
or M.CT IN (@CT)
In the drop down, if I select 'All' for @CT and any one or two values for @PC, the report runs fine.
If I select 'All' for @PC, the report doesn't return anything (in the report manager).
Interestingly, if I select upto 967 individual values for @PC from the dropdown, the report runs fine. If I select 968 or anything higher than this, then the report doesn't return anything.
I tried using function, split, join (tired pretty much everything that I found in google). Please help and thanks in advance.
Post #1399104
Posted Wednesday, January 2, 2013 6:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, June 16, 2013 1:45 PM
Points: 76, Visits: 229

It looks like the general issue after the MS security update MS11-100. This security update limits the max number of form_keys to 1000.

When opening the report in the Web Browser and you select the 'Select All' and the browser informs you with 'completed with error on page' is a symptom of this issue.

See also

You solve this by adding the following line in the web.config file of the ReportManager in the "appSettings" section.
<add key="aspnet:MaxHttpCollectionKeys" value="3000" />

Choose a value that meets your needs

Regards Kees
Post #1401802
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse