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 SQL-RD. 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 “Grandaddy” of all schedule types.
Dynamic Report Subscription
Here is where the advanced features begin. A Dynamic Report subscription enables you to automate SQL reports that have multi-value parameters. Each parameter value will get its own unique file output and is then delivered to a specified user. Similar to Data Driven subscriptions in Reporting Services, this function reels through a database, populates the key parameter based on database values, and then delivers the report to a corresponding email address. The Dynamic schedule, however, goes further; it can link differing databases together without the need to write a view in SQL server. This is pretty handy when the distribution list that holds the key parameter values is in a separate database from the list of corresponding emails, folders, or SharePoint directories. You can also use the Dynamic schedule to send a static SSRS report to changing database list of recipients. In other words, the report parameters don't need to change, but the recipients do. In the reverse, the schedule can send a dynamic report to a static list of destinations. The report needs to run a number of parameter values but places them all in the same file directory.
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.
Even Driven Report Subscription
SQL-RD 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 SQL-RD. The schedule type you require largely depends on the type of report and how it must be distributed.