Thursday, September 5, 2013

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

image

  • The Data Alert Designer window will open

image

  • 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

image

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

image

image

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

image

  • 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.

image

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

image

  • 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.

image

  • 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.

image

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.

image

Here is a sample data alert email.

image

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.

image

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

image

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.

image

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

6 comments:

  1. Well said.
    Very timely Post.
    Very remarkable topic to talk about
    Very remarkable point
    Truly a remarkable subject to talk about
    Thumbs up for a fantastic

    website advertising


    ReplyDelete
  2. I am trying to use Data Alerts with SSRS 2012 in Sharepoint integrated mode. When I choose "New Data Alert", it gets stuck in an infinite loop saying "Retreiving data feed names, please wait...".

    Do you have any suggestions to resolve this issue? Thanks for sharing your knowledge!

    ReplyDelete
  3. Product data feeds can be complex to setup properly, but once you make an initial investment, it will save you money down the road. product data feeds

    ReplyDelete
  4. Hi, I just wanted to verify that this feature is only available in sharepoint integrated mode, correct?

    ReplyDelete
  5. Is there a way to only have users see and manage their own data alerts? If you give the "manage data alerts" permissions level in site settings it will give users the ability to manage all data alerts on the site not just the ones they have created. When my read only user tries to go to "mydataalerts.aspx" it says that they do not have access.

    ReplyDelete
  6. These ways are very simple and very much useful, as a beginner level these helped me a lot thanks fore sharing these kinds of useful and knowledgeable information.
    Mobile Marketing Services
    Mobile Marketing Companies
    Digital Mobile marketing

    ReplyDelete