Hello everyone, I have some reports in sharepoint which work fine when I use the date parameters for 2-3 months, but when I use one year in date parameter or try it for heavy reports,report dose not work and it seems like connection is closed at this point as it prompts for parameters again.However the query is still running in SSMS. Any help is appreciated.
I would guess you are hitting some sort of timeout when querying too much data. You can either: - increase the timeout setting - reduce the dataset you are querying - rewrite the query so it runs faster even if you query more data - look at possible missing indexes - upgrade hardware
It sounds as a timeout, you should be able to change timeout values in reporting services and/or in Internet Information Services. You should also try to tune your underlying query and indexes. Are your statistics up to date? If not, you can have unpredictable performance issues depending on your data distribution and your filter ranges.
So many questions to ask you - are there lots of columns being returned? - how many rows are being returned in the 2 types of execution - the one that works and the one that doesnt? - how long do the queries take to run in SSMS if you supply the parameter values? - are you using stored procedures to return the data? - are you using functions in the query anywhere?