Before you configure report distribution, you need to know which rendering clients can accommodate large documents. The recommended format is Adobe Acrobat Reader (PDF), but you can choose from any format that supports pagination. You can specify the format when you define how the report is distributed.
You can distribute reports via subscription — an important consideration for working with large reports. Through a subscription definition, you control how the report is distributed and rendered. You can use either a standard subscription or a data-driven subscription to deliver the report. You can also configure your subscriptions to be rendered as a PDF in a file share delivery. Use a desktop application to work with the report after it is generated. Set permissions on the file share to determine who can view the report. Note that after the report is on the file share, it is no longer controlled or secured by Reporting Services.
Using parameters and filters
Because the full set of data is retrieved and then filtered on the report server, the report may not perform as well as a report that filters data at the source using query parameters.
I note in Chapter 7 that for any report a query must be executed and the dataset regenerated each time you change a parameter that modifies the SQL query. It is possible to get better reporting performance by pulling a larger set of data into the dataset and then filtering the rows that will appear in the report.
When you request a report, Reporting Services merely retrieves and renders the snapshot. You then see the data and layout that were current for the report at the time the snapshot was created. As I mention in Chapter 10, using a snapshot approach helps to improve the overall performance of your reporting environment in a number of situations:
Caching in on performance
Caching is a performance-enhancement technique. To enhance report server performance, you can preload the cache. To preload the cache with a collection of parameterized report instances, you create a data-driven subscription that uses the Null Delivery Provider. Preloading the cache is achieved through a specialized rendering extension called the null rendering extension.
When you specify the Null Delivery Provider as the method of delivery in the subscription, the report server targets the report server database as the delivery destination.
This feature is especially useful if you want to cache multiple instances of a parameterized report in which different parameter values are used to produce different report instances. Note that you can only specify query-based parameters on the report. In contrast with other delivery extensions, the Null Delivery Provider does not have delivery settings that you can configure or drive through a subscription definition.
The previous tip was excerpted from Chapter 14, 'Optimizing Report Performance,' from the book "Microsoft SQL Server 2005 Reporting Services for Dummies" by Mark Robinson, courtesy of Wiley Publishing. Click here for the complete collection of book excerpts.
This was first published in July 2006