Continuing our Series on SQL-RD and ways “out of system workflows” maximize SSRS’ capabilities, let’s explore exactly how SQL-RD 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 SQL-RD’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 SQL-RD can be used to publish SQL Server reports.
The Single Schedule is SQL-RD’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 are some distinct difference we will discuss throughout this article. To setup a scheduled SSRS report, open SQL-RD. Select “Single schedule” from the Home Ribbon near the top.
The Single Report Schedule wizard will appear. One of the great things about SQL-RD 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. SQL-RD by default automatically pulls in the report’s name and description, however this information can be change at anytime. Clicking the triple dots, browse the Report Service URL for the report you wish to schedule.
Next up, its 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 SQL-RD. Simply enter the desired value for the report parameter, or select it from the drop down list. SQL-RD will automatically run the report for the given parameter each scheduled time. In the case of performing a 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, lets 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 SQL-RD adds previously unavailable destinations such as Fax, SFTP, ODBC (sending the report to a database), Printer, and SMS. Also SQL-RD 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 TIFF. 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 a custom tasks can be set to send an email notifying the recipient that the report is ready. We will go a bit deeper in to 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. SQL-RD 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 SQL-RD’s more basic functions that enable you to publish SQL Server Reports outside of SSRS. Soon we will look at advanced schedules such as Event Driven or Data Driven systems that enable you to drive reports against databases or events. To learn more about how SQL-RD is used to schedule SQL reports, and how it augments SSRS’s capabilities, visit: http://www.christiansteven.com/solutions/sql-rd/
Republished by Blog Post Promoter
- See more at: http://www.christiansteven.com/how-to-publish-sql-server-reports-outside-of-ssrs/#sthash.xMSoZNBl.dpuf