Report generator: Advanced

Advanced options

Running a report
When running a report there are four output methods available:

• Printed
  The report will be printed on the currently selected printer, or in the case of the label option, the currently selected label printer.

  Note: Currently, under MSDOS the label option will not swap printers automatically.

• Displayed
  If the report is not too wide (>80 characters) then this will display the results on the screen.

• Summary
  Instead of printing/displaying the individual records the summary option will just display the summary. This could be used to find out how many small animal records you have, how many dogs etc. Ideal for answering 'How many XXXXXX ?' questions where you are not concerned with the individual records just the totals.

• Disk
  The report will be stored on the disk for later use, instead of being printed, (but the summary will still be printed as normal).

  There are four forms of disk output:

  1. Spooled - The report is 'printed' to a disk file. This can be useful if you want to leave the report generator running and not have to worry about whether a printer is available or not. The printer may be broken, printing bills, too noisy etc. or you may want to accumulate different reports and print them together. A spooled report may be printed at a later date via the Operating system's print commands e.g. under MSDOS use the 'print' program, and under Unix use the 'lp' program.

  2. ASCII - This is like a spooled report except the headings and page formatting is disabled and all that is generated is a long continuous list of the selected items. This is useful for importing information from the Premvet 5 system into other programs e.g. a spreadsheet, work processor, graphics package etc.

     If ASCII is selected another question will appear relating to the 'delimiter'. A delimiter is a character that separates each item on a line so other packages know where items start and finish. The choice of delimiters depends on what you are planning to do with the file. The documentation supplied with the package concerned will usually mention under 'importing data' the delimiter required. The available delimiters are:

     CSV - This stands for 'Comma Separated Values', each item is surrounded by quotes and separated by commas e.g.

     "Mr T Melvin","12 High Street","Edinburgh","12","11","0"
     

     COLON - Each item is separated by a colon. e.g.

     Mr T Melvin:12 High Street:Edinburgh:12:11:0
     

     PIPE - The broken vertical bar symbol '|' e.g.

     Mr T Melvin|12 High Street|Edinburgh|12|11|0
     

     TAB - Each item is separated with a tab character. If you want to manipulate a disk file under Unix then this is the best option to use as tab is the default delimiter under Unix.

     Note: No check is made to see if the delimiter is included with the item being output, so if you use the selected delimiter in records you should select a different delimiter to use.

  3. Record - Selected records are stored on the disk and then this record file can in turn be searched.

     Imagine you have 15,000+ records on file and wish to analyse all your small animal canine cases by running various different search criteria. While you should be able to scan all the records each time this will be time consuming.

     You may have 7,000 cases, the report generator will have to scan all 15,000+ records each time. By using the report output you can first search on just small animal canine records, and this will extract all the appropriate records. You would set up the new search criteria and search the record file. This means you search all 15,000 records once and in subsequent searches you are only scanning 7000 records making the search quicker. In addition the summary figures will be related to the 7000 records and not the 15,000. This procedure can be repeated to any level.

     To use a report file when running a report you should enter minus one (-1) as the client number to start at. You will then be prompted to enter the report file to use, and the record to end at will refer to the last record stored within the record file.

  4. BDS - The 'BDS' option only applies to client records and the purchase ledger. This option will create a file that is compatible with the 'Mail merge' option within the Premvet 5 system. You can extract the required clients/suppliers then send them a merged letter.

     If you have used the merge printing option before you will be aware that a letter has to be set-up with various reserved words in it e.g. &client for client name, &species for species etc. With the report generator you can output up to 16 of 66 separate fields for a record card, having a reserved word for each one would be unwieldy and possibly impractical. To get round this problem each selected item will be allocated a reserved word automatically.

     When you select items to printout the order in which they are selected is the order they are printed, we can use this to allocate &'number' e.g. &1 client number, &2 client name etc. to each item. The reserved word is shown on the summary beside the appropriate fields. NOTE: You don't need to select the client number as this will automatically be included.

     So your letter file should contain &1, &2, &3 as appropriate.

Output options - summary
Looking at the overall output options we get a tree structure:

Output method :       Printed Displayed Disk Summary
                                         |
                                 |-------|-------|-------|
Output Type   :               Spooled  ASCII  Record    BDS 
                                         |
                                    |----|------|-----|
Delimiter     :                    CSV  Colon  Pipe  Tab

WARNING: You are well advised to use the automatic name option as it is by this name the system is able to recognise where the file originated.

Advanced uses - search
In the search section of this chapter we looked at comparing a selected item with a preset value e.g. Client type =SA. There may be times when you want to compare one item with another. To do this you would enter the field abbreviation within the search criteria. A list of the available abbreviations can be obtained from an option on the utilities menu. Abbreviations are entered in a similar manner to reserved words; that is, you prefix the abbreviation with an ampersand symbol '&'. There can be no spaces between the & and the abbreviation.

For example, to list all stock items where the quantity is less than the minimum quantity you would search on the quantity field with the criteria <&mi (&MI is the abbreviation for minimum amount). The report generator will replace &mi with the minimum quantity from the record card.

You can also perform calculations on these abbreviations and you have to indicate a calculation is following by prefixing the calculation with a question mark (?) e.g. in the above example we searched for quantity below minimum (&MI); this does not take into account any goods on order (&ON). The following criteria could be used:

Search criteria against quantity - <?&on*&cp+&mi

Note: Goods on order are stored as the 'outer' quantity and the quantity and minimum amounts are stored as 'inner' sizes so we must convert to a standard by using the pack contents field (&CP).

This is fine as long as the items you are searching against are valid fields, the next section explains how to search (and print) items which do not have a specific field.

