How to Use This Manual

This manual describes how to use the DP4 utilities REPSETUP and REPPRINT, known collectively as Report Writer, to produce reports, create files for export and delete records from your DP4 databases.

Previous experience of Report Writer is not assumed, but a basic knowledge of DP4, including fields, tables, roles, key fields, indexes and other database concepts is necessary to benefit fully from the information provided by this manual.

This section covers the following topics:

  • What is the Report Writer?
  • The SALESORD Database
  • How The Manual is Organised


What's New in the Report Writer Manual

This page will tell you about recent changes to this manual. The most recent changes are mentioned first

New in 4.621

  • 20 July 2004 Documentation of operator precedences corrected.

New in 4.620

  • 15 Dec 2003 Information added on how REPPRINT prints reports (incorporated from an Itim DP4 Web article). Also the Conditions and Formulas section has been revised.

  • 17 Oct 2002 What's New Page Added


What is the Report Writer?

Report Writer can be used to create a range of business reports: simple field listings, address labels, summary reports showing sub-totals or complex invoices using pre-printed stationery. However, Report Writer does not allow you to specify font or colour information, or create graphical output. If you need such facilities you will need to use QA Build, or develop a program using C/C++, or use a third party Report Program such as Crystal Reports.

Report Writer also allows you to create files for export to other systems, such as spreadsheets, and delete records that satisfy specified conditions.

Report Writer creates the report program you want, depending on your responses to a series of questions, in most cases requiring Yes or No answers or option selection from menus. Default responses are suggested where appropriate and context-sensitive help is always at hand.

Report Writer programs are created, compiled and saved with their DP4 application database. Report Writer makes extensive use of the DP4 Data Dictionary and does, therefore, have access to information about your application database. In this way, Report Writer can ensure that only relevant options are offered throughout the creation of the report.

Report Writer programs can be created automatically for particular tables. This is useful when you need to list or export all the fields from one or more tables.


The SALESORD Database

The examples in this manual are based upon the dealings of a fictitious company, Acme Sales Order Plc, which sells a number of products throughout the United Kingdom. An example database called SALESORD is supplied on which you may experiment with the database utilities. SALESORD stores information on Acme employees, customers, orders and supplier’s materials. Its structure is illustrated below.


How The Manual is Organised

This manual is divided into the following sections:

  • Introduction to Report Writer

    Discusses Report Writer terms that you should familiarise yourself with, summarises the facilities of Report Writer and describes how to use its interface

  • Creating a Report Program

    Takes you through the steps necessary to create a Report Writer program. These include loading REPSETUP from the System menus, running the automatic report-generation facility, and using the report editing options of REPSETUP to build reports that exactly match your requirements

  • Conditions and Formulas

    Describes how to construct formulas, which are used in Report Writer to determine the value of user-defined fields and to specify conditions for totalling fields and extracting records

  • Printing a Report (REPPRINT)

    Describes how to use REPPRINT to output a report or export file, and to run a deletion

  • Managing Report Writer Programs

    Tells you how to manage your Report Writer programs with REPSETUP. Among the management tasks are: testing Report Writer programs to assess the impact of database changes and optionally modify the report; moving programs between databases; printing details of programs; deleting programs that are no longer required

  • Error Control and Error Reports

    Describes what some of the common error messages mean including, Join error, Divide by zero error, Insufficient field size, and Insufficient space

Appendixes

  • Glossary

    Explains the Report Writer terms and concepts

  • Loading from the Command Line

    Shows you how to load REPSETUP and REPPRINT from the command line. All the command tails you may use are described

  • Sample Reports for the SALESORD Database

    Explains the standard reports provided with the SALESORD database


How to Comment on this Help File

Our aim is to provide you with top quality documentation. In order to do that, we need your help. Feedback from you is the best way we have of measuring the quality of our documentation, so comments, criticisms and suggestions on our manuals are greatly appreciated.

If you are familiar with HTML you can use the view source option to get an editable copy of each page. You are welcome to e-mail us with edited versions of pages of this or any other DP4 manual. You can contact us at CustomerCare@itim-ts.com.


Introduction to Report Writer

This chapter introduces you to Report Writer. It discusses:

  • Report Writer terms with which you should familiarise yourself
  • How Report Writer works, which summarises the facilities available in Report Writer
  • Specifying Your Report with REPSETUP
  • How to use Report Writer's interface


Report Writer Terms

We recommend that before creating any reports with Report Writer, you read this section to gain an understanding of the principles behind Report Writer.

  • Lowest table

    Your report can only contain database fields that reside in, or are upwardly accessible from, the table you specify as the lowest table

  • Joined tables

    The tables whose fields are accessible after the lowest table has been specified

  • Fast pass table

    Normally, records are selected from the database according to the extraction condition specified on the lowest table. This means that the condition is applied to every record in the lowest table. A fast pass table enables you to produce a report more quickly, where it is possible to apply the extraction condition to fewer records, by testing a field in a parent or grandparent table. For example, if printing CUSTOMER details for just the South East England region, it is more efficient to select REGION as the fast table and specify the condition REGION_CODE="SEE" on the REGION table, rather than on the CUSTOMER table. The fast pass table cannot be an index-only table; these do not contain records on which the extraction condition can be tested

  • Sequencing table

    Processing time is saved when sorting can be avoided by accessing the records through a suitable index. For example, if you require a CUSTOMER detail report in customer name order, you can avoid sorting by the CUSTOMER.NAME field, if you choose the index-only table INDEX_CUST_NAME as the sequencing table. You can only specify a sequencing table if the table is the parent of the lowest table, or the parent of the fast pass table where one is used. The index being used must be specified as 'update' in SETLINK and be up to date

  • Extract file/program

    Report Writer programs can be exported to ASCII format files (.PAR). An extract file can be posted to another application database

  • Prime parent

    A table has a prime parent when the keys of an immediate parent table appear as the first part of the key of the child table. For example, the ORDER_HEADER table which has the key ORDER_NUMBER is the prime parent of the ORDER_LINE table which has the key ORDER_NUMBER+LINE

  • Break point

    When you sort a report by a field or use a sequencing table, all the records that have the same value for the sort field or key are placed in the same section of the report. You can order the records within each of the sections by sorting on other fields. The point where the value of a sort item changes is known as a break. It is possible to control what is printed at a break point, for instance, you might choose to print some sub-totals and throw a new page. (It is possible to break on a field that is not sorted; you may wish to do this when the records are already in the right order)

  • Folding report fields

    By default the Report Writer places the data for the report fields in columns across the page with headings above the columns. Each row represents a record. You can print the data from one record on several lines by folding the report. This causes the next field to be printed on the following line


How Report Writer Works

Report Writer comprises two programs REPSETUP and REPPRINT.

REPSETUP is an interactive program builder. By answering a series of questions, you tell REPSETUP the specification of a program you want to produce and REPSETUP produces it for you. When you specify a program it is stored on your applications database. Once you have written a program you can modify it, or take a copy of it to form the basis of another program.

REPSETUP allows you to:

  • Create or amend existing Report Writer programs

  • Copy an existing program to create a new program based on it

  • Automatically create report or export programs for selected tables

  • Move Report Writer programs from one database to another

  • Rename selected Report Writer programs

  • Print out details of Report Writer programs in summary or detail format

  • Test Report Writer programs, usually after making changes to the Data Dictionary

  • Delete all or selected Report Writer programs

REPPRINT allows you to run the programs created by REPSETUP. Because they are processed against the database at runtime, you can be confident that your programs always use the latest data. REPPRINT makes a single pass of the database and creates an extract file of the selected records. It processes the extract file to produce the report, export the file or delete the selected records.

If a change has been made to the database structure, any programs need to be reviewed, unless the data independence facility was used. In this case, the programs use the previous structure of the database.


Specifying Your Report with REPSETUP

When you run REPSETUP to create a Report Writer program you have two choices:

  • For simple reports listing fields from one or more tables, use option 2 - Automatic program generation on the Edit menu of the initial REPSETUP screen

  • To create a new report or edit an existing 1 use option 1 - Program editing. You can copy from an existing report using option 7 once you have selected a name for your new report. REPSETUP will not allow you to create a report which has the same name as an existing mapset. If you do do this the Creaet option will be greyed out when you have entered the report name.

  • The stages involved in creating a report are as follows:

    1. Table details and type
    2. Heading
    3. Field processing
    4. List/delete condition
    5. Fast pass condition

    6. Output devices and format

Each of these Program editing options is briefly described in the following sections

  • Table details and type

    Select the type of program to be created (REPORT, EXPORT, DELETE), choose the lowest table to be used for the program, fast pass table and sequencing table.

  • Heading

    Add the report heading you want printed at the top of each page and specify the report width in characters.

  • Field processing

    Select the fields to be processed by the program, including fields in the joined tables, fields that are calculated from formulas and fields that are entered by the user at run-time. A number of options are available to process fields, you may:

    • Total a field's value
    • Sort by the field
    • Choose whether to list the field on each detail line, or on/before break points
    • Choose whether to repeat the field's value when it is identical to that in the previous record
    • Start a new page or print a sub-total when the field's value changes
    • Fold the report output onto the next line after the field is printed
    • Change the formatting and spacing for the field

    • Conditionally total the field

  • List/delete condition

    Specify the selection criteria that must be met for records to be included when reporting, exporting or deleting.

  • Fast pass condition

    Specify a condition for record selection on the fast pass table if one is chosen.

  • Output devices and Format

    Select the output device used by the program. Various print formats can be selected for different field types. You can:

    • Limit the number of records selected by specifying a range. The lower value of the range is called the Start value and the upper value is called the Stop value
    • Choose to pause after each page of output to the screen
    • Print a summary report comprising just sub-totals (without detail lines)
    • Suppress the standard page heading that includes the name of your company, database and program as well as the current date, time and page number


Report Writer User Interface

Report Writer is implemented using the standard user interface. Each question Report Writer asks has a help message. Report Writer always suggests a sensible default to you. If you are unsure how to answer a question, you may accept the default with confidence.

Standard Key SAA Alternative Action

Entering Data

<Enter> | <Tab> | Accepts the entry in a field
<Tab> | <Enter> | Accepts the entries in a screen (as does <Enter> on the last field)
<Esc> | <F3> | Returns to the stage you were at previously, without saving any changes
<Arrow> keys |  | Move the cursor one field at a time

Selecting from Popup Lists

<Ctrl+P> or <Spacebar> |  | Popup a list of available options. The benefit of doing this is that you get help messages telling you what each option does as you move from one option to another
<Arrow> keys |  | Move the cursor through the options
<Page up> and <Page down> |  | Display any further items in the list
<Enter> | <Tab> | Selects an item in the list

Selecting from Menus

<Enter> | <Tab> | Selects the highlighted menu option
mnemonic letters and option numbers |  | Select their menu options
<Arrow> keys |  | Move the cursor through the menu options
<X> or <Esc> |  | Exit from the menu

Getting Help

<F10> or <Ctrl+J> <F1> Toggles help on and off


Creating a Report Program

This section takes you through the steps necessary to create a Report Writer program with REPSETUP. It covers:

  • Loading REPSETUP from the DP4 System menu
  • Automatic Report Generation facility for Individual Tables

  • Editing a Program to build reports that exactly match your requirements

You can load REPSETUP from the command line or from the DP4 system menu. Appendix A tells you how to run REPSETUP and REPPRINT from the command line.


Loading Repsetup from the DP4 System Menu

  1. From the DP4 System main menu, select option 5 - Other standard facilities

  2. Select option 3 - Report Writer facilities. This calls up the Report Writer Facilities menu

  3. To run REPSETUP, choose option 1 - Setup & utilities

  4. On entry to REPSETUP, from either the command line or the menu system, you are asked for the name of the database you wish to use (unless you specified this in a -DB command tail). The REPSETUP screen appears:

  5. This screen has a menu bar across the top of it, and there is a pull-down menu under each word on the menu bar. Select the Edit menu. This menu gives you access to the program creation and editing facilities, and also to the automatic program generation facility.


Automatic Program Generation

You can use this facility to generate simple Report Writer programs automatically.

To use this facility:

  1. Run REPSETUP as described in Loading Repsetup_from the DP4System Menu, specifying the database you wish to use.

  2. Select the Edit menu and choose option 2 - Automatic program generation. The following screen appears:

    This screen presents you with a series of questions about the program you wish to generate. Wherever possible, Report Writer provides default answers and help messages are available throughout. For further information on each of the questions, click on the appropriate section of the illustration above.

  3. When you have finished answering the questions, click on OK. You are then shown a list of the tables on the database, unless you answered 'Yes' to the question "Create programs for all tables?".

  4. Select the tables for which you want to generate programs using the cursor keys or the <space> key to position the highlight over tables and then pressing <Enter>. Selected items are indicated by an asterisk. Pressing <Enter> on a selected table deselects it. When you have finished, press <Tab> to confirm your selection. To cancel, press <Esc>. Report Writer shows you a list of the tables you have selected and displays a message saying that it is posting the programs.

  5. To print your reports or export files, use REPRINT, as described in Printing a Report


Editing a Program

To create exactly the report you want:

From the Edit menu, select option 1 - Program editing. You are asked for the name of the program you wish to edit:

  • To choose an existing program, either type its name or press <Ctrl+P> to call up a list from which to select

  • To create a new program, type in the name you wish to give it

On the menu which appears select option 1 - Create new program. The Report Program Editing menu appears:

The options on this menu form a logical sequence of steps in the creation and editing of a program. (You can click on the items in the illustration above to jump to the appropriate sections in this document.) The first two options are the initial overall control parameters - you must specify these before you choose the fields to be used in your program:

  • Option 1 - Table details and type

    Selects the program type, the tables used by the program and (for a report program) the page width

  • Option 2 - Heading

    Changes the heading at the top of each page

Other topics in this section are:

  • Field Processing
  • Field Formatting
  • List/Delete Condition
  • Fast Pass Condition
  • Output Devices and Format
  • Copy Another Program


Table Details and Type

  1. Select option 1 in the Report Program Editing menu: Table details and type. The following screen appears:

    Click on the appropriate areas of the illustration above to pop up a description of the questions in this screen.

  2. When you have answered the required questions, click on OK. The screen closes and you are returned to the Report program editing menu. Select the highlighted option, which is Option 2 - Heading.


Heading

In the Heading screen, you can enter up to four lines for a heading. Each line is up to 80 characters long. This heading appears at the top of each page of your report. You are also asked for a heading when creating a delete or export program.

Customising Headings

This section provides information that helps you to customise the report headings.

The standard headings printed are:

  • The copyright message. This can be suppressed with the REPPRINT command tail -NOCOPY. It is printed using map 7 of the mapset with the same name as the report. You can modify it using the DP4MAPEDIT utility

  • The client name, database name, report name, and so on. These can be suppressed by answering 'No' to the appropriate question in the 'Output devices and format' screen. These fields are printed using map 8 of the report's mapset, and can be modified by using the DP4 MAPEDIT utility

    Map 8 is overwritten each time the report is edited or tested.

  • The headings for the fields. To print data in the headings, include extra fields at the start of the report that have blank headings:

    • Variable fields can be used to provide changing values

    • Constant fields can be used to provide heading information

    If you fold after these fields without listing, they are listed once at the top of every page. In addition, make sure that these fields are listed on break lines, otherwise when a new page is started before a break line, the fields are not printed at the top of that page

What to do next

Having specified the heading, you are returned to the Report Program Editing menu, where option 3 - Field processing is highlighted. Select this option.


Field Processing

Field processing involves choosing which fields are used in your report and how those fields appear. The fields you select can appear as they are, or they can be used to derive fields for the report. In addition, you can specify how you want to use these fields (for instance, sorted or totalled) and whether you want to modify the default presentation format.

When you select option 3 - Field processing, a screen appears, showing the fields processed by the program:

This screen keeps track of the fields you have selected for use in your program and gives you a summary of the options specified for them.

Press <Enter> to add a new field to the program.

