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.
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.