Using SQL-RD to schedule SQL reports? If so you are already pretty familiar with SQL-RD’s powerful schedule functions such as the Event Driven scheduler or the Data Driven Subscription scheduler. These functions are powerful tools that enable you to dynamically send a report to various users, perform various business process management tasks, and much more. However, there are a number of tools in the SQL-RD toolbox that can make the scheduling experience even better.
These little-known features don't often get the limelight, however, they make SQL-RD into a highly robust, dynamic report and task scheduling system. Let’s look at how to get extra umph out of your SSRS Reports.
This is a very simple feature that can come in handy when you schedule SQL reports. Custom calendars enable you to create a specific calendar that a report will run by. For example, an SSRS report needs to run 3 times in one March, 4 times in April, but only once in May. There is no specific interval to this schedule; the dates are determined by management needs. The SQL-RD administrator can create a Custom Calendar that runs the report on those specific dates. Custom Calendars give users the flexibility to schedule reports even when SQL-RD doesn't have a way of calculating the interval. Alternatively, a Custom Calendar can be used as an Exception Calendar. For example, an SSRS report is delivered to customer’s every day. However, the customer should not receive the report on national holidays. With Custom Calendars, the administrator can set an exception calendar that will not run the report on the holidays they choose.
Well, this feature is actually pretty well known if you are an Event-Based Schedule or Data-driven Schedule user. Inserts give you the ability to place constant or dynamic values into report parameters, email messaging, or customizing the report’s destination. What is not very well known is all the various applications for inserts. For example, a date can auto-calculated for date parameters. A user can select “CurrentDate” as an insert, and simply drag and drop it into their date parameter field in SQL-RD. Now the report will run every day with data from that current day. Insert’s usefulness doesn't stop there. Combining Inserts with the Event Driven system and the Database Custom Actions can create an automated database update workflow. Do you have your own formula that needs to be placed in report parameters, database records, or anywhere else? Inserts can do that too. Inserts can even be used to create default email signatures and messaging as well. One could argue that inserts are the life blood of SQL-RD!
Multiple SMTP Servers
With this feature, you can have SQL-RD send emails from differing email accounts. For example, the HR Department wants all HR related reports to be delivered from HR@Acme.com, versus the Sales Department wants all reports to be delivered from their Sales@Acme.com account. With Multiple SMTP servers this is accomplished. This feature makes it much easier to serve reports for multiple departments without the emails coming from a cryptic email address. One can also use this feature, to send emails various individual accounts, making the experience a bit more personal for the recipient.
Multi-threading and Collaboration
This is probably the most powerful feature discussed in this article. SQL-RD has the ability to process up to 8 reports at the same time with Multi-threading. This ensures that reports are being churned out to users as quickly as possible. This also allows you to throttle the load on your SQL database by limiting the number of reports that can be generated at one time by reducing the thread count. Collaboration allows administrators to farm out multiple SQL-RD servers and assign various reports to different SQL-RD clients. With report processing being distributed across multiple SQL-RD installations, there is no need to worry about certain reports slowing down or holding the reporting process. Combining Multi-threading with Collaboration brings out even more power. Each SQL-RD instance gets 8 threads. In other words, when using collaboration, with 2 SQL-RD instances you can run 16 reports simultaneously. With 4 SQL-RD instances, 32 reports can be processed simultaneously! There is no limit to the number of SQL-RD servers that can collaborate.
This little-known feature allows you to see what reports are running when in a calendar view. It is helpful to have a tool where you can see when certain schedules are set to run, helping you manage scheduling load and understand where possible bottlenecks can occur. Via the outlook’s Gant Chart view, you can see how long it takes for a schedule to complete tasks. SQL-RD outlook gives you the ability to effectively plan how your reports and tasks are deployed and adjust your plans as necessary.
These features may not present the same “wow” factor that SQL-RD’s powerful schedule types provide, but they do help make the scheduling process more efficient. From inserts ability to customize every part of your report, to multithreading scalability, these tools can help solve a number of challenges that may arise. These are the only tools in the toolbox, however. There is much more to explore inside of SQL-RD!