4

I have a single shared dataset which calls a stored procedure. I have multiple tables which use the same dataset and has filters on the table itself to only include certain records.

Does the dataset get called for each table or does it only get called once?

user172839
  • 1,035
  • 1
  • 10
  • 19
  • 1
    When you say *Shared Dataset*, do you mean an actual Shared Dataset deployed on the Report Server, or a Dataset internal to the report shared by multiple report items? – Ian Preston Nov 18 '14 at 09:36

1 Answers1

4

The easiest thing to do is run the report and see what happens in the database. In this example I have used SQL Server Profiler to view the database activity. I have tested using a simple report run through Visual Studio.

Dataset:

enter image description here

Report with two tables, different filters, same Dataset:

enter image description here

enter image description here

enter image description here

Run the report:

enter image description here

Check what has been recorded in SQL Server Profiler:

enter image description here

You can see that the Dataset query has been run only once. So in this case we can say that referencing a Dataset multiple times will not cause it to be loaded multiple times.

With SSRS it's always risky to say this will always be the case in all scenarios, but based on this example it seems like a good bet.

Ian Preston
  • 38,816
  • 8
  • 95
  • 92
  • you can safely say that this will *always* be the case in all scenarios - as long as the elements live inside the report. That isn't to say though that the performance will be better when the dataset is executed only once - you'll have a good chance that the database handles several small calls in parallel faster than SSRS will transport one larger dataset and filter it multiple times. – vstrien Nov 18 '14 at 14:28