We have published a number of guides for scheduling SSRS reports to email, or data driving SSRS reports. Today let’s review how to schedule SSRS conditional subscriptions. Using Event Based Schedules in SQL-RD, you can build complex workflows that can trigger RS reports based on events or conditions, for example database record has changed; email has been received; file has been updated, etc. You can monitor for these conditions at certain intervals, or in real-time. If these conditions are met, then it will run specified reports and even BPM tasks. Using SQL-RD to schedule SSRS conditional subscriptions is actually quite easy. The real challenge is working through exactly how to structure the schedule and the tasks.
Let’s first start off with an example. Robert is the reporting admin for the Accounts team. A mandate has come down from the managing director with a goal to improve cash flow by 25% within the next quarter. What can the Accounts team do to contribute to this goal? Collect invoices faster. Using SQL-RD, as invoices are due, the Event Based Schedule can send them to customers immediately instead of waiting on the Accounts team to perform their weekly run. Moreover, if invoices are past due, the team can be immediately notified along with the customer. This is where you can schedule SSRS conditional subscriptions with the Event-Based Schedule.
To set this up in SQL-RD, first, start an Event Based Schedule. Name this schedule and provide a description. Hit next to continue to the conditions step. The conditions screen is where you will establish the criteria for your reports and tasks. You can have multiple conditions. There are a variety of conditions including if a database record exists/modified, if a file exists/modified if there is an unread email or even data on a port. Since we are looking for due invoices, we will monitor a database for new records. Simply write your query to a table or view. If the event based schedule returns any records (based on your criteria), then the schedule will trigger your task.
When you’ve finished creating your conditions, click next. You have the option to trigger a report, an existing schedule or a task. In this case, we will trigger a new SSRS report. Select the report that must be triggered. Using the table/view SQL-RD monitors, the report can be run given data found in each record. In the parameters tab, drag and drop the Event Based data inserts into the appropriate fields. In this case, our table would have invoice number parameter, and my table has an InvoiceID field in my view/table. Once done, SQL-RD will run a unique report for each record returned in our condition; each report will run with associated InvoiceID.
Setting the Destination
Next, set the destination for the report. In this case, we are sending the invoice to the relevant customer that has the due invoice. Just like configuring the parameters, drag and drop the Event Based data insert into the “To” field of our email. This ensures that the specific due invoice will be delivered to the corresponding email address. You can customize the email as well. If the fields exist in the table/view SQL-RD is monitoring, then you can customize the body of the email with the recipient’s name, or even point of data. For example, if the invoice is overdue, the email can include how many days the invoice is past due, or even how much is owed. Based on your data, the invoice can be sent to additional addresses or different destinations such as Fax. Finish the destinations, then add any additional tasks such as updating the database reflecting that the notification was run.
Now you have a schedule that will constantly watch for invoices that are due or past due. Customers that are more quickly notified about their account status will more than likely pay much more quickly. The faster invoices are paid translates directly to increased cash flow.