SQL Server 2005 Reporting Services Screen Flicker / Redraw / Refresh Issue

We’ve had an annoying problem with a couple of our SQL Server 2005 Reporting Services (SSRS) reports lately. Two of our reports have a lot of parameters, over twenty each. When a user picked a value from a drop down to use for one of the many parameters we have, the entire report would flicker and slowly repaint. Very annoying and time consuming. Further it didn’t seem to matter if the report ran in the report manager, or hosted in a web page report viewer control.

We worked with this, and after several searches I finally found one article on the web which told us what our problem was.

http://sqlreportingservices.spaces.live.com/blog/cns!17F39A64B3FF4C5C!238.entry

The article does a good job of explaining the issue and how to fix it; I wanted to rebroadcast it to help bring attention to it. The summary version: if you have hard coded default values or expressions for the parameters for some reason SSRS will sometimes flicker when the user selects something from the list.

Instead, the answer is to use a query to supply the default value. Fortunately SSRS makes it easy, just create a dataset with something like:

SELECT ‘Default Value’ as TheDefaultValue

When we took our report and replaced all the hard coded defaults (and we had a lot) with queries to supply the defaults our flicker / redraw / repaint issue went away.

Again, read the linked post for a thorough understanding. My goal here is to spread the word; it took me a lot of search and wading through pages of posts to find ONE that had my answer. I thought if I had that much problem finding it others may too.