#10 Tip: Report Alerting

Since the first release of SQL Server Reporting Services back in 2004, there has been the ability to automate the execution and delivery of reports with subscriptions. Commonly reports are delivered by email or to the file system, and there is an extensibility capability to allow developers to create custom delivery channels.

There are two types of subscriptions: standard and data driven. Standard subscriptions allows a user to configure and schedule a report delivery to themselves. Data driven subscriptions, as the name implies, retrieves a query result (based on any valid query), and each row in the query result is used to execute and deliver a report to, for example, a specific email address. A single data driven subscription could send reports to many recipients. Note that data driven subscriptions are only available in Business Intelligence and Enterprise editions of SQL Server.

Email subscriptions can be used to easily alert audiences with reports. A standard subscription will always run according to its configured schedule, and so is probably not a good choice to use as an alerting mechanism. The data driven subscription, however, can be configured to use a query that only returns rows when alert conditions are met. It is possible that a data driven subscription query will return zero rows and therefore will not execute or deliver any reports, and it is also possible to schedule the subscription to run at frequent intervals, perhaps every minute.

A new approach to drive alerts notification with Reporting Services was introduced in SQL Server 2012. The data alerts feature was designed to specifically address alert notifications by email. Providing Reporting Services is integrated with SharePoint, users can configure alerts that will automate notifications. The feature must be setup by an administrator, but is intended to be used by business users.

Working with report subscriptions and data alerts is covered in the Microsoft BI Academy “Zero to Microsoft Self-Service Business Intelligence course, written and presented by the author of this article.