FIX: Strange Refresh of Parameters in SQL Reporting Services

This is an oldie-but-goodie I posted back in 2007 about SSRS Parameter refreshes.

Input Parameters should refresh on your SSRS report if there are dependencies, for instance a list of valid Sales Reps might be dependent upon a selection of Sales Territory first.
It makes sense that a change in Territory would trigger a refresh or postback in order to regenerate a list of valid values.

However, you may notice some strange behavior that causes a postback of parameters even when no dependency exists.

According to Microsoft, if a default value or valid values list is “too complex” for the RS engine to comprehend at runtime it will determine that it is dependent and therefore a candidate for refreshing.

What this really boils down to is default expressions. Your expression could be as simple as =Year() to default to the current year on a list, or even =2006. Seems simple enough, but once you throw that equals sign in there, RS crosses its’ arms and says “looks like VB to me, so I’m going to reevaluate it.” …EVERY time ANY other parameter changes. And that means a nasty post back, and time wasted in front on the screen to a user.

There is a workaround, as a dataset doesn’t send persnickity RS into such a tizzy. If you can, take those VB expressions and turn them into datasets that return values based on an SQL statement.

So =Year()  in the expression would become something like

Select Year(getdate()) as CurYear

Then set the default to use that new dataset and the CurYear value.

For simple fixed values, you can do this as:

Select 2006 as CurYear

More complex statements may take a bit of code, but the rewards will be a friendlier user interface that doesn’t need to refresh every single parameter on screen when you make one little change to a filter.

About the Author