Reporting by Exception with Data Alerts

Business Reports are a fact of life. They provide essential information on how an organisation is running and so on a regular basis reports are produced and studied.

SQL Server Reporting Services (SSRS) allows reports to be run on demand; a user selects a report, enters the parameters and the report is executed and displayed straight away. It also allows scheduled reports; a report and parameters are selected and a schedule of when it should be executed, say weekly, is determined and the report is executed and usually emailed to the user.

On demand and scheduled reporting facilities are all fine but the end result is a report that user has to read which can be time consuming particularly if there are a lot of reports. In many cases a considerable amount of time would be saved if the user only had to look at a report if some exceptional condition occurs, such as items reaching low stock levels, or the occurrence of loss making orders, or a change in market direction, etc. This is reporting by exception.

SQL Server 2012 Reporting Services introduces an reporting by exception facility called Data Alerts which allows conditions to be associated with the data stream that feeds the report. If the conditions are true it runs and delivers the report to the user. If the conditions are false it does nothing. So the user only gets to see the report if some interesting happens. Note that: The Data Alert conditions are independent of the report itself; so for example the a report could be set to run if stock levels drop below 10, but the report shows all products regardless of stock level.

A few points about Data Alerts:

1.       They are only part of SQL Server 2012 and later

2.       They only operate when SQL Server Reporting Services is in SharePoint Integrated mode

3.       The report has to be configured with a Data Source that uses Stored Credentials

Consider a simple example report that shows product stock levels. This has been deployed into a Reports library on a SharePoint 2010 site that is running SSRS 2012 integrated mode.

The report just displays stock levels and takes a single parameter the LocationID. The report below shows the stock for LocationID 3.

Now imagine that the user only wants to see this report if the stock is below 10 for any of the products.

Parameters have to be defined and the report has to be executed before a Data Alert can be created using the Action menu for the port. This is a requirement because alerts are dependent on the parameters.

A Silverlight control allows the conditions to be defined and a schedule for how often to check:

Once an alert has been created the Data Alerts menu for the report in SharePoint can be used to Edit, Delete and Run Data Alerts:

Whether the alert executes it checks the data against the conditions and if the is result is true it runs the report and emails it to the recipient. If the result is false the report is not executed, as in the example above. You can add multiple alerts for the same report and even the same combination of parameters. It’s as simple as that.