Extrapolated fields
Extrapolated fields are fields that do not have an entry to search or printout on. e.g. You may want a list of stock items where the total holding (at cost) of any item is greater than -1000.00.

You can specify up to three separate extrapolated fields and these are labelled 'Special 1 - 3'. When these fields are selected you will be asked to enter, first of all, what to print out.

Advanced uses - Files and directories
This page gives an explanation of the file names created and used by the report generator.

All report generator files are stored in a sub-directory below the directory containing the Premvet 5 software e.g. if the Premvet 5 system is installed in C:\VET then report generator files are contained in C:\VET\REPORT.

Deleting unwanted files
Generator files can be deleted via the erase option under 'Edit a report'. Output files will have to be deleted manually with the appropriate Operating System command.

Files names used follow the following convention:

• Generator files
  These are all prefixed with '.?RG'. The question mark is replaced depending on the content of the generator file e.g. A generator file created from the stock control section called MINMAX will be given the prefix .SRG - MINMAX.SRG, client reports will be prefixed .VRG. See the table below for the appropriate options.

• Output files
  The report generator will automatically allocate a name to output files if none is entered. It is only if files are created by this means do they appear on the 'List available reports' option. They are allocated names as follows: they will have the basic name ??_REP.?, where the 1st two character indicate the specific report type and source and the final question mark will be a number in the range 1 - 99. The options are outlined in the table below.

  Output      STOCK    CLIENTS    AP      H.F.     PUR.     NOM.
  ----------|--------|--------|--------|--------|--------|-------
  Generator | *.srg  | *.vrg  | *.arg  | *.hrg  | *.prg  | *.nrg
  Report    | SR_REP.| VR_REP.| AR_REP.| HR_REP.| PR_REP.| NR_REP.
  Spooled   | SV_REP.| VV_REP.| AV_REP.| HV_REP.| PV_REP.| NV_REP.
  BDS Merge |  n/a   | VB_REP.|  n/a   |  n/a   | PB_REP.|  n/a  
  

  Note: If when allocating a manual filename for BDS merge files, an underscore (_) is included in the file name then the merge printing program will automatically look in the report sub- directory otherwise the full pathname must be specified.

Running Report Generator scripts outwith the menus

You can run any report generator script from the command line, this enables you to automate any of the reports e.g. using at or cron. The summary is sent to 'standard out' making it very easy to mail/file the results. The actual results can be sent to a CSV or text file in the text sub-directory (same name as the report being run) or 'standard out'. The location and name of the file will be shown on the summary report.

When run as a stand-alone option there is are no page breaks, any heading boxes are suppressed and the column width is increased to 999 characters. You cannot use '/' to ask for search critiera - when run stand-alone all search critera must be specifed within the report. If run before 6am on the 1st of a month the system will use the last dat of the previous month.

The option is similar to the other stand-alone options within Premvet, and has the format:

The report name is shown on the summary page, so to run an existing report that you are not sure what it is called, run it manually to check everything is working as expected, look at the 1st line of the summary:

 Report run - Client Agreement Check (CACheck.vrg)   on 14.12.2001

        vet -p rgen CACheck.vrg Spool 

	vet -p rgen CACheck.vrg Spool | mail -s "Client Agreement" tom

	vet -p rgen CACheck.vrg > /u/home/tom/CACheck

Running via cron

As with all Premvet cron jobs they should NOT be run by 'root', we suggest you run them as the user 'vet' making sure they don't clash with other jobs e.g. validations, end of day/year and backups.

So to run the job at 1:10 am on the 1st of each month you would use

10 1 1 * * /usr/local/bin/vet -p rgen CACheck.vrg Spool | mail -s "Client Agreement" tom

If you have a few reports to run it is recommended you do NOT add them all into cron. This can lead to over-runs and having to edit cron every time a new report is added is a waste of time and a possible cause of errors. Rather you should:

• Create a text file (with any editor) listing all the reports you want to run. For example, if every month you want to run three reports, create the file 'month' e.g.

  /usr/local/bin/vet -p rgen st1.srg Disk
  /usr/local/bin/vet -p rgen ps712.arg Spool
  /usr/local/bin/vet -p rgen ct2
  

• Make it 'executable' e.g. chmod +x month
• Now all you do is schedual ONE job 'month' via cron, if you need to add new reports just change the script 'month'. The same applies to any end of year reports.

Note: Do NOT save the script in the main 'vetdata' sub-directory as one of the periodical security sweeps will remove the execute permissions and the report will NOT work. We suggest you save it in the home directory of the user running the job.

Running over mutiple databases

You can use a simple shell script to run a single report over multiple copies of the vet system.

To save having to maintain multiple copies of the report-to-run (as they should all be the same), the system will use the Enviroment Variable REPORT, this should be set to the top level directory of the database containing the report description and dictionary files.

For example, this script will assume all the vet systems live under /w and that the reports have been defined in the main live vet system (/v/vetdata). This script includes comments so you can see what is happening.

MIRROR=/w         # Where do the vet systems live?
rm -f /u/tom/test.csv # Remove the file
REPORT=/v/vetdata  ; export REPORT  # Use the reports in the main vet system

# Loop over all the possible directories 
for x in `ls $MIRROR`
do
    # Does the directory have a vet system in it?
    [ -f $MIRROR/$x/u/vetdata/param.exe ] || continue
    # Run the report
    vet -d $MIRROR/$x/u/vetdata -p rgen dogs Disk /u/tom/test
done

You should not use any sort options when running this method as while individual sites will be sorted the overall date will not. If you are searching clinical records, the csv file with the actual records found will be in the text directory relative to the database being searched.

The system will still include individual summary pages for each database set allowing the totals to be brokendown.