How do I use Data-Driven Package Schedules for Paginated Reports on Premise or SSRS reports in PBRS?

Unlike a Data Driven Schedule, a Data Driven Package will determine the data driver further into the schedule. A Data-Driven Package allows you to send a package of reports to an individual or group from one schedule.

• Destinations

How to Create a Data-Driven Package Schedule Report for Paginated Reports on Premise or SSRS Reports?

Step 1: Navigate to Data-Driven Package.

Step 2: Click Next to save the information continue to the General Section of the Wizard.

General Wizard

Step 3: Enter a Package Name, this must be unique.

Step 4: Use the "..." button next to the Create In option to select a folder where the package will be stored. 

Step 5 (optional): You may enter a short description to help other users identify exactly what this schedule is and what it is expected to do.

Step 6 (optional): You may enter some keywords which can be used later by Smart Folders to identify this schedule.

Step 7: Click Next to save the information continue to the Schedule Section of the Wizard. 

Schedule Wizard

Step 8: Select the scheduling frequency for this schedule. There are a variety of options:

• Daily: Run a report every day or at a frequency of days.
• Sub options: Repeat every X Days.

• Weekly: Run a report on a weekly time frame.
• Sub options: Repeat every X weeks.

• On: Select the specific days of the week the schedule will run. If only once a week, select only the day of the week it will run.

• Week Days: Run the schedule Monday through Friday.

• Working Day: Run the schedule starting on a specific day of the month. Indicate which day of the month the schedule will run. E.G. run the schedule on the 4th working day of the month.

• Monthly: Run the schedule on a monthly time frame.
• Use the following options: Checking this box will enable you to select frequency options such as the “last Thursday of the month.” Also you can include or exclude specific months from the schedule.

• Annual: Run the schedule every year at a specified time.

• Custom Calendar: Select the custom Calendar you wish to use. You can create a new custom calendar from the menu as well. Please see Custom Calendars for more information.

• Other: Other scheduling options.
• Run Schedule every X Minutes, hours, days, weeks, months, years.

• None: No scheduling is required for this item.

Step 9: To specify an End Date for the schedule, check the End Date Box and input the desired date. If you do not select this option, the schedule will automatically be configured to continue until many years in the future.

Step 10: Finish the set up of the scheduling frequency by determining a Start tine and a Next Run date and Repeating Interval of the schedule.

• Start Date: Enter the desired starting date for the schedule. This section can be the current date (providing schedule time has not already passed) or a date in the future.
• End Date: If the schedule is due to end after a certain date enter that here. If the schedule is to run indefinitely, then leave it blank.
• Schedule time: On the Next Run date, the package will run at this time.
• Exception Calendar: Choose a calendar that will instruct the schedule to NOT run on those specified days. Please see Custom Calendars for more information.
• Next to run on: The package will next run on this date.
• Repeat Every: Rerun the package every x minutes from the scheduled time until your specified time.

• Until: After this date, there will be no automated scheduling of this package.
• Enable this Schedule: Uncheck this option to Disable the package. Disabled packages are not deleted, but they do not execute automatically. You can re-start the automatic scheduling at a later date by checking this option again. Or right-clicking this schedule from the main screen and selecting Enable.

Step 11: Click Next to save the information continue to the Data Driver Section of the Wizard. 

Step 12: Create the Data Driver.

The Data Driver is where you set up the source for data for your data-driven reports. If your data source or data selection criteria changes this is where you will need to make any required changes.

Creating a SQL Data Driver

Step 13: Click Build to specify the data selection criteria. This will return you to the Build Data Driver tool.

Step 14: Choose either an ODBC DSN or a SQL Server Account.  Check either option to select the required information from the dropdown list. 

 

Step 15: When prompted enter a User ID and Password that PBRS should use to log into the SQL Server Account or Database. 

Step 16: Click Connect to connect to the Database or Account.

Step 17: Select the table from the database that holds the required data.

Step 18: To retrieve the necessary information from the database, you need to create a query. This can be accomplished by utilizing either the simple or advanced tabs available in the interface.

Step 19: Click Parse, to test the query.

Step 20: Click OK.

Step 21: Select the key column for the data that is returned for the reports. The information that drives the schedule is required to have a key column so that each row in the table is uniquely identified by the value in this column. The key column is there as an identifier which is used to troubleshoot in cases where there is a problem with one of the records.

 

Step 22 (Optional):  You can instruct PBRS to Group reports by email address. Please note that enabling this option disables the ability to embed the reports in the email body for email destinations.

Step 23 (Optional): Where the reports are grouped you can also instruct PBRS to Merge Multiple Reports.  You may group reports into a single file (PDF and MS Excel formats only). Selecting each option will bring up the options for the output format that you can further customize.

