The Power of Reporting Services Subscriptions – Data-driven Subscriptions – E-Mail

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 each time the subscription is run, or use a query to provide the parameters each time the subscription is run.

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.

In this post, I will walk you through how to set up a Data-driven Subscription using E-mail.

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.

• E-mail – The report will be delivered via email.

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

This is the exciting step! Here, you can define a query that will return your list of recipients and/or values for parameters.

One way to use this: say you want to send a report only to customers that have ordered in the past month. You could write a query to get the customer’s email address based on order dates in the previous month.

Another example: I have reports that pull the email addresses from the database based on the user’s department in the company.

In the example below: I’m using this to set my report parameters with options that wouldn’t be available in a standard subscription. I do this when I don’t want to have a set of default parameters for the report, only for the subscription.

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

In this step, you will specify your email delivery information.

• To – who the email will be delivered to. Set a static email, or choose a value from your query.
• Cc – who the email will be cc’ed to. Set a static email, or choose a value from your query.
• Bcc – who the email will be bcc’ed to. Set a static email, or choose a value from your query.
• Reply to – by default, replies will go the account the reports are sent from. You can set a different email address here. Set a static email, or choose a value from your query.
• Include Report – you can set a static or dynamic option of True or False.
• Render Format – you can set a static option, get the value from your query, or choose no format.
• Priority – this sets the priority in Outlook. You can set a static option, get the value from your query, or choose no value.
• Subject – the subject line of the email. You can set a static subject, or get the value from your query.
• Comment – this will appear in the body of the email. You can set a static option, get the value from your query, or choose no value.
• Include link – determines if you want to include a link to the report in the body of the email. You can set a static option, get the value from your query, or choose no value.

There are so many creative ways to use this! Perhaps you want the report to have a Priority of High if sales are above or below a certain dollar value. The Subject could be dependent on any field in the query. Perhaps you don’t include the report and the link if certain criteria are not met.

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 your query in Step 3.

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.

Those are the steps to set p a data-driven subscription and have it delivered via email. These subscriptions provide a lot of flexibility and options. I encourage you to find a way to use these in your environment if the opportunity presents itself! I will cover Windows File Share and NULL Delivery Provider in future posts!