Batch Scheduling with the Jet Excel Add-in – Support Topics

Moderate

Overview

The Scheduler feature of the Jet Excel add-in allows you to create reports and have them regenerated on a scheduled basis. This is particularly useful for automatically generating reports and emailing them to a list.

The Scheduler also includes "Batch" capability which allows you to use a generic report... but customize it (multiple times) for specific individuals.

Let's consider the example where you want to send your salespeople a list of overdue orders so that each salesperson 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-To Video

Dynamics Batch Scheduling

This video demonstrates a step-by-step process for setting up and using Batch Scheduling using a template developed by Jet Reports. (the Jet report and the Batch file used in the video)

The rest of this article will look at how to perform this process without using that special template.

How the Jet Scheduler Works

The scheduler setup window is available from the Schedule option on the Jet ribbon.

In that setup window, the user fills in information to indicate how often the report will be run, who will be emailed a copy, and how the report's options will be populated when the report is run. When the Schedule button is clicked, a Windows Scheduled Task is created.

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 which was specified by the user in the Jet Scheduler.

When to use Batch Scheduling to Dynamically Schedule Reports

With Batch Scheduling, the options for a report can dynamically read from the database while 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:

(this will automatically hide the Options sheet when the report is run)
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 non-batch method would require that report settings be adjusted.

How to use Batch Scheduling to Dynamically Schedule 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.

Creating the Batch File

The workbook we have created so far (the Outstanding Orders report) is not the one we are going to using in the Jet Scheduler. Instead, we are going to create another workbook which will contain the information the scheduler needs to find and run our report .
In this new "batch file" workbook, we are going to create an NL("Rows") that will create a row for each salesperson.

Using Dynamics NAV for our example, we will place the following in cell B4
```
B4:  =NL("Rows","Salesperson/Purchaser","Code")
```
```
C4:  =NL("First","Salesperson/Purchaser","E-mail","Code",$B4)
```
If the report were run, it would 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
Finally, were going to add the titles and options in the batch area:

Note: Since all cell contents must be contained within quotation marks, you need to use Excel's "=" notation and also double any quotation marks you need in your text. For example: /m "update" would be entered in the cell as ="/m ""update"""

AUTOPILOT Parameters

Here is what each of the titles and options used above mean

AutoPilot

This tells the batch file where the autopilot.exe file is located on your system.
Mode

/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.
Input Path

/I Specifies the input directory or the path to a particular report.
Output Path

/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.
Email

/E Emails the report(s) to the email address(es) specified (using Microsoft Outlook)

/S Emails the reports via SMTP (Simplified Mail Transfer Protocol) instead of Outlook. SMTP settings must be configured in the Jet Application Settings. Requires /E to also be present.
Options

/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.)
Other Options

For a complete list of available parameters, see Dynamic Batch Scheduling Command Line Parameters

Scheduling the Batch File

Be sure to save your batch file.

Click the Schedule button on the Jet Ribbon
On the General tab, give your report a unique name
On the Reports tab, select the batch file you created for the Source. Then select an location for the Output
Back in the General tab, toggle the 'Create and execute batch file from report' to On. Then select the named range that you made called Batch.
Click the Save button at the bottom of the window.
Done!

When the scheduled task run, your batch file will be loaded into Excel, the Jet functions inside the batch file will be run, and then the commands contained in the named range (Batch) will be carried out. Those commands state to load your Outstanding Orders report, run it specifically for each salesperson, and email the personalized results to that salesperson.