Data Alerts in SSRS 2012 (Only in SharePoint Mode)

SQL Server 2005  came with a particular component, it doesn’t came with a UI, all configurations were in the back-end (underground), but all the functionality were there, you could configure conditions to send and receive mails. That component was “Notifications Services”, but disappear in SQL Server 2008 and 2008 R2 until SQL Server 2012, it appear again, now with the name “Data Alerts”

When Notification Services disappear with the next version of SQL Server, Microsoft commented that the functionality will come back but integrated to Reporting Services component, but the question was When? In which version?

Now, in SQL Server 2012 Microsoft integrate in Reporting Services (only in SharePoint Mode) Data Alerts, it is the version that in old time was called Notification Services, now complementing SSRS as a new service similar to Subscribe, Export, Print, etc.

Data Alert enable you to send a mail alert when a condition is complied, the interesting is that the conditions are with data in the report, any object visualized with data (table, tablix, chart, etc) can be referenced for conditions. Comparing against specific values or to another column of the object.

Now, when you execute a Report in SharePoint, you have a combo Actions on the top with the option “New Data Alert”

You can see all the information for configure Data Alert in next picture:

1.       Report data name. List all the data names in the report

2.       Show the result set from the Report data selected. This columns and data will be the essential for the configuration of the Alert

3.       Here start the main configuration: “Alert me if any data has” or “Alert me if no data has”

4.       Conditions:

a.       Select any column from  the report data selected

b.      Use a condition, depending of type of data selected the condition are different, for numeric o string

c.       The 3th input value can be other column (from the same report name) or use a specific value

d.      Add Rule. You can add conditions like you need (And/Or)

5.       Schedule Settings. Configure when the condition is going to be evaluate

6.       Email setting. Destination if the condition is evaluate positive

When the alert conditions are positives the mail sent to recipients looks like the next

Just show rows where the condition was positive, in this case: “Total” columns is greater than 1000

To manage any Data Alerts configured before, you just need to drop down the options in the Report Library, where the reports were deployed:

Conclusions.

Pro.

1.       Is very grateful that Microsoft include Data Alerts en SQL Server 2012, the lost service in 2005 comes back

2.       Is pretty simple to configure a Data Alert (if you have already installed SharePoint mode)

3.       The SharePoint mode is really very powerful and easy to manage reports, deploys, Subscriptions and you can give just one integrate interface to users

Cons.

1.       Microsoft needs to work harder to put Data Alerts in optimal conditions:

a.       You can’t use “expressions” to configure conditions, this is an essential concept like in Reporting Services, this give us a huge possibilities and dynamic, for the moment is just comparing with other columns or a static value

b.      Just the visible columns can be part of the Data Alert, it is, hidden columns are not available, event when in the data set comes

c.       Data compares must belong to the same report data name, you can’t cross between different report data.

d.      You can’t format the columns in the mail