Continuing our series on PBRS and ways “out of system workflows” maximize SSRS’ capabilities, let’s explore exactly how PBRS can publish SQL Server reports. In the past, we have looked at why an administrator would want to schedule SSRS reports outside of SQL Server. Reasons ranged from increased flexibility, more export options, and report output security. In this article, we will run through setting up two of PBRS’s most basic scheduling functions: the Single Report Schedule and the Packaged Report Schedule. Though this article isn’t meant to be a fully fledged tutorial, it will give administrators feel for how PBRS can be used to publish SQL Server reports.
The Single Schedule is PBRS’s most basic form of SSRS report schedule. It involves calling on the desired SSRS report, setting the schedule for said report, specifying where the report will be delivered, and the format for the report. A single schedule is not unlike the basic report subscription in SSRS. However, there is some distinct difference we will discuss throughout this article. To setup, a scheduled SSRS report, open PBRS. Select “Single schedule” from the Home Ribbon near the top.
The Single Report Schedule wizard will appear. One of the great things about PBRS is that reports can be scheduled from various instances of SSRS. In the General section of the wizard, place the report service URL where the desired report is contained. The report service URL can be changed on a report by report basis. PBRS by default automatically pulls in the report’s name and description, however, this information can be change at any time. Clicking the triple dots, browse the Report Service URL for the report you wish to schedule.
Next up, it's time to set the schedule’s well, schedule. There is a ton of flexibility in determining the SSRS report’s timing. From daily, daily weekly or monthly reports, administrators can set the report to run whenever they wish. Custom calendars can be created as well.
SSRS report parameters are easily handled in PBRS. Simply enter the desired value for the report parameter, or select it from the drop-down list. PBRS will automatically run the report for the given parameter each scheduled time. In the case of performing multiple passes against a certain parameter, it would be best to look at using a Data driven schedule. Date parameters can be automated too! Using the floating inserts window, date values can be auto-calculated when the report runs. For example, if a report always needed to run for the current year, you can use the “current year” insert.
Next up, let's discuss the destination and format. There are a number of destinations SSRS reports can be sent to such as email, file share, and SharePoint. However, PBRS adds previously unavailable destinations such as Fax, SFTP, ODBC (sending the report to a database), Printer, Dropbox and even SMS. Also, PBRS adds an impressive array of report formats. Not only the PDF, Excel, and CSV are available, but reports can be outputted in other formats such as HTML. SSRS report naming can be customized as well. The file output can be renamed with a department code, customer name and more. A time or date stamp can be appended to the file name to help keep track of when the report is generated. As an added bonus to file output customization, administrators can add multiple layers of encryption to PDF and Excel files.
Finally, the last step is Custom Tasks. This tool enables you to create workflows before or after the report has generated. Tasks range from simple tasks such as opening a program or document, to powerful system tasks that grant users the ability to update SQL databases, create tables, run scripts and even trigger SSIS packages! For example, the SSRS report can be sent to SharePoint, then custom tasks can be set to send an email notifying the recipient that the report is ready. We will go a bit deeper into custom tasks in another article, but suffice it to say that custom tasks can certainly take your report scheduling to the next level!
Clicking finish, your SSRS report schedule has been completed. PBRS will run your report and deliver your report to its destination every day without fail! The package schedule works in a similar fashion to the Single Schedule, however, it allows you to batch multiple SSRS reports for distribution! As opposed to a user receiving multiple emails and files filled with various reports, the Package schedule sends all the desired reports in a single email, and can even combine multiple reports into a single PDF, Excel, or Text file. The Single Schedule and Package Schedule are PBRS’s more basic functions that enable you to publish SQL Server Reports outside of SSRS.