SQL Server Reporting Services Data Alerts

SQL Server 2012 introduced data alerts as a way to notify users of changes to report data, enabling them to view reports only when changes in the data may have business implications. By using this data driven alerting solution you no longer have to seek out information, it comes to you. 

Data alert messages are sent by email. Depending on the importance of the information, you can choose to send messages more or less frequently and only when results change. You can specify multiple email recipients and this way keep others informed to enhance efficiency and collaboration.

Good example of the data alerts can be sending out monthly sales report data only if total sales amount is less than a specified amount.

 

Data Alerts Key areas

  • To create data alerts on reports, the reports must be saved or uploaded to a SharePoint library. This means that you cannot create alerts on reports saved to a report server in native mode or your computer. Also, you cannot create alerts embedded in custom applications.
  • You can create data alerts on reports with data source configured to use stored credentials, or no credentials. You cannot create alerts on reports configured to use integrated security credentials or prompt for credentials. The report is run as part of processing the alert definition and the processing fails without credentials.
  • SQL Server alerting database is automatically created with Reporting Services in SharePoint mode install. Data alert definitions and alerting metadata are saved in the alerting database. By default, this database is named ReportingServices<GUID>_Alerting.
  • When the data alert definition saved, alerting creates a SQL Server Agent job for the alert definition. The job includes a job schedule. The schedule is based on the recurrence pattern defined on the alert definition. Running the job initiates the processing of the data alert definition.
  • An alert has only one owner, the person who created it. The alert owner can view information about your data alerts and delete and edit your data alert definitions
  • Alerting administrators, users with SharePoint Manage Alerts permission, can manage data alerts at the site level. They can view lists of alerts by each site user and delete alerts.
  • Reporting Services data alerts are different from SharePoint alert. SharePoint alerts on any document type, including reports. SharePoint alerts are sent when the document definition changes. Data alerts are sent when the data shown in a report satisfied rules in the alert definitions.
  • Multiple alerts on a report and multiple users can create the same or different alerts on a report
  • Report has to have data regions, to create an alert on it.

 

Here is Microsoft diagram for data alert definition

data alerts process

 

 Creating data alert definition

  1. Locate the SharePoint library that contains the report that you want to create a data alert definition for.
  2. Run the report. Verify that the report shows the data that you want to receive alert messages about. NOTE: The parameter values you chose to run the report are saved in the alert definition and will be used when report is rerun as a step in processing the alert definition. To use different parameter values, you must create a new alert definition
  3. On the Actions menu, click New Data Alert. The Data Alert Designer opens, showing the first 100 rows of the first data feed that the report generates in a table.

actions to create new data alert

4. Select data feed in the Report data name drop-down list.

  1. Click Add rule. In the list, select the column that you want to use in the rule, and then select a comparison operator and enter the threshold value.
  2. Select an option in the recurrence list. Depending on the type of recurrence, enter an interval.
  3. Enter the email addresses of alert message recipients. Separate addresses with semicolons. If the email address of the person who created the alert definition is available, it is added to the Recipient(s) box.
  1. Click Save.

 

data alerts scheduling

 

Data Alerts Maintenance

Once setup, alerts can be maintained by right clicking on the report requiring alert maintenance, and then selecting Manage Data Alerts 

The alert maintenance screen provides the following information: last run, status, sent alerts.  Alert can be edited to change items, such as, the rule criteria, the dataset, or the email recipient.

Reference

http://technet.microsoft.com/en-us/library/gg492251%28v=sql.110%29.aspx

http://technet.microsoft.com/en-us/library/gg492252.aspx

http://en.wikipedia.org/wiki/List_of_SMS_gateways

 

Leave a Reply

Your email address will not be published. Required fields are marked *