Page refreshing after the parameter selection in SSRS report

Question

I have couple of parameters in my SSRS report. some are multivalued and some are regular with drop down list. each time while selecting a different parameter value the page is getting refreshed. Is there any way to avoid this page refreshment on each parameter selection.

Thanks in advance. Maria

Solution 1

I just encountered to this problem today, and the following post definitely helped me.

http://blog.summitcloud.com/2009/12/fix-refresh-of-parameters-in-ssrs/

quoting from the post:

According to Microsoft, if a default value or valid values list is “too complex” (could be as simple as =Year() expression) for the RS engine to comprehend at runtime it will determine that it is dependent and therefore a candidate for refreshing.

The post in the link shows a simple workaround example:

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

Hope it helps.

Solution 2

Not easily unless you willing to roll your own report viewer control. This is done because SSRS has parameters which can be used to determine the value of other parameters. For example imagine your looking at website users and your first option is OS, perhaps it has Vista, XP, and Mac.

When you select Vista the page refreshesh to get a list of all the various versions of Vista.

If you are using thing like this, then you won't be able to prevent the refresh.

Solution 3

Following is an approach that worked for me based on a post I read elsewhere.

In my scenario I had a boolean parameter for which I had provided Yes and No captions and corresponding true and false values directly (not via a query). I also had 2 date time parameters. None of these were tied with each other.

Every time, I entered values for any of the dayes and tabbed over, SSRS would refresh itself. It seems SSRS tries to resolve the expressions which in my case were the values for the Yes and NO options.

The solution was to use a dataset to return the Yes and No values which resolved the issue for me.

Comments

[pili]

I Right clicked Report Data Pan, but there is no menu item to show hidden datasets. I get only "Add Dataset". Any idea pls?