CODEWITHSUNDEEP
sqlsql-server-2008reporting-services
Rajib Akhter
Rajib Akhter

Reputation: 11

Optimize Reporting in Reporting Services

I have 10 reports and 250 customers. All the reports are ran by my customers and each report take parameters. Depending on the parameters, same report connects to different database and gets result. I know with different parameters caching is not an option. But I dont want to run these reports on live data during day time. Is there anything I can do (snapshot, subscription) that can run overnight and either sends these reports or save a snapshot that could be used for next 24 hours?

Thanks in advance.

Upvotes: 1

Views: 486

Answers (2)

Jamie F
Jamie F

Reputation: 23809

As M Fredrickson suggests, subscriptions might work here depending on the number of different reports to be sent.

Another approach is to consolidate your data query to a single shared datasource. Shared datasources can have caching enabled, and there are several options for refreshing that cache, such as on first access or on a timed schedule. See MSDN for more details.

The challenge with a cached datasource is to figure out how to remove all parameters from the actual data query by moving them elsewhere, usually the dataset filter in the report, or into the filters of the individual data elements, such as your tablixes.

I use this approach to refresh a 10 minute query overnight, and then return the report all day long in less than 30 seconds, with many different possible parameters filtering the dataset.

You can also mix this approach with others by using multiple datasets in your report, some cached and some not.

Upvotes: 1

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

I would suggest going the route of subscriptions. While you could do some fancy hack to get multiple snapshots of a single report, it would be cleaner to use subscriptions.

However, since you've got 250 customers, and 10 different reports, I doubt that you'll want to configure and manage 2,500 different subscriptions within Report Manager... so I would suggest that you create a data driven subscription for each of the reports.

Upvotes: 0

Related Questions