You have a report you wish to run regularly through the batch server, but it has a parameter used to select records for the report based on a date. When you submit the report interactively, you manually enter the date for 7 days ago, but what if you want to run this as a recurring task, how can you setup the date in this way? Yes, this article is all you need to know about how to do this… it’s like I’m a mind reader!
Of course, these steps apply to any batch task that has a date parameter, but I will use a report as it’s easier for me to setup.
Preamble
I’ll quickly explain my setup before going into how to setup the recurring task, as this will hopefully allow you to understand what I’m trying to achieve
Setup report in Crystal
My report will simply list all the batch server tasks which were submitted since a particular date, which is entered at runtime.
Here is my custom Crystal Report. As with any custom report, it is a copy of an existing standard report, to get all the required parameters, etc. in order to execute within X3 context.
My report is based on the ABATRQT table in the X3 schema
I’ve defined my date parameter to filter the returned data based on DFIN field
That’s about it for my Crystal Report, so will now copy the report definition file “ZBATCHJOBS.rpt” to the directory “D:\Sage\X3ERPV12\Folders\X3\REPORT\BRI“ Note this is the X3 folder, as the report has to be run against the X3 schema. Best practise is to put your customisations into the child folder itself to which it applies.
I have attached my report definition as a ZIP file in the following link, in case you want to follow along with this article: 4617.ZBATCHJOBS.zip
Steps in Sage X3
Connect to X3 folder
Create or check for Activity code
Navigate to Development, Data and parameters, Development setup, Activity codes
Create suitable Activity code or check for an existing one. I will create and use code ZREP
Create Report definition
Navigate to Development, Script dictionary, Reports
Take particular note of the Parameter definition, which is where I have specified my date field
Setup Destinations for X3 folder
If not already done so...
Navigate to Setup, Destinations, Destinations
Test the report works
Navigate to Reports, Reports, Printouts
Run the report interactively and make sure it works OK
The output shows all the records expected for the date specified
Test report through the batch server
Run the report through the batch server as a manually submitted request to make sure it works OK when run through the batch server
Navigate to Usage, Batch server, Query submission
Click “Validation”
Click “Print” to submit
Navigate to Usage, Batch server, Query management
Once we see the job finished OK, check the log file
Everything looks good, so will now get onto the main reason for this article, which is to submit the batch job as a recurring task!
Setup report to run as a recurring task
This is documented in the online help at https://online-help.sageerpx3.com/erp/12/staticpost/recurring-task-management/
Setup the recurring task as normal, until we get to the relative date:
Looking specifically at the Relative Date section, I am going to use the following settings:
The Date Field is the screen/field name. If you run the report interactively and then use ESC-F6 keys to see the field name you find it is [RPT] screen name and VALEUR1 as the field name. There may be multiple fields and even multiple screens, but each field can be identified this way… or you can use the Magnifying Glass to see the available fields.
Base date is the date on which your date calculation is based. For my requirement I want to take current date as the starting point, but you can choose from a variety of specific dates from the drop-down list, shown below.
Increment and time unit work together to allow you to add or deduct a certain number of days, weeks, months, or years from the base date
You can alternatively use a formula, although to be honest I haven’t had to play with that yet. If you want to use the formula, you need to select "Formula" from the drop down to be able to edit that field
The Parameter definitions are the same as we used before, the date specified here is irrelevant as will be calculated using the relative date:
The job gets set as a recurring task. Once the first one has run OK, check the log file
In the log file we can see the “From date” is 7 days ago, as expected
As it’s tricky for me to check the print out (as I don’t have a real printer) for testing purposes I can set the output to print to disk file, so can check the results easier. This will obviously overwrite the previous file each time it runs.
Conclusion
I hope this article has provided insight into the Relative Date functionality for you to consider if it would be useful for you. Feel free to leave a comment below to let me know your feedback.