Use SSRS Data Alert

 

SQL 2012 Reporting Services has data alerts capability.  It is a data driven alerting solution. You could use it to inform the users about reporting data that is important to them at a relevant time through email or SMS format.

Here are the steps to create a data alert:

• Open the report that you want to create the data alert.
• Click on the New Data Alert on the Actions drop down menu

• The Data Alert Designer window will open

• Edit the Alert Name
• Create the Alert Rules that are applicable/interested by the users by clicking on the (Add rules…) on the right side pane. 
• Select and set the data feed columns from the list that you will use to create the rule i.e. Low Water Count.Value

• Set the threshold i.e. is greater than 30

• You may change the option from Alert me if any data has:  to Alter me if no data has:

• Set the the time frequency that you want the alert to be sent at Schedule settings session.  Click on Advanced option to set the stop date or option for sending message only the alert result changed.

• Set the message recipients and other information in the Email settings session.

• Click on Save button to create the alert.

 

Manage the Data Alert

Once the data alert been created, you could manage/edit it using Data Alert Manager.

• To open the data alert manager, right click on the report and click on the Manage Data Alert menu.

• Right click on the alert name, you could select Edit or Delete the alert to modify it.
• You could also select Run option to execute the data alert.

Data Alert Manager also provides alert histories and status for review. When condition met, the system will send out the e-mail message and you will see the Sent Alerts Count increased.

Here is a sample data alert email.

It provides the actual data alert result and rules/report parameters used for the SSRS report.  In addition, there is a hyperlink in the message for use to review the report detail as needed.

Setup SMS Alert

If you like to send out the data alert to the mobile device with SMS format, you could use the SMS through email option. Different provider uses different SMS gateway.  Make sure you use the correct gateway address.  For example, if you have AT&T mobile number, you could set the PhoneNumber@mms.att.net or phoneNumber@txt.att.net in the recipients address.  When the data alert condition met, it will send a big text message to that phone number.

Depended on the provider you used, it is likely to be several of them. Here is a sample:

Requirements

To use the SSRS data alert, you must have the followings:

• SQL 2012 SSRS integrated mode.  It could be integrated with SharePoint 2010 or SharePoint 2013.
• Enable SQL Server Agent
• Enable SSRS email delivery option.
• Need Manage Alert SharePoint permission to setup/manage the data alert.
• Use stored credentials or no credentials for report data source.

If the New Data Alert option is grayed out, the report data source must be configured to use either integrated security credentials or prompt for credentials. To enable the option, you must set the data source to use stored credentials or no credentials.

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