ChristianSteven Software Business Intelligence Blog

Triggering Threshold Alerts with SQL Server Reporting Services (SSRS)

Microsoft's SQL Server Reporting Services (SSRS) does a fine job of publishing reports and making them available on a timed schedule or on-demand. But, in recent years, are you finding that managers and customers are become more specific about when they want to receive their reports? Are they asking for reports to be triggered, not at a set time or day, but when a business event occurs?  Event Driven Reporting | SQL-RD | SQL Server Reporting Services | SSRS

Threshold alerts are all about notifying someone, or doing something. when certain values (i.e. inventory, cash flow, activity, etc.) either exceed or drop below a specific point. For example, a warehouse requires that it has at least fifty widgets on hand at all times. If inventory ever drops below this point, then the procurement specialist must be immediately contacted. Or, if the inventory drops below fifty widgets, then automatically order enough widgets to bring the inventory back into compliance. This is essence of threshold based alerting. For techies, this is principally reacting to a database record change; an insert, update or delete event.

SQL-RD has a specific schedule type that assists with this. The Event Based schedule can monitor a database, file system, port or email inbox. Based on a certain set of conditions, the software will trigger a series of reports, alerts, or tasks. In the case of our widget warehouse example, an Event Based Schedule would be set to monitor the widget inventory database. If the supply of widgets falls below fifty, then the software will send both an email (with or without a report attachment) and text message to procurement manager stating the desired item and how much needs to be ordered.

It is possible to use the Event based schedule to automatically order more widgets as well. This largely depends on how your inventory and ordering system works. But, you could, for example, automatically email or Fax an order to the supplier, or perhaps upload a CSV file to the supplier's FTP site. Depending on the relationship with the supplier, SQL-RD can even be used to directly update the supplier’s database with the low inventory data. Ultimately the desired outcome is that the supplier would know item is low, and how much was needed to replenish the stocks. Threshold alerts allow you to inject massive business benefits through the use of report automation in SQL Server in conjunction with SQL-RD.

The same concept can be applied to medical inventory. Guy’s and St. Thomas Hospital in the United Kingdom uses Event Based schedules to automatically notify suppliers when orders are overdue. Using SQL-RD, there are an untold number of applications for threshold alerts, and even more methods for creating workflows in the event that thresholds are reached or surpassed. 

Learn more:

Learn More About SQL-RD

Topics: sql server reporting services ssrs sql-rd ssrs reports data driven subscriptions business intelligence software business intelligence reporting data driven subscription Microsoft Business Intelligence schedule ssrs report ssrs subscription ssrs subscriptions schedule sql report schedule ssrs reports schedule sql reports