SQL Reporting Services maximum number of parameter items (SSRS)

Symptoms:

• Reporting Services don't show data after selecting parameters
• Reporting services displays blank page
• Report is working fine in Visual Studio Preview mode, but not on Report Server web server
• Parameters are used with more than a thousand item values

Causes:

• There is a onstraint (on .NET framework or Web server I think) that allows only 1,000 values to be loaded under parameter drop down boxes

Recently, I faced very odd issue and I couldn't remember if I've seen this before. Actually, my report was working fine and day after when I ran the report only blank screen appeared with no data and without parameters as well.

I figured out that everything was working just fine inside of Business Intelligence Studio, but not on Report Server web site though. I spent 4 hours completely focused on looking for any solution, even when I realized very quickly that there is no constraint on number of parameters or parameter values (I learned this even in MS SQL2005) and I found a solution on one of so many forums that I have read that day. Solution was obvious, but I didn't restart whole Windows Server and that took me one more hour. Also, I had to add tags from below into 2 config files and one tag was missing and I find it by myself somehow.

Usually, it works only after one tag is added, but it wasn't for me again. Don't worry, I wasn't in panic this time, but just a step before final solution.


Solution:

1. Locate Reporting services configuration files (web.config) on both Reporting Server and Report Manager folders - my locations are listed below, but this is not fixed location and files could be placed under different path, so try to search for them if you cannot find them
2. Open files - Visual Studio or Business Intelligence Studio is always recommnded, but Notepad should be fine in the most cases as well
3. Add the following tags under <appSettings> section. Note: If <appSettings>  doesn't exists in config files add the whole tag from below just after <\system.web> section. If "appSettings" is already there just add 2 keys from below. I put 30000 as maximum number of items, but you can use any number of values.
• <appSettings>
  <add key="aspnet:MaxHttpCollectionKeys" value="30000" />
  <add key="aspnet:MaxJsonDeserializerMembers" value="30000" />
  </appSettings>
  
4. Restart all MS SQL Server services. In my case only Windows server restart was helpful.

Here are locations on my machine where I found the config files:

• c:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
• c:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager

Good luck. I think this could be helpful.


 Mike C.