HP 3000 Manuals HP 3000 Manuals
Creating The Product/Option Report
The product/option report that you are about to develop uses a start of
report function to display a screen. This screen allows the user to
enter the starting and finishing values for a range of product numbers
for the report.
The report then lists each of these products and the options for each
product. The report calculates the cost of stock for each option by
multiplying the quantity on hand by the cost price. When all options for
a product have been listed, the total cost of stock for that product is
calculated and printed. Finally, the report calculates the total cost of
stock for all products listed.
Some Preparations
If you are developing the HP ALLBASE/SQL based application, read on below
to create a select list that you will need to join the option table and
the product table.
If you are developing the KSAM based application or the HP TurboIMAGE/iX
based application, skip to Report Header to begin defining this report.
Creating the HP ALLBASE/SQL Based Applications Select List.
To create the select list:
The select list you will create, prod_opt, requires an additional field
specification opt_descript. This field specification is identical to the
existing description field specification.
1. Use the copying utility to create it now.
2. Then create and generate the prod_opt select list.
The prod_opt select list is as follows:
product_no = product_no,
description = description,
supplier_no,
lead_time,
option_no,
opt_descript = option.description,
cost,
quantity,
unit_measure
This select list uses the new field specification opt_descript. This is
necessary to avoid duplicate field specification names in the select
list.
This select list requires you to enter the right hand side of the
product_no, description, and opt_descript columns. These entries are
required to define the columns completely to avoid ambiguity.
This select list also demonstrates the use of qualified names in the
column definitions. The column definitions for the product_no column and
both description columns contain the name of the table as a qualifier.
This qualifier is necessary to avoid ambiguous column definitions. The
terms on the right hand side of these entries use the standard SQL syntax
for referencing table columns. That is, you must reference a column as
table_name.column_name.
Now you may begin defining the report.
Defining the Report Header
To define the report header:
1. Call up the report header screen and enter the data shown below.
Two sets of data are shown: one for KSAM and HP TurboIMAGE/iX and
the other for HP ALLBASE/SQL.
2. Accept the default entries for any fields not listed here.
3. Press the Commit Data function key when you have completed the
screen.
KSAM Based and HP TurboIMAGE/iX Based Applications
Report Name prod_opt
Report File Designator prodop
Primary File[.Record] product
Index 1
Characters per line 100
Start of Report Function product_range
Name
The last field is one that KSAM developers and HP TurboIMAGE/iX
developers haven't used yet. It is the name of a function that is
executed when the report starts. In this application, the function
displays a screen that asks the user for the range of product numbers to
be included in the report. The report is produced when this function
finishes.
HP ALLBASE/SQL Based Applications
Report Name prod_opt
Report File Designator prodop
Primary File[.Record] prod_opt
Characters per line 100
Start of Report Function product_range
Name
This report uses an HP ALLBASE/SQL select list as the primary report
file. The select list contains columns from two HP ALLBASE/SQL tables
(the product table and the option table). While you develop this report,
you will see how you can use the selection and sorting facilities
provided by HP ALLBASE/SQL in conjunction with HP ALLBASE/4GL.
The last field contains the name of a function that is executed when the
report starts. In this application, the function displays a screen that
asks the user for the range of product numbers to be included in the
report. The report is produced when this function finishes.
Creating the Screen
Now you are ready to create the product_range screen.
![[]](../../pic3k/M012318/FFN893c50.gif)
product_range Screen
This data screen requires two data input fields. Neither field uses any
data movement or logic.
To create the screen:
1. Define the screen header
2. Paint the image.
3. Create both fields using the dictionary field specification
product_no to ensure that automatic product number range checking
is done. You don't need to enter any screen field details.
4. Remember to generate the screen.
Creating the Function
Next you create the product_range function.
Function - product_range
This function is called as the start of report function for the prod_opt
report.
If you are developing the KSAM based or HP TurboIMAGE/iX based
applications, continue reading below.
If you are developing the HP ALLBASE/SQL based application, turn to HP
ALLBASE/SQL Based Applications for a description of the function you will
create.
KSAM Based and HP TurboIMAGE/iX Based Applications
1. Create the product_range function.
It executes the product_range screen, retrieves data from the
screen, and initializes some items for use later in the report.
2. After creating it, generate the function.
1 SCREEN product_range
2 IF *S01 $>=$ *S02 THEN MOVELOOP 2 *S01 1
*P02 -1 ELSE ENTER 5
3 MOVELOOP 2 *P01 1 *S01 1
4 SHOW
5 MOVE " " *P03
6 OFF *ALL
7 EXIT
The function operates as follows.
Line 1 This step executes the product_range data screen.
This screen allows the user to enter the range of
products to be included in the report. When the
user commits the screen, this step terminates and
the next step is executed.
The start of report function is the only function
in a report that can display a screen.
Line 2 This step checks whether the lower limit value is
less than the upper limit value. If it is, control
passes to step 5.
If the lower limit value is greater than the upper
limit value, this step swaps the two values. The
MOVELOOP command moves the two screen fields to two
scratch-pad fields.
Scratch-pad fields are general purpose working
variables that assume the attributes and value of
the data moved into them. If the source is
numeric, the scratch-pad field becomes numeric. If
the source is alphanumeric, the scratch-pad field
is treated as an alphanumeric variable. You can
reference scratch-pad fields by number in the form
*P01 to *P99.
You can also assign names to scratch-pad fields
using the Scratch-Pad Fields screen in the
dictionary menu. Refer to the HP ALLBASE/4GL
Developer Reference Manual for more details on
naming and accessing scratch-pad fields.
In this case the MOVELOOP command is performed
twice. The first screen field, *S01, is moved to
the second scratch-pad field *P02. Then the second
screen field, *S02, is moved to the first
scratch-pad field *P01. Note that the step factor
for the screen fields is positive, while the step
factor for the scratch-pad fields is negative.
Line 3 This step moves the contents of the scratch-pad
fields back to the screen fields in reverse order.
This swaps both the values. Note that both step
factors are positive.
Line 4 This step redisplays the screen field buffers.
Step 3 moves values directly into the internal
screen field buffers so this step displays the new
values. The swap is complete and the user has seen
the change on the screen.
Lines 5, 6 and 7 These steps initialize scratch-pad field *P03 with
a single space and initialize all the user switches
for later use in the report. The function then
exits.
Now turn to Report Sorting to continue defining the report.
HP ALLBASE/SQL Based
Applications
The product_range function requires two variables, first_product and
last_product. Both variables have a length of six characters and have a
type U edit code.
1. Create the variables for HP ALLBASE/SQL applications.
2. When finished, generate the function
The product_range function is called as the start of report function for
the prod_opt report. It executes the product_range screen, retrieves
data from the screen, initializes some items for use later in the report,
and declares and opens a cursor for the report.
1 SCREEN product_range
2 IF *S01 >= *S02 THEN MOVELOOP 2 *S01 1 *P02 -1
ELSE ENTER 5
3 MOVELOOP 2 *P01 1 *S01 1
4 SHOW
5 MOVE *S01 V-first_product
6 MOVE *S02 V-last_product
7 SQL get_prod_opt
8 EXIT
The function operates as follows.
Line 1 This step executes the product_range data screen.
This screen allows the user to enter the range of
products to be included in the report. When the
user commits the screen, this step terminates and
the next step is executed.
The start of report function is the only function
in a report that can display a screen.
Line 2 This step checks whether the lower limit value is
less than the upper limit value. If it is, control
passes to step 5.
If the lower limit value is greater than the upper
limit value, this step swaps the two values. The
MOVELOOP command moves the two screen fields to two
scratch-pad fields.
Scratch-pad fields are general purpose working
variables that assume the attributes and value of
the data moved into them. If the source is
numeric, the scratch-pad field becomes numeric. If
the source is alphanumeric, the scratch-pad field
is treated as an alphanumeric variable. You can
reference scratch-pad fields by number in the form
*P01 to *P99. You can also assign names to
scratch-pad fields using the Scratch-Pad Fields
screen in the dictionary menu. Refer to the HP
ALLBASE/4GL Developer Reference Manual for more
details on naming and accessing scratch-pad fields.
In this case the MOVELOOP command is performed
twice. The first screen field, *S01, is moved to
the second scratch-pad field *P02. Then the second
screen field, *S02, is moved to the first
scratch-pad field *P01. Note that the step factor
for the screen fields is positive, while the step
factor for the scratch-pad fields is negative.
Line 3 This step moves the contents of the scratch-pad
fields back to the screen fields in reverse order.
This swaps both the values. Note that both step
factors are positive.
Line 4 This step redisplays the screen field buffers.
Step 3 moves values directly into the internal
screen field buffers so this step displays the new
values. The swap is complete and the user has seen
the change on the screen.
Lines 5 and 6 These two steps move the first and last product
numbers for the report to the variables
first_product and last_product.
Line 7 This step calls the SQL logic block get_prod_opt
which declares and opens a cursor on the prod_opt
select list. You will create the logic block soon.
Line 8 The function then exits.
Note that this function does not contain a FILE *NEXT command. The
report generator issues the equivalent of a FILE *FIRST command to
retrieve the first record in the active set for the cursor.
SQL Logic Block - get_prod_opt.
The get_prod_opt SQL logic block contains the following SELECT command.
Create and generate this SQL logic block now.
SELECT :prod_opt FROM product, option
WHERE product.product_no = option.product_no
AND product.product_no BETWEEN :V-first_product
AND :V-last_product
ORDER BY product_no, option_no;
To join two tables, HP ALLBASE/SQL requires that they have at least one
common column. In this case, both the product table and the option table
contain a product_no column.
This SELECT command declares and opens a cursor on the prod_opt select
list. The active set for this cursor contains all the records where the
value in the product_no column is equal to or greater than the value in
the variable first_product and equal to or less than the value in the
variable last_product. For each product number in the specified range of
product numbers, this select command retrieves the corresponding product
records, and all option records for these products.
The select command also contains an ORDER BY clause to specify that the
select list records are sorted in product number order, and within each
product number, records are sorted by option number.