Creating a JSON Data Driver

Step 1: Select JSON Data Driver.

Step 2: Enter your JSON string and click Validate.

Step 3: Select the row identifier for the data collection for the reports. The information that drives the schedule is required to have a row identifier so that each row in the table is uniquely identified by the value in this column. The row identifier is there as an identifier that is used to troubleshoot in cases where there is a problem with one of the collections.

Step 4: Click Validate.

 

Step 5: Click OK to clear the Valid message

• Group reports by email address: You can instruct PBRS to group reports that are sent to the same email address. Please note that enabling this option disables the ability to embed the reports in the email body for email destinations.

• Merging Multiple Reports: Where the reports are grouped you can also instruct PBRS to optionally merge the output of the grouped reports into a single file (PDF and MS Excel formats only). Selecting each option will bring up the options for the output format that you can further customize.

Step 24: Click Next to save the information continue to the Reports Section of the Wizard. 

Report Wizard

A package may be created with no constituent reports and then the reports can be added later. However, you may also add reports at this stage in the wizard - and amend, delete or add some more later.

Step 25: Click Add to Add a Report to the package.  Choose the type of report you want to add from the dropdown..

Step 26: Complete the required report information by following the steps in the Report Wizard.  Here you will configure the following:

• The Report
• Datasourcse if required
• Output Format for the chosen report.  For more information about Output Formats, click here.
• Report Filters
• Report Settings
• The Naming for the Exported File
• Exception Handing for this report
• If a Dataset refresh is required before executing this report.

Report Wizard

The following example shows adding an SSRS report to the package

• Use SSRS Account: Select the SSRS Account where your report server is located, and then click on (...) button next in Report Location. 
• Service URL: Uncheck 'Use SSRS Account' checkbox, to 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.

• Report Name: Write in the name of the package.
• Format: Select the output format for the report.

Datasources Wizard (If required)

Step 27: Here you will authenticate to the database 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.

Parameters Wizard

Step 28: 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.

• 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 will be included at run time.

Naming (Optional) Wizard

Step 29: In this section, you can specify a new name for the exported report. You have the option to choose a static name or utilize dynamic inserts to generate a unique report name. Additionally, you can customize the file extension and append the date and time to the export, ensuring each file is uniquely identifiable.

• 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

Step 30: In this section you will determine what should happen if the report is Blank or the Report Export Fails.

• 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.

Method

• 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.

Actions

• 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.

Step 31: Click OK to save the report and return to the main wizard.  Repeat steps 26-30 until all required reports have been added to the package.

• Edit: Select a Report and click Edit to change it's properties. Or simply double-click on the Report.
• Remove: Select a Report and click Remove to delete it.
• Green Up and Down Arrows: Once your Reports have been added to the package you may want to change the order in which they are executed.  Highlight the Report you wish to move up or down the list and click on the arrows to do so until the desired list order has been achieved.  The order that they appear on the screen will be the order in which they are executed.

Step 32: If you have more than one report exporting to PDF, Excel or Text, you can choose to merge the reports of the same output format type into a single file before distribution. 

Merge all PDF Files: Merge PDF outputs into a single PDF file. The reports will appear in the merged PDF in the order they are displayed in the package schedule. Checking this box will bring up additional PDF Options.

Merge all Excel Files: PBRS will then merge all excel outputs in the package into a single excel file.  You can create individual worksheet names, by using inserts, or you can leave the Worksheet name blank.

You may also add a password at this time.

Run Package Multi Threaded:  Enabling this option allows multiple reports to run concurrently. The number of reports that can be executed simultaneously is determined by the Multi-Threading settings that have been configured, as well as the number of threads currently in use by PBRS at the time the schedule is executed.

Step 33: Click Next to save the information continue to the Destination Section of the Wizard. 

Destination Wizard

Step 34: 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.

Step 35: Click Add to begin creating a new destination. There are several options to choose from including: Email, Disk, Fax, FTP, ODBC, Printer, SharePoint, SMS, and Dropbox, Azure Blob Storage, OneDrive.

• 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.
• Green Up and Down Arrows: Once your destinations have been created you may want to change the order in which they appear on the screen.  Highlight the destination you wish to move up or down the list and click on the arrows to do so until the desired list order has been achieved.
• For more information of adding destinations, click here.
• For more information about Output Formats, click here.

Customizing the Destination

With the Data-Driven Schedule, you can determine the delivery method of your report based on your data. In your table, specify the delivery method by creating a column for each delivery type (FTP, SharePoint, Email, etc.). For each record specify their email, fax number, and so on.

