Subscriptions | MicrosoftВ® SQL Server(TM) 2005 Reporting Services Step by Step (Step by Step (Microsoft))

Up to this point, we have discussed only one way for users to receive reports. They log on to the Report Manager site, find the report they want, and execute it, which is known as pull technology. The user pulls the information out of Reporting Services by initiating the execution of the report.

Reporting Services also supports push technology for delivering reports. In a push technology scenario, Reporting Services initiates the execution of the report, and then sends the report to the user. This is done through the report subscription.

Standard Subscriptions

Reporting Services supports several types of subscriptions. The first is the standard subscription, which is a request to push a particular report to a particular user or set of users. The standard subscription is usually a self-serve operation. A user logs on to the Report Manager site and finds the report they want. The user then creates the subscription by specifying the schedule for the push delivery and the delivery options.

Standard subscriptions have two delivery options: e-mail and file share. The e-mail delivery option, of course, sends an e-mail to the specified e-mail addresses with a link to the report or with the report itself either embedded as HTML or as an attached document. The file share option creates a file containing the report in a specified folder on a file share. The file share option can be used to place the report into a document store managed and/or indexed by another application, such as Microsoft’s SharePoint Portal Services.

Creating a Standard E-Mail Subscription with an Embedded Report

You have been hired as the traffic manager for Galactic Delivery Services and are responsible for routing transport traffic. As part of your job, it is important to know what the weather is like at all the hubs. Rather than taking the time to go look at the Weather report on the Report Manager website, you want to have the report e-mailed to you hourly.

Open the Report Manager and navigate to the Chapter 08 folder.
Click the entry for the Weather report.
Select the Subscriptions tab. The Create/View Subscriptions page appears.
Click New Subscription. The Subscription Properties page appears, as shown in Figure 11–9.

Figure 11–9: The Subscription Properties page
The Delivered By drop-down list defaults to Report Server E-Mail. Leave this set to the default setting.
Type your e-mail address for To. Note, you can enter multiple e-mail addresses, separated by a semicolon (;), and you can also enter e-mail addresses for Cc and Bcc.
Enter an e-mail address for Reply-To. This can be your own e-mail address, someone else’s, or a dummy e-mail address that does not even exist.
By default, the subject of the e-mail is the name of the report, followed by the time the report was executed. Change Subject to @ReportName.
Leave the Include Report check box checked. This includes the report in the e-mail. Uncheck the Include Link check box.
The Render Format drop-down list defaults to Web Archive. Leave this selected.
Select High from the Priority drop-down list.
For Comment, type This e-mail was sent from Reporting Services.
For Run the Subscription, select the option When the Scheduled Report Run Is Complete.
Click Select Schedule. The Schedule page appears.
Select Hour.
Leave the schedule to run every 1 hour and 00 minutes. Set the start time to five minutes from now.
Today’s date should be selected for Begin Running This Schedule On.
Check Stop This Schedule On and select tomorrow’s date.
Click OK to return to the Schedule Properties page.
Note the default parameter values for this report appear in the Report Parameter Values section of this report. If necessary, you can specify parameters to use when running this subscription. Leave the parameter set to its default.
Click OK to create this standard subscription and return to the View/Edit Subscriptions page.

After the time specified by your schedule has passed, refresh this page. You should see the time of the execution in the Last Run column and Mail Sent To followed by your e-mail address in the Status column. You should also have a high-priority e-mail waiting for you in your mailbox.
Do not delete this subscription until you have had a chance to look at the My Subscriptions page in the section “My Subscriptions.”

Creating a Standard E-Mail Subscription with a Report Link

You have just been promoted to sales manager for the Axelburg office of Galactic Delivery Services. Congratulations! Being a good manager, you want to keep tabs on how your salespeople are doing. To do this, you want to view the Invoice-Batch Number Report each week to see how much you are invoicing your clients. As a memory aid, you want to receive an e-mail each week with a link to this report.

