SSRS 2008 Report does not run in sharepoint 2010

have report developed in SSRS 2008. Report is deployed to sharepoint 2010 in dashboard. Report has parameters and have been supplied. Report runs fine locally in my computer. But when we try to run the report in sharepoint by clicking the apply button , it does not run. To test if something was wrong with connection string, I have downloaded the rdl file to my computer and tried running in my computer. Reports runs fine. I am not sure what could be the reason for report not running. It used to run earlier in sharepoint. Could anyone help me in resolving the issue.
 
I have observed that when all the values in parameter are selected (Select All) report does not run and when some of values in parameters are selected report runs fine in sharepoint. I see the processing symbol fraction of seconds and disappearing when all values in parameters are selected. It does not show any error message when report is not being displayed.
 
Below is the screenshot of the report from report builder and in sharepoint site.
ReportBuilderscreenshot.png
Sharepoint2010report-scrrenshot.png
asrithapAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ValentinoVBI ConsultantCommented:
Sometimes Sharepoint messes up the parameters when a report is redeployed.  Try deleting it from Sharepoint and then redeploying it.  Hopefully that solves your issue.  If not then we'll need to further investigate.
0
asrithapAuthor Commented:
Hi, I have deleted the report rdl file and redeployed it to sharepoint library. I have selected some filters and cliked on Apply button. Report works fine. But the issue is only when i select all values from the paramters and click on apply button. The loading symbol appeards fraction of seconds and disappears and report does not run. I have tried running the report locally on my computer it runs fine when all values in parameters are selected. But it does not run in sharepoint 2010. I have tried to execute the report queries in database and found that queries return values in 9seconds. I am not sure whats the root cause of the problem.Truly appreciate your guidance. Need advice and Thanks in a dvance.
0
asrithapAuthor Commented:
Hi Valentino, I have tried redeploying report in sharepoint and report did not work in sharepoint but it does run in local computer. I am unable to figure out what could be the cause . Could you please help me in this fixing this issue. I truly appreciate your help.For your information, there are about 1600 values in one parameter and other parameter has around 20 values in it. Is this is reason for issue? Thanks in advance .
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

ValentinoVBI ConsultantCommented:
I've re-read your comments and I think SharePoint may have an issue rendering the report due to the amount of data.  You mentioned that it runs fine with some values selected and doesn't run at all with all values selected.  I've seen situations in which I was able to render a report just fine in BIDS, within seconds, while on SharePoint that same report would take almost minutes to render exactly the same.

How many pages would it need to render with all the values selected?

Also, 1600 values in a parameter, wow, can't you group them together somehow?  I mean, a dropdown with 1600 values is just not usable anyway...
0
asrithapAuthor Commented:
Hi thank you for responding.
Basically  the report displays count of records.It does not show records in pages. User will run the report and export to csv format . I am attaching the rdl file for your understanding. When I select all 1600 values in Applicant parameter and click on apply in sharepoint, i dont even see the report processing symbol. I too feel that selecting 1600 is not usable way as you have said,but here users are used to this report selecting all the values. When export to csv is selected when all the parameters  1600 values are selected , and all disasters values (15) the number of rows that csv format shows is shown in attached screenshot. Please advise me how to proceed further. This report used to run a month ago. Recently 3-4 applicants have been added to database since then this report does not work in sharepoint.
I have seen in one of the blogs that in config settings of ssrs file there is max limit of keys. Below is the url for your reference, is this is the cause of the issue?
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/cb6ede72-6ed1-4379-9d3c-847c11b75b32/report-manager-operation-cannot-run-due-to-current-state-of-the-object?forum=sqlreportingservices.

Below are the steps they suggested.
Go to the reporting services installation folder.
Go to the report server folder and make a backup of the file “Web.config”. Once you have the backup, open the original file in notepad and add the following entry under the Configuration section:
    <appSettings>
                <add key="aspnet:MaxHttpCollectionKeys" value="10000"/>
    </appSettings>
Go to the Report Manager folder and again make a backup of the file “Web.config”. Once you have the backup, open the original file in notepad and add the following entry under the appSettings tag:
< add key="aspnet:MaxHttpCollectionKeys" value="10000"/>
PWDataExport---Copy.rdl
PWDataExport-SCREENSHOT.png
0
ValentinoVBI ConsultantCommented:
That setting could be the solution yes, after a certain patch this is apparently set to 1000 by default, which is not sufficient for your situation.  Any chance to try it out?

Perhaps another option is adding an <ALL> item to your parameter list?  Your parameter query would be something like:

<your current query>
UNION ALL
select -1 as ID, '<ALL>' as Label
order by ID asc

Open in new window

and the WHERE clause of your query that retrieves the data would be something like:

WHERE -1 IN (@Param) or ID IN (@Param)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
asrithapAuthor Commented:
I do not have access ssrs config file right now. I will have to check with my senior on this.
 But that forum link says that we can change the setting as per our need.
<appSettings>
                <add key="aspnet:MaxHttpCollectionKeys" value="10000"/>
   </appSettings>

value to 2000 as per my requirement now. I am not sure the impacts of changing this setting.
 I will try to check the other option meanwhile changing the query and come back to you if i have any issue . Thank you so much for your valuable guidance.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.