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