Share This Post

How to create SSRS Subscriptions for ConfigMgr 2007 – Part 1

SQL Server Reporting Services has many nice features. Perhaps one of the most powerful is the ability to create subscriptions to previously created reports. Subscriptions can take the form of data-driven, e-mail, file share delivery and/or integration with Sharepoint.

This series of articles will focus on how to define SSRS prerequisites for subscriptions, permissions, e-mail and file share delivery methods.
While primarily intended for ConfigMgr 2007 R2 administrators, this information applies to anyone using SSRS subscriptions.

Background SSRS Subscription Overview (from Books Online)

Subscription Overview

A subscription is a standing request to deliver a report at a specific time or in response to an event, and then to have that report presented in a way that you define. Subscriptions provide an alternative to running a report on demand. On-demand reporting requires that you actively select the report each time you want to view the report. In contrast, subscriptions can be used to schedule and then automate the delivery of a report.
Standard and Data-Driven Subscriptions

Reporting Services supports two kinds of subscriptions: standard and data-driven.

Standard subscriptions are created and managed by individual users. A standard subscription consists of static values that cannot be varied during subscription processing. For each standard subscription, there is exactly one set of report presentation options, delivery options, and report parameters.

Data-driven subscriptions are dynamic in that the presentation, delivery, and parameter values are retrieved at run time from a data source. You might use data-driven subscriptions if you have a very large recipient list or if you want to vary report output for each recipient. To use data-driven subscriptions, you must have expertise in building queries and an understanding of how parameters are used. Report server administrators typically create and manage these subscriptions. For more information, see Data-Driven Subscriptions.

E-Mail, File Share, and Custom Delivery

Subscriptions use delivery extensions to determine how to distribute a report and in what format. When a user creates a subscription, he or she can choose one of the available delivery extensions to determine how the report is delivered. Reporting Services includes support for e-mail delivery and delivery to a file share. Developers can create additional delivery extensions to route reports to other locations. Another delivery method is called the null delivery provider. This method is not available to users. Null delivery is used by administrators to improve report server performance by preloading the cache.

Parts of a Subscription

A subscription consists of the following parts:
1) A report that can run unattended (that is, a report that uses stored credentials or no credentials).
2) A delivery method (for example, e-mail) and settings for the mode of delivery (such as an e-mail address).
3) A rendering extension to present the report in a specific format.
4) Conditions for processing the subscription, which is expressed as an event.
Usually, the conditions for running a report are time-based. For example, you may want to run a particular report every Tuesday at 3:00 P.M. GMT. However, if the report runs as a snapshot, you can specify that the subscription runs whenever the snapshot is refreshed.
5) Parameters used when running the report.

Parameters are optional and are specified only for reports that accept parameter values. Because a subscription is typically user-owned, the parameter values that are specified vary from subscription to subscription. For example, sales managers for different divisions will use parameters that return data for their division. All parameters must have a value explicitly defined, or have a valid default value.

Subscription information is stored with individual reports in a report server database. You cannot manage subscriptions separately from the report to which they are associated. Note that subscriptions cannot be extended to include descriptions, other custom text, or other elements. Subscriptions can contain only the items listed earlier.

Let’s check that we have the prerequisites in place for subscriptions to be created.
1) Reporting Services is installed and running
2) SQL Server Agent is running – SQL Server Agent is the scheduling agent, so must be functional
3) SMTP is installed and configured
4) Using Reporting Services Configuration Manager, we have defined valid email settings (this will be the sender email)
In Part 2, I’ll describe how to properly setup report subscriptions and required permissions.

Share This Post

Specializing in System Center Products: Configuration Manager 2007, Operations Manager 2007, Desired Configuration Management, SMS 2003, software distribution, ITMU, SUS & WSUS. Database products and solutions: Microsoft SQL Server 2005 & 2008, Business Intelligence Development Studio, Analysis Server, Reporting Services, Integration Services. Microsoft Windows Server 2003 & 2008, SharePoint, PerformancePoint, Active Directory, DNS, Group Policy, Security, VBScript, ASP, Visual Basic, WMI, HTML and XML.

Leave a Reply