• Data driving the Report's Distribution
• As mentioned above, PBRS will pull delivery data from your database.
• In this example, we will be using email addresses found in the table.
• With Data-Driven inserts, simply drag and drop the field that indicates your desired destination (email in this case).

Customize Recipient Messaging

• You can also customize the messaging the recipient receives. Using Data-Driven Inserts, PBRS will automatically pull data from your table and use it to customize the subject, body or report format.

• Simply drag and drop fields from your table to the desired position.

Step 36:  Click Next to save the destination(s) and to continue to the Exception Handling section of the Wizard.

Exception Handling Wizard

Step 37: In this section you will determine what happens when an Exception occurs and determine how many Schedule Retries should occur.

• Treat as “error” if not completed in X minutes: If a report takes longer than the specified amount of time to run, this option will treat the schedule as an error and follow the appropriate action. The “Auto-calculate” option instructs PBRS automatically determine how long a schedule should take to run the report. If it takes longer than the calculated amount of time, then it is an error.

• On error, retry executing schedule every: If set to 0, PBRS will deem the schedule as "Failed" the first time it encounters an error. The schedule will not run again until its next scheduled time. Change the value to tell PBRS how many times you want it to retry running the report before declaring it as "Failed."
• 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.

Method

• 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.

Actions

• 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.

Step 38:  Click Next to save the destination(s) and to continue to the Custom Tasks section of the Wizard.

Custom Tasks Wizard

Step 39:  In the section you have the option of setting up Custom Tasks. Custom tasks are business process automation tools that can be auto triggered before or after a report runs.  Simply navigate to the task you would like to set up and drag & drop to the right hand side of the screen.  This will automatically open the Configuration screen for the selected task.

If you have no Custom Tasks to set up you may skip this step and simply click Finish to save the information and complete the Schedule set up.

• Edit: Select a Custom Task and click Edit to change it's properties. Or simply double-click on the Custom Task.

• Delete: Select a Custom Task and click delete to remove it.

• Green Up and Down Arrows: Once your Custom Tasks have been created you may want to change the order in which they appear on the screen.  Highlight the Custom Tasks you wish to move up or down the list and click on the arrows to do so until the desired list order has been achieved.  The order that they appear on the screen will be the order in which they are executed.

• Import: If you already have a default Custom Task List created you may import it using this option. 

• Export: If you wish to create a Default Task list from the tasks created here, simply click on the Export button, give the task list a name, when prompted and it is ready to be used again and again in other schedules.

• Run Custom Tasks Once for each data driven record or once for the entire schedule.  If you have set up Custom Tasks you now need to choose if the Tasks should run after every export generation or at the end of the schedule when all reports have been generated.

For more information on Custom Tasks, click here.

Step 40: Once all the tasks have been added click Finish to complete the schedule to save the information and to complete the Schedule set up.

 

Data-Driven Package Report Schedule Context Menu

Right-Click on a schedule to see the following actions:

• Properties: Edit the schedule from here. Or you can just double click on the schedule.
• Open: This will open the package and show its constituent reports.
• Add Report: Use this to add one or more reports to an existing package.
• Copy: Use this to copy the schedule. Right-click in the "white space" of the folder you wish to copy it to and select the Paste button.
• Rename: Rename the package.
• Enabled: Schedules are enabled when there is a check icon beside this option. To stop a schedule from running, or to "pause" it for a while, select this option to remove the check icon. Disabled schedules will not run until they are enabled again.
• Move: Use this option to move the report into an existing package.
• Execute: This button will execute the schedule immediately. Note that the next run date and time is not moved on as a result of a manual execution. They only move on if the schedule is run automatically by one of the schedulers .
• Delete: Selecting this option will delete the schedule.
• Execute On: Use to option to execute the schedule to another collaboration server. 
• Test Package: Use this option to test the schedule and export it to selected "test" destinations.
• Split into Single Schedules: This will split all the constituent reports in the package into Single Report Schedules.

• Ad-Hoc Email to Recipients: Select this option to send an ad-hoc email to all recipients of this package. You can use this to alert recipients to a planned system outage, or any other useful information.
• Create Shortcut: Use this option to create a shortcut you can save in any location on your PC. Execute the shortcut to execute the schedule in PBRS.

Data-Driven Package Properties

• To access your schedule properties, right-click on a schedule and select properties.
• Similar to the schedule wizard, you adjust settings to your schedules such as timing, error handling, or custom tasks.
• General

You can view the Schedule Unique ID in the General Wizard.

• Schedule

• Destinations

• Reports

• Exception Handling

• History

• Tasks

• Sharing: Here you can share this schedule with multiple users. 

• Data-Driver