Using an Event Based schedule, you can trigger an existing schedule, or a SSRS new report, based on events. New reports allow you to use "insert" data from the event that triggered the schedule.
This article applies to:
- Power BI Paginated Reports (On Premise - Power BI Report Server)
- Microsoft SSRS Reports (On Premise - SQL Server Reporting Services)
- Select New Reports and click Next.
- Click Add and select For SSRS or Paginated Reports.
- Report Properties will appear.
- 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.
- Report Name: Write in the name of the package.
- In this section you will authenticate to the database what the report runs against, set snapshots, and other options.
- Login Required: Set the authentication information for the report.
- Preview: View the report prior to execution.
- In 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 PBRS, 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 PBRS 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.
- PBRS 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 be included at run time.
Naming (Optional) Wizard
- With PBRS, you can use Event Based Data inserts to customize the output file name of the report.
- Default Naming Convention: PBRS 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
- 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 PBRS 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.
- 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.
- File size check: Assume reports is blank if size is under "X" bytes.
- 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.
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.
- 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.
Customize Recipient Messaging
- You can also customize the messaging the recipient receives. Using Event Based Data Inserts, PBRS will automatically pull data from the record that triggered the schedule and use it to customize the subject, body or report format.
- 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.