query defining parameter dropdowns is run twice!

I built a very simple report which uses a query to define the options in the parameter’s dropdown. I used that same dataset to define the default for that parameter (meaning that it will just pick the first row from the dataset and use that as the default). When I run the report watching a Profiler trace on the SQL database, it runs that query twice. (Presumably, that’s once to fill the dropdown list and once to figure out the default.) That seems silly to me since it is the same query that is the same dataset in Reporting Services. Is there any way around this? My parameter bar is rendering twice as slowly as it should be.

I've tested against SSRS 2005 SP1 and the CTP of SP2.

Can you confirm your test by removing the default and re-testing to make sure it gets run once. Then adding the default again to confirm it goes back to 2 executions?|||Yes, it only runs it once if I don't specify that the parameter should have a default from a query.

Query based parameter value changes back to default value

I have several parameters on my report, some have non-queried lists, and
some have queried lists. All of them have default values. When I ran
reports on the Report Manager, I changed the value of the parameters from
the drop-down list to produce different reports. My problem is whenever I
changed the value of a parameter(pA), the value of those queried-based
parameters that were listed after pA would switch back to their respective
default values. This didn't happen when I previewed reports on Visual
Studio .Net Development Environment. Is this a bug, limitation, or am I
missing something?
Any help would be appreciated.
Paul
--
Message posted via http://www.sqlmonster.comPaul,
It sounds like your default values are calculated values (begin with a '=').
The way that RS computes these is that whenever you change a parameter, it
recalculates all the calculated parameters after it. This behaviour is to
allow cascading parameters. As far as I know, there is no way to turn this
calculation off. However, I worked around it by creating another dataset
which had a column for each default value (I do a lot of default dates). You
can then set the default values for the parameters to the dataset value in
the parameters window. I have to use SQL to do the calculations, but for
most things, this should be an easy workaround. It gets rid of the changing
values and also the refresh that takes place when you change calculated
parameters
"Paul via SQLMonster.com" wrote:
> I have several parameters on my report, some have non-queried lists, and
> some have queried lists. All of them have default values. When I ran
> reports on the Report Manager, I changed the value of the parameters from
> the drop-down list to produce different reports. My problem is whenever I
> changed the value of a parameter(pA), the value of those queried-based
> parameters that were listed after pA would switch back to their respective
> default values. This didn't happen when I previewed reports on Visual
> Studio .Net Development Environment. Is this a bug, limitation, or am I
> missing something?
> Any help would be appreciated.
> Paul
> --
> Message posted via http://www.sqlmonster.com
>|||Hi David, that works great. Thank you very much!!
Paul
--
Message posted via http://www.sqlmonster.com