3 Creating Reports

Listing the Workfile

Once Query has found entries and copied them into the workfile, the contents of the workfile can be listed to a display, to a printer, or to a spool file. When listing the contents of the workfile, you must first tell Query to which device the information is to be listed; this is done with the OUTPUT TO command. Once Query knows which device to use, you can execute the LIST command to list data items in a columnar format or the LINEAR LIST command to list data items in a linear format.

Specifying the Output Device

To specify the device on which reports and the output from the INFO command are written, execute the OUTPUT TO command:

OUTPUT TO device [,"width"[,"lenght"] ]

The parameter device tells Query the physical location of the output device. The following are acceptable values for device:

Value

Description

DISPLAY
Specifies that your terminal is the output device.
PRINTER
Specifies that printer defined with printer no. 0 is the output device.
STDOUT
Specifies that HP-UX stdout is the output device.
STDERR
Specifies that HP-UX stderr is the output device.
CONSOLE
Specifies that the console of your HP9000 system is the output device.
"spool file name"
Specifies that the listing is to be stored in a spool file to be used later.
"printer number"
An integer number specifying the printer number of the output device. (For more information on printer number, refer to the chapter "Output Operations" in the Eloquence Manual.)
The device specified by an OUTPUT TO command remains the designated output device until another OUTPUT TO command is executed.

The parameter width specifies the number of characters per line; valid values for width range from 20 through 264. If you do not specify it, Query chooses a default width depending on the output device specified: 80 characters for the display, 130 characters for the printer, 130 characters for spool files, and 80 characters for a printer number.

The parameter length is used to specify the number of lines per page on the output device. If you do not specify it, Query chooses a default device page length depending on the device specified: 20 lines for the display, 66 lines for the printer or spool file, and 66 lines for a printer number.

For example, assume you want to list the workfile to a printer whose printer number is 0; the printer contains narrow paper that holds 80 characters per line. You would type the following: 

   OUTPUT TO PRINTER, "80"

Listing Data Items in Columnar Format

Once you have found the data items you want to list, you can copy them from the workfile to the output device with the LIST command. This command prints the data items in columnar format and allows you to specify a page heading, which is printed at the top of each page of the report. The syntax of the LIST command is:

LIST ["heading"] [,item list ]

The heading is printed at the top of each page in the report. It can consists of at most 80 characters.

The item list is a list of the data sets and/or data items to be listed from the workfile. If you do not supply an item list, Query assumes that you want all values in the workfile listed.

The values copied from the workfile are listed in columns. The head of the column lists the data set name and data item name.

For example, in the previous FIND command, values for the OPTION data set with corresponding PRODUCT_NO value of 100 were placed in the workfile. Using the following OUTPUT TO and LIST commands, the sample listing below could be generated: 

   OUTPUT TO DISPLAY RETURN

   LIST RETURN

The data items are listed in the same order that they are specified in the item list parameter. Or, if a data set is specified (or all items are to be listed), the order of each data item listed in the FIND command or in the schema determines the order they are listed with the LIST command.

If there are more columns than will fit on the output device width, Query wraps the columns around. For example:

Another list command, LINEAR LIST, can be used to avoid wrap around.

Listing Data Items in a Linear Format

Query's LINEAR LIST command allows you to copy items from the workfile and list them, one item per line, on the output device. Optionally, you can also supply a page heading with this command, causing the heading to be printed at the top of each page of the report. The syntax for the LINEAR LIST command is:

LINEAR LIST ["heading"] [,item list ]

The heading is printed at the top of each page in the report. It can consist of at most 80 characters.

The item list is a list of the data sets and/or data items to be listed from the workfile. If you do not supply an item list, Query assumes that you want all values in the workfile listed.

The LINEAR LIST command lists the data items and data sets specified in item list in a linear format (one data item per line). For example:

The data items are listed in the same order that they are specified in the item list parameter. Or if a data set is specified (or all items are to be listed), the order each data item was listed in the FIND command or in the schema determines the order they are listed with the LIST command.

Formatting the Listing

In addition to page headings and a choice between columnar or linear listings, Query provides other commands for controlling the format of a listing:

Sorting Data Items

Once items have been placed in the workfile with the FIND command, you can sort the items alphabetically or numerically with the SORT BY command. Its syntax is as follows:

SORT BY sort list

The sort list is a list of data items to be sorted; items in the list are separated by commas. Items are sorted in ascending order unless the name of an item is followed by a D (specifying to sort in descending order). A maximum of 10 items may be sorted. An array cannot be sorted.

For example, using the sample SAD database, assume that you want to list the CUSTOMER data set in ascending order by NAME, for every customer whose order number is larger than "106"; if a customer has placed more than one order, you want to list these orders in ascending order based on order number. To do this, you would execute the following commands: 

   FIND CUSTOMER FOR CUSTOMER.ORDER_NO>"106"
   SORT BY NAME,CUSTOMER.ORDER_NO

   LIST NAME,CUSTOMER.ORDER_NO,CUSTOMER.PRODUCT_NO
Executing these commands causes the following to be displayed:

Alternately, assume that you wanted to list the CUSTOMER data set in reverse alphabetic order; like before, if a customer has placed more than one order, list those orders in ascending order based on order number. To do this, you would execute the following commands: 

   FIND CUSTOMER FOR CUSTOMER.ORDER_NO>"106"

   SORT BY NAME D,CUSTOMER.ORDER_NO

   LIST NAME,CUSTOMER.ORDER_NO,CUSTOMER.PRODUCT_NO
This would cause the following to be displayed:

Computing with Numeric Data Items

To compute and list the total of a numeric data item, use the TOTAL command. Syntax is as follows:

TOTAL item list

The item list is a list of the data items you want to be totaled. The items must be a numeric. A maximum of ten items may be totaled, and no arrays may be totaled.

For example, assume you want to know the total dollar amount that one salesperson sold in a certain month. Using the SAD database, you would type the following: 

   FIND PRICE FOR SALESPERSON = "15"

   TOTAL PRICE

   LIST

While the SORT BY command remains in effect until the next SORT BY command or FIND command is executed, the TOTAL command is in effect only until the next LIST command. Thus, you must re-enter the TOTAL command for every list.

Breaking a Listing into Groups

To break up a list by a change in the value of a single data item, use the BREAK ON command. Syntax is as follows:

BREAK ON item

The data item must be in the workfile.

For example, assume you want a list of the products each salesperson has sold and you want to list the salespeople in groups. You would execute the following commands: 

   SORT BY SALESPERSON

   BREAK ON SALESPERSON

   LIST CUSTOMER.PRODUCT_NO
The following would be displayed:

Even though SALESPERSON was not specified in the LIST command, the breaks still occur. Also note that a sort was done prior to the LIST. If a sort had not been done, the values of SALESPERSON would not have been in order and the breaks would not be what you wanted.

Creating Subtotals within a List

Use of the TOTAL command will produce a grand total. You can also use TOTAL to produce subtotals. Syntax is as follows:

BREAK ON item TOTAL item list

For example, to list the products each salesperson has sold and the total amount used, type the following: 

   SORT BY SALESPERSON

   BREAK ON SALESPERSON TOTAL PRICE

   LIST CUSTOMER.PRODUCT_NO
This would cause the following to be displayed:

As with the BREAK ON item, the data items being totaled do not have to be listed.

Eloquence Query Manual - 19 DEC 2002