1

I'm seeing some weird behavior I can't quite figure out. I have a report with a bunch of parameters, none of them are cascading. A handful of the parameters are set to allow multi-select and the available/default values are retrieved from data sets that used stored procedures.

When I load a report every data set executes, which I expect. However, if I change a parameter value, such as the date for the date parameter, every data set executes again. Why is this?

What's compounding the situation is I have 2 copies of each parameter (Param1, Param1_Internal, Param2, Param2_Internal, etc.). I have it setup like that for formula's I'm using in the report to determine if the user selected 'Select All' for the multi-select parameters. So for example I'll have Param1 and Param1_Internal set from data set 1, Param2 and Param2_Internal set from data set 2. When I change an unrelated parameter data set 1 will execute twice, and then data set 2 executes twice.

Any suggestions to:

  1. Get these data sets to run once each, even though one data set feeds available values for 2 parameters?
  2. Stop the data sets from running every time I change an unrelated parameter?

I am currently on SQL Server 2016.

Thanks

Edit

So, I found the answer to part of my question. In some cases I am using an expression as the parameter value in the data set that calls my stored procedure. It looks like when you do this, SSRS will execute that data set every time you change any parameter value. I still am hoping someone will have advice on how to have a dataset run once in a situation where I have 2 parameters using it for default/available values.

User181
  • 111
  • 8

2 Answers2

1

You can cache data set results if they are in a shared dataset stored on the SSRS server. This way the first time you run it will execute the query, then the second will just pull from the cache:

https://learn.microsoft.com/en-us/sql/reporting-services/work-with-shared-datasets-web-portal#caching

Use with caution though, as if the backing values of your parameters change often you could get inconsistent results with your reporting.

mallan1121
  • 489
  • 7
  • 14
1

You can stop the refresh on parameter change by making sure the below is set within the parameter properties:

enter image description here

GandRalph
  • 590
  • 2
  • 10