Extract Data from Broadcast Job

JOBXLDATA jobfile|jobid [fieldlist.lst]

This program extracts statistical and accounting data for an individual job into a spreadsheet file, and saves it as XLSX, XLS, CSV, TAB or PDF.

The output file name defaults to the same as the given input job instance file but with extension .XLSX, .XLS, .CSV, .TAB or .PDF.  You can also select a specific output file name with these extensions using a parameter in the field list file.  The program can extract either a formatted summary sheet, or a table of all job items, or both.  Only one output format is available from each run of the extraction.

If the parameter is given as a job ID number (and if Job ID is specified as being unique within the system) then the program searches for the Job ID. In this case the output file is placed in the EXTDATA directory under the defined USERJOBS directory for the system.

Format parameters and the fields available for extraction are controlled by default by the file JXLFIELDS.LST in the defined USERJOBS directory for your system.  You can override this in two ways:

The $extract_xlfields command can be used in the instance UJP to specify an override list filename.  This overrides JXLFIELDS.LST.

You can specify (with full path) a fieldlist.lst file as the second parameter on the command line.  This overrides both JXLFIELDS.LST and the file specified on $extract_xlfields

Take care when using multiple override formats: you need to maintain a single format for jobs which you intend to consolidate in to a combined file for the job owner.

The listfile has two purposes: first it specifies the following formatting parameters (examples appear in the supplied file):

SUMMARY=a value from 0 to 3 to specify the sections to include:
0Detail list only
1Detail list in first sheet, summary in second sheet
2Summary in first sheet, detail list in second sheet
3Summary sheet only
HEADER=a value of 1 adds a header row to the detail list (default); a value of 0 suppresses the header row
DATEFMT=a value of 0 uses MM/DD/YYYY for dates (default), a value of 0 uses a local date format.
TIMEFMT=a value of 0 uses HH:MM:SS for times (default);  a value of 1 uses a local time format; a value of 2 uses HH:MM only.
COLOR=a value of 1 sets green (success) or red (failure) color for the text of the columns STATUS, STATUSDESC, OUTCOME, and OUTCOMEDESC.  This applies to XLSX, XLS and PDF formats only.

The following parameters take a double-quoted string value for each or the captions on the summary worksheet.  If the value is omitted or empty, the corresponding row is suppressed.

MAINHEAD=defines the main heading of the Summary sheet
DATE=defines the caption of the date field
TIME=defines the caption of the time field
SUMNAME=defines the sheet name for the summary sheet
JOBHEAD=defines the job number heading: %d must be included to show the job number
TOTAL=defines the description for the total count
SENT=defines the description for the sent count.  If [] appears in the text, it is replaced by a calculated percentage value in square brackets.
FAIL=defines the description for the failed count.  If [] appears in the text, it is replaced by a calculated percentage value in square brackets.
RETRIES=defines the description for the retries count
SECONDSdefines the description for the connect seconds count

The next two parameters define the list content:

SORT_COLUMNS=a comma-separated list of up to eight columns on which the detail list is to be sorted.  The highest-level sort comes first.  Columns are numbered from 1 upwards, each column corresponding to one entry in the field list, including entries for hidden columns (which may be useful to sort on).
IGNORE_OUTCOMES=a comma-separated list of outcome codes (from $fax_status2), to specify list items which are not to appear in the detail list.  If you specify a Summary worksheet, then the count of ignored rows is added to the Excluded count.  Normally the Excluded field shows only items excluded by the launcher, so that no FS file is created for them (duplicates, blanks, etc.).

For PDF output only the following parameters are used (and are ignored for other formats):

PDF_HEADER=a page header line used on each page of the details report.  @JOB_INSTANCE is expanded.
PDF_FOOTER=a page footer line used on each page of the details report.  @JOB_INSTANCE is expanded.
PDF_SIZE=one of the following page sizes: A4, B4, A3, LTR (default), LEGAL.  This page size is used for the details report, and is also used to select either A4 or Letter for the summary report.
PDF_LANDSCAPE=a value of 1 to select landscape format for the details report
PDF_MARGINS=six values (in inches), to specify the left, top, right and bottom margins, plus the offset of the header and the footer from the paper edge.
PDF_GRIDLINES=a value of 1 to specify that gridlines are to be shown.
PDF_TITLE=the title of the PDF (in PDF properties).
PDF_AUTHOR= the author of the PDF (in PDF properties).
PDF_SUBJECT=the subject of the PDF (in PDF properties).

The final parameter can override the output XLSX file.

OUTPUT=defines either an override output pathname or, if it starts with '.', an override file extension.  Valid file extensions are .XLS, .XLSX, .PDF, .CSV and .TAB.  In the last two cases only the detail sheet is output (if selected) and written as a separated-value file. The separator for .CSV is the List Separator from Windows Regional Settings, and for .TAB Is a tab character. We do not recommend .XLS format because of its row count limitation and larger size.  And .XLS files cannot be used in JOBCONS.  In the full pathname, only the principal path variables such as @FFBASE and @FFJOBS are expanded, plus the @JOB_INSTANCE variable which is expanded to the job instance number (with leading zeros).  Only one OUTPUT= line is allowed.

The second section lists keywords for the fields to be included, together with field format and width and a field heading value.  Full documentation for the field content and selection appear in the original supplied file. If you define custom variables for your broadcast, you can also add them to the extracted data.  JXLFIELDS.DFT-LST shows the full list of available fields, with a brief description of each, but the following features of the field list should be specially noted:

The fields CALLTIME and CONNECT are unlikely to be meaningful when a single job item is attempted both as a fax and as an e-mail transmission.

You can count separately the call and connect times in each of a set of priorities.  For example specifying CALL_6_7_15 as a field name counts the call time for the item when transmitted with priorities 6, 7 and 15.  This may be helpful when calls are charged differently for different priorities.

Proof and Report FS files are excluded unless the job option keyword ExtractProofRep keyword is given.

Failed transmissions are excluded from the CALLTIME and CONNECT unless job option keyword ExtractFailTimes is given.

If you change or add to the selected fields in JXLFIELDS.LST, bear in mind that you will not be able to consolidate XLSX files from before and after the change.

The jobfile parameter may have a '+' prefix to indicate that the filename is a list of job instance files to be processed.

This program can be invoked automatically by JOBCONS, but can also be run as an end-job-task or independently from a console session.  It returns a positive errorlevel on failure.




See also the provided files JXLFIELDS.LST and JXLFIELDS_PDF.LST in the USERJOBS folder.  In case you modify these files, the default values are also provided with -DFT in the filename.  The first example file shows all fields; the second shows a subset more suitable for PDF output.