Open the Report Manager and navigate to the Axelburg folder.
Click the Invoice-Batch Number Report to execute it.
Click New Subscription in the toolbar for the View tab. The Subscription Properties page appears.
Delivered By defaults to Report Server E-Mail. Leave this as the default setting.
Type your e-mail address for To.
Enter an e-mail address for Reply-To.
Change Subject to @ReportName.
Uncheck the Include Report check box. Leave the Include Link check box checked.
Render Format is not used because we are just embedding a link to the report.
Select High from the Priority drop-down list.
For Comment, type Remember to check the invoice amounts.
For Run the Subscription, select the option When the Scheduled Report Run Is Complete.
Click Select Schedule. The Schedule page appears.
Select Week.
Leave Repeat After This Number of Weeks set to 1.
Check Today for On day(s). For example, Check Mon if today is Monday. Uncheck all the other days.
Set the start time to five minutes from now.
Today’s date should be selected for Begin Running This Schedule On.
Check Stop This Schedule On and select tomorrow’s date.
Click OK to return to the Schedule Properties page.
At the bottom of the Schedule Properties page, you see a list of the parameters for the selected report. Leave the default values for the parameters.
Click OK to create this standard subscription and return to the Report Viewer page.

When the scheduled time has passed, you will receive an e-mail with a link to this report.

Standard Subscriptions and Execution Snapshots

In addition to creating your own schedule for your standard subscriptions, you can also synchronize your subscriptions with scheduled execution snapshots. For example, the Weather report is set to create an execution snapshot every hour. We want to receive an e-mail with the new version of the report after each new execution snapshot has been created.

One way to do this is to keep the schedule for the execution snapshot synchronized with the schedule for the subscription. The execution snapshot runs, and then the subscription runs one minute later. This can cause problems if the execution snapshot occasionally takes more than one minute to create or if one of the schedules is edited.

A better solution is to let the creation of the execution snapshot drive the delivery of the subscription. The When the Report Content Is Refreshed option does just that (refer to Figure 11–9). When this option is selected for a subscription, the subscription is sent out every time a new execution snapshot is created. Of course, this option is only available for reports that have execution snapshots enabled.

Multiple Subscriptions for One Report

Nothing prevents a user from creating more than one subscription for the same report. Perhaps you want a report delivered every Friday and on the last day of the month. You can’t do this with one subscription, but you can certainly do it with two—a weekly subscription for the Friday delivery and a monthly subscription for delivery on the last day of the month.

Another reason for multiple subscriptions is to receive a report run for multiple sets of parameters. You saw it is possible to specify parameter values as part of the subscription properties. Using this feature, you could have one subscription send you a report with one set of parameters and another subscription send you the same report with a different set of parameters.

Embedded Report versus Attached Report

When you choose to include the report along with the subscription e-mail, the report can show up either embedded in an HTML e-mail or as an attached document. If you select the Web Archive format, the report is embedded. If you select any of the other render formats, the report is sent as an attached document.

Having the report embedded in the e-mail makes it convenient for the user to view the report: it is simply part of the body of your e-mail. However, not all e-mail packages support HTML e-mail, so some users might be unable to view an embedded report. If a user is unsure of the capabilities of their e-mail package, they should choose the Acrobat (PDF) file format. This format is sent as an attachment and can be viewed by just about anyone.

Standard Subscriptions and Security

Not all users can create standard subscriptions. In fact, it is possible to view a report, but not be able to subscribe to it. To subscribe to a report or create a subscription for delivery to others, you must have rights to the Manage Individual Subscriptions task. Of the four predefined security roles, the Browser, Content Manager, and My Reports roles have rights to manage individual subscriptions.

Managing Your Subscriptions

An active user may subscribe to a number of reports scattered throughout a number of folders. Just remembering all the reports you subscribed to can be a big challenge. Managing all those subscriptions can be even tougher. Fortunately, the Report Manager provides a way to view all your subscriptions in one place.

My Subscriptions

The My Subscriptions page consolidates all your standard subscriptions in one place.

Click the My Subscriptions link at the top of the page. The My Subscriptions page appears, as shown in Figure 11–10.

