Here is a oldie but a goodie.
I’ve created a capacity trending and forecasting report, when I uploaded the report to the SSRS web console, the report tool for ever to load and everytime I clicked on the report parameters a post back is occuring slowing down the report. One of the report parameters contains 6000+ server names. Report parameter postback normally happens when there is a dependacy of one parameter on another and when the return set from the post back is quite big the user-experience of the user opening the report is really crappy.
My solution after struggling for a couple days to find out what’s happening is to create “dummy” sql statements and contain these statements in a data set. The parameter default and available values is then the contents of these datasets
Doing this drastically improved the report performance and prevented post backs in totallity.
What I did:
One of the report parameters was the days of forecasting for the report
I’ve then created a data set called “otherdefaults”
The sql query for this dataset looks like this
As you can see the defalt value for the forecasting to be performed by the report is 10 days, there is other information in the query as well for the other parameters in the report.
The parameter details are as follows
As you can see the defaultdays value in the sql query is the available value for the parameter.
By creating and using datasets for your report parameters you can drastically improve responsiveness and performance.
Output of the report