Jet Scheduler Using /X Batch Files
The Jet Reports Scheduler allows you to create reports and have them regenerated on a scheduled basis. This is useful for periodically updating web information (as html) or for automatically generating reports and emailing them to a list.
The purpose of this article is to explain how to use the batch file generation option in the Jet Scheduler to create scheduled reports that are specific to the individual recipient of the report.
For example, you may want to send your salespeople a list of overdue orders so that they can take action to determine the problem and update the customer. In this article, we will show you how to create a single Jet Report that will automatically be emailed to each salesperson showing them their overdue orders from the previous day.
How the Jet Scheduler Works
The Jet Scheduler is a feature of Jet Reports that allows reports to be scheduled to be run in the future and emailed to the appropriate recipients. The scheduler setup window is available from the Schedule option in the Jet menu or ribbon.
The user fills in information to indicate how often the report will be run, who will be emailed a copy and how the options will be populated when the report is run in the Jet Scheduler window. When the Schedule button is clicked, a Scheduled Task is created in Windows (Start Menu, Control Panel, Scheduled Tasks).
The scheduled task runs in the background and when it wakes up, it calls a Jet Reports utility named Autopilot which runs the scheduled report with the information specified by the user in the Jet Scheduler.
When to Use Batch File Generation to Dynamically Configure Reports
Most reports can be scheduled by filling in the options in the Jet Scheduler window and clicking Schedule. This includes setting up options that can be modified when the report is run as well as e-mail recipients.
If you need to specify options that change on a per-user basis, you can accomplish this with the Batch File Generation option in Jet Scheduler.
As an example, let us look at a simple overdue orders report. This report shows orders that were promised to ship by today, but are still open.
We could schedule this to update the date filter automatically so that it runs for the current day and goes to all salespeople by setting the option on the date filter to Today() and adding the salesperson's email addresses to the recipient address list.
With these settings, all salespeople would get the same report which includes their orders as well as everyone else's If we want to send each salesperson the report, filtered for only the orders that they are responsible for, we need to use batch file generation. Note also, that if a salesperson is added, removed or has a change of email address, the report settings must be adjusted.
How to Use Batch File Generation to Dynamically Configure Reports
With Batch File Generation the options for a report can read from the database dynamically as the report is run.
In our example, we want to run the report once for each salesperson, filter it so that all orders on the report are relevant to that person and then email it.
To do this, we are going to set up the Jet Scheduler like this:
Note that we are no longer emailing. We wil take care of that later.
When we click the Create and Execute Batch File from Report option, we are telling Jet to create a batch file when the report is scheduled. This batch file will run a program called AutoPilot with the parameters that we specify.
Setting up the Report
The workbook we have created so far is not the one we are going to using in the Jet Scheduler. For that purpose, we are going to create another workbook which will contain the information the scheduler needs to find and run the report we have created.
In this new "batch file" workbook, we are going to create an NL("Rows") that will create a row for each salesperson.
We will place the following in cells B4 and C4:
And after the report is run, it will look like this:
Next, we are going to create a named range called "Batch" by highlighting cells D4:I5 and typing the word Batch in the Name box in Excel. Note that the named range is highlighted in yellow in the illustration below, but for space reasons, does not include the whole area in this example.
Finally, were going to add the titles and options in the batch area:
Here is what each of these mean:
This tells the program where the autopilot is located on your system.
/M This sets the mode to run the report. There are different keywords you can use to run the report in different ways (i.e. Update, Convert, SimpleWeb). We are going to use Update so that it will update the values and save the workbook in the output folder allowing us to then email it.
/I Specifies the input directory or the path to a particular report.
/O Specifies the output directory where the newly created report copies will be saved. You can create dynamic folder names or create dynamic file names so that you do not copy over existing folders or files. For this exercise, we are creating a copy for each salesperson and then mailing it to them.
/E Emails the report(s) to the email address(es) specified.
/S Emails the reports via SMTP. SMTP settings must be configured in the Jet Application Settings. Requires /E
/P This will allow us to specify the salesperson filter on the fly. /P uses named ranges in the workbook to update the values. This must always be the last parameter in the command line. With this functionality, you can have it change all of the filters on the Options Sheet within a report (Date Ranges, Salesperson Codes, DataSources, etc.)
When the batch file report is run, it is expanded to look like this. For each line in the batch, you can see that all the options for AutoPilot are provided.
Be sure to save this batch file report.
To update the scheduler, click the Schedule button on the Jet Essentials ribbon. On the "Report tab", specify the name of the batch file report and, on the "Output" tab, select the named range that you made called Batch.