Figure 11–10: The My Subscriptions page
You can click on any heading to sort your list of subscriptions.
Click the Edit link next to WeatherReport. The Subscription Properties page appears.
You can make changes to this subscription, if you desire. Click Cancel to return to the My Subscriptions page.
Click the WeatherReport link in the Report column. You jump to the Weather report.
Click your browser’s Back button.
Click the text in the Folder column for the Invoice-Batch Number Report. You jump to the Axelburg folder.
Click your browser’s Back button.

The My Subscriptions page lists all the standard subscriptions you created on this Report Server. This makes the subscriptions much easier to manage. You can sort the list several different ways to help you find and manage the subscriptions. You can also use the My Subscriptions page to delete unwanted subscriptions.

Let’s delete these subscriptions, so you do not waste computing power e-mailing reports.

Check the check box in the headings. This automatically checks the check box next to each subscription.
Click Delete in the toolbar.
Click OK to confirm the deletion.
Click the Home link at the top of the page. You return to the Home folder.

Data-Driven Subscriptions

A better name for a data-driven subscription might be “mass mailing.” The data-driven subscription enables you to take a report and e-mail it to a number of people on a mailing list. The mailing list can be queried from any valid Reporting Services data source. The mailing list can contain fields, in addition to the recipient’s e-mail address, which are used to control the content of the e-mail sent to each recipient. As mentioned in Chapter 2, the Enterprise Edition of Reporting Services is required for you to use data-driven subscriptions.

Creating a Data-Driven Subscription

Transport 1305 has been acting up. GDS wants all its mechanics to have a good background on the types of problems this transport is having. To facilitate this, the results from the Transport 1305 Monitor report should be e-mailed to all mechanics every four hours. Employees holding the position of Mechanic I should receive the report as a high-priority e-mail. Employees holding the position of Mechanic II should receive the report as a normal-priority e-mail.

Open the Report Manager and navigate to the Chapter 08 folder.
Click Show Detail.
Click the icon in the Edit column for the Transport 1305 Monitor report.
Select the Subscriptions tab.
Click the New Data-Driven Subscription button. The first page of the Data-Driven Subscription process appears, as shown in Figure 11–11.

Figure 11–11: Data-Driven Subscription process, first page
Type Maintenance Watch on Transport 1305 for Description.
Select Report Server E-Mail from the Specify How Recipients Are Notified drop-down list.
Select the Specify a Shared Data Source option.
Click the Next button. The Shared Data Source page appears, as shown in Figure 11–12.

Figure 11–12: Data-Driven Subscription process, Shared Data Source page
Use the tree view to find the 2006 Conference folder in the Galactic Delivery Services folder.
Select the Galactic shared data source in the 2006 Conference folder.
Click the Next button. The Query page appears, as shown in Figure 11–13.

Figure 11–13: Data-Driven Subscription process, Query page
Type the following for the query:
```
 EXEC stp_MechanicMailingList
```
Click Validate to make sure you don’t have any typos or other problems.
If the query does not validate successfully, look for the error in the query you typed. Otherwise, click Next. The Data Association page appears as shown in Figure 11–14. Here, you can associate columns in the result set with fields in the subscription e-mail.

Figure 11–14: Data-Driven Subscription, Data Association page

Set the following properties on this page:

Property	Value
To	Specify a static value
Specify a static value (For To)	(Type your e-mail address here. Normally; you would select the e-mail address from a database field, but we want to have a valid e-mail address for our example. Because your system cannot send interplanetary e-mail, we have to use your e-mail address.)
Reply-To	Specify a static value
Specify a static value (Reply-To)	Reports@Galactic.SRA
Render Format	Specify a static value
Specify a static value (Render Format)	Acrobat (PDF) file
Priority	Get the value from the database
Get the value from the database (Priority)	Priority
Subject	Get the value from the database
Get the value from the database (Subject)	Subject
Include Link	Specify a static value
Specify a static value (Include Link)	False

Click the Next button. The Parameter Values page appears, as shown in Figure 11–15.

