The Power of Reporting Services Subscriptions – Data-driven Subscriptions – Null Delivery Provider

A very powerful feature of SQL Server Reporting Services is Data-driven Subscriptions. I’ve covered how to set up regular subscriptions using both Windows File Share and E-mail. Data-driven subscriptions give you additional flexibility to have recipients change or use a query to provide the parameters each time the subscription is run.

Note: to use Data Driven Subscriptions, you must have Evaluation, Developer or Enterprise version of SQL Server. This feature is not supported in Standard or Express. The user setting up the subscription must be part of a role that has the Manage All Subscriptions task – for example, Content Manager.

Data-driven Subscriptions – E-mail

Data-driven Subscriptions – Windows file share

Null Delivery Provider – The What and Why

In this post, I will show you how to set up a Data-driven Subscription using the Null Delivery Provider. But first, let me answer the important question: what is this, and why would you use it?

A feature of Reporting Services that can be used to improve performance is report caching. To sum up what could be a future post of its own: caching will save a copy of the report in the ReportServerTempDB, which reduces the time it takes for a report to render for a user. This can be very useful if you have a report that takes a long time to generate, and is run frequently with no parameters or the same parameters.

When you set up a report to use caching, the cache is created the first time a user runs it. You set the cache to expire after a certain amount of time. So, say you have a report that takes 5 minutes to generate, and 10 users will run it every day, with the first one coming in at 7:00 AM. You set the report to cache a temporary copy, and expire it every day at 7:00 PM. The first user would come in and run it, and it would take 5 minutes. Every other user that runs it between the time of the first user and the expiration would notice a significant decrease in rendering time.

But, how can you help the users by reducing the time it takes the first user to run it in the morning?

The answer is to set up a report subscription using the Null Delivery Provider. This saves a copy of the report in cache at a time you specify – in this example, perhaps 6:00 AM. When the first user comes in to run the report, the rendering time is reduced.

Data-driven Subscriptions

Open a report and go to the Subscriptions tab. Click “New Data-driven Subscription”.

Step 1

The first step is to establish a description, delivery method and data source for this subscription.

Description – provide a name, which you will see on the Subscriptions tab for the report. I usually put in something that tells me how often this subscription is run and who or where is it being delivered to.

Specify how recipients are notified – This will determine how the report is delivered.

• Choose Null Delivery Provider

Specify a data source that contains recipient information:

• Specify a shared data source – choose a data source that exists in the Reporting Services installation. I always use this option, as it makes administration much easier.
• Specify for this subscription only – you can set up a data source for this item only.

Step 2

If you chose to use a shared data source, you will see this screen, where you can navigate your folders to find your data source. I usually store mine in the “Data Sources” folder – that makes them easy to find!

If you choose to specify a data source for this subscription only, you will see this screen. Enter a connection string and credentials for your data source.

Step 3

In this step, you define the query that can return parameter values for you.

When using caching for a report, be aware that it works best when you have no parameters, or very few choices. Every distinct combination of parameters will create a different version of the report in cache.

I am going to set my start date, end date, and folder parameters for this report in this step.

You have the option to specify a time-out, in seconds.

You can click the Validate button to ensure the query will run against your data source.

Step 4

There are no delivery settings for Null Delivery Provider. The report is always “delivered” to ReportServerTempDB.

Step 5

If your report includes parameters, you will set them here. You can set a static value for each, or, as with the case below, choose a value from the query.

Step 6

The final step is to define when the subscription will be processed.

• When the report data is updated on the report server – the subscription will be processed when the snapshot is refreshed, if the report is set up to use a snapshot.
• On a schedule created for this subscription – create a schedule for this one report subscription.
• On a shared schedule – use a pre-defined schedule, created in Site Settings.

One thing to keep in mind with Null Delivery is that the copy of the report in cache must be expired before you create a new copy using this method. I would suggest setting up the report cache to expire on a specific schedule, shortly before this subscription runs.

This explanation of Null Delivery Provider is just one piece of report caching. Caching can be a way to increase performance for your users, if used correctly! Consider using both caching and data-driven subscriptions to improve your environment.

Now…off to celebrate my birthday!

Advertisement