If you’re familiar with subscriptions in SQL Server Reporting Services, you may have wondered what the “Null Delivery Provider” is for. While it may seem odd to deliver the reports to a black hole, it does come in handy from time to time. In this post, I’ll show you how the “Null Delivery Provider” can help you increase performance by automatically creating cached reports. The basic idea is to create a data-driven subscription to run the report with a number of different parameter values to build the cached copies. This is similar to creating Execution Snapshots, but in this case we can specify a number of different sets of parameters, where as Execution Snapshots allow only one set of parameters. The “Null Delivery Provider” allows you to do this without having to save the reports somewhere, taking up valuable disk space, or have them emailed to someone, taking up valuable sanity.

In case you’re not familiar with cached reports, SSRS lets you create copies (or “caches”) of a report after the data has been gathered, but before the report has actually been rendered. When a user runs the report, it’s rendered from the cached copy which already has all the data. This means the queries don’t have to be run every time the report is generated. If you’d like to read more about report caching, there are some good articles on MSDN.

This example uses the Product Line Sales SSRS 2005 sample report. Everything for this example still works the same in SSRS 2008.

Step 1) Prepare Shared Data Sources

Data-driven subscriptions require data sources to store the credentials, rather than prompting the user or using Windows Authentication. Make sure that the “AdventureWorks” shared data source is using stored credentials.

Step 2) Gather Report Parameters

Create a query to generate the report parameters used in the subscription. A cached report is only used if the selected report parameters exactly match the parameters used to create the cached copy. By creating cached copies with a number of commonly used report parameter combinations, you increase the chances that users receive the cached copy. In this case, I’ve created a query that generates a number of combinations of Category and Subcategory parameters. (Note that I only use single values for Subcategory, although it is a multi value parameter).

Step 3) Enable Report Caching

Browse to the Product Line Sales report in Report Manager and click the Properties tab. Then click the Execution option on the left hand side of the window. Select the “Cache a temporary copy of the report. Expire copy of report on the following schedule:” option. Also select the “Report-specific schedule” option and click the “Configure” button to set up a schedule. Cached copies of the report will expire according to this schedule.

Step 4) Create Data-Driven Subscription

Now we can set up the data-driven subscription that will create the cached reports for us. Click the “Subscriptions” tab for the Product Line Sales report. Click the “New Data-driven Subscription” button to get started. (Note: If this button is disabled, you most likely haven’t set up the data source to use stored credentials.) Enter a description and select the “Null Delivery Provider” delivery option. Also select the “Specify a shared data source” option. Click next.

In the next screen, select the AdventureWorks shared data source from the Data Sources folder and click next. Then enter the query from above in the text area. This query returns the report parameters for the subscription. Click next. The Null Delivery Provider doesn’t have any options, so at the next screen you can just click next and move on.

The next screen allows you to set up the report parameters used by the subscription. Here, select the “ProductCategoryID” field from the query for the Category parameter. Select the “ProductSubcategoryID” field from the query for the Subcategory parameter. Use the default values for the start and end dates. The report will run with each pair of category and subcategory ids returned by the query. Click next to keep going.

The next screen allows you to set up a schedule for when the subscription is run. Make sure this schedule will run after the expiration schedule for the cached reports. If the reports are already cached when the subscription runs, it won’t actually do anything. The reports will be run from the cached copies, instead of creating new cached copies.

Step 5) Tell Your Boss You’re Awesome

That’s it. You’re done. And now users won’t have to wait for SSRS to retrieve the data every time they run the report. Time to go inform your boss that you are invaluable.

For Best Results…

This works best for reports that take some time to retrieve the data. For example, a report that pulls some or all of its data from a web service. If the report normally takes only a couple seconds to run, you aren’t going to notice much of a difference.

If the report needs the absolute latest data every time it’s run, this isn’t going to work well. It’s best for reports where the data changes on a predictable schedule. If it shows sales totals from the previous day, then you can run the subscription at the end of each day. If it’s current stock prices, caching the data isn’t your best option.

Follow

Related posts: