Creating a column by defining expressions with Prompted Query

You can create a column in your report by defining an expression. An expression adds, subtracts, multiplies, or divides the values in two or more columns and places the results in a new column.

About this task

In this example, you create a total earnings column that is the sum of each employee's salary and commission.

Procedure

To create a column by using expressions:

  1. Select the table, either by typing the name on the Tables panel or by selecting the table from the Table List.
  2. Select the columns from the Columns panel by typing any character beside each one.
  3. At the bottom of the Columns panel, select choice 1, Expression (A+B, etc.).
  4. Press Enter.
    The Expression panel displays, which is shown in the following figure.
    Figure 1. Enter the expression you want to use.
     PROMPTED QUERY                                       MODIFIED  LINE    1
                                 +-------------------------------------------+
   Tables:                       |                  Columns                  |
     Q.STAF +--------------------------------------------------------------+ |
            |                          Expression                          | |
   Columns: |                                                              | |
     NAME   | Type an expression.  You can use the following arithmetic    | |
     DEPT   | operators: add(+), subtract(-), multiply(*) and divide(/).   | |
     SALARY |                                                              | |
     COMM   | ( salary + comm                                            ) | |
            | (                                                          ) | |
   *** END  | (                                                          ) | |
            | (                                                          ) | |
            | (                                                          ) | |
            +--------------------------------------------------------------+ |
            | F1=Help  F4=List  F12=Cancel                                 | |
            +--------------------------------------------------------------+ |
                                 |   2. Summary Functions (SUM, etc.)...     |
                                 +-------------------------------------------+
                                 | F1=Help  F5=Describe  F7=Backward         |
                                 | F8=Forward  F12=Cancel                    |
                                 +-------------------------------------------+
 
 
 OK, ENTER performed. Please proceed.
  5. Enter your expression.
    For example, enter:
    SALARY + COMM

    You can also create more complex expressions. For more information, press the Help function key on the Expressions panel. You can also press the List function key to see columns that you can use in your expression.

    The names of the tables and columns you selected are displayed in the echo area. The new column that you created is also displayed under the heading Columns. The Specify panel displays again with choice 3, Row Conditions, selected, as shown in the following figure:

    Figure 2. The new column, SALARY+COMM, appears in the echo area.
     PROMPTED QUERY                                       MODIFIED  LINE    1
                                                    +------------------------+
   Tables:                                          |        Specify         |
     Q.STAFF                                        |                        |
                                                    | Select an item.        |
   Columns:                                         |                        |
     NAME                                           | 3 1. Tables...         |
     DEPT                                           |   2. Columns...        |
     JOB                                            |   3. Row Conditions... |
     SALARY                                         |   4. Sort...           |
     COMM                                           |   5. Duplicate Rows... |
     SALARY+COMM                                    |   6. Time Period...    |
                                                    +------------------------+
                                                    | F1=Help  F12=Cancel    |
   *** END ***                                      +------------------------+