Sample 2: Worksheet

#### Search Results

Search

# Sample 2: Worksheet

#### Task: List cruises sorted according to cruise starting date and show the cheapest cruise price available for each data and the cheapest cruise ofer all. Do not show cruises which cost less than 500.

This transaction uses the Worksheet to define fields for horizontal and vertical display, change field output definition, a sort field, a control field and a system function at control breaks.

#### Transaction Specification

Primary File | SAG-TOURS-E-CRUISE
Display Fields | 1A: START-DATE 1B: END-DATE 2: CRUISE-PRICE 3: CRUISE-ID 4: START-HARBOR 5: DESTINATION-HARBOR 6: CHARTER-YACHT-TYPE
Control Field | 1: START-DATE
System Function | `Minimum` on field CRUISE-PRICE
Sort Field | 2: START-HARBOR
Selection Criteria | AT GE 500

This section covers the following topic:

- Create the Transaction SAMPLE2

## Create the Transaction SAMPLE2

Add a Transaction

- Add a transaction called SAMPLE2 with the description Worksheet as described in Step 1 of the section Sample 1 earlier in this chapter.

To Choose File

The reports requires data from the file SAG-TOURS-E-CRUISE.

- Choose the file SAG-TOURS-E-CRUISE as described in Step 2 of the section Sample 1.

To Choose Fields

The report requires data stored in the fields CRUISE-ID, CHARTER-YACHT-TYPE, START-DATE, END-DATE, START-HARBOR, DESTINATION-HARBOR and CRUISE-PRICE.

- Choose the fields as described in Step 3 of Sample 1.

To Determine Display Sequence for Vertical Output

START-DATE and END-DATE are to be displayed first with START-DATE above END-DAT E.

1. Enter a "question mark (?)" in the Sequence column next to the field START-DATE.

   The Help: Seq Column window appears:

2. Mark Vertical Output.

        11:56                      ***** Super Natural *****                 1993-05-24
        SAMPLE2                          - Worksheet -                           SN3011

        Ref DB    Field Name                 +------------Help: Seq Column------------+
        AA  1K    CRUISE-ID ................ ! Field: START-DATE                      !
        AE  1K 2  START-DATE ............... !                                        !
        AH  1K 2  END-DATE ................. ! Used to define the output sequence for !
        AJ  1K    START-HARBOR ............. ! this field.                            !
        AK  1K    DESTINATION-HARBOR ....... !                                        !
        AP  1  2  CHARTER-YACHT-TYPE ....... !   _  Horizontal Output                 !
        AT  1  2  CRUISE-PRICE ............. !   X  Vertical Output                   !
        BE        __________________________ !   _  Output as Text                    !
        BF        __________________________ !                                        !
        BG        __________________________ +----------------------------------------+

3. Press Enter.

   The Help: Vertical Output window appears.

4. Mark the field next to 1.

        11:56                      ***** Super Natural *****                 1993-05-24
        SAMPLE2                          - Worksheet -                           SN3011

        Ref DB    Field Name                 +------------He +-Help: Vertical Output--+
        AA  1K    CRUISE-ID ................ ! Field: START- ! Field: START-DATE      !
        AE  1K 2  START-DATE ............... !               !                        !
        AH  1K 2  END-DATE ................. ! Used to defin !       Number           !
        AJ  1K    START-HARBOR ............. ! this field.   !    x     1             !
        AK  1K    DESTINATION-HARBOR ....... !               !    _     7             !
        AP  1  2  CHARTER-YACHT-TYPE ....... !   _  Horizont !    _     8             !
        AT  1  2  CRUISE-PRICE ............. !   m  Vertical !    _     9             !
        BE        __________________________ !   _  Output a !    _    10             !
        BF        __________________________ !               !    _    11             !
        BG        __________________________ +-------------- !    _    12             !
        BH        ________________________________   ___   _ !    _    13             !
        BI        ________________________________   ___   _ !    _    14             !
        BJ        ________________________________   ___   _ !    _    15             !
        BK        ________________________________   ___   _ !                        !
                                                             +------------------------+

   This is the number of the column the field will be in.

5. Press Enter.

   The Help: Horizontal Output window appears:

6. Mark the field next to 1A.

        11:59                      ***** Super Natural *****                 1993-05-24
        SAMPLE2                          - Worksheet -                           SN3011

        Ref DB    Field Name                 +------------ +-Help: Vertical Output----+
        AA  1K    CRUISE-ID ................ ! Field: STAR ! Field: START-DATE        !
        AE  1K 2  START-DATE ............... !             !                          !
        AH  1K 2  END-DATE ................. ! Used to def !       Number             !
        AJ  1K    START-HARBOR ............. ! this field. !    X    1A               !
        AK  1K    DESTINATION-HARBOR ....... !             !    _    1B               !
        AP  1  2  CHARTER-YACHT-TYPE ....... !   _  Horizo !    _    1C               !
        AT  1  2  CRUISE-PRICE ............. !   m  Vertic !    _    1D               !
        BE        __________________________ !   _  Output !    _    1E               !
        BF        __________________________ !             !    _    1F               !
        BG        __________________________ +------------ !    _    1G               !
        BH        ________________________________   ___   !    _    1H               !
        BI        ________________________________   ___   !    _    1I               !
        BJ        ________________________________   ___   !    _    1J               !
        BK        ________________________________   ___   !                          !
                                                           +--------------------------+

   This positions the field at the top of the column.

7. Press Enter.

   1A appears in the Sequence column next to the field START-DATE.

   You can also enter 1A directly in the Sequence column.

8. Do the same for the field END-DATE but specify the position 1B.

   This positions the field END-DATE directly beneath the field START-DATE in column 1 of the report.

   You can also enter 1B directly in the Sequence column.

To Change Header of Vertical Output Column

The columns for vertical output have the header of the field marked with the letter nearest A by default. This report requires a column header which is more appropriate to the contents, so you need to change the header for the first field in the column.

1. Enter a "question mark (?)" in the Definition column next to the field START-DATE.

   The Help: Def Column window appears:

        08:51                      ***** Super Natural *****                 1993-02-04
        SAMPLE2                          - Worksheet -                           SN3011

        Ref DB    Field Name               +-------------Help: Def Column-------------+
        AA  1K    CRUISE-ID .............. ! Field: START-DATE                        !
        AE  1K 2  START-DATE ............. !                                          !
        AH  1K 2  END-DATE ............... ! Used to modify the output definition for !
        AJ  1K    START-HARBOR ........... ! this field.                              !
        AK  1K    DESTINATION-HARBOR ..... !                                          !
        AP  1  2  CHARTER-YACHT-TYPE ..... !   _  OD  Output Definition               !
        AT  1  2  CRUISE-PRICE ........... !                                          !
        BE        ________________________ +------------------------------------------+

2. Mark the Output Definition field.

3. Press Enter.

   You can also enter "OD" directly in the Definition column. The Define Field Output window appears.

4. Make the following entries in the Heading fields:

        09:40                      ***** Super Natural *****                 1993-02-03
        SAMPLE2                          - Worksheet -                           SN3011

        Ref DB    Field Na +-------------------Define Field Output--------------------+
        AA  1K    CRUISE-I ! Field: START-DATE                                        !
        AE  1K 2  START-DA !                                                          !
        AH  1K 2  END-DATE ! Format/Length ....... N8                                 !
        AJ  1K    START-HA ! Numeric sign ........ _ (Y/N)                            !
        AK  1K    DESTINAT ! Output length ....... 8__                                !
        AP  1  2  CHARTER- !        dec. places .. _                                  !
        AT  1  2  CRUISE-P ! Heading ............. START DATE____________________     !
        BE        ________ !                       END DATE______________________     !
        BF        ________ !                       ______________________________     !
        BG        ________ ! Edit mask                                                !
        BH        ________ !   ______________________________________________________ !
        BI        ________ ! Suppress identical values .. _ (Y/N)                     !
        BJ        ________ ! Print zero values .......... _ (Y/N)                     !
        BK        ________ ! Occurrences (1)  ___ : ___  (2)  ___ : ___               !
                           !                                                          !
                           +----------------------------------------------------------+

5. Press Enter.

To Determine Display Sequence for Horizontal Output

The rest of the fields are to be displayed horizontally.CRUISE-PRICE is to be displayed first after the vertical output column, then CRUISE-ID, START-HARBOR, DESTINATION-HARBOR, and CHARTER-YACHT-TYPE. When you just enter a number in the Sequence column, the field is displayed horizontally by default.

1. Type "2" in the Sequence column next to the field CRUISE-PRICE.

2. Type "3" in the Sequence column next to the field CRUISE-ID.

3. Type "4" in the Sequence column next to the field START-HARBOR.

4. Type "5" in the Sequence column next to the field DESTINATION-HARBOR.

5. Type "6" in the Sequence column next to the field CHARTER-YACHT-TYPE.

To Define Control Break Processing

The report must present data sorted according to the cruise starting date and show the cheapest cruise price available for each date. However, the report should not show cruises costing less than 500.

1. Enter a "question mark (?)" in the Sort/Control (S/C) column next to the field START-DATE.

   The Help: S/C Column window appears.

2. Mark the Control Field field.

3. Mark the Sort/Control Level field.

   The Help: S/C Column window now has the following entries:

        10:57                      ***** Super Natural *****                 1993-02-03
        SAMPLE2                          - Worksheet -                           SN3011

        Ref DB    Field Name                         +--------Help: S/C Column--------+
        AA  1K    CRUISE-ID ......................   ! Field: START-DATE             !
        AE  1K 2  START-DATE .....................   !                                !
        AH  1K 2  END-DATE .......................   !  Type                          !
        AJ  1K    START-HARBOR ...................   !    _  S  Sort Field            !
        AK  1K    DESTINATION-HARBOR .............   !    x  C  Control Field         !
        AP  1  2  CHARTER-YACHT-TYPE .............   !                                !
        AT  1  2  CRUISE-PRICE ...................   !  Sort Order                    !
        BE        ________________________________   !    _  A or 'blank'  Ascending  !
        BF        ________________________________   !    _  D             Descending !
        BG        ________________________________   !                                !
        BH        ________________________________   !  Sort/Control Level            !
        BI        ________________________________   !    x  1     _  4     _  7      !
        BJ        ________________________________   !    _  2     _  5     _  8      !
        BK        ________________________________   !    _  3     _  6     _  9      !
                                                     !                                !
                                                     +--------------------------------+

4. Press Enter.

   The entry C1 appears in the Sort/Control (S/C) column next to the field START-DATE.

   You can also enter C1 directly in the Sort/Control (S/C) column. When the value of the control field START-DATE changes, the report is to display information concerning the field CRUISE-PRICE.

5. Enter a "question mark (?)" in the Function (Func) column next to the field CRUISE-PRICE.

   The Help: Func Column window appears.

6. Mark the System Functions field.

        11:33                      ***** Super Natural *****                 1993-02-03
        SAMPLE2                          - Worksheet -                           SN3011

        Ref DB    Field Name              +-------------Help: Func Column-------------+
        AA  1K    CRUISE-ID ............. ! Field: CRUISE-PRICE                       !
        AE  1K 2  START-DATE ............ !                                           !
        AH  1K 2  END-DATE .............. ! Used to define special functions for this !
        AJ  1K    START-HARBOR .......... ! field:                                    !
        AK  1K    DESTINATION-HARBOR .... !                                           !
        AP  1  2  CHARTER-YACHT-TYPE .... !   X  SF  System Functions                 !
        AT  1  2  CRUISE-PRICE .......... !   _  IA  Interfield Arithmetic            !
        BE        _______________________ !   _  CB  Special Break Conditions         !
        BF        _______________________ !                                           !
        BG        _______________________ +-------------------------------------------+

   The Specify System Functions window appears.

7. Mark the Min column next to Grand End of Report to display the minimum value of the field CRUISE-PRICE in the whole report.

8. Mark the Min column next to 1 START-DATE to display the minimum value of the field CRUISE-PRICE each time the value of the (1st) control field START-DATE changes.

   The Specify System Functions window now has the following entries:

        16:59                      ***** Super Natural *****                 1993-02-02
        SAMPLE2                          - Worksheet -                           SN3011

        +--------------------------Specify System Functions---------------------------+
        ! Field: CRUISE-PRICE                                                         !
        !                                                                  N   N   N  !
        ! Level  Control Field                    Cnt Tot Sum Avg Max Min Cnt Avg Min !
        ! Grand  End of Report                     _   _   _   _   _   X   _   _   _  !
        !     1  START-DATE                        _   _   _   _   _   X   _   _   _  !
        !                                          _   _   _   _   _   _   _   _   _  !
        !                                          _   _   _   _   _   _   _   _   _  !
        !                                          _   _   _   _   _   _   _   _   _  !
        !                                          _   _   _   _   _   _   _   _   _  !
        !                                          _   _   _   _   _   _   _   _   _  !
        !                                          _   _   _   _   _   _   _   _   _  !
        !                                          _   _   _   _   _   _   _   _   _  !
        !                                          _   _   _   _   _   _   _   _   _  !
        !                                                                             !
        +-----------------------------------------------------------------------------+

