Schedule Batch Reports using the Jet Excel Add-in
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
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 are used in the video).
The rest of this article will show how to perform batch scheduling without using that special template.
How the Jet Scheduler Works
The scheduler setup window is accessible via the Schedule option on the Jet ribbon.
In that setup window, the user enters information about how frequently the report will be run, who will receive an email copy, and how the report's options will be populated when the report is run. When you select the Schedule button, a Windows Scheduled Task is created.
When the scheduled job wakes up, it uses the Autopilot utility to run the report with the user-specified information from the Jet Scheduler.
When to use Batch Scheduling to Dynamically Schedule Reports
-
Batch Scheduling allows a report's options to be dynamically read from the database while it is running.
In our example, we want to run the report once for each salesperson, filter it so that only relevant orders for the person appear on the report, and then email it.
To perform this, set up the Jet Scheduler similar to the following ( this will automatically hide the Options sheet when the report is run):
-
By adjusting the date filter's option to Today() and adding the salesmen's email addresses to the recipient address list, we could schedule this to update the date filter automatically so that it runs for the current day and goes to all salespeople.
With these settings, each salesperson would receive an identical report containing their orders, as well as those of all other salespeople. Batch file generation is required if we wish to send the report to each salesperson, filtered to include only the orders they are accountable for. Keep in mind that the non-batch method would require adjusting report parameters if a salesperson was added, deleted, or had their email address changed.
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 and e-mail recipients that can be modified when the report is run. If you need to specify options that change for every user, 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 as an 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 similar to the following:
-
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: To ensure all cell contents are within quotation marks, use Excel's "=" notation and double any required quotation marks in your text. For instance, /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
Tells the batch file where the autopilot.exe file is located on your system.
-
Mode
/M: 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 will use Update to update the values and save the workbook to the output folder for emailing.
-
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 to avoid copying existing folders or files. For this exercise, we will make a copy for each salesperson and mail 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. /E is required as well.
-
Options
/P: Specifies the salesman filter on the fly. /P updates the values in the spreadsheet using named ranges. This should always be the last parameter on the command line. This functionality allows you to adjust all of the filters on the Options Sheet within a report (date ranges, salesperson codes, data sources, and so on).
-
Other Options
For a complete list of available parameters, see Dynamic Batch Scheduling Command Line Parameters.
Schedule the Batch File
Make sure to save your batch file.
-
Click the Schedule button on the Jet Ribbon.
The Scheduler - New Scheduled Task window is displayed.
-
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.
When the scheduled task runs, Excel will load your batch file, run the Jet functions inside the batch file, and then carry out the commands found in the named range (Batch). These commands allow you to load your Outstanding Orders report, run it for each salesperson separately, and email the personalized results to the salesperson.
Related Articles