MultiValue parameter, select all no data

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services 2005 Development » MultiValue parameter, select all no data

MultiValue parameter, select all no data

Rate Topic

Display Mode

Topic Options

Author

Message

pjrpjr7

Posted Thursday, December 20, 2012 2:12 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, February 04, 2013 5:55 AM
Points: 27, Visits: 61

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

Kees Beimans

Posted Wednesday, January 02, 2013 6:05 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 4:58 AM
Points: 76, Visits: 228

Hi,

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 http://support.microsoft.com/kb/2661403/en-us

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 »

Permissions