1

I have a requirement in which I have to consolidate 5 SSRS reports. All 5 reports have exact same parameters. Currently, I have consolidated all the 5 tablixs into one report and have created a new dropdown parameter from where a user can select a particular report.

My Question is how do I maintain CACHE for each of the 5 selection from the dropdown. All 5 tablix have their own 5 different dataset which calls each of their store procedures. Currently its taking almost 5 min to load the report when different report is selected from the dropdown parameter.

OR whats the best way to conslidate these 5 reports in one. Please help. Thank you.

Jatin Garg
  • 85
  • 10
  • hey @Alan Schofield - Can you help me with this please. – Jatin Garg Mar 11 '19 at 22:03
  • What is in the tablixs? Instead of having 5 datasets pointing to the same stored procedure run with the same parameters, can you have each table pointing to the same dadtaset and run it only once? – Chris Latta Mar 12 '19 at 01:22
  • Hey Chris - All the 5 datasets point to 5 different stored procedures. All 5 tablixs have pretty different data so I wont be able to consolidate into one dataset. – Jatin Garg Mar 12 '19 at 03:25
  • Ok, then you have to investigate optimising the database side. Take a look at the execution plan for the stored procedures. Are they doing full table scans? If so, could the queries they are doing be optimised by adding an index or two to cut out the full table scans? Alternatively, do your stored procedures run fast in Sql Server Management Studio but slow in SSRS? You might have a case of [parameter sniffing](https://stackoverflow.com/questions/16924144/why-does-the-2nd-t-sql-query-run-much-faster-than-the-first-when-called-by-repor/16929881#16929881) – Chris Latta Mar 12 '19 at 04:04
  • There's no way it should take that long for a single report with a single dataset, honestly. I've currently got a report that contains about 15 datasets -- all load up on initial run and it takes less than 10 seconds every time it runs. It almost certainly means you have some optimizing in the procs to make it faster. Out of curiousity, how is your dropdown selecting the report? Do you have all five tablixes in the same report and just show/hide based on the parameter? – Steve-o169 Mar 12 '19 at 12:19
  • Hey Steve - I have 5 different datasets pointing to 5 stored procs. So its not a single dataset. Yes I have 5 tablixs based on show/hide parameter as selected by the end user – Jatin Garg Mar 12 '19 at 15:14
  • Sorry, I was unclear. My initial assumption was that only one subreport was called for each parameter value. Since you're loading all five subreports and hiding four based on the parameter, I'm confused why it would take so long to switch. Would you show a little more information about the expressions used to hide each subreport? – Steve-o169 Mar 12 '19 at 19:36

1 Answers1

0

You can have SSRS cache all 5 versions of the report. One way to do this in SSRS 2008+ is to create a data-driven subscription. Write a small query that returns 5 rows with the different parameter values. Schedule this to run and use the value from the query to populate the parameter. Set the cache to last for something like 600 minutes.

This will run the report once with each parameter value and cache them all. It will last for 10 hours so you can use it throughout the day.

Keep in mind that this does not apply to the other parameters, it only caches the combinations of parameters that you have run it with in the past 10 hours.

If you can't do data-driven subscriptions, separate ones for each cache will work just fine. In newer versions of SSRS, you can set up cache refresh plans instead of subscriptions.

If you have trouble with this approach, I would recommend setting up a nightly run to prepare the data in a table that is optimized for reading. This ETL will take the load off the procedures and should speed up the on-demand queries.

StevenWhite
  • 5,907
  • 3
  • 21
  • 46