Figure 11–15: Data-Driven Subscription, Parameter Values page
Leave this page set to the defaults. Click the Next button. The Notify Recipients page appears, as shown in Figure 11–16.

Figure 11–16: Data-Driven Subscription, Notify Recipients page
Select the On a Schedule Created for This Subscription option.
Click the Next button. The Schedule page appears.
Select the Hour option.
Change the schedule to run every 4 hours 00 minutes.
Set the start time to five minutes from now.
Today’s date should be selected for Begin Running This Schedule On.
Check Stop This Schedule On and select tomorrow’s date.
Click Finish.
Once the scheduled time for your subscription has passed, refresh this page. You should see the time of the execution in the Last Run column and Done: 8 processed of 8 total; 0 errors in the Status column. You should also receive eight e-mails (eight mechanics are in the database, and we sent an e-mail to each one) with the Transport 1305 Monitor report attached.
If you do not want to receive eight e-mails every four hours for the next day, you can delete this subscription.

Data-Driven Subscriptions and Security

Not all users can create data-driven subscriptions. To create a data-driven subscription for a report, you must have rights to the Manage All Subscriptions task. Of the four predefined security roles, only the Content Manager role has rights to this task.

Data-Driven Subscriptions and Event-Driven Behavior

You can do a couple of tricks with data-driven subscriptions that make them even more powerful. For instance, at times, you might not want a subscription sent out until after a certain event has occurred. For instance, you may want to e-mail a report to a number of recipients after a specific data update process has completed. While a data-driven subscription is a scheduled process, rather than triggered by a particular event, we can make it behave almost as if it were event-driven.

You need a field in a status table that contains the completion date and time of the last data load. You also need a field in a status table that contains the date and time when the report was last distributed. With these two flag fields in place, you can simulate event-driven behavior for your data-driven subscription.

First, you need to build a stored procedure that returns the mailing list for the report distribution. To this stored procedure, add logic that checks the date and time of the last data load, and the date and time of the last report distribution. If the data load is complete and the report has not yet been distributed today, the stored procedure returns the mailing list result set. If the data load is incomplete or if the report has already been distributed today, the stored procedure returns an empty result set.

Now you create a series of data-driven subscriptions based on this stored procedure. If the data load completes sometime between 1:00 A.M. and 3:00 A.M., you might schedule one data driven to execute at 1:00 A.M., another at 1:30 A.M., another at 2:00 A.M., and so on. When each data-driven subscription executes, the stored procedure determines whether the data load is complete and whether the report was already distributed. If the stored procedure returns a result set, the data-driven subscription e-mails the report to the mailing list. If the stored procedure returns an empty result set, the data-driven subscription terminates without sending any e-mails.

This same approach can be used to e-mail reports only when the report data has changed. You create a stored procedure that only returns a mailing list result set if the data has changed since the last time the report was e-mailed. This stored procedure is used to create a data-driven subscription. Now the data-driven subscription only sends out reports when the data has changed; otherwise, it sends nothing.

Data-Driven Subscriptions and Report Caching

If you looked closely, you may have noticed that the Specify How Recipients Are Notified drop-down list included the entry Null Delivery Provider. This doesn’t seem to make much sense—why would you create a subscription and then not send it anywhere? This Null Delivery Provider is used to support report caching.

Suppose you have a report with a number of possible report parameter combinations that would benefit from caching. As you have seen, report parameter values must match for a report to be pulled from cache. How do we create cached copies of the report with all the possible parameter combinations? The answer is a data-driven subscription using the Null Delivery Provider.

The first step is to create a query that returns all the possible report parameter combinations (or at least the most popular ones) for this report. You then use this query to create a data-driven subscription to execute the report with each of these parameter combinations. If report caching is enabled, the data-driven subscription would cause a copy of the report to be cached with each of these parameter combinations. This is true even if the Null Delivery Provider is used and the report is never delivered anywhere by the subscription. Because the subscription created all these cached copies with the various parameter value combinations, no matter what combination of parameters a user enters the following day, the report is rendered from a cached copy.