How do I add new SSRS reports to Event Based Schedules in SQL-RD?

Using an Event Based schedule, you can trigger an existing schedule, or a new report, based on events. New reports allow you to use "insert" data from the event that triggered the schedule.

New Reports

New SSRS Report in Event Based Schedule using SQL-RD
  • Select New Reports and click Next.
1New SSRS Report in Event Based Schedule using SQL-RD
  • Click Add.
  • Report Properties will appear.

Report Properties

Report Wizard

New SSRS Report Properties Wizard in Event Based Schedules in SQL-RD
  • Use SSRS Account: Select the SSRS Account where your report server is located, or create one by clicking on (...) button next in Report Location.
Hint: SQL Azure Reporting Services- when writing your report, make sure you have enabled Forms Authentication. You should use the 2010 .asmx address for your reporting service URL.
SSRS. Report Server Browser in SQL-RD.
  • Select the SSRS Account or create one by writing the name into the combo box.
  • SSRS Server URL: Enter the address of your reporting services server and click Connect.
  • Select the report and click OK.
SSRS. Creating New SSRS Account in SQL-RD.
  • Click OK.

SSRS Account is now added in SQL-RD.

  • Report Service URL: Enter the address of your reporting services server.
  • Report Location: Browse through the report server browser to select the desired report. Authenticate to the reports server if necessary.
Hint: SQL Azure Reporting Services- when writing your report, make sure you have enabled Forms Authentication. You should use the 2010 .asmx address for your reporting service URL.
SSRS. Report Server Browser in SQL-RD.
  • Report Name: Write in the name of the package.

Datasources Wizard

New SSRS Report Properties Wizard in Event Based Schedules in SQL-RD
  • In this section you will authenticate to the database the report runs against, set snapshots, and other options.
  • Login Required: Set the authentication information for the report.
New SSRS Report Properties Wizard in Event Based Schedules in SQL-RD
  • Preview: View the report prior to execution.

Parameters Wizard

New SSRS Report Properties Wizard in Event Based Schedules in SQL-RD
  • n this section, you will determine the parameters for your report (if any). If your report has no parameters, you may skip this section by clicking next.
  • With SQL-RD, you can use the "Event Based" inserts for any parameter in your report. Ensure the filter values for each record are listed in your database. At runtime, the insert will contain the column data from the record that triggered the Event Based schedule.
  • Using Event Based Data Inserts you can pull these values into your report.
  • Drag and drop your Event Based Data inserts into the desired filter fields. If you do not wish to enter event based data value in your parameter, you can still manually type a value or select them from the drop-down box.
  • There are 3 requirements in order for SQL-RD to detect your SSRS Report parameters and their parameter values:
    • The parameter must be in use in the report.
    • The parameter must be visible.
    • The parameter must be set to be prompted at run time.
  • If these three requirements are achieved, you should see the parameters in your report listed in this screen.
  • For each parameter, select from the drop down list the value the report must use. You can type a value into the field as well.
  • SQL-RD can handle SSRS Reports with Cascading parameters, or reports where you can select multiple parameter values.
  • Select All: Selecting All Available Values will run the report given all the values.
  • Specified Value: Select the value you want in your report.
  • Null Value: Set no value for this parameter.
  • Default Value: It will select the default value.
  • All Values (at run time): Any new values will automatically will be included at run time.

Naming (Optional) Wizard

New SSRS Report Properties Wizard in Event Based Schedules in SQL-RD
  • With SQL-RD, you can use Event Based Data inserts to customize the output file name of the report.
  • Default Naming Convention: SQL-RD will name the output file in the following format: reportname.format extension, e.g. Catalog Report.pdf.
  • Customize the output file name: Choose your own filename or right-click and use the Insert Function to insert a value.
  • Customize output extension: Choose your own extension. This is useful for system integration. For example, the default extension for a character separated file is "CSV," but you can give your export an extension of "txt" so that the file can be read by another already existing system you may have. You may also right-click and use the Insert Function to insert a value.
  • Append date/time: This is useful for the following reasons:
    • If the filename is the same each time, and it is being exported to the same folder each time, then it will be overwritten by the latest one each time. By appending date and time to the filename, each file remains unique, and no files are overwritten.
    • You can track which reports ran and when they ran by looking at what the report is named.

Exception Handling Wizard

New SSRS Report Properties Wizard in Event Based Schedules in SQL-RD
  • Check if the Report is Blank: If a report is blank because it genuinely returned no data, recipients can misconstrue this as an error with the scheduler. This option allows you to identify genuine empty reports and instruct SQL-RD on what to do with them.
  • Ignore the report and subsequent tasks: if the report is blank, do not send the report. The report will not be delivered to the destination. No custom tasks will be run.

Method

New SSRS Report Properties Wizard in Event Based Schedules in SQL-RD
  • Select the Method that will determine whether a report is blank.
  • SQL Query: Select this option to use a user made query that will determine if the report is blank. If the query returns no results, the report is blank.
  • Click Build.
  • Get values from a database window will appear. For more information about Get values from database, click here.
SSRS. Get values from database interface in SQL-RD
  • File size check: Assume reports is blank if size is under "X" bytes.
New SSRS Report Properties Wizard in Event Based Schedules in SQL-RD

Actions

New SSRS Report Properties Wizard in Event Based Schedules in SQL-RD
  • Select an action from the task list. This task will be executed in the event that a schedule is blank.
  • For more information about tasks, click here.
Tip: You can send a notification if a report is considered blank instead of sending the report. Simply select “check if a report is blank” then select “Ignore the report.” In the actions tab, select “Send Email” from the list. Compose your email and save.
  • Click OK.

Destination Wizard

New SSRS Report Properties Wizard in Event Based Schedules in SQL-RD

In this section, you will decide where your schedule will be delivered. The list in the center will display the list of destinations you have added to the schedule. You can organize the various destinations’ order by clicking on the green up and down arrows.

  • Add: Click here to add a destination. You have several options which are: Email, Disk, Fax, FTP, ODBC, Printer, Sharepoint, SMS, and Dropbox.
New SSRS Report Properties Wizard in Event Based Schedules in SQL-RD

  • Edit: Select a destination and click to edit it's properties. Or simply double-click on the destination.
  • Delete: Select a destination and click this button to delete it.
  • Import: click here to import from the list of default destinations.
  • For more information about Type of Destinations, click here.
  • For more information about Output Formats, click here.

Click OK.

Customize Recipient Messaging

  • You can also customize the messaging the recipient receives. Using Event Based Data Inserts, SQL-RD will automatically pull data from the record that triggered the schedule and use it to customize the subject, body or report format.
Using Event Based Data Inserts in Email Destination in SQL-RD
  • Simply drag and drop fields from your table to the desired position.
For example, to customize an email greeting, type the greeting, and add the recipients name as shown above.