SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MultiValue parameter, select all no data


MultiValue parameter, select all no data

Author
Message
pjrpjr7
pjrpjr7
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 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.
Kees Beimans
Kees Beimans
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 229
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search