Report publishing can often be a chore for SQL server administrators. Users constantly requesting reports or the unending duty of managing report subscriptions. There is a way to end this suffering. No, we are not talking about a career change. The only way to release this burden is automating SQL reports! When using a third party program, it can actually be easy, and if you are nerdy enough, fun! Using an external tool such as SQL There are a few ways to automate your SSRS reports.
Single Report Subscription
This is the most basic schedule type found in PBRS. It involves specifying your SSRS report, setting the execution time, report format, and the destination of the report. All of the schedule types discussed in this article will share this basic concept. As basic as the Single Report Schedule is, there are still a great many features built into it. Report parameters such as date parameter types can be auto-calculated using inserts. Blank report detection ensures that a report containing no data is not sent to a recipient. The Single Report Subscription also has a package variant that enables you to batch multiple SSRS reports and distribute them to users. Moreover, a group of reports can be merged into a single text, PDF, or Excel file. Most of the schedule types discussed in this article will share these features, but the Single Schedule is truly the “Granddaddy” of all schedule types.
Data Driven Report Subscription
Many refer to this function as the “Dynamic Schedule on Steroids.” This function performs a similar action as the Dynamic Report Subscription; reeling through a database, populating the key parameter, and sending the report to a corresponding email address. However, the Data Driven Schedule can customize every part of the report distribution process. Using your database, report format, report destination, database settings, and multiple parameters can all be customized based on records in your table or view. Even the messaging behind the report’s email can be customized. For example, using a data driven subscription, a unique email containing the unique report can be sent to each record. The body of the email can be customized to included the recipient’s name or possible points of data that preview the report.
Event Driven Report Subscription
PBRS not only can automate SQL reports based on a regular time schedule, but it can also trigger SSRS reports based on events. Event Driven Subscriptions can monitor for certain conditions such as changes in a database, file system, processes, or an email inbox. If the conditions are met, the schedule will trigger the SQL report. This can all occur in real time. The report can even reflect the changes in the database that occurred. This enables you to build extremely complex report workflows without the need for you to build any logic yourself. Other condition options include monitoring a port or IP address for data, or watch for certain windows to be present on the machine.
All of these subscription types have a package variant where you can batch and merge multiple SSRS reports. There are many different ways to process reports either within SSRS or using a 3rd party system such as PBRS. The schedule type you require largely depends on the type of report and how it must be distributed.