SQL Server Reporting Services: Create Data-Driven Subscription
In this SQL Server Reporting Services tutorial, business intelligence developers will create data-driven subscription in Reporting Services. Data-driven subscriptions enable SQL Server Business Intelligence developers deliver database reports in various formats in mass numbers with single task.
After SQL report developers create data-driven subscription in SQL Server Reporting Services, generally a stored procedure is used to populate the dataset which will be used as execution parameters of a Reporting Services report. For each record in the dataset, the SQL Server Reporting Services report will be executed seperately. Besides providing input parameters for the Reporting Services report, the dataset also defines email delivery properties of each report. For example, the dataset generally returns an email field. This email field is used for defining the email delivery "To" field which is the target e-mail address that the report will be send.
Go to the SQL Server Reporting Services folder where you want to create data-driven subscription. Choose the SSRS report and choose Manage from context menu.
When the report metadata is displayed, goto Subscriptions tab.
If you are dealing with a new report, you will find an empty subscriptions list.
In the report subscriptions tab, there is two butons which can be used to create new subscription, or to create new data-driven subscription.
Press "New Data-driven Subscription" buton to create a new data-driven subscription in Reporting Services 2008
The first step for creating a subscription for a report is to provide a descriptive text and how the reports will be delivered to the recipients.
Enter a name for subscription in Description field.
E-Mail is one of the options that can be used for defining how recipients are notified. This Reporting Services tutorial will give its sample on email delivery of database reports using data-driven subscription
One last configuration in this step is for defining the data source of the dataset which will drive the subscription. Actually, this datasource will help BI developers to connect to the datasource where the data of email adresses of recipients and required parameters for the report resides.
It is better to use a shared data source instead of creating a data source valid only for this subscription.
So continue with "Specify a shared data source" option instead of "Specify for this subscription only"
If you have choosen the option to use a shared data source object for selecting details about mass emailing of dynamic reports, the following data-source browser will help you to point the correct shared-data source saved on the SQL Server Reporting Services instance.
After defining the data source required for parameters for automatic report creation and required for delivery properties, it is time to fetch the data that will be used in the following steps.
If you read the firt text above the textarea, you will notice that you are requested to provide a command that will return a result set.
Specify a command or query that returns a list of recipients and optionally returns fields used to vary delivery settings and report parameter values for each recipient:
I used the following sql command, actually a SQL Server stored procedure call:
exec sp_Subscriptions_List
Here is the source code of sql stored procedure returning the list of recipients for this Reporting Services data-driven subscription tutorial.
CREATE Procedure sp_Subscriptions_List
as
select s.SubscriptionId, e.Email
from Subscriptions s
inner join SubscriptionCategories c on c.SubscriptionCategoryId = s.SubscriptionCategoryId
inner join Subscribers e on s.SubscriberId = e.SubscriberId
where
c.LatestContentDate > ISNULL(s.LastSentDate, '20110101')
and s.active = 1
and e.active = 1
and c.active = 1
If you continue to read:
The delivery extension settings and report parameter values can use field values returned by command or query.
If there are field values that map to these settings, include the fields in your command or query.
The delivery extension has the following settings: TO, CC, BCC, ReplyTo, IncludeReport, RenderFormat, Priority, Subject, Comment, IncludeLink, SendEmailToUserAlias
The report takes the following parameters: SubscriptionId
As you see in the above guide text, the first parameter list given is related with delivery attributes like recipient address, report render format, etc.
But the second parameter list belongs to the Reporting Services report that we are creating subscription.
The SubscriptionId parameter is what I created in sample Reporting Services report SubscriptionEmail for report layout and report data.
There is a time-out period which will fail the process in case the data source command fails to responde within given period of time in seconds.
Before you continue to next step, press "Validate" buton to execute the data source command, and populate a sample result set for future data-driven subscription steps. If the Validate buton displays an error message, you should correct sql command or sql query that you have entered into the above textarea before continue with next steps
In step 4, the recipients of the Reporting Services report subscription is defined.
It is possible to provide a static value for the report subscription recipient or use a dynamic method like reading from previous sql query.
It is meaningful to use a static e-mail address, if you will create let say sales reports of each representative but send these only to their managers. When static recipient is used for during data-driven subscription, all reports will be delivered to the same address. I remember I did this for a customer in US. The requirement was to create PDF documents of each report and place these reports on a shared folder where a third-party company can reach and process each report using PDF documents.
But in the SQL Server Reporting Services tutorial, I want to distribute each report to different recipients so I choose to "Get the value from the database" email field that the above SQL command or SQL query returns. This is a dynamic distribution of reports and makes the report data-driven subscription.
Other options like "Render Format" option can be static and you can choose from available Reporting Services rendering formats : Word, PDF, Data Feed, PRL Renderer, TIFF file, CSV (comma delimeted), HTML 4.0, MHTML (web archive), Excel or XML file with report data
Since I want to distribute sample subscription report via email, I chose MHTML. So report recipients will get HTML emails
If you read the rendering format dynamically from data source query that drives the subscription, you can define recipient A, B and C will get PDF reports. On the other hand recipient D will get report as TIFF file, etc.
Priority is an other option that can be configured at this step.
At this step Reporting Services developers can define the email subject dynamically or parameterized as follows:
@ReportName was executed at @ExecutionTime
@ReportName and @ExecutionTime are variables that has report name and the report execution time values in it.
This enables a dynamic subscription subject definition.
Unfortunately @ReportName and @ExecutionTime variables seem to be the only Reporting Services parameters that can be used to customize subject in report subscription.
Here is a short note that can be useful about this topic E-Mail Delivery in Reporting Services
Report managers can also define a static subject or read from the data source query using a subject field
Step 5 enables Report developers provide each report execution with related report parameters.
Since we want to create unique reports for each subscriber, the data source query should provide report parameters that the Reporting Services require.
In the sample Reporting Services report SubscriptionEmail, I'm using only one report parameter which I named @SubscriptionId
If in your case, you have 5 parameters, you should provide parameter values from your data source query for each report parameter.
Otherwise, Report Manager will not be able to render Reporting Services report successfully.
Using Step 6, Report Managers can define a schedule which will control how often and when the Reporting Services will execute the data-driven subscription process and start deliver email reports.
If you have chosen to use a schedule created for this subscription only, in step 7 Report Server managers can create the schedule and define its details here.
The subscription process schedule enables Business Intelligence developers to define very detailed and flexible schedules. It is possible to create schedules that will run with periods in minutes, hours, daily, weekly and monthly.
In this step you can define a schedule that will be valid in future. Besides the end date for the task can also be configured in this step
After the data-driven subscription in Reporting Services 2008 is completed, you will see a SQL Server job created in SQL Server Agent Jobs node. The name will not be description which is bad about SQL Server Reporting Services subscriptions.
Now if you go to Report Manager web site and check the target Reporting Services report subscription tab, you will see the new created subscription listed.
And here is a sample e-mail which was delivered to me. This is the output of this SQL Server Reporting Services subscription e-mail.
Right after the first subscription is processed, you can see the Last Run time and process Status on the Report Subscription tab.
One last note for SQL Server Reporting Services administrators and Business Intelligence developers, before you create the subscription and process the subscription task successfully it is important to configure E-mail Settings for SQL Server Reporting Services 2008 R2. The SMTP Server address should be correctly configured.
In order to configure e-mail settings, Reporting Services administrators can use the Configuration Manager tool following the menu path
All Programs > Microsoft SQL Server 2008 R2 > Configuration Tools > Reporting Services Configuration Manager
