5 Ways To Use Event Driven Subscriptions With SSRS

I have previously discussed how Data Driven subscriptions in SSRS can be created using PBRS to deliver reports to various users and destinations, even when using SQL Standard Edition. In PBRS, SSRS reports scheduling can also be accomplished based on certain conditions. Once these conditions are met, then the associated SQL report will be triggered based on given parameters, and then delivered to a specified location. One could build an Event Driven system inside of SQL Server, however, PBRS provides the advantage of require little to no coding and shortened setup time. In other words, there is no need for you to provide the logic necessary to drive the report. Moreover, managing the logic and the subscriptions within PBRS is both easier and more efficient. There are a number of conditions that could be created with Event Driven Subscriptions. Let’s look a few that are built into PBRS.

Database Record Exists/Database Record Modified.

This condition can be used to trigger SQL reports based on records or record changes in your database. For example, the Accounting Department now has a need to send out invoices as soon as an order has be placed in the system. As opposed to running a batch of invoices each day, management wants invoices delivered to the customers in real time. With Event Driven Subscriptions, these invoices can be delivered to each customer in real time. Imagine your SSRS report (the invoice report) having a parameter such as orderID or Customer Number. The Event Based Schedule in PBRS will monitor a table or view in the SQL database for new orders. If an order appears, the Event Based schedule will run the report or invoice given the corresponding order number or customer number, then deliver the invoice to the customer’s corresponding email address, fax machine, and so forth. The Event Based Schedule can also monitor for changes in records. If a database record has been modified, PBRS will trigger the report; running the report given the altered parameters, report recipient, and more.

File Exists/File Modified.

In addition to monitoring databases, PBRS can also monitor file systems. PBRS will monitor a directory for the existence of a file, and trigger a report or task if necessary. This could be the directory on the PBRS machine, or another machine or file share. Moreover, PBRS can monitor an FTP site for changes in files as well. For example, A workflow requires that a certain set of files need to be in place prior to report execution. If these files are in place, the Even Based Schedule will trigger the report. Wild cards can be used  as well. In other words, a condition can be set to look for a specific file or files that have a certain extension, regardless of actual file name.

Process Exists/Window is Present.

PBRS also monitors processes on a machine. If a specific process exists, PBRS could trigger a report, or even perform other tasks such as running a program or script. If a window is present on the PBRS machine, PBRS can also trigger a report, send an alert or any other number of BPA tasks.

Unread Email is Present.

This is a very interesting Event Based Subscriptions condition. PBRS can monitor an inbox of your choice for a specific email or type of email. If the email exists, a report can be triggered. One would ask the question, “why would I want to monitor an inbox for SSRS reports scheduling?” Many PBRS users have built very unique processes with this this condition. For example, a rudimentary report request system can be built with this condition. A user would send an email to report system requesting a specific report be run with certain parameters, the system will interrogate this email for parameter values, and other information, then run the report given the email contents, then send the completed report back to the requested user.

Data is Received on a Port.

SSRS report scheduling can be handled using the “port listener” condition. PBRS will monitor a specific port or IP address for data. Based on the results PBRS can trigger a report with the given data. For example, field technicians must call into a system to update their jobs. Once they have completed their job update, a report must be sent. However this data exists only on a port. PBRS will monitor this port for data. If it is there, PBRS will trigger the report accordingly.

With these conditions, Event Driven Subscriptions have a variety of ways they can be triggered according to the situation at hand. Most users will find that the will most frequently use the database conditions for SSRS reports scheduling. Different conditions can be combined to create an extremely precise event driven report. Going beyond reports, the event based conditions can be used to automate other functions such as updating database records, triggering SSIS packages, and more.

Get Features PDFStart Free Trial