You are then presented with the following screen of questions. You may not need to answer all of them, depending on the field you are processing. Report Writer does its best to supply default answers wherever possible, by drawing on its information about your database. Help messages are available throughout. Click on the appropriate area in the illustration below to pop up further information on each question. Note that the Total field and Local break only descriptions are too long for pop-up display, so are treated as separate topics; clicking on these areas will jump to those topics.

What to do next

Once you have answered the questions which apply to the field, you are returned to the list of fields which you have selected for use in your program. On the menu bar at the bottom of the screen, the Format option is highlighted.


Total field?

Answer 'Yes' if you want to total this field's values. The total appears on the subtotal and grand total lines of the report.

Printing a Total Beneath a Column

To print a total beneath the column of figures being totalled, specify the field as:

Total Field ? 'Yes'
List field ? 'Yes'
List on break lines 'Yes'

If you want to add a label to the total value you will have to adopt a different approach.

Labelling aTotal

A frequent requirement is to produce a total with a label. Say you want to produce a simple report which appears like this:

NAME                    ORDER VALUE
Bellerose Capt D P      226.55
                        727.94

Total Order Value:      954.49

in field processing specify:

  1. CUSTOMER.NAME as a list field, on which you are sorting

  2. ORDER_VALUE as a list field. Total it, but do not list it on break lines, and specify Fold ? 'Yes' to print the next field at the beginning of a new line

  3. LABEL_TOTAL as a calculated field with the formula: "Total Order Value: ". Only list this field on break lines

  4. ORDER_TOTAL as a calculated field with the formula: ORDER_VALUE. Only list this field on break lines

The important point here is that although ORDER_VALUE was totalled in step 2, the total was not printed at that point. The second reference to ORDER_VALUE in step 4, prints the ORDER_VALUE total. Any reference to a totalled field on the subtotal line is a reference to its totalled value. Separating the listed ORDER_VALUE from the totalled ORDER_VALUE allows you to create a label for the total. It also allows you to make use of the totalled value in formulas.

Using Totals in Formulas

The following example extends the previous report to show the use of totals in formulas. To produce a report that looks like this:

NAME                     ORDER VALUE

Bellerose Capt D P       226.55
                         727.94

   Total Order Value:    954.49
   Average Order Value:  477.24
   Total number of orders  2

in field processing specify fields 1 to 4 as before, plus:

  5. ORDER_COUNT as a calculated field with the formula: 1. The field is totalled to provide a count of the listed records, but not printed

  6. LABEL_AVERAGE as a calculated field with the formula: "Average Order Value: ". Only list this field on break lines

  7. AVERAGE_VALUE as a calculated field with the formula: ORDER_VALUE/ORDER_COUNT. Only list this field on break lines. Fold the field to insert a carriage return before the next field

  8. LABEL_COUNT as a calculated field with the formula: "Total number of orders: ". Only list this field on break lines

  9. COUNT_ORDERS as a calculated field with the formula: ORDER_COUNT. Only list this field on break lines

The important point again is that once a field is totalled you can make use of the totalled value later, either by itself or in formulas, just by referring to the field and specifying list on break lines.

Printing a Running Total

To print a running count at each detail line, for example:

NAME                     ORDER VALUE    RUNNING COUNT

Bellerose Capt D P         226.55             1
                           727.94             2

  Total Order Value:       954.49
  Average Order Value:     477.24
  Total number of orders:    2

specify a calculated field with the formula: PREVIOUS $ + 1. The field should be listed but not totalled.


Local break only?

Only applicable when the field is used as a break. The default is 'No'. Answer 'Yes' to modify what happens when the field breaks. The effect of this option is to prevent fields in this break and below from being printed in sub-total lines for breaks above.

This is best explained by way of example. In the SALESORD database, material orders are stored in the ORDER_LINE table. Now, suppose a daily sales report is required where the total number and value of each material sold is displayed together with a grand total of the materials' value. You set up the report as follows:

Field Sort List? List on break? Break? Total?

MATERIAL_CODE 1 No Yes Yes No
QUANTITY No Yes No Yes
LINE_VALUE No Yes No Yes

The report output takes this form:

Material Quantity Line
Code Value

19949819 676 845.00
19949826 199 509.44
...
69840 30841.35

Note
The last line is the grand total line. Notice that it contains a grand total for the quantity field which is not a useful value to display.

To prevent the total number of materials sold from being displayed you can use the local break facility and set up a slightly more complicated report, as in:

Field Sort List? List on break? Break? Total? Local Brk?
DUMMY='X' No No Yes No No
HEADING='Total' No Yes No No
LINE_VALUE No Yes No Yes
MATERIAL_CODE 1 No Yes Yes No Yes
QUANTITY No Yes No Yes
LINE_VALUE No Yes No Yes

This report prints the totals needed for each material code. A break is generated on the DUMMY field at the end of the report, simulating the grand totals. The total materials' value is printed in this break. Without the local break field, totals for the QUANTITY and second LINE_VALUE would be printed as well.


Field Formatting

Choose the Format option to alter the default appearance of the field in the report. A screen like that illustrated below appears. Questions that are irrelevant to the field type being processed are greyed out. Report Writer offers default answers wherever it can and help messages are available throughout. Click on the appropriate areas of the illustration below for further details on the question.

What to do next

When you have completed these questions, you are returned to the list of tables you have selected for use in your program. Again, the option Add new field to report program is highlighted. You can repeat the procedures given in the previous section Field Processing and the current section Field Formatting as often as necessary.

Once you have selected all the fields you need, exit from the list of fields.

Report Writer checks the information you have supplied and you are required to correct any errors or inconsistencies before proceeding.

You are then returned to the Report Program Editing menu. Select the highlighted option - Output Devices and Format


List/Delete Condition

This option from the Report program editing menu allows you to specify a formula to be used in determining whether a record is to be selected for processing on the report (or export or delete). The default value is 'ALL'. You have 5 lines for entering the formula. See Section 4 for the rules for specifying a formula.

You can set up any valid formula using the operators and functions described in Section 4. The system accepts field names in either upper or lower case, but field values for C, M, P or U type fields must be in the appropriate form and enclosed within single or double quotation marks.

An error message is displayed if any aspect of the formula is incorrect or non-implementable. You should refer to field names in full, that is:

<table_name>.<table_role>.<field_name>.<field_role>

Alternatively, the field itself can be referred to as $ and another field in the report can be referred to as #4, where 4 is the field number within the report.

Note
Any field used in a formula must have been previously selected during field processing, whether or not you wish to print its values in the report.


Fast Pass Condition

You are asked for this only if you have specified a fast pass table. You use this option to specify the formula for the criteria for record selection. The condition is applied to each record of the fast pass table.

Using a condition to list from the fast pass table restricts the records to be scanned to those meeting the fast pass selection criteria. Any other conditions (such as a list/delete condition) are then applied only to those records. Any field(s) in the fast pass table can be used in the formula, provided they have previously been set up in the list of fields. You can also use non-database fields, which are defined using fields, in the fast pass condition.


Output Devices and Format

This option is used to specify where the report is to be output to and how the report as a whole (as opposed to individual entries within it) is to appear. Again, which of the following details you are required to supply depends on the type of program you have created. Click on the appropriate area of the illustration below to pop up a description of the question.

What to do next

After defining the format of your fields, specify which records you want to print by choosing either of the following from the Report Program Editing menu:

  • Option 4 - List/Delete condition allows you to specify a formula to determine whether a record is to be selected for processing.

  • Option 5 - Fast Pass condition is applicable only when you have specified a fast pass table.


Copy Another Program

This option from the Report program editing menu allows you to create a new program by using an existing program as a template.

You are asked for the name of the program you wish to create.

Once a unique name has been entered, you are asked for the name of the program you wish to copy. Press <Ctrl+P> to call up a list of available programs.

You may then amend, add or delete details, just as if you were creating a new program from scratch, but the original program is not altered.


Conditions and Formulas

This section is divided into the following topics:

  • Where Conditions and Formulas are Used
  • Creating Conditions and Formulas
  • Formula Entry
  • Formula Syntax
  • Operators and Functions by Category
  • Operator Precedence
  • Operator/Function Reference
  • Pre-Defined Variables
  • Using Functions/Macros


Where Conditions and Formulas are Used

Conditions are used in Report Writer to determine the following:

  • Condition to total

  • Condition to extract on the lowest table

  • Condition to extract on the fast pass table

For example, you can have a condition to extract records on the fast pass table:

REGION_CODE=LOOK_AT_REGION

where LOOK_AT_REGION is a region code entered at runtime.

Formulas are used in field processing to set the value of a calculated field. For example, the following formula prints a running total for detail lines:

PREVIOUS $ + 1

The expressions for conditions and formulas use the same syntax. They differ in that the expression for a condition must evaluate to TRUE or FALSE. So, it must result in a Boolean (Yes/No) data type. The expression for a formula must result in the data type of its calculated field.


Creating Conditions and Formulas

An expression may include operators, standard and user-defined functions, calculated fields, predefined variables, database fields, constant values and parentheses.

Operators and functions must be used with operands of the correct data type. Consider the formula expression:

"STATISTICS FOR: " CONCAT REGION.NAME

In this formula, CONCAT joins two character fields to give one character field. "STATISTICS FOR: " is, of course, a character string. It is essential that REGION.NAME is also a character field. If it were another type, such as integer, Report Writer would reject the formula.

The operators obey the normal algebraic precedence rules. In summary, the ABS is evaluated first, then MOD, *, / and then +, -. Operations of the same priority are performed from left to right. The normal precedence can be overridden using round brackets, so:

MATERIAL.QUANTITY.IN_STOCK - ORDER_LINE.QUANTITY * PRICE

means 'multiply PRICE by ORDER_LINE.QUANTITY and subtract from MATERIAL.QUANTITY.IN_STOCK'.

(MATERIAL.QUANTITY.IN_STOCK - ORDER_LINE.QUANTITY) * PRICE

means 'subtract ORDER_LINE.QUANTITY from MATERIAL.QUANTITY.IN_STOCK and multiply the result by PRICE'.

To include comments with your expressions use the comment delimiters { }. For example:

(1 + VAT_RATE) * PRICE

{VAT_RATE is a calculated field that equals a decimal fraction}


Formula Entry

You may find it useful to remember the following points when editing formulas:

  • Formulas consist of 5 lines of at most 64 characters each. Internally they are treated as one long line of 320 characters. In theory this means you can split an identifier over two lines, but this is almost certainly a very bad idea.
  • You can use <Ctrl+Y> to delete the part of the formula from the current position to the end of the line. Pressing <Ctrl+Y> will move the remaining lines up, joining the next line onto the end of this line.
  • You can use <Ctrl+N> to split the current line and move the rest of the formula down.
  • You can use <Ctrl+U> to restore the original state of the formula.

Formulas that are unusually long or complex can often be handled more conveniently by using the form #n for field names, or by defining additional fields to act as work fields to store intermediate values. For example, suppose we have a formula:

<condition_1> AND <condition_2>

We can define a non-database field X with a formula:

<condition_2>

and then use the formula:

<condition_1> AND X


Formula Syntax

DP4 uses essentially the same formula processor in several DP4 utilities: QAB; Report Writer; Browser; Auditor; RECASTDB. This formula processor uses a sytax that is generally similar to that used by most programming languages. The syntax is explained in detail on the following pages, but you may find a brief over-view helpful.

  • Operands must be separated by spaces or operators that cannot be part of the operand.

    For example, 'A MOD B' has to be written 'A MOD B' and not 'AMODB', but A+B is acceptable.

  • An expression may use operators (+ - * / and also operators like mod denoted by an identifier), function calls (e.g. round(rate*amount,2), references to fields (customer.name), standard variables (for example end_key in QAB, or CLIENTNAME in Report Writer), and use constant values and parentheses.

  • For the purpose of matching names neither case nor underscores are significant. Thus all the following are equivalent: CUST_NR, Cust_Nr, cust_nr, custnr.

  • When referencing fields in formulas and conditions you can only refer to the fields already defined as part of the report. Note that this is quite different from QAB. As a result your field processing may include fields that are never listed.

  • DP4 does not prohibit you from declaring fields with names that have a predefined meaning as an operator or built-in function. For example you might inadvertently use YEAR as a field name in a program or on the database. If you do this the operator or function will not be available under that name. To minimise the chances of this causing you a problem you should use the RESWORDS program to post the reserved words for QAB to your database. (This list also does duty for Report Writer)

  • If an identifier is not the name of a field, role, or table defined on the database or a local field defined on your program, or a macro (but not a function) defined using QAB Function Definition, The formula processor will look for a match with the names of the operators and functions built into the formula compiler. In this case a partial match is acceptable: as long as at least three characters match the interncal function or operator, and as many characters as are specified match, then the identifier is considered to match. Thus, SEC(time), SECOND(time), and SECONDS(time) all extract the seconds from a time, but SECD(time) would generate an error. You are recommended only to use abbreviated operator or function names where there is no danger of confusion, for example MIN(time) would not extract the minutes from a time, because MIN is a function in its own right. As it takes a numeric parameter you would get a compiler error.

  • Each operator and function must be used with operands of the correct data types. Consider the formula expression:  code_prefix CONCAT code_number
    In this formula CONCAT joins two character fields to give one character field. It is essential that code_prefix and code_number are both character fields. If code_number were, say, an integer type, an error would occur at compile time.

  • Conditions and formula expressions are not simply worked out from left to right. Operands have a precedence of operation, which is overruled only by parentheses. For example: ( 1 + vat_rate ) * price
    The multiply operator * has a higher precedence than the add operator +. Without the parentheses, the expression would mean multiply vat_rate by price and then add 1.

    It is sometimes worth using parentheses even when not actually required, if this makes your intention clearer. On the other hand using parentheses where the precedence should be obvious can be confusing.

  • You may include comments in your expressions, using the comment delimiters { }. For example:
    (1 + vat_rate) * price
    {vat_rate equals a decimal fraction such as 0.175}

  • The DP4 Formula Processor allows you to process multi-occurs fields. This is where DP4 differs most from other programming languages in the way it processes expressions. How the DP4 Formula Processor handles-multi-occurs fields in formulas is introduced in Multi-Occurs Fields and Processing


Comments

Comments can be included in formulas and conditions. The comment must be delimited by a { character at the start and a } character at the end. Comments may not be nested. A comment may be placed anywhere where spaces would be permitted, and is equivalent to a space. This means you can't put a comment in the middle of an identifier, or field reference.


The Operands

An operand is the value that is acted upon and can take a constant or variable value, for example 1.15 or PRICE. This section is divided into the following topics:

  • Constant Values

  • Field Referencing

  • Multi-Occurs Fields


Constants

Ordinary Constant values use the syntax shown below:

Field Type Syntax Examples
Boolean

The identifiers listed in the examples.
In simple comparisons of the form field_name=character string if field_name is a boolean field and character string matches the yes or no word for the field, the comparison will be accepted and translated appropriately. (If character string is not recognised there will be a compilation error.)

 TRUE
Yes
FALSE
No
Character

Use single ' or " as the delimiter for the start and end of the string. You cannot include the delimiter in the string.

If you want to initialise a multi-occurs character field such as an address with a constant value you will need to use a string that is carefully constructed. See below for details.

 'ABC 123'
"Hello World"
Date

Use the day.month.year format (not your preferred format), where day, month and year are numbers. The Year should be between 100 and 9999, if the year is below 100 1900 is added.
Dates must be valid according to the rules used for the Gregorian calendar.

 8.1.1999 (8th Jan 1999)
02.07.63 (2nd July 1963)
1.1.2000 (1st Jan 2000)
20.3.1 (20th Mar 1901)
Numeric

A numeric constant uses the following syntax Optional -, 1 or more digits including an optional decimal point.

 1
99999.99
-7.5
Time

Use the hours:minute:second:hundredth format, where all four parts are two digit constants.
The format hh:mm:ss.hh is also accepted.
Times must be between 00:00 and 23:59:59.99.
Seconds and/or hundredths may be omitted, if so they are set to 0.

12:14:56:99
12:14:56.99
17:30:45
17:00

For types other than character you can use a number of constants separated by the concatenation operator to assign to a multi-occurs field. For example a multi-occurs numeric field could be intialised like this: 1 |2 | 3

Null Constants

Null values are denoted using the following identifiers:

Field Type | Null Name
Character | null_char
Date | null_date
Numeric | null_number
Time | null_time

Testing for NULL

If you want to test for a NULL value, you usually only need expression = NULL or expression <> NULL irrespective of data type.

There is no null value for Boolean fields.

Take care to assign all the values of an occurs field. If you assign fewer occurs than the field has, then it is padded with the last value specified

Initialising Multi-occurs Character Fields

To intialise the contents of a multi-occurs character field using a constant, consider the field as one long character field including all the occurrences next to each other. For example, to assign a multi-occurs field ADDRESS of type C20*4, consider it as a field of length 80. Address each character according to its position within the character string(an X shows the start of each occurrence):

 X1234567890123456789
"123 Cherry Lane 		X1234567890123456789
Stokehampton 		X1234567890123456789
Cheshire 		X1234567890123456789
ST8 5LN
"

The trailing spaces can be omitted if you like. You may find it easier to initialise each occurrence separately, and this will be necessary if the field is bigger than the maximum size of a formula.


Field Referencing

Reference by Name

Fields should usually be referenced using the full field name.

The full form for referencing a database fields in Report Writer is as follows:
tablename.fieldname.fieldrole

Blank field roles are denoted by _. Report Writer will accept non ambiguous abbreviated field names in the following formats:

  • fieldname
  • fieldname.fieldrole
  • tablename.fieldname
  • tablename.fieldname.fieldrole

The rule for deciding on whether a name is ambiguous is not the same in Report Writer as in QAB. In Report Writer the only fields considered when checking whether a name is ambiguous are the fields already processed in the field processsing list.

$ field reference

$ may be used to refer to the current field in a formula, or a condition to total. In the context of a formula, $ is only valid when used as PREV($).

#Field Number Syntax

In Report Writer you can designate a field used in a condition or formula by number rather than by name. For example #3 refers to the field listed as a field number 3 in the Report. Obviously changing the order of the fields could cause such a reference to become invalid. However, REPSETUP attempts to automatically correct formulas using this syntax when the order of fields is changed.


Multi-Occurs Fields

Multi-Occurs Fields and Processing

Fields in DP4 can have up to 99 occurrences. Inside the formula expressions may have more occurrences in this - there is no predefined limit on the number of occurrences in an expression. Multi-occurs fields can be thought of as arrays or sequences of variables. You can reference or modify the contents of a multi-occurs field using the QA Build formula processor, and construct a multi-occurs value by combining single occurs field.

Throughout this section we shall assume the field in the table below is defined:

Field | Type | Value
SALES_CODE | I2*5 | 23 43 7 19 2

  • Referencing Occurrences within a Field

    In a formula you can reference individual occurrences from a field or expression using the subscript operator [] like this:  SALES_CODE[5]. It is also sometimes possible to refer to a subrange of occurences of a field using this operator as well using the following syntax: SALES_CODE[2..3]. The [] operator works differently for character fields. For full details, see Array Subscript Operator.

  • Using Multi-occurs Fields in Formulas

    Most operators and functions in the formula processor work with multi-occurs expressions. This allows calculations which might require a for or while loop in other languages to be expressed in a single expression. For example SALES_CODE+1 adds 1 to each occurrence of SALES_CODE, while SALES_CODE*SALES_CODE multiplies corresponding occurences of SALES_CODE together. See Expressions with Multi-ccurs Operands for details.

    There are also a number of functions are intended specifically for multi-occurs fields, for example the MINIMUM function returns the minimum of a set of values expressed as a multi-occurs field. Thus MIN(SALES_CODE) returns the value 2. The SUM function returns the sum of the occurrences. SUM(SALES_CODE > 7) returns 3.

  • Creating Multi-Occurs expressions

    You can construct a new multi-occurs expresion by combining expressions with the concatenation operator |. For example the SALES_CODE value could be assigned using this expression: 23 | 43 | 7 | 19 |2. The | operator can be used with any type of field except character fields. Character fields are concatenated as strings using the concat operator, but you can also initialise a multi-occurs field directly as explained in Constants.

  • Assigning to all or part of a Multi-occurs Field

    You can easily assign a formula to a fixed range of occurrences in a multi-occurs field, by selecting the low and high occurs values appropriately in the field details.

    One weakness of the DP4 formula processor is that is not possible to assign a value to the nth occurrence of a field, or a variable subrange of occurrences. This is because in field processing you always have to specify a fixed range of occurences to process. There is a work-around for this problem, which is explained in Assigning to a Variable Occurrence.


Array Subscript operator [ ]

This section describes how you reference multi-occurs fields and expressions in formula processing.

Single Occurrence

You can reference a single occurrence of a multi-occurs expression using the [ ] array subscript operator like this:
multi-occurs expression[subscript expression]

multi-occurs expression is typically a field reference, but it can be any expression.
subscript expression should be an expression that evaluates to an integer between 1 and the number of occurrences in the first expression. If the number is out of range either a compiler time or a run time error will be generated.

Except for character fields, there is nothing unusual about the [] operator compared with other languages. For character fields the [] operator is very unusual indeed. For a character field, if the multi-occurs expression is a field reference, and subscript expression is an integer constant, and the [ is immediately next to the last character of the field reference, with no intervening spaces, then the [] operator behaves as you might expect - it extracts the complete occurrence from the field. In any other situation the [] operator extracts a single character from the field - if subscript expression evaluates to n then the result is the nth character in multi-occurs expression.

Examples

Suppose our program defines the following fields with the given values:

Field | Type | Value
ADDRESS | C30*4 | 123 Cherry Lane Stokehampton Cheshire ST8 5LN
BUDGET | N5*12 | 1 2 3 4 5 6 7 8 9 10 11 12
NAME | C30 | James Parrot Seed
I | I5 | 1

The table below shows the result of using the [] operator in various ways:

Expression | Result
address[1] budget[1] name[1] address[2] | '123 Cherry Lane' 1 'James Parrot Seed' 'Stokehampton'
address[i] budget[i] name[i] address[2][i] | '1' 1 'J' 'S'
address [1] budget [1] name [1] address [2] | '1' 1 'J' '2'
address[1][10] name[1][10] | 'y' 'r'
(address)[10] (name)[10] | 'y' 'r'

The syntax address [1] to extract the first character of the address, is probably sufficiently confusing that you should avoid using it. Using the double subscript [1][1], or the parenthesised form is probably better.

To refer to a range of characters within a character expression you must use the head() or tail() functions or both in combination.

Range of Occurrences

You can reference a range of occurrences using the subscript operator and two full stops, [ .. ]. The range must be specified by constant values, and multi-occurs expression must just be a field reference. If you want a range defined by variables, you must use a combination of head()and tail(). Using the example fields from above we have:

Expression | Result
BUDGET[2..4] | 2 3 4
ADDRESS[2..3] | 'Stokehampton Cheshire'


Expressions with Multi-occurs Operands

On the whole this section does not apply to character operands. A character field is always treated as a single string: for instance if ADDRESS is C30*4, it is treated by the formula processor as a string 120 long. Most operators and functions that accept character operands treat the character operand as a single string. So while the > operator applied to two instances of the budget field returns an array of 12 Boolean values, applied to two address fields it returns a single Boolean.

Most operators and functions in the DP4 formula processor accept multi-occurrence fields and expresions as operands as well as single-occurrence fields; though there are a few exceptions to this it is extremely unlikely you would ever want to use a multi-occurs expression where one is not allowed. This page explains how multi-occurrence operands are treated by the formula processor.

  • Unary Operators and Functions

    Except for functions that are specifically designed to process a multi-occurs field and return a single value (SUM, EVERY, ANY, MINIMUM, MAX) or perform some other special action on a multi-occurs field, the result of unary arithmetic operators/functions is that produced by applying the operator to each occurrence of the operand in turn. Thus ABS(-1|-2|-3) returns 1 | 2 | 3.

  • Binary Arithmetic Operators

    The formula budget+1 has as its result an occurs 12 field where the i'th occurrence is budget[i]+1. If VAT is another occurs 12 field then budget + VAT has as its i'th occurrence budget[i] + VAT[i]. The occurs of the left operand determines the number of occurs in the result - the formula 1 + budget has as its result the single value budget[1]+1.

    The other binary arithmetic operators (such as *, - and /) all behave in a similar way. Usually the second operand of an arithmetic binary operator should have either the same number of occurrences as the first operand or one occurrence, but this is not enforced by the formula compiler. The actual result, R, of A op B where 'op' is a binary operator calculated as follows:

    R[i] = A[i] op B[min(i, N)] where N is the number of occurrences of B.

    Note that, as already noted for addition above this means that if A and B have different occurrences then A op B is not the same as B op A.

    Concatenating a 0 or a 1 onto the end of the second operand may sometimes be useful. For example, to apply VAT holiday for a variable number of months you might use a formula like this:
    BUDGET + (HEAD(VAT,i) | 0).

  • Relational and Logical Operators

    The relational operators =, <>, <, >, <=, >= are all treated in the same way as the arithmetic operators: thus budget.THIS < budget.LAST has as its result an occurs 12 Boolean field, the i'th occurrence of which is the result of budget.THIS[i] < budget.LAST[i].

    Again the logical operators behave in the same way. Consider the following formula:
    budget.this>0 or budget.last = 0

    First the two relational expressions are evaluated giving two occurs 12 Boolean fields as the result. The OR then OR's the corresponding occurrences together to give another occurs 12 Boolean result. The ANY or EVERY functions are often useful when you have created results of this nature.

  • Results

    A similar process is used when assigning the final result of a formula to its field - if the target field has more occurrences than the result, then remaining occurrences will all contain the last occurrence of the result, if it has fewer the excess occurrences are discarded.

    Concatenating results with 0 may sometimes be useful to stop the last occurrence being used repeatedly.


Assigning to a Variable Occurrence

It may happen that in a Report Writer program we need to be able to assign a value to the nth occurence of some field, where n is a variable. This type of prcessing is very awkward in Report Writer. It may sometimes be possible to use some kind of trick to change the field - the PROVIDED operator is sometimes useful for this. However the most general method is to use the | operator and the HEAD and TAIL functions.

To assign a single value to a multi-occurs numeric field:

  1. Add the multi-occurs field to the field processing list (see Field Processing) and in the field details, set low occurs to 1 and high occurs to the number of occurrences in the field.
  2. Add a formula which uses a combination of the head() and tail() operators.

    For example, to assign to the ith occurrence of the N10*12 budget field you could use something like this :
    head(budget,i-1)| new_value | tail(budget, 12-i)

To assign to n adjacent occurrences you can extend the same technique as follows:

head(budget,i-1)| new_value_1 | new_value_2 |...| new_value_n | tail(budget, 12-i-(n-1))


Operators and Functions by Category

This section gives a quick lookup table for each category of operators and functions. The categories are arithmetical, character, date, logical, multi-occurrence and time.

Operands and parameters may have the following datatypes:

Data Type | Description
C | Mixed case character (ASCII collate)
D | Date
I | 16-bit integer (range -32K to 32K)
L | 32-bit integer
M | Mixed case character (Database collate)
N | Number (for very large or non integer numbers)
P | Pseudo-numeric (Digit only character fields)
T | Time
U | Upper case character with Database collate
Y | Boolean (Yes/No)

To simplify the lookup tables note the following:

  • where N is indicated, I and L may be used

  • where C is indicated, M, P and U may be used

The following categories are covered:

  • Numeric
  • Character
  • Date
  • Logical
  • Multi-Occurrence
  • Relational
  • Time


Arithmetical Operators and Functions

Operator/Function | Description | Operand Types |  | Result Type
 |  | First | Second |
− | Subtract | N | N | N
/ | Divide | N | N | N
+ | Add | N | N | N
* | Multiply | N | N | N
ABS | Absolute value | N |  | N
MOD | Modulo | N | N | N
ROUND | Round off number | N | N | N
TRUNC | Truncate number | N | N | N


Character Operators and Functions

Operator/Function | Description | Operand Types |  | Result Type
 |  | First | Second |
ASCII | Convert character to ASCII code | C |  | N
CHR or CHAR | Convert ASCII code to character | N |  | C
CONCAT | Join character fields | C | C | C
HEAD | Leading characters | C | N | C
IN | One string in another? | C | C | Y
NUMTOCHAR | Convert number to characters | N | - | C
POS | Position in character field | C | C | N
TAIL | Trailing characters | C | N | C
UPPERCASE | Convert to upper case | C |  | C
VALIDNUMBER | Only numbers in string? | C |  | Y


Date Operators and Functions

Operator/Function | Description | Operand Types |  | Result Type
 |  | First | Second |
− | Subtract | D | DN | ND
+ | Add | D | N | D
DATETONR or DATETONUM | Convert date to a number | D |  | N
DAY | Day part of date | D |  | N
MONTH | Month part of date | D |  | N
NRTODATE or NUMTODATE | Convert number to date | N |  | D
SETDAYS | Set day part of date | D | N | D
SETMONTHS | Set month part of date | D | N | D
SETYEARS | Set year part of date | D | N | D
YEAR | Year part of date | D |  | N


Logical Operators and Functions

Operator/Function | Description | Operand Types |  | Result Type
 |  | First | Second |
AND | Logical AND | Y | Y | Y
IF THEN ELSE | If statement | Y | Any | Any
NOT | Not | Y |  | Y
OR | Logical OR | Y | Y | Y
PROVIDED | Provided | Any | Y | Any


Multi-Occurrence Operators and Functions

Operator | Description | Operand Types |  | Result Type
 |  | First | Second |
| | Concatenation | DNTY | DNTY | DNTY
[ ] | Array Subscript | Any | Any |
ANY | OR all occurrences | Y |  | Y
EVERY | AND all occurrences | Y |  | Y
HEAD | Leading occurrences | Any | N | Any
MAXIMUM | Maximum occurrence | N |  | N
MINIMUM | Minimum occurrence | N |  | N
POSITION | Position multi-occurs field | Any | Any | N
SUM | Sum occurrences | NY |  | N
TAIL | Trailing occurrences | Any | N | Any


Relational Operators

Operator | Description | Operand Types |  | Result Type
 |  | First | Second |
< | Less than | Any | Any | Y
<= | Less than or equal to | Any | Any | Y
<> or != | Not equal to | Any | Any | Y
= or == | Equal | Any | Any | Y
> | Greater than | Any | Any | Y
>= | Greater than or equal to | Any | Any | Y


Time Operators

Operator | Description | Operand Types |  | Result Type
 |  | First | Second |
HOURS | Hour part of time | T |  | N
HUNDREDTHS | Hundredths part of time | T |  | N
MINUTES | Minute part of time | T |  | N
SECONDS | Seconds part of time | T |  | N
SETHOURS | Set hour part of time | T | N | T
SETHUNDREDTHS | Set hundredths part of time | T | N | T
SETMINUTES | Set minute part of time | T | N | T
SETSECONDS | Set second part of time | T | N | T


Operator Precedence

The larger the number, the higher the precedence. Operators with equal precedence are grouped left to right unless parentheses are used.

Precedence | Operator | Description
14 | a[n] | Subscript
14 | f() | Function call
13 | PREV/TOT | Modifiers
12 | CHARTONR DATETONR NRTODATE | Conversion operators
11 | YEAR MONTH DAY HOUR MINUTE SEC HUNDREDTH | Extraction
10 | SUM | Multi-occurs operator
10 | CONCAT |
10 | SETYEAR SETMONTH SETDAY SETHOUR SETMINUTE SETSECOND SETHUNDREDTH | Date/Time Operators
10 | UPPERCASE | Character operator
9 | - | Unary Minus
9 | ABS | Absolute Value
9 | ~ | Bitwise Complement
8 | * / | Multiplication/Division
8 | MOD | Remainder after division
8 | & | Bitwise and
7 | IN | Test occurrences
7 | + - | Addition/Subtraction
7 | XOR ^ | Bitwise XOR
7 | ! | Bitwise OR
6 | < > <= >= | Relational
5 | = == <> != | Equal/not equal
4 | NOT | Logical/bitwise operator
3 | AND | Logical/Bitwise operator
2 | OR | Logical/Bitwise operator
1 | PROVIDED SIGNEDON | Numeric/Logical Binary operators


Operator/Function Reference

The operator/function reference defines each operator and function in the following format:

Syntax: | NAME(D parameter)
Return Value: | D return_value
Precedence: | 1 15
Description: | Description of the operator or function
Example: | Example usage and results

The components of the syntax and return value are shown as:

NAME | Name of the function or operator
D | Data type of the parameter, as defined in the data type table on page ?
 | Where a C is specified, U, P, S and M data types may also be used. Where an N data type is specified, the I and L data types can also be used
 | Where more than one data type can be passed, these are listed (for example, NDT for Number, Date and Time). Multi-occurs data types are indicated by the following syntax: [..]
parameter | Descriptive name of the parameter
 | Where more than one parameter is passed, each parameter is separated by a comma
return value | Descriptive name for the return value

The following functions are available:

  • ABS
  • Add +
  • AND
  • ANY
  • Array Subscript [ ]
  • ASCII
  • CHARTONR or CHARTONUM
  • CHR (or CHAR)
  • Concatenation Operator |
  • CONCAT
  • DATETONR or DATETONUM
  • DAY
  • Divide /
  • Equal = (or ==)
  • EVERY
  • Greater than >
  • Greater than or Equal to >=
  • HEAD
  • HOUR
  • HUNDREDTH
  • IF THEN ELSE
  • IN
  • Less than <
  • Less than or Equal to <=
  • MAX
  • MIN
  • MINUTES
  • MOD
  • MONTH
  • Multiply *
  • NUMTOCHAR
  • NOT
  • Not Equal to <> or !=
  • NRTODATE
  • OR
  • PATTERN_MATCH
  • POS
  • ROUND
  • SECONDS
  • SETDAYS
  • SETHOURS
  • SETHUNDREDTHS
  • SETMINUTES
  • SETMONTHS
  • SETSECONDS
  • SETYEARS
  • - Subtract
  • SUM
  • TAIL
  • TRUNC
  • UPPERCASE
  • VALIDNUMBER
  • YEAR


ABS

Syntax: | ABS(N value)
Return Value: | N result
Precedence: | 9
Description: | ABS gives the absolute magnitude of a number. This has the effect of removing the negative sign from negative numbers and leaving positive numbers unchanged
Example: | ABS(−101) = 101


Add +

Syntax: | N value_1 + N value_2 D date_1 + N number_of_days
Return Value: | N result/D result
Precedence: | 7
Description: | This operand adds numerical or date fields
Example: | 21 + 34 = 55 25.12.1991 + 7 = 01.01.1992


AND

Syntax: | Y boolean_value_1 AND Y boolean_value_2 N integer_value_1 and N interger_value2
Return Value: | Y result/N result
Precedence: | 3
Description: | AND results in TRUE only if both operands are TRUE. The result is FALSE otherwise. When used with two numeric values a bitwise and is performed: you may prefer to use the & operator in this case.
Example: | TRUE AND TRUE = TRUE TRUE AND FALSE = FALSE 5 & 6 = 1


ANY

Syntax: | ANY(Y[..] multi_occurs_boolean_field)
Return Value: | Y result
Description: | ANY performs an OR operation on all the fields of a multi occurrence boolean field
Example: | ANY(FALSE|FALSE|TRUE) = TRUE


ASCII

Syntax: | ASCII(C string_of_characters)
Return Value: | N[..] ascii values
Description: | ASCII converts a character string to a multi occurrence number field giving the ascii values of each character
Example: | ASCII("ABC") = 65|66|67


CHARTONR

Syntax:

CHARTONR(C character_field)

Return Value:

N extracted_number
Precedence: 12
Description:

CHARTONR extracts the numerals in a character string and creates a number

Example:

CHARTONR("KT3 6BT") = 36
CHARTONR("KTA CBT") = 0


CHR (or CHAR)

Syntax: | CHR(N[..] multi_occurs)
Return Value: | C ascii_character_string
Description: | CHR (or CHAR) converts a multi-occurs number field to a character field
Example: | CHR(78|79) = "NO"


CONCAT

Syntax: | C part_1 CONCAT C part_2
Return Value: | C result
Precedence: | 10
Description: | CONCAT joins two character fields CONCAT can be used in combination with HEAD and TAIL to concatenate parts of a field
Example: | "HE" CONCAT "LLO" = "HELLO" "HELLO " CONCAT (HEAD("JOE BROWN",3)) = "HELLO JOE"

You can create a new character field CUSTOMER_DETAILS, defined as follows:

The CUSTOMER_DETAILS are printed like this:

ANG Aschmeyer J
2 Camsix Chase
Hertford

REPSETUP works through the formula, ignoring carriage returns, and filling each print line. For example, if a field in the formula is 30 characters long, REPSETUP inserts 30 characters. This may result in fields being split over two or more lines. Let's look at the previous example again:

111233333333333333333333333333
333344444444444444444444444444
444455555555555555555555555555

where:

1 is REGION_CODE
2 is " "
3 is NAME
4 is ADDRESS[1]
5 is ADDRESS[2]


Concatenation Operator |

Syntax: | DNTY value_1 | value_2
Return Value: | DNTY [..] multi_occurs_field
Description: | This operator combines two fields into one multi-occurs field
Example: | 2|3{multi occurs field 1st occurrence = 2, 2nd occurrence = 3}

This operand may be useful when initialising multi occurrence numbers. You can also use it to create arrays for MAX and MIN to work on. It has lower priority than any operator except IF THEN ELSE.

The | operator does not work with Character fields. Use CONCAT instead.

C programmers who were expecting | to be a bitwise or operator should note that QAB uses # for that operator instead.


DATETONR

Syntax:

DATETONR(D the_date)

Return Value:

N numeric_representation
Precedence: 12
Description:

DATETONR converts a date to the number of days since 1st March 1980
To work out the day of the week, use MOD 7. The date 1st March 1980 is a Saturday, and so the modulus will range from 0 for Saturday through to 6 for Friday

Example:

DATETONR(02.03.81) = 366
DATETONR(08.03.80) MOD 7 = 0 {Saturday gives 0}


DAY

Syntax: | DAY(D the_date)
Return Value: | N
Precedence: | 11
Description: | DAY extracts the day part of a date
Example: | DAY(25.12.91) = 25


Divide /

Syntax: | N value_1 / N value_2
Return Value: | N result_of_division
Precedence: | 8
Description: | This operand is used for dividing numbers. Dividing by zero produces an error
Example: | 21 / 7 = 3

When dividing by a power of ten you may be able to avoid rounding errors that might otherwise be introduced by replacing the division by a multiplication: for example prefer x*0.01 to x/100. In other situations, multiplying by 1.00 (with an appropriate number of zeros after the decimal point) may also be beneficial.


Equal = (or ==)

Syntax: | NCDTY value_1 = NCDTY value_2
Return Value: | Y result_of_comparison
Precedence: | 5
Description: | This relational operator compares two fields and gives TRUE if they are equal
Example: | "SMITH" = "JONES" = FALSE 3.142 = 3.142 = TRUE

This operator may not work as expected when used with floating point numbers. Make sure the numbers do not contain any rounding errors by using the round or trunc functions if necessary.


EVERY

Syntax: | EVERY(Y[..] multi_occurs_boolean_field)
Return Value: | Y result
Description: | EVERY performs an AND operation between all the fields of a multi occurrence boolean field
Example: | EVERY(FALSE|FALSE|TRUE) = FALSE


Greater than >

Syntax: | NCDTY value_1 NCDTY value_2
Return Value: | Y result
Precedence: | 6
Description: | This relational operator compares two fields and gives TRUE if the first is greater than the second
Example: | 100 > 100 = FALSE "SMITH" > "JONES" = TRUE

For a record with EMPLOYEE.NAME 'Jones' and ORDER_VALUE 7.00 with the following conditions:

NOT ORDER_VALUE > 5.00

The record would not be extracted because 7.00 is greater than 5.00 but NOT sets the result to FALSE.

ORDER_VALUE > 5.00 AND EMPLOYEE.NAME = 'Black'

The record would not be extracted because although 7.00 is greater than 5.00, the EMPLOYEE.NAME is not 'Black', and Yes AND No results in FALSE.

ORDER_VALUE 5.00 OR EMPLOYEE.NAME = 'Black'

The record would be extracted because only one of the conditions need be TRUE


Greater than or Equal to >=

Syntax: | NCDTY value_1 >= NCDTY value_2
Return Value: | Y result
Precedence: | 6
Description: | This relational operator compares two fields and gives TRUE if the first is greater than or equal to the second
Example: | 100 >= 100 = TRUE "JONES" = "SMITH" = FALSE


HEAD

Syntax:

HEAD(NCDTY field, N length)

Return Value:

C leading_characters[..] leading_values

Description:

HEAD gives the leading characters of a character field up to a specified length. HEAD can also be used to obtain the occurrences from a multi occurrence field from the first field up to the specified number

Example:

HEAD("HELLO",2) = "HE"
HEAD(2|3|4|9|0,3) = 2|3|4


HOUR

Syntax: | HOUR(T the_time)
Return Value: | N hour
Precedence: | 11
Description: | HOUR extracts the hour part of a time
Example: | HOUR(12:34:01:88) = 12


HUNDREDTH

Syntax: | HUNDREDTH(T the_time)
Return Value: | N hundredth
Precedence: | 11
Description: | HUNDREDTH extracts the hundredth of a second part of a time
Example: | HUNDREDTH(12:34:01:88) = 88


IF THEN ELSE

IF THEN ELSE allows conditional assignments to be made, depending on the value of a Boolean expression. It is used for conditional formulas for calculated fields.

Both expression 1 and expression 2 are always evaluated.

Syntax: | IF expression THEN (expression1) ELSE (expression 2)
Return Value: | NCDTY value_to_assign
Description: | IF THEN ELSE allows different assignments to be made, depending on the value of a boolean expression
Example: | IF 109 THEN 25 ELSE 5 = 25

Field DISCOUNT might have a formula:

IF ORDER_VALUE > 500 THEN 0.1 * ORDER_VALUE ELSE (IF ORDER_VALUE > 200 THEN 0.05 * ORDER_VALUE ELSE 0)

to give zero discount on orders up to 200, 5% on orders over 200 and up to 500, and 10% discount on orders over 500


IN

Syntax: | NCDTY occurrence_1 IN NCDTY occurrence_2
Return Value: | Y result
Precedence: | 7
Description: | IN tests whether occurrence exists in another
Example: | "SMITH" IN "JOHN M. SMITH" = TRUE 3 IN 1|2|4 = FALSE


LAST

Syntax: | NCDTY field
Return Value: | NCDTY value
Precedence: |
Description: | This operator allows you to reference the value the field had on the previous line
Example: | You might want to define a calculated field to count records as they are extracted, and print the count on each line. The appropriate formula would be: (LAST $) + 1


Less than <

Syntax: | NCDTY value_1 NCDTY value_2
Return Value: | Y result
Precedence: | 6
Description: | This relational operator compares two fields and gives TRUE if the first is less than the second
Example: | 100 < 100 = FALSE "JONES" < "SMITH" = TRUE


Less than or Equal to <=

Syntax: | value_1 <= value_2
Return Value: | Y result
Precedence: | 6
Description: | This relational operator compares two fields and gives TRUE if the first is less than or equal to the second
Example: | 100 <= 100 = TRUE "SMITH" <= "JONES" = FALSE


MAX

Syntax: | MAX(DNT[..] multi_occurs_field)
Return Value: | D, N or T result
Description: | MAX gives the maximum value contained in a multi occurrence field
Example: | MAX(12|42|34) = 42


MIN

Syntax: | MIN(DNT [..] multi_occurs_field)
Return Value: | D, N or T result
Description: | MIN gives the minimum value in a multi occurrence field
Example: | MIN(12|42|34) = 12


MINUTE

Syntax: | MINUTE(T the_time)
Return Value: | N minutes
Precedence: | 11
Description: | MINUTE extracts the minute part of a time
Example: | MINUTE(12:34:01:88) = 34


MOD

Syntax: | N value MOD N divider
Return Value: | N result
Precedence: | 8
Description: | The modulus operator gives the remainder after dividing the first operand by the second. MOD always gives a positive number. MOD 0 produces an error
Example: | 22 MOD 5 = 2 −1 MOD 7 = 6


MONTH

Syntax: | MONTH(D the_date)
Return Value: | N month
Precedence: | 11
Description: | MONTH extracts the month part of a date
Example: | MONTH(25.12.91) = 12


Multiply *

Syntax: | N value_1 * N value_2
Return Value: | N result
Precedence: | 8
Description: | This operand is used for multiplying numbers
Example: | 21 * 7 = 147


NOT

Syntax: | NOT expression
Return Value: | Y result/N result
Precedence: | 4
Description: | NOT gives the opposite value of a boolean field, and the 32 bit bitwise complement of a number. For bitwise nots you may prefer to use the ~ operator.
Example: | NOT TRUE = FALSE NOT FALSE = TRUE NOT 7=4294967288


Not Equal to <> or !=

Syntax: | value_1 <> value_2
Return Value: | Y result
Precedence: | 5
Description: | This relational operator compares two fields and gives TRUE if the first is not equal to the second
Example: | 100 <> 100 = FALSE "JONES" != "SMITH" = TRUE


NRTODATE

Syntax: | NRTODATE(N number_of_days)
Return Value: | D date
Precedence: | 12
Description: | NRTODATE converts a number to a date. The number is interpreted as the number of days since 1st March 1980
Example: | NRTODATE(366) = 02.03.81


NUMTOCHAR

Syntax: | NUMTOCHAR(N number)
Description: | NUMTOCHAR converts a number to a left justified character array The number of decimal places is taken from the scale factor of the number
Example: | NUMTOCHAR(1.5) = "1.5"


OR

Syntax: | Y boolean_1 OR Y boolean_2 N integer_1 or N interger_2
Return Value: | Y result/N result
Precedence: | 2
Description: | OR results in TRUE if either operand is TRUE. The result is only FALSE if they are both FALSE. For N operands the result is the bitwise OR of the operands. You may prefer to use the ! operator in this case.
Example: | TRUE OR FALSE = TRUE TRUE OR TRUE = TRUE/FALSE OR FALSE = FALSE 5 OR 6 = 7


PATTERN_MATCH

Syntax:

PATTERN_MATCH(C pattern, C data)
Return Value:

Y result
Description:

This function searches for a pattern in a character array. Wildcards can be included in the pattern, using the syntax:
* Match any group of characters
? Match any one character
Any other character must match exactly
pattern_match() is case sensitive
The following rules are used:

  1. If the length of the pattern is 0, pattern_match() returns TRUE
  2. If the pattern is longer than the data, and the pattern matches up to the end of the data, TRUE is returned if the remaining characters are ' ' (space) or '*'
  3. If the data is longer than the pattern and the pattern matches up to the end of the pattern, the match only succeeds if all the remaining characters in the data are ' ' (space)
Example:

pattern_match("*Sm*","A. Smith") = TRUE
pattern_match("???S*", "A. Smith") = TRUE


POS

Syntax:

(NCDTY value_1, NCDTY value_2)

Return Value:

N position

Description:

POS determines the position of one field in another larger field. POS works for character fields and multi occurs fields of any type. If there is no match the value given is zero

Example:

POS("EL","HELLO") = 2
POS(7, 12|3|7|37) = 3


PREVIOUS

Syntax: | NCDTY field
Return Value: | NCDTY value
Description: | This operator allows you to reference the value the field had on the previous line. This operator does the same as LAST
Example: | You might want to define a calculated field to count records as they are extracted, and print the count on each line. The appropriate formula would be: (PREVIOUS $) + 1


PROVIDED

PROVIDED allows conditional assignments to be made, depending on the value of a Boolean expression. It is used for conditional formulas for calculated fields.

Syntax:

<expression> PROVIDED <boolean_expression>

Return Value:

NCDTY value_to_assign

Description:

The calculated field takes the value of the expression if the boolean_expression is TRUE, but is set to zero for numeric fields or the null value for other types of field if it is FALSE.

Example:

If field LARGE_ORDER has the formula:

ORDER_VALUE PROVIDED ORDER_VALUE > 400

and field SMALL_ORDER has the formula:

ORDER_VALUE PROVIDED ORDER_VALUE <= 400

Then, the output looks like this:

ORDER_VALUE    LARGE_ORDER    SMALL_ORDER

  500.00         500.00           0.00
  200.00           0.00         200.00
    7.00           0.00           7.00


ROUND

Syntax: | ROUND(N value, N decimal_places)
Return Value: | N result
Description: | ROUND rounds a number to the specified number of decimal places ROUND returns a number with the same scale factor as the precision
Example: | ROUND(21.66,1) = 21.7


SEC

Syntax: | SEC(T the_time)
Return Value: | N seconds
Precedence: | 11
Description: | SEC extracts the second part of a time
Example: | SEC(12:34:01:88) = 1


SETDAY

Syntax: | D the_date SETDAY N the_day
Return Value: | D date
Precedence: | 10
Description: | SETDAY sets the day part of date. There is no check made that the resulting date is valid
Example: | 25.12.1991 SETDAY 31 = 31.12.1991


SETHOUR

Syntax: | T the_time SETHOUR N the_hour
Return Value: | T time
Precedence: | 10
Description: | SETHOUR sets the hour part of a time
Example: | 12:34:01:88 SETHOUR 10 = 10:34:01:88


SETHUNDREDTH

Syntax: | T the_time SETHUNDREDTH N the_hundredth
Return Value: | T time
Precedence: | 10
Description: | SETHUNDREDTH sets the hundredth part of a time
Example: | 12:34:01:88 SETHUNDREDTH 21 = 12:34:01:21


SETMINUTE

Syntax: | T the_time SETMINUTE N the_minute
Return Value: | T time
Precedence: | 10
Description: | SETMINUTE sets the minute part of a time
Example: | 12:34:01:88 SETMINUTE 15 12:34:15:88


SETMONTH

Syntax: | D the_date SETMONTH N the_month
Return Value: | D date
Precedence: | 10
Description: | SETMONTH sets the month part of a date
Example: | 25.12.1991 SETMONTH 8 = 25.08.1991


SETSECOND

Syntax: | T the_time SETSECOND N the_second
Return Value: | T time
Precedence: | 10
Description: | SETSECOND sets the second part of a time
Example: | 12:34:01:88 SETSECOND 3 12:34:03:88


SETYEAR

Syntax: | D the_date SETYEAR N the_year
Return Value: | D date
Precedence: | 10
Description: | SETYEAR sets the year part of date
Example: | 25.12.1991 SETYEAR 2000 = 25.12.2000


SIGNEDON

Syntax:

(N value) SIGNEDON boolean_expression

Return Value:

N result

Precedence:

Changes the sign of a number if the Boolean expression is FALSE

Description:

Example:

If ORDER_VALUE is 4.23 then ORDER_VALUE SIGNEDON (DR/CR='CR') has the value:

  • +4.23 if DR/CR='CR'
  • -4.23 if DR/CR='DR'

where DR/CR is a field that defines whether the order is a Debit or Credit


Subtract −

Syntax: | N value_1 − N value_2 first_date − second_date date − number
Return Value: | N or D result
Precedence: | 7
Description: | This operand can be used for subtracting numerical or date fields If the second date is later than the first date, the result is a negative number of days
Example: | 43 − 32 = 11 25.12.1992 − 25.12.1991 = 366 25.12.1992 − 30 = 25.11.1992


SUM

Syntax: | SUM(NY[..] multi_occurs_field)
Return Value: | NY result
Precedence: | 10
Description: | SUM adds the occurrences of a multi-occurs field. If the multi-occurs field is of boolean type, SUM adds the number of TRUE occurrences
Example: | SUM(12|3|5) = 20 SUM(TRUE|FALSE|TRUE) = 2


TAIL

Syntax:

TAIL(NCDTY[..] field, N position)

Return Value:

C trailing_charactersNCDTY[..] trailing_values

Description:

TAIL gives the trailing characters of a character field of a specified length. TAIL can also be used to obtain the specified number of occurrences from the end of a multi occurrence field

Example:

TAIL("HELLO",1) = "O"
TAIL(12|23|2|6|70,2) = 6|70


TOTAL

Syntax:

TOTAL N value

Return Value:

N value

Description:

This operator allows you to reference the running total of a field that is being totalled. The value returned is the total including the current line.

On a subtotal line, the formulas defining fields are re-evaluated using the appropriate total value of any totalled field requested, so the use of the TOTAL operator is unnecessary in that case

Example:

When referring to the current total on a detail line, for example in calculating a moving average, you could use:

TOTAL ORDER_VALUE/TOTAL COUNT

where ORDER_VALUE is a totalled database field, and COUNT is a totalled, calculated field with the formula: 1


TRUNC

Syntax: | TRUNC(N value, N decimal_places)
Return Value: | N truncated_number
Description: | TRUNC truncates a number to the specified number of decimal places TRUNC returns a number with the same scale factor as the precision
Example: | TRUNC(21.66,1) = 21.6


UPPERCASE

Syntax: | UPPERCASE(C field)
Return Value: | C upper_case_characters
Precedence: | 10
Description: | UPPERCASE converts a character field to uppercase
Example: | UPPERCASE("Hello") = "HELLO"


VALIDNUM

Syntax:

VALIDNUM(C character_field)

Return Value:

Y result

Description:

VALIDNUM checks whether a character field contains only numeral characters (0 9). The result is TRUE if the field contains only numeral characters

Example:

VALIDNUM("A123") = FALSE
VALIDNUM("0123") = TRUE


YEAR

Syntax: | YEAR(D the_date)
Return Value: | N year
Precedence: | 11
Description: | YEAR extracts the year part of a date
Example: | YEAR(25.12.1991) = 1991


Pre-Defined Variables

Some pre-defined variables are available in formulas:

Variable Name | Return Value
CLIENT_NAME | Your company name
CLOCK | Current time
DB_NAME | Name of the database against which the report is being run
FILENAME | Name of the file being printed
LOGGED_ON | TRUE if user has logged on via menu
NOW | Current time
NULL | The null value
OUTPUT_DEVICE | Output device combination. For Example: Screen, file or printer
PAGE_DEPTH | Number of lines that can be printed on a page
PAGE_NR | Number of page being printed
READ_ACCESS | User's read access level
REPORT_NAME | Name of the report program
START_DATE | Date program was loaded
START_TIME | Time program was loaded
SYS_CLOCK | Current time
SYS_DATE | Today's date
TODAY | Today's date
USER_LANGUAGE | User's selected language code
USER_NAME | Name of logged user
WRITE_ACCESS | User's write access level

You cannot print predefined variables out directly, you must use them in formulas.


Using Functions/Macros

In QA Build you can define functions for use in QA Build and Report Writer formulas. The text is evaluated as a macro wherever the name is specified in the formula.

For information on how to define functions with QA Build, refer to Function Definition, in the QA Build Developer's Guide.


Printing a Report (REPPRINT)

REPPRINT is used to output a report or export file and to run a deletion.

You can run REPPRINT and REPSETUP from the command line or from the system menu. For details of running these applications from the command line see Loading from the Command Line

What REPPRINT does

This section is provided to enable users of Report Writer to know what their Report Writer program should look like. It is provided for two related reasons:

  • If your report is not what you expect you can look at the rules and form a judgement as to whether it is your program or REPPRINT which is at fault.
  • The author wishes to avoid the maintenance problem which has been caused in the past by users complain that a particular report is printed differently from a previous version. It has sometimes happened that upon making a change to list something not previously listed or vice versa in order to satisy one user then another user has complained of precisely the opposite problem!

  1. At the top of each page various headings are printed under user control containing information about what report is being printed and when, followed by the users heading, followed by headings for the fields of the report. If the report is folded then all the headings for all the field are printed unless fold headers are selected in which case just the headers for the next fold to be printed are printed. For exports any headings are normally omitted, and if included they are printed only on page 1 of the report.

  2. For each record processed a detail line will be printed unless it is a subtotal only report. For the first line of the report all the fields which are listed are printed. On subsequent lines only fields that have changed or which are repeated are printed until a break occurs. Following a break all the fields to the right of the break will be printed even if they have not changed. If the first line of a new page is a detail line all the fields will be printed then too, but if the first line of a page is a break line then the next detail line will be printed normally. If a report is a subtotal only report which has some 'list before break' fields then a detail line will produced before every break.

  3. If the value of a field which is a break field changes than a break line is printed. If there are multiple break fields than a break on any particular level necessarily implies that there is a break on all the subsequent break fields. If several breaks occur at once then as many as possible of them are superimposed. The algorithm used to superimpose is slightly complicated to explain but the basic idea is simple: if the number of records processed since the last break at a particular level is the same as the number of records at a lower break level (that is a break field that is nearer the top of the field list) then the fields for the two break levels are grouped together. In each such case the lowest and highest break levels that have matching record counts are processed together.

  4. Fields are listed on a break line if they are at or to the right of the lowest break in the group being processed and before the break field after the last break field in the group. Fields to the right of this are printed if they are not break fields and are totalled or calculated. Fields to the left are printed if the break line is being printed at the top of the page, or if the last break line was at a lower level and no detail lines hace been printed since (which can only happen in subtotal only reports), but only if they are set to be listed on break alines and also on detail lines.

    Example. Consider the following data. The first three fields are all break fields. All the fields are listed, the break fields are not repeated and all the fields are listed on total line. The numeric field is totalled.

       A B C 1.0
   A B C 1.3
   A B D 1.0
   B A D 2.0
   B A D 1.0

    The following output should be produced. Subtotal lines are asterisked.

       A B C 1.0
         1.3

       C 2.3 *

       D 1.0
               (Not printed if you suppress one item subtotals)
       D 1.0 * (Not printed if you suppress one item subtotals)

   A B   3.3 *

   B A D 2.0
         1.0

   B A D 3.0

         6.3  (This is the grand total and would be printed on the
               grand totals page only)

  5. Treatment of multi occurrence fields.

    If a multi occurrence field is listed but not repeated REPPRINT will superimpose lines like this:

       Name         Customer Address        Unpaid Invoices   Amount

   Major        10 Downing St           1000              £ 10
                London                  1001              £100 SW1
   Major                                                  £110

  6. Formulas

    Formulas. When a calculated field is printed on a subtotal line then if it is calculated using any totalled fields then the field is calculated using the total that appears on that line.

  7. Exports

    Exports are not paginated and no spacing lines are printed before or after subtotals. The delimiter is not printed on the last field.

  8. Field Layout

    The width occupied by a field is whichever is the wider of the heading and the width required to display the selected number of occurrences.

    For numeric fields that are right justified (including fields with leading zeros or asterisks) the heading and the field itself are right justified within this width.

    For fields other than numeric fields, and for numeric fields, if left justified output or default format are selected, then both the heading and the data begin immediately following the spacing from the previous field.

Using maps

REPPRINT does all its output using maps created by REPSETUP. It is therefore theoretically possible to tailor the output by editing the maps but most of the maps are reposted every time the report is edited. However the following maps are not altered once they have been posted by REPSETUP.

Map Nr | Description
3 | Printed before grand total
4 | Printed after grand total
6 | Printed at bottom of each page except the last - normally blank
7 | Printed at top of each page unless -nocopy specified
10 | End of report

The other maps are as follows:

  • 14,15,16,24,25,26 etc.

    These are maps used for spacing before totals (14 etc) after folds (15 etc) and after totals (16 etc). In theory you could edit them to add text and so on. However when you add or delete fields to the report the text will be likely to get attached to the wrong field.

  • Maps 11,21,31 etc are the headings for the fields Maps 12,22,32 etc are for the data. These maps are posted every time the report is edited or tested.

  • 8 Company name data,time,page etc. You can remove fields from this map to suppress individual fields.

    The fields printed are:

    1 | client name (from sysdata)
    2 | dbname
    3 | report name
    4 | date
    5 | time
    6 | the word 'page'
    7 | the pagenr

    However the map will be overwritten next time the report is tested or edited.


Loading REPPRINT from the DP4 System Menu

REPPRINT can be accessed from the DP4 menu as follows:

From Release 4.621

  1. From the DP4 System menu, select option 5 - Other standard facilities.

  2. Select Import/Export and Reporting facilities:

  3. To run REPPRINT, choose Run Report Writer programs.

Earlier Releases

  1. From the DP4 System menu, select Other standard facilities.

  2. Select Report Writer facilities.

  3. To run REPPRINT, choose Run Report Writer programs.

Choosing the Report to Print

  • Once you have started the REPPRINT program, you are asked to specify which database you wish to use. Then you must indicate which program you wish to run. Press <Ctrl+P> to call up a list of available programs. Depending on the type of program and the options you chose when you created the program you may be asked a number of supplementary questions

    While REPPRINT is running the program, messages appear on screen telling you the number of:

    • Records processed
    • Records extracted
    • Sort passes required
    • The current sort pass

    The report is then output to the device(s) you have specified.


Managing Report Writer Programs

This section tells you how to manage your Report Writer programs with REPSETUP. Among the management tasks are:

  • Testing Report Writer programs to assess the impact of database changes and optionally modify the report
  • Moving programs between databases
  • Printing details of programs
  • Deleting programs which are no longer required

    Before testing, moving or deleting Report Writer programs, make sure that there are no C applications that use mapsets with the same name as the Report Writer program. If there are, the REPSETUP management options will affect their mapsets.


Testing Programs

To test programs, run REPSETUP and then open the Test menu. You can test all the programs or just a selection.

To test all the programs, select option 1 - Test all programs.

To test selected programs, select option 2 - Test selected programs. You are asked which program(s) you wish to test - press <Ctrl+P> to call up list of available programs.

You are asked about the output device(s) you wish to use for the errors. You can ask for the errors to go to the screen, a printer or a file. If you choose to print the errors, you need to specify in the Print device field the device whose characteristics you want to use for printing or formatting the print file. In the Output file field, you need to give the print file a name. Report Writer supplies the default file extension .ERR, but you can change this if you wish.

Options

Select option 7 - Options to refine the testing process. You are asked the following series of questions:

  • Test only?

    Answer 'Yes' if you want to test the program but do not want any corrections to be made to it.

  • Pause on error?

    Answer 'Yes' if you would like Report Writer to ask what action to take whenever a problem is encountered during testing. If you are going to do this, you will probably find it just as easy to use the Program editing option from the Edit menu to validate the program, as this allows you to correct problems interactively.

  • Skip programs marked OK?

    Answer 'Yes' if you do not want Report Writer to test those programs which are marked as usable. Answer 'No' if you wish to test programs which have failed unexpectedly in REPPRINT.


Moving Programs

When you have created a program, you might want to transfer it to another database or rename it. To do either of these, run REPSETUP and then open the Move menu.

You can move either all the programs in the database or just a selection.

Moving is made up of two actions:

  1. Extracting the program to a program file

  2. Posting the program file to a new location

To move all the programs, select option 1 - Extract all programs

To move selected programs, select option 2 - Extract selected programs. You are then asked which program you wish to move. Press <Ctrl+P> to call up a list of available programs.

You are asked about the output device(s) you wish to use:

You can ask for the output to go to the screen, a printer or a file. If you choose to print the output, you need to specify in the Print device field the device whose characteristics you want to use for printing or formatting the print file. In the Output file field, you need to give the print file a name. Report Writer supplies the default file extension .PAR, but you can change this if you wish.

When you have extracted a program, it is in ASCII format. You must post it to its new location - see Posting a Program File below.

Renaming Programs

To rename a program, select option 3 - Rename programs from the Move menu.

You are asked which program you wish to rename. Press <Ctrl+P> to call up a list of available programs. You are then asked what the new name is to be.

Posting a Program File

Posting is the second half of the move operation. When you have extracted a file, you need to post it to its new location.

To post a file, first make sure that you have selected the database to which you wish to post the program. If you have only just extracted the program, this involves coming out of REPSETUP and then going back into another database.

Once you are in the database, run REPSETUP and open the Move menu. Then select option 4 - Post program file. You are asked to specify which file you want to post. Press <Ctrl+P> to call up a list of available files.

The file appears on screen as it is posted and you are returned to the Move menu when the posting is completed.

Options

Option 7 - Options from the Move menu allows you to specify how you want Report Writer to deal with errors during program posting.

You are asked a series of questions, as described in the following sections.

  • Pause on error?

    Answer 'Yes' if you would like to be prompted for the action to be taken by Report Writer whenever a problem is encountered during posting.

  • Post without echoing file?

    Answer 'Yes' to suppress the display of the file being posted. This speeds up the posting process, but if errors are encountered it is harder to see what is wrong.


Printing Programs

To print a program, run REPSETUP and then open the Print menu. You can opt to print all programs or just a selection of them.

To print all the programs, select option 1 - Print all programs.

To print selected programs, select option 2 - Print selected programs. You are asked which program you wish to print. Press <Ctrl + P> to call up a list of available programs.

You are asked about the output device(s) you wish to use.

You can ask for the output to go to the screen, a printer or a file. If you choose to print the output, you need to specify in the Print device field the device whose characteristics you want to use for printing or formatting the print file. In the Output file field, you need to give the print file a name. Report Writer supplies the default file extension .PRN, but you can change this if you wish.

Options

Use this option to specify whether you want to print the whole program or only a summary of it.


Deleting Programs

To delete a program, run REPSETUP and then open the Delete menu.

You can delete all programs or just a selection. You are asked to confirm your action. Take special care with the Delete All Programs option.


Error Control and Error Reports

This section details the following errors:

  • Join Error
  • Divide by Zero Error
  • Insufficient Field Size
  • Insufficient Memory
  • Insufficient Space
  • Other Errors


Join Error

This occurs when the extract program is trying to find a field in a table, other than the lowest table, and the appropriate entry is missing.

For example, a record for the customer number on an order might not exist in the table CUSTOMER, thereby making the order an orphan. The report would then attempt to find the customer details and, when it fails to do so, would report the fact as a join error.

The program prints out the name of the lower table it is trying to join from and the key fields for the table it is unsuccessfully trying to access.

The join error can be corrected by either removing the orphan or adding a parent.


Divide by Zero Error

Where this occurs, the formula which the program is trying to evaluate is printed out, together with the value of all the fields affected.

The error can be corrected either by modifying the data, which would be a good approach if just a few records had bad data, or by modifying the formula, for example:

If (ORDER_LINE.QUANTITY 0)Then PRICE / ORDER_LINE.QUANTITY

works with a zero quantity.


Insufficient Field Se

Insufficient Field Size

The report is produced normally except that the offending fields are filled with ???.

The solution is to alter the presentation length of the offending fields, either in the report program or in the output format.


Insufficient Memory

The solution is to reduce the complexity of the report.


Insufficient Space

This generally means that there is not enough space on the hard disk for Report Writer to write its work files.


Other Errors

There are some other errors that may occur as a result of inconsistent or corrupt report parameters. The error message identifies the problem.


Loading from the Command Line

This Appendix is divided into the following topics:

  • Loading REPSETUP
  • Loading REPPRINT


Loading REPSETUP

You can load REPSETUP from the command line or from the system menu. In general, you want the user to use the menu with the command tails already set up by you.

The following command tails can be appended to REPSETUP to specify the database and Report Writer programs:

Command Tail |  | Purpose
−DB <database_name> |  | Specify the database
−PARA <name> −PROG <name> | } | Specify the Report Writer program
−ALL |  | Apply selected actions (−EXTRACT, −TEST, −DELETE, −PRINT, −POST) to all Report Writer programs

The command tails that restrict the action of REPSETUP are:

Command Tail | Purpose
−EXTRACT | Extract the Report Writer programs to an ASCII file
−POST <name> | Post the Report Writer program from the named ASCII file
−RENAME | Restrict REPSETUP to renaming Report Writer programs
−TEST | Test a Report Writer program (and upgrade from an earlier release)
−DELETE | Restrict REPSETUP to deletion
−PRINT [−DETAIL] | Restrict REPSETUP to program printing. DETAIL is optional and selects detailed format
−HELP | Popup menu of help topics

Output to files and devices is controlled by these REPSETUP command tails:

Command Tail | Purpose
−SCREEN | Direct output to the screen
−FILE <name> | Send output to the named file
−DEVICE <name> | Send output to the named device
−PRINTER | Send output to the default printer
−QUIET | Reduce screen output to improve speed

If the output device is set up at the REPSETUP phase, you are not prompted for it when you run REPPRINT. Devices specified in the command line override those set up in REPSETUP.

The device for listing errors is assumed to be the same as for listing the output, unless otherwise specified. The default action which REPSETUP takes on failure is to ignore it. If you wish it to do something else, you need to specify this in a REPPRINT command tail.

The default action for delete programs is test only, so that no deletion takes place.


Loading REPPRINT

You can run REPPRINT from the command line or from the system menu. In general, you want the user to use the menu with the command tails already set up by you. REPPRINT takes the following command tails:

Command Tail | Purpose
−DB <database_name> | Specify the database
−PROG <program_name> | Specify the name of the program
−ARGS <my_values> | Specify the command tails to REPPRINT when the 128 character command line maximum is insufficient. <my_values> is a file holding the command tails and their values
−SCREEN | Direct report output to the screen
−PRINTER <number> | Direct report output to the printer
−FILE <filename> | Direct report output to a .PRN file
−DEVICE <name> | Select the device to determine the characteristics for print or file output
−NOERROR | Suppress production of the error report
−ERRSCREEN | Direct the error report to the screen
−ERRPRINTER <number> | Direct the error report to the printer
−ERRFILE <filename> | Direct the error report to a .PRN file
−ERRDEVICE <name> | Use the named device for formatting errors
−JOINFAIL action

Action must be one of following three words:

  • IGNORE - Cause join fail errors to be ignored and defaults used
  • SKIP - Cause join fail errors to be ignored and the records skipped
  • QUIT - Cause join fail errors to terminate the program
ASK | Prompt the user for the action to take
−DIVFAIL action Action must be one of following three words:
  • IGNORE - Ignore divide by zero error and use defaults
  • SKIP - Omit items with divide by zero error
  • QUIT - Terminate the program on divide by zero error
ASK | Prompt the user for action to take
−EXTRACT <extract_file> | Force creation of an extract file
−REPRINT <extract_file> | Reprint from the specified extract file
−SUBTOTAL | Force a subtotal only report
−GRANDTOTS | Force a grand totals only report
−DELETE | Action deletions (for delete programs)
−EXHEADS | Force headings on export output
−<field_nr> <value> | Specify a runtime value, where <field_nr> is the variable field number
−COMFORT <n> | Display a count of records processed − count is updated every <n> records
−START <page_number> | Page to start/restart printing
−START_1 <start_value>
−STOP_1 <stop_value> 		When start and stop values have been specified in REPSETUP, you can preload the <start_value> and <stop_value> to specify the range of records to be selected
−PAUSE | Pause at the end of each page
−RANGE | Extract only within range of values. For example, you might want only those records belonging to customers with names beginning A to G. You are asked for the values for the key fields of the lowest or fast pass table if one is specified
−NOTRAIL | Suppress report trailer
−NOCOPY | Suppress DATAFIT/REPORTER/ DELETER message on page headings
−KIND | Prompt for retry if the printer is not available
−NEW | Delete the existing file when sending output to a file

An example of a REPPRINT command line is:

REPPRINT −DB SALESORD −PROG RUNTIME −SCREEN −DIVFAIL QUIT −1 SEE

This produces a report as follows:

  • On the screen
  • From a database called SALESORD
  • Using a program called RUNTIME
  • If there are any errors, they are directed to the screen
  • The action for any divide by zero failures is to terminate the REPPRINT program
  • No extract file is produced

  • The value for runtime field 1 is "SEE". (If −1 SEE were omitted, the value for LOOK_AT_REGION would be prompted for)

A second example is:

REPPRINT −DB SALESORD −PROG INVOICE

This produces a report as follows:

  • From a database called SALESORD
  • Using a program called INVOICE
  • The report output, and any error output, is directed as chosen in REPSETUP. If none was set, the program prompts for this information
  • Any join or divide by zero error is ignored (the fields' default values are used)
  • No extract file is produced


Sample Reports for the SALESORD Database

This section describes five sample programs that you can run against the SALESORD database, described in Appendix C. They demonstrate how to use the Report Writer to create some typical business reports:

  • LISTING

    A standard detail report listing database fields in columns

  • MAIL

    Lists customer names and their addresses for mailing labels. This report makes use of the fold field facility, within field processing, to print fields down the page

  • TOTALS

    Lists customer order details by region. This report demonstrates useful techniques, such as:

    • The use of start and stop values which allow the user to specify the range of regions to be printed when running the report
    • How to print a running count on each detail line

    • How to print a block of statistics after each region category, including the total number of records, the total value of orders and the average value of orders

  • RUNTIME

    This report prints the same information as the TOTALS report, but shows you how to make use of a runtime field to create a report for the region specified by the user at runtime

  • INVOICE

    A customer invoice on pre-printed stationery. In this report, the standard Report Writer page headings are switched off, and the predefined variable TODAY is used to print today's date


LISTING: Listing MATERIAL Table Records

LISTING is a standard detail report listing database fields in columns.

Table Details and Type

Program Type: | Report
Page Width: | 80
Lowest table: | MATERIAL
Fast pass table: | MATERIAL
Sequencing table: | MATERIAL

Report Heading

"Standard detail report listing database records".

Field Processing

The MATERIAL_CODE and DESCRIPTION are printed in columns.

Nr Name Type Occurs Sort Tot List L/Brk P/Brk Rpt Page Break Fold

1 MATERIAL_CODE U8 Yes No No Yes No No No
2 DESCRIPTION C20 Yes No No Yes No No No

Record Selection Condition

All records from the MATERIAL table are selected.

Output Options

Screen ? Yes Blank character
Printer ? No Remove trailing blanks ?
File ? Yes
Print device DEFAULT_PRINTER Ask for start/stop values ? No
File Name LISTING Pause after each page ? No
Repeat fold headers ? No Subtotal only program ? No
Print date/time/pagenr ? Yes Suppress 1 item totals ? No
Spacing after last fold 1 Suppress grand totals ? No
Spacing at end of page 4

Running the Report

REPPRINT -DB SALESORD -PROG LISTING


MAIL: Printing Mailing Labels

MAIL lists customer names and their addresses for mailing labels.

Table Details and Type

Program Type: | Report
Page Width: | 80
Lowest table: | CUSTOMER
Fast pass table: | CUSTOMER
Sequencing table: CUSTOMER |

Report Heading

"Customer address labels".

Field Processing

Fields are normally printed across the page, like this:

          Barnes J       Townsend House
                         Little Downham
                         Pymore

In field processing, you can specify that the field is folded. This causes printing to continue at the beginning of the next line, as in:

          Barnes J (folded)
          Townsend House
          Little Downham
          Pymore

Summary of field processing:

Nr Name          Type Occurs Sort Tot List L/Brk P/Brk Rpt Page Break Fold

1  NAME           C30                  Yes   No    No  Yes  No    No   No
   in CUSTOMER
2  ADDRESS        C30                  Yes   No    No  Yes  No    No   No

Record Selection Condition

All records from the CUSTOMER table are selected.

Output Options

The standard page header that includes the date, time and page number is switched off.

Screen ?      Yes                Blank character
Printer ?     No                 Remove trailing blanks ?
File ?        Yes
Print device  DEFAULT_PRINTER    Ask for start/stop values ? No
File Name     MAIL               Pause after each page ?     No
Repeat fold headers ?    No      Subtotal only program ?     No
Print date/time/pagenr ? No      Suppress 1 item totals ?    No
Spacing after last fold  1       Suppress grand totals ?     No
Spacing at end of page   4

Running the Report

REPPRINT -DB SALESORD -PROG MAIL


TOTALS: Printing Customer Orders for Several Regions

TOTALS lists customer order details by region. It demonstrates invaluable techniques, such as:

  • The use of start and stop values, allowing the user to specify the range of regions to be printed when running the report
  • How to print a running count on each detail line
  • How to print a block of statistics after each region category, including the total number of records, the total value of orders and the average value of orders

Table Details and Type

It is possible to produce the report more quickly by specifying the REGION table as the sequencing table, otherwise the report would require sorting on the CUSTOMER.REGION_CODE field. Furthermore, there are fewer records to test the start and stop range against if the REGION table is used as the fast pass table.

Program Type: | Report
Page Width: | 80
Lowest table: | ORDER_HEADER
Fast pass table: | REGION
Sequencing table: | REGION

Report Heading

"Prints customer order details with statistics for all regions".

Field Processing

The main features of the field processing are:

  • Fields 1 to 5 print customer order details in five columns of data for each region

  • Field 2, RUNNING_COUNT, prints a running total at each detail line by using the formula:
    PREVIOUS $ + 1

  • Field 4, ORDER_VALUE, has totalling turned on, but list on break turned off. The fact that the field is totalled is very important. It means that the totalled order value is accessible on break lines, later in the report, just by referring to the ORDER_VALUE again, as in:

    Field 11 ORDER_AVERAGE
    Formula: ORDER_HEADER.ORDER_VALUE/ORDER_COUNT

    which prints the average order value for the region, and:

    Field 13 ORDER_TOTAL
    Formula: ORDER_HEADER.ORDER_VALUE

    which prints the total order value

  • Fields 5 to 13, are printed after the region break, have no headings, and are specified as list on break

  • Field 5 is folded so that the calculated field 7, LABEL_STATISTICS, is printed beneath the listed fields for each region. To print some text that varies depending on the region, the formula for LABEL_STATISTICS is:

    "STATISTICS FOR " CONCAT REGION.NAME

    Only fields specified in the field list can be used in formulas, so REGION.NAME is declared as field 6

  • Fields 8 and 9 print the subtotal line:

    "Total number of orders: " ORDER_COUNT

    where field 9, ORDER_COUNT, has the formula: 1, and is totalled to provide a count of the records printed in the region category

  • Fields 10 and 11 print the subtotal line:

    "Average order value is: " ORDER_AVERAGE

    where field 11, ORDER_AVERAGE, has the formula ORDER_HEADER.ORDER_VALUE/ORDER_COUNT, and is not totalled (ORDER_VALUE was totalled in Field 4, ORDER_COUNT was totalled in Field 9)

  • Fields 12 and 13 print the subtotal line:

    "Total value of orders is:" ORDER_TOTAL

    where Field 13, ORDER_TOTAL, has the formula: ORDER_VALUE, and is not totalled

Fields processed by program:

Nr Name             Type Occurs Sort Tot List L/Brk P/Brk Rpt Page Break Fold

1  REGION_CODE       U3                   Yes   No    No  No   Yes   No   No
2  RUNNING_COUNT     N3              No   Yes   No    No  No   No    No   No
3  NAME              C30                  Yes   No    No  No   No    No   No
   in CUSTOMER
4  ORDER_NUMBER      L5              No   Yes   No    No  No   No    No   No
5  ORDER_VALUE       N8.2            Yes  Yes   No    No  Yes  No    No   Yes
6  NAME              C30                  No    No    No  No   No    No   No
   in REGION
7  LABEL_STATISTICS  C45                  No    Yes   No  No   No    No   Yes
8  LABEL_COUNT       C25                  No    Yes   No  No   No    No   No
9  ORDER_COUNT       N5              Yes  No    Yes   No  No   No    No   Yes
10 LABEL_AVERAGE     C25                  No    Yes   No  No   No    No   No
11 ORDER_AVERAGE     N7.2            No   No    Yes   No  No   No    No   Yes
12 LABEL_TOTAL       C25                  No    Yes   No  No   No    No   No
13 ORDER_TOTAL       N10.2           No   No    Yes   No  No   No    No   No

Record Selection Condition

Records are selected from the fast pass table REGION, depending on the start and stop values entered at runtime.

Output Options

Notice that the report asks the user for start/stop values, that Pause after each page ? is 'Yes', and that the grand totals are suppressed.

Running the Report

REPPRINT -DB SALESORD -PROG TOTALS

Enter the start value, for example "CAM", press <Tab> and enter the stop value "SEE".

The start value must appear earlier in the record sequence than the stop value. For example, entering "SEE" followed by "CAM", produces an empty report.

Alternatively, the start and stop values can be preloaded with command tails like this:

REPPRINT -DB SALESORD -PROG TOTALS -START_1 CAM -STOP_1 SEE


RUNTIME: Printing Customer Orders for one Region

RUNTIME prints the same information as the TOTALS report, but shows you how to make use of a runtime field to create a report for the region specified by the user at runtime.

Table Details and Type

Program Type: | Report
Page Width: | 80
Lowest table: | ORDER_HEADER
Fast pass table: | REGION
Sequencing table: | REGION

Report Heading

"Summary report printing sub-totals for one category asked for at runtime".

Field Processing

LOOK_AT_REGION is a runtime field that holds the region code supplied by the user at runtime.

Nr Name Type Occurs Sort Tot List L/Brk P/Brk Rpt Page Break Fold

1 LOOK_AT_REGION U3 No No No No No No No
2 REGION_CODE U3 Yes No No No Yes Yes No
3 RUNNING_COUNT N3 No Yes No No No No No No
4 NAME C30 Yes No No No No No No
in CUSTOMER
5 ORDER_NUMBER L5 No Yes No No No No No No
6 ORDER_VALUE N8.2 Yes Yes No No No No No Yes
7 NAME C30 No No No No No No No
in REGION
8 LABEL_STATISTICS C45 No Yes No No No No Yes
9 LABEL_COUNT C25 No Yes No No No No No
10 ORDER_COUNT N5 Yes No Yes No No No No Yes
11 LABEL_AVERAGE C25 No Yes No No No No No
12 ORDER_AVERAGE N7.2 No No Yes No No No No Yes
13 LABEL_TOTAL C25 No Yes No No No No No
14 ORDER_TOTAL N10.2 No No Yes No No No No No

Record Selection Condition

The condition to select records from the fast pass table, REGION, is:

REGION_CODE=LOOK_AT_REGION

LOOK_AT_REGION is entered by the user when the report is run.

Output Options

Screen ? Yes Blank character
Printer ? No Remove trailing blanks ?
File ? Yes
Print device DEFAULT_PRINTER Ask for start/stop values ? No
File Name TOTALS Pause after each page ? Yes
Repeat fold headers ? No Subtotal only program ? No
Print date/time/pagenr ? Yes Suppress 1 item totals ? No
Spacing after last fold 1 Suppress grand totals ? Yes
Spacing at end of page 4

Printing the Report

REPPRINT -DB SALESORD -PROG RUNTIME

Enter the REGION_CODE, for example "IRL".

Alternatively, the runtime value can be preloaded with a command tail like this:

REPPRINT -DB SALESORD -PROG RUNTIME -1 IRL

where -1 is the field number of the runtime field, LOOK_AT_REGION.


INVOICE: Printing a Customer Invoice

INVOICE prints a customer invoice on pre-printed stationery. In this report, the standard Report Writer page headings are switched off, and the predefined variable TODAY is used to print today's date.

Table Details and Type

The report is sequenced by customer.

Program Type: | Report
Page Width: | 80
Lowest table: | ORDER_LINE
Fast pass table: | CUSTOMER
Sequencing table: | CUSTOMER

Report Heading

"CUSTOMER INVOICE Acme Product Sales Plc".

Field Processing

The main features of the field processing are:

  • Field 1, CUSTOMER.NAME, presents customer records one per page (New page on change ? 'Yes' and Break on change 'Yes')

  • Fields 1 and 2 print the customer name and address at the top of the invoice, for example:

    Barnes J
    Townsend House
    Little Downham
    Pymore

  • Field 3, STATEMENT_DATE, prints today's date. It is a calculated field with the formula: TODAY, where TODAY is a predefined variable

  • Fields 4, 5, 6, 7 and 8 print the order details

  • As well as printing a column of order line values, Field 8 prints a total beneath the column. This is due to Total field ? 'Yes' and List on break lines ? 'Yes'

Nr Name             Type Occurs Sort Tot List L/Brk P/Brk Rpt Page Break Fold
1  NAME              C30                  Yes   No    No  No   Yes   Yes  Yes
   in CUSTOMER
2  ADDRESS           C30   1-3            Yes   No    No  No   No    No   Yes
   in CUSTOMER
3  STATEMENT_DATE    D10                  Yes   No    No  No   No    No   Yes
4  ORDER_NUMBER      L5              No   Yes   No    Yes No   No    No   No
5  LINE              I3              No   Yes   No    No  Yes  No    No   No
6  DESCRIPTION       C20                  Yes   No    Yes Yes  No    No   No
7  QUANTITY          I5              No   Yes   No    Yes Yes  No    No   No
   in ORDER_LINE
8  LINE_VALUE        N8.2            Yes  Yes   Yes   Yes Yes  No    No   No

Record Selection Condition

All records from the CUSTOMER table are selected.

Output Options

Pause after each page ? 'Yes' allows you to view the report page by page. Suppress 1 item totals ? 'Yes' stops a LINE_VALUE total being printed when the invoice only contains one line.

Screen ?      Yes                Blank character
Printer ?     No                 Remove trailing blanks ?
File ?        Yes
Print device  DEFAULT_PRINTER    Ask for start/stop values ? No
File Name     INV                Pause after each page ?     Yes
Repeat fold headers ?     No     Subtotal only program ?     No
Print date/time/pagenr ?  No     Suppress 1 item totals ?    Yes
Spacing after last fold   1      Suppress grand totals ?     Yes
Spacing at end of page    6

Printing the Report

REPPRINT -DB SALESORD -PROG INVOICE


Application Database

The DP4 database stored on disk as .DAT, .IND and possibly other files, which defines the tables, fields and data (including Report Writer programs). (See also Database ).


Ask for start/stop values?

Answer 'Yes' if you want to be asked at runtime for a range of records to be processed, rather than processing all records. For instance, if you are creating a list of customers and you wish to print out only those customers who live in a certain region, you could use the REGION table as a fast pass table and then specify the start and stop values as the region you require.


Base date

This is asked only when formatting a date. The default is 'No'. If you answer 'Yes', you are asked for the base date. The number of days elapsed days is then calculated. This is used, for example, where you are creating an export file for another system, which stores dates as the number of days after a base date.

If you answer 'No', you are offered a choice of print format:

Year in full Answer 'Yes' to print 1995 rather than 95

Year first Answer 'Yes' to print 99 12 08 rather than 08 12 99

Month first Answer 'Yes' to print 12 08 99 rather than 08 12 99

Month name Answer 'Yes' to print Dec rather than 12

The following table shows the possible combinations of the above options for December 8th, 1999:

Year in full | Year first | Month first | Month name | Example
 |  |  |  | 08 12 99
Yes |  |  |  | 08 12 1999
 | Yes |  |  | 99 12 08
 |  | Yes |  | 12 08 99
 |  |  | Yes | 08 Dec 99
Yes | Yes |  |  | 1999 12 08
Yes | Yes | Yes |  | 1999 12 08
Yes | Yes | Yes | Yes | 1999 Dec 08
Yes |  |  | Yes | 08 Dec 1999
Yes |  | Yes |  | 12 08 1999
Yes | Yes |  | Yes | 1999 Dec 08
Yes |  | Yes | Yes | Dec 08 1999
 | Yes | Yes | Yes | 99 Dec 08
 | Yes |  | Yes | 99 Dec 08
 |  | Yes | Yes | Dec 08 99


Blank character

You can change any blanks in C, M, P or U type fields to another character.


Blank if zero?

This applies to numeric fields only. If you say 'Yes', the field is left blank whenever its value is zero.


Break on change?

Specify whether the field is to be used as a break. The default is 'No'. Answer 'Yes' when you want to subdivide the report according to the value of the field. The usual reason for doing this is to produce subtotals for logical groups of records when you are sorting with the field. For example, with ORDER_LINE as the lowest table, you would choose ORDER_NUMBER as a break field if you wanted to calculate the value of each order.

More than one break field may be specified, so that several levels of subtotalling may be calculated. At each break, subtotalling is first forced on any break fields to the right of, or below, the field whose value has changed. All totalled and calculated fields to the right of a break field are printed. Other database fields with the same break level (that is, up to the next break field on the right) are also printed. It follows that it is important to list the fields in the correct order. For example, if you wanted to total the value of orders by customer and by order, then CUSTOMER_NUMBER must precede ORDER_NUMBER, which must precede LINE_VALUE.

Break may also be used without totalling, to insert a blank line in the report, but in this case you would need to specify List on break lines 'No' for all fields.


Break Point

The point where the value of a sort item changes is known as a break point. It is possible to control what is printed at a break point. For instance, you might choose to print some sub-totals and throw a new page.


Calculated Field

A field whose values are calculated by a formula.


Child Table

A table is a child if it defines the key fields of another table. (See Parent Table ).


Condition

A formula which must evaluate to TRUE for the next action to be performed. (See also List/Delete Condition and Fast Pass Condition ).


Context-Sensitive Help

Help for each option and field within Report Writer.


Create programs for all tables?

The default is 'No'. If you say 'Yes' one report is generated for each table on the database. Reports are generated with the name aut<xxxx>, where <xxxx> is the table's internal reference number. Exports are generated with the name exp<xxxx>.


CUSTOMER

Stores the customer's name, address and region code, credit arrangements and balance. The primary key is a unique number that identifies the customer


Data Dictionary

The set of table and field definitions (including names, types, roles and relationships) that define the database.


Data Independence

A scheme to ensure that the program accessing the data does not need to know the physical details of the data storage.


Data Type

Fields can be one of several different data types, including character, date, number, time and yes/no.


Data Type

Specify whether the new field is a character, date, number, time or yes/no field by typing the appropriate character as shown in the following table:

Type this... | If you want...
C | Mixed case character (ASCII collate)
D | Date
I | 16-bit signed integer
L | 4-byte signed integer
M | Mixed-case character (Database collate)
N | Number (for very large or non-integer numbers)
P | Pseudo-numeric (Digit-only character fields)
T | Time
U | Upper-case character with Database collate
Y | Boolean (Yes/No)

Note
If you select a database field, Report Writer fills in this information automatically, but you can override it.


Database

A file of structured data that can be accessed by Report Writer.


Database Field

A field that is on the database. (See also Field ).


Decimal Point

In export programs, you can force a particular character to be used for the decimal point. Enter the decimal point you want or NULL to use the default decimal point character.


Delete only if all records in lowest table satisfy conditions?

This option is applicable only to delete programs. In deletions, if the table to delete is not the lowest table, you can specify that records should be deleted only if all the lowest level conditions are satisfied. You could, therefore, delete orders where the quantity to ship was zero on all order lines.


Delete Program

A type of Report Writer program. The delete program is used to delete data from the database.


# $ EMPLOYEE

Stores details of Acme employees who manage customer sales regions and customer accounts, including the employee's name, gender, company grade and date of birth. The primary key is a unique number that identifies the employee


Export Program

A type of Report Writer program. An ASCII file, comma delimited by default, which can be used to post data into DP4databases or other proprietary software products such as WordStar, SuperCalc and Lotus.


Extract File

There are two kinds of extract file:

  • In REPSETUP you can create an ASCII format file (.PAR) that enables Report Writer programs to be transferred between application databases.

  • In REPPRINT you can force the creation of an extract file (.DTA) that contains the records selected for the report. A report can subsequently be produced from this extract file more quickly than from the Report Writer program. However, the extract file will become out of date if either the Report Writer program or application database is updated.


Fast Pass Condition

The condition for record selection on the fast pass table.


Fast Pass Table

Records may often be selected more quickly by applying the extraction condition to a parent or grandparent table, where fewer records need to be processed, rather than the lowest table. The parent or grandparent table selected is known as the fast pass table.


Fast Pass table

If you specify a fast pass table, REPSETUP searches for data to extract by first considering the condition you specify for the fast pass table.

Use of a fast pass table can greatly speed the production of reports, as in the following example. Suppose you require a list of customer orders in a specified region. You can choose the REGION table as a fast pass table. Later in the program you are prompted for a fast pass condition , and your response could be:

REGION_CODE = "SEE"

The result is that only records for customers in South East England are considered by REPPRINT.

By default, the fast pass table is the same as the lowest table.


Field

A single item of data in a record. (See also Field Type ).


Field Delimiter

The character, often a comma, used to separate fields in an export file.


Field delimiter

Enter the character you wish to use as a field delimiter in export files. The usual choice is a comma.


Field

When a field name is entered, it must be either the name of field in the joined tables on the database, or the name of a new field which you want to define yourself. Press <Ctrl+P> to call up a menu of accessible database fields.

If there is a pre-displayed field name, over-typing the name of a new field instead of accepting the pre-displayed value inserts this new field immediately before the pre-displayed one. Alternatively, <Insert> or <Ctrl+V> can be used for this purpose.


Field Role

A role that is applied to distinguish a field from another field of the same type.


Field Type

There are three types of field which can be used in a report:

  • Database Field Any field from the joined tables

  • Calculated Field A field that is calculated from a formula

  • Runtime Field A non-database field that the user can enter a value into at runtime


File name

If you have chosen to output the report to a file, you need to give the name of the file to be used. Unless you specify otherwise, REPSETUP gives the file the extension .PRN.


File?

Answer 'Yes' if you want the report exported to a file.


Fold (new line)?

If you say 'Yes' (the default is 'No'), the report line is folded back to the left hand side after this field, so that the next field begins at the start of a new, printed line underneath.

For example:

Barnes J
Townsend House
Little Downham
Pymore

The CUSTOMER.NAME field is folded so that it appears immediately above the ADDRESS which is the next field processed.


Folding Report Fields

An option in Report Writer that forces the next field to be printed at the beginning of a new line, instead of at the next column position on the current line.


Formula

An expression used to calculate values for calculated fields.


Formula

Here you specify exactly how a field is to be evaluated, if you have chosen a calculated field. Some formulas used in the standard report TOTALS are:

Calculated Field | Formula | Meaning
LABEL_STATISTICS | "STATISTICS FOR " CONCAT REGION.NAME | Prints a text string on the subtotal line
ORDER_COUNT | 1 | Counts the records selected to provide a total on the subtotal line
ORDER_AVERAGE | ORDER_HEADER. ORDER.VALUE/ ORDER_COUNT | Provides an average value for orders on the subtotal line
RUNNING_COUNT | PREVIOUS $+1 | Provides a running count of listed order records

See Conditions and Formulas for detailed information on formulas.


Glossary

This section describes the following terms and concepts that you will find in this manual.

• Application Database
• Break Point
• Calculated Field
• Child Table
• Condition
• Context-Sensitive Help
• Data Dictionary
• Data Independence
• Data Type
• Database
• Database Field
• Delete Program
 • Export Program
• Extract File
• Fast Pass Condition
• Fast Pass Table
• Field
• Field Delimiter
• Field Role
• Field Type
• Folding Report Fields
• Formula
• Index
• Index-Only Table
• Indexed Records
 • Joined Tables
• List/Delete Condition
• Lowest Table
• Multi-Occurs Field
• Number Format
• Operand
• Operator
• Output Devices
• Parent Table
• Post
• Precision
• Prime Parent
• Printer File
• Program Type
 • Record
• Report Program
• REPPRINT
• REPSETUP
• Runtime Field
• Scale Factor
• Sequencing Table
• Sort
• Table
• Table Link


Heading

Specify the heading that is to be used when this field is printed. Up to 4 lines are allowed, with up to 16 characters on each line.

The print width for the field is the greater of the field length and the longest heading line. The default report format allows one additional space between fields.


Horizontal layout?

This is relevant only to fields with multiple occurrences. Specify whether the occurrences are to be printed across the page (as in budgets), or one underneath the other (as in addresses). The default is 'No', which gives a vertical layout.


In parentheses?

If you say 'Yes', negative values are enclosed in parentheses, the sign being omitted. An example N type field of length 8 and precision 2 is printed as:

(7.56) in parentheses
-7.56 >not in parentheses


Include prime parents?

Answer 'Yes' if you want to include fields from the prime parent table. A table has a prime parent when the keys of an immediate parent table appear as the first part of the key of the child table. For example, the ORDER_HEADER table is a prime parent of the ORDER_LINE table.


Index

A retrieval mechanism that allows records to be accessed in a particular order.


Index-Only Table

An index-only table is inhibited from having records entered into it, and is used to create secondary indexes for its child tables.


Indexed Records

Records in a table are indexed by the primary key, and any secondary keys in the table.


Joined Tables

The tables whose fields are accessible after the lowest table has been specified.


Length

This is the size that is to be used in conjunction with the heading in calculating the print width for the field. If you do not allow enough space for a field, the report prints out a series of ??? for numbers and truncates character fields.

For numeric fields with decimals, the length must include the decimal point and at least one digit before the point. If the field is being totalled, you need sufficient length for the total value.

Note
If you select a database field, Report Writer fills in this information automatically, but you can override it.


List before break?

Answer 'Yes' if you are going to print this report program as a subtotal only report and you want this field to be printed before any break (subtotal) lines. For example, if the field is the REGION_CODE, the report is printed like this:

Region Code

SEE

STATISTICS FOR: South East England

Total number of orders: 10
Average order value is: 518.49
Total value of orders is: 5184.89

If you answer 'No', then, provided you answered 'Yes' to List on break lines, the report is printed like this:

STATISTICS FOR: South East England

Total number of orders: 10
Average order value is: 518.49
Total value of orders is: 5184.89


List/Delete Condition

The selection criteria that must be met for records to be included when reporting, exporting or deleting.


List field?

Answer 'Yes' if you want this field to appear in the list output. You would answer 'No' if you were using this field simply as part of a formula to calculate another field, or if you only want the field output on break lines.


List on break lines?

Answer 'Yes' if you want this field to be listed on any break (subtotal) and grand total lines.

When a change in the value of a break field is encountered, all fields with the answer 'Yes' to List on break lines? are printed on the subtotal line, with a blank line before and after the subtotal line. The value taken for those fields is the current subtotal, that is, the total calculated since the last change in the break field. This is also the value used if a totalled field occurs in any formula for a calculated field on the subtotal line.


Low Occurs/High Occurs

If a field has multiple occurrences, such as the CUSTOMER.ADDRESS field, you can specify which of the occurrences you want to appear in the report.

For example, CUSTOMER.ADDRESS has three occurrences. You might want only lines 2 and 3 to appear. You would therefore specify a low occurs of 2 and a high occurs of 3.


Lowest Table

Specification of this table defines what fields you have access to in the report. Only fields from tables that are upwardly related to the lowest table can be included in the report.


Lowest table

The lowest table only gives you access to its fields and those of its parents, grandparents and so on. For example, if you choose ORDER_HEADER as the lowest table, fields in CUSTOMER, REGION and EMPLOYEE are accessible, but fields in MATERIAL are not, because it is not upwardly related to ORDER_HEADER.

The lowest table and all the tables upwardly accessible from it are known as the joined tables.

You can enter the name of any table on the database, provided your security level allows access to it. If you enter a blank, or your entry does not exactly match a known table name, or you press <Ctrl+P>, REPSETUP offers a menu of the allowable tables. Choose the table you want by moving the cursor to highlight a name and then pressing <Enter>.


MATERIAL

Stores details of each material including its code, description, unit price, quantity in stock and re-order level together with the code of the principal and the alternative supplier of the material. The material code is the primary key to the MATERIAL table


Multi-Occurs Field

Fields may have multiple occurrences. For example, an address field with three occurrences can hold a three-line address.


New page on change?

Specify whether a change in this field's value should cause the report to skip to a new page. The default is 'No'. You may often choose to break the report or throw a new page for the field on which you are sorting. For example, in the TOTALS report, a new page is thrown when the REGION_CODE changes.


Number Format

The format in which numbers appear when printed.


Number format

Use this field to specify how you want numbers to be printed - press <Ctrl+P> to see a list of options.


Operand

The item that is acted upon in an expression. It can take a constant or variable value.


Operator

The item specifying the action to be performed on operands in an expression. Operators must be used with operands of the correct data type. Some examples of operators are: * (multiply), - (minus), AND (logical AND).


ORDER_HEADER

Stores the details of the date and time the order was placed, its value and customer payments made against it, delivery details, customer number and code of the Acme employee responsible for the account. The primary key is a unique number that identifies each order. A customer may have several order header records, each of which may have several order lines giving materials and quantities ordered. An order header record may also have several order payment records


ORDER_LINE

Includes one record for each different material in an order. ORDER_LINE contains the order number, a line number that identifies an order line, the material code and quantity ordered, together with the value of the order line. The order number plus line number form the primary key that identifies each record


ORDER_PAYMENT

Includes one record for each payment made against an order. ORDER_PAYMENT records the order number, a payment number that identifies separate payments, the media code (cheque, direct debit, credit card), the amount paid, the card number and payment date and time


Output Devices

The screen, printer or file selection to which data is sent.


Page width

Enter the width, in characters, that your program should use. For report programs this depends on the width of the paper being used by your printer and how many characters per inch you are going to print. Typical column widths are 80, 132 and 255 but you can specify a width up to 510. For export programs if you accept the default 0, each line is printed as long as it needs to be.


Page width

This is the number of characters that can be printed on one line of the report. The default value is 132. As each field is specified, a running total of the width so far is displayed. The print width of any field is either the heading length or the data length, whichever is the longer, plus the user-specified spacing.

If the page width is exceeded, the report is folded, which means that printing returns to the left margin and continues on a new line. A warning message to this effect is displayed.


Parent Table

A table which has a one-to-many relationship with another table (a child table). (See Child Table ).


Pause after each page?

Answer 'Yes' if, when the report is printed on screen, you want to prompt the program to continue after each screen of information.


Post

To send information to the database.


Precedence

If you choose to sort using this field, you are asked to say what priority this field takes in the sort sequence.

For example, if you wanted to sort orders by region and then customer name, you would assign the REGION_CODE field sort priority 1 and the CUSTOMER.NAME field sort priority 2:

ANG Anderson H
Arrowsmith E
.
.
.
CAM Abrahams P F
Russell P


Precision

This is the number of decimal places displayed.


Precision

This is the number of decimal places required. A maximum of 8 decimal places is allowed. Specify a precision of zero for integers. Numbers that are defined with a precision of zero are printed without the decimal point.

Note
If you select a database field, Report Writer fills in this information automatically, but you can override it.


Prime Parent

A table has a prime parent when the keys of an immediate parent table appear as the first part of the key of the child table.


Print date/time/page nr?

If you answer 'No', Report Writer suppresses all heading information, that is, Company Name, Database Name, Program Name, Date, Time and Page number. The default is 'Yes'.


Print device

If you have chosen to output the report to a printer, you need to specify which printer you are going to use. Press <Ctrl+P> to call up a list of the print devices on your system.


Print hundredths?

This applies to a time field. Answer 'Yes' if you would like hundredths of a second to be shown.


Print in default format?

If you say 'Yes', the fields are printed as specified in DFSETUP. If you say 'No', you are asked how you want the fields to be printed.


Print in quotes?

An example N type field of length 8 and precision 2 is printed as:

"7.56" in quotes
7.56 not in quotes

A C type field of length 8 would be printed as:

"HAMLET" in quotes
HAMLET not in quotes


Print seconds?

This applies to a time field. Answer 'Yes' if you would like seconds to be shown.


Printer File

An ASCII file (.PRN) to which report output has been directed.


Printer?

Answer 'Yes' if you want the report to be printed.


Program Type

The type of Report Writer program you wish to create. (See also Delete Program, Export Program and Report Program ).


Program type

There are three options: Report, Export and Delete:

  • The Report option is used for formatted output which may be directed to the printer, the screen or a file

  • The Export option produces a file, comma delimited by default, which can be used to post data into DP4 databases, or other proprietary software products such as Excel and Lotus.

  • The Delete option produces a program which, when run, deletes data from the database.

    The program type selected determines the later selections that you need to make.


Program type to generate

You can select either a report or export program. For security reasons, you cannot automatically generate a delete program.


Record

A single set of information, including the key fields and data fields, that is stored in a table.


REGION

Stores details of the UK regions where customers live, including the name of the region and the company employee managing the region. The primary key is a unique code that identifies each region


Remove trailing blanks

Any C, M, P or U type fields can be printed without trailing blanks.


Repeat fold headers

This option is available only with report programs. In reports with folds, you have the choice of field headings repeated every time a new record is printed, or just once at the top of each page. If the fields before the fold are set up with Repeat if unchanged 'No', then the headings are only repeated when the value of one of these fields is printed.


Repeat if unchanged?

Specify whether the value of the field is to be printed if it is the same as the previous value. The default is 'Yes'. The value is always printed following a new page and, in some circumstances, following a break.


Replace existing programs?

The only reason for answering 'No' would be if you had customised an automatically generated program. However, if you want to do this, it would be better to create a new program copied from the generated one.


Report Program

A type of Report Writer program, used to produce formatted output which may be directed to the printer, the screen or a file.


REPPRINT

The DP4 utility that allows you to run programs created by REPSETUP


REPSETUP

The DP4 utility that allows you to create and amend report, export and delete programs interactively. REPSETUP also provides utilities for managing Report Writer programs.


Role

You are asked to fill in this information if the field you have selected has more than one role. For example, the QUANTITY field of the MATERIAL table has the roles IN_STOCK and RE_ORDER_LEVEL.

Press <Ctrl+P> to call up a list of possible roles.


Runtime Field

A non-database field that the user enters a value into at runtime.


Scale Factor

This determines how the field is stored and processed, as opposed to how it is displayed.


Scale Factor

This determines how the field is stored and processed, as opposed to how it is displayed. Giving a field a length of 6 and a precision of 2, followed by a scale factor of 2, would mean the number 503.37 would be processed as 50337, then converted back for display. This feature eliminates precision rounding errors.

You are advised to make scale factor equal to precision in normal circumstances.

Note
If you select a database field, Report Writer fills in this information automatically, but you can override it.


Screen?

Answer 'Yes' if you want the program to be output to the screen.


Separate thousands?

Answer 'Yes' if you would like thousands to be separated from hundreds, for example:

1,900

The separator is usually a comma, but you can alter this in DFSETUP.


Separator

This applies to time and date fields. Define the character you would like to be used to separate hours and seconds. A common choice is the colon.


Sequencing Table

A sequencing table provides a means of accessing the records in the desired order. This saves processing time because the report would otherwise require sorting.


Sequencing table

The data is read from the lowest table using the primary index of the sequencing table. As with the fast pass table, it is essential to have the index set to 'update' in SETLINK. The sequencing table must be the parent of the lowest table or, if one is selected, the fast pass table.

For example, CUSTOMER is a parent of ORDER_HEADER and you can assume that the index is active. The primary key field for ORDER_HEADER is ORDER_NUMBER, but suppose that we want to list the orders in customer number sequence instead. By choosing CUSTOMER as the sequencing table, we are able to achieve this without sorting on the ORDER_HEADER.CUSTOMER_NUMBER field. This can greatly speed up the production of the report. Note that although index only tables do not have any data in them, and therefore cannot be used as fast pass tables, they can be used as sequencing tables.

You are only asked for a report sequencing table if the lowest table has at least one parent, which may be an index only table.

If you answer 'Yes', you are asked for the name of the table you wish to use for sequencing. Enter the name, or press <Ctrl+P> for a list of possible tables.


Sort

Records can be printed in the order you want by selecting one or more fields in the report as sort fields. You can specify whether the records are ordered in ascending field value or in descending field value. If sorting on several fields, you can specify the sort precedence.


Sort

You can choose to use one of three sort methods on each field - 'None', 'Ascending', or 'Descending'. Ascending and descending have different meanings according to the data type of the field, as shown in the examples below:

Data Type | Description | Ascending | Descending
C | Character | A - Z | Z - A
D | Date | Jan - Dec | Dec - Jan
I/L/N | Integer/Number | -100 to 100 | 100 to -100
T | Time | 9am - 5pm | 5pm - 9am
Y | Yes/No | N/A | N/A

If the data is not to be sorted using this field, select 'None'. An example where sorting would be useful is where customers are only indexed by number, but you want the report to show them in alphabetical order of names. You would then sort by the CUSTOMER.NAME field in ascending order.


Spacing after last fold

Enter the number of blank lines you require after printing data for the last fold and moving to an earlier fold.


Spacing at end of page

Enter the number of blank lines you require at the end of each page of the report.


Spacing from last field

The number of character spaces between this field's print width and the previous one defaults to 1. You can change this to give additional control over the layout of the report.


Subtotal only program?

If you answer 'Yes', only the subtotal lines of the report are printed.


SUPPLIER

Stores the company's name and address. The primary key is a unique number that identifies the company


Suppress 1 item totals?

Answer 'Yes' to suppress the printing of subtotal lines when they are the total of just one line. The single line is still printed with the same values as would be on the subtotal line.


Suppress grand totals?

If you have chosen totalling of any fields, you can use this option to decide whether to create an extra page at the end of the report with grand totals of those fields. The default is 'Yes', which means that you do not want this extra page.

Since version 4.420 Report Writer has only printed totals on the grand totals line, and not other fields set to be listed on break lines. This is probably not the behaviour one would wish for. However in the interests maintaining compatibility the current behaviour will not be changed - i.e. only totalled variables will be printed on the grand total page. However it is quite easy to print out a grand total line with with other fields. All that is necessary is to put a break on a field which never changes (such as a run time input field or a dummy field) prior to all the other break fields. A break line will only be printed for this field at the end of the report and will contain the correct fields.

If you do this you will probably wish to suppress the grand totals.


Table

A two-dimensional representation of data in which fields comprise the columns and records the rows.


Table

If the field you have specified appears in more than one table, you are asked to specify which table you mean to use. For example, the NAME field appears in several tables, including REGION, CUSTOMER and EMPLOYEE.

Press <Ctrl+P> to call up a list of the tables in which the field appears.


Table Link

A link between tables is formed by the inclusion of the fields of one table in a second table. Tables are created with the MAKEDB utility.


Table to delete

You may delete from the lowest table or any other table upwardly accessible from it. If the lowest table has no parents, this question is skipped.


Trailing sign?

An example N type field of length 8 and precision 2 is printed as:

7.56- with trailing sign
-7.56 without trailing sign


Type

There are three types of field which can be used in a report:

  • Database field

    Any field from the joined tables. If you cannot specify the field you need, but are sure it is on the database, it may be in a table that is inaccessible from the lowest table. Try re-specifying the lowest table

  • Calculated field

    (for example, the field STOCK_VALUE, which could be calculated by multiplying MATERIAL.QUANTITY.IN_STOCK by MATERIAL.PRICE)

    Now is a good time to state a general rule for formulas. Database fields used in formulas must be specified earlier in the field list. Such fields need not, however, be printed in the report

  • Runtime supplied field

    This is a non-database field that the user can enter a value into at runtime. For example, in a report that prints customer details for different regions, you could specify a runtime field called LOOK_AT_REGION. The condition for extracting records from the database is subsequently specified as REGION_CODE = LOOK_AT_REGION


Unit name

Press <Ctrl+P> to choose from a list of available units. If you want to set up other units, you need to do this in DFSETUP.


Vertical space after fold

Enter the number of blank lines you want after each fold. The default is 1 line.


Vertical space after subtotals

Enter the number of blank lines you want after each subtotal line. The default is 1 line.


Vertical space before subtotals

Enter the number of blank lines you want before each subtotal line. The default is 1 line.


Word for No

This is only asked if the data type is Y. It is the text that is to be printed when the value of the field is 'No'. It can be up to 8 characters long, and values of blank are allowed. The default is 'No', truncated if necessary.


Word for Yes

This is only asked if the data type is Y. It is the text that is to be printed when the value of the field is 'Yes'. It can be up to 8 characters long, and values of blank are allowed. The default is 'Yes', truncated if necessary.