9. Press Enter.

   A chevron (>) appears in the Function (Func) column next to the field CRUISE-PRICE to indicate that the column has been used.

Note
For further information on control break processing, see The Sort/Control (S/C) Column and The Function (Func) Column in Working with the Worksheet in the User's Guide.

To Define Further Sort Order

The cruises for each starting date are to be sorted according to their start harbor.

- Enter "S2" in the Sort/Control (S/C) column next to the field START-HARBOR.

  You can also use the help system by entering a "question mark (?)" as described above.

  The Worksheet now has the following entries:

       09:21                      ***** Super Natural *****                 1993-02-04
       SAMPLE2                          - Worksheet -                           SN3011

       Ref DB    Field Name                         Seq   S/C  Func Def  Info Del
       AA  1K    CRUISE-ID ......................   3__   ___   __   __   __   __
       AE  1K 2  START-DATE .....................   1A_   C1_   __   __   __   __
       AH  1K 2  END-DATE .......................   1B_   ___   __   __   __   __
       AJ  1K    START-HARBOR ...................   4__   S2_   __   __   __   __
       AK  1K    DESTINATION-HARBOR .............   5__   ___   __   __   __   __
       AP  1  2  CHARTER-YACHT-TYPE .............   6__   ___   __   __   __   __
       AT  1  2  CRUISE-PRICE ...................   2__   ___   >_   __   __   __
       BE        ________________________________   ___   ___   __   __   __   __
       BF        ________________________________   ___   ___   __   __   __   __
       BG        ________________________________   ___   ___   __   __   __   __
       BH        ________________________________   ___   ___   __   __   __   __
       BI        ________________________________   ___   ___   __   __   __   __
       BJ        ________________________________   ___   ___   __   __   __   __
       BK        ________________________________   ___   ___   __   __   __   __

       Fill in required definitions
       Command ===>
       Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
             Help  Next  Exit  Field Incl  Flip  -     +           Sel   Run   Canc

To Define Selection Criteria

1. Issue the `SELECTION` command. You can also abbreviate this command to `SEL`.

   The Selection Editor appears. The report requires information on cruises costing more than 500.

2. Enter "AT GE 500" in the editor area.

   `AT` is the Super Natural field reference for the field CRUISE-PRICE. You can always use field references instead of field names in the full-screen editors. The field references for fields in the Field Name column of the Worksheet are listed next to them in the Field Reference (Ref) column.

   `GE` is the operator `Greater Equals (greater than or equal to)`. Instead of `GE`, you can use the special characters `>=`.

To Run the Transaction

1. Run the transaction as described in Step 6 of the section Sample 1.

   The Report Line Length window appears because you have chosen too many fields to be displayed together horizontally on one screen:

        10:13                      ***** Super Natural *****                 1993-02-03
        CRUI2                            - Worksheet -                           SN3011

        Ref DB    Field Name     +-----------------Report Line Length-----------------+
        AA  1K    CRUISE-ID .... !                                                    !
        AE  1K 2  START-DATE ... ! The selected fields required a line size of .. 108 !
        AF  1  2  START-TIME ... ! The field headers required a line size of .... 109 !
        AJ  1K    START-HARBOR . ! The available Online  line size at present ... 79  !
        AK  1K    DESTINATION-HA !                                                    !
        AP  1  2  CHARTER-YACHT- !   _  Enlarge Available Line Size                   !
        AT  1  2  CRUISE-PRICE . !   _  Change to Report Type Vertical List           !
        BE        ______________ !   _  Truncate Headers                              !
        BF        ______________ !   _  Remove Excess Fields                          !
        BG        ______________ !   _  Remove Excess Fields and Truncate Headers     !
        BH        ______________ !   _  Modify Transaction                            !
        BI        ______________ !                                                    !
        BJ        ______________ +----------------------------------------------------+
        BK        ________________________________   ___   ___   __   __   __   __

2. Mark the Enlarge available line size option.

3. Press Enter.

   The report is generated. Part of the resulting report is shown below:

   Because you chose the option Enlarge Available Line Size, the report is wider than the screen.

4. Use PF11 and PF10 to shift to the left and right in the report.

5. Use PF9 to position the field at cursor position to the left-hand side of the screen.