How to Make a Report from a Table › Creating Your Report From an SQL Table › Step 3— Choose columns and specify selection criteria

Step 3— Choose columns and specify selection criteria

When you create a report that retrieves data from a table, you do not have to display the whole table. You can restrict the scope of your report in three ways:

• By choosing which columns you want to display
• By specifying selection criteria to restrict which rows are retrieved for your report
• By specifying that you want to display only unique rows on your report

Choosing columns

You don't have to display all of the columns in your table. By using the Column Select screen, you can select only those columns that you want to include in your report.

For example, if you just wanted to list employees' names and department numbers, you would only select those columns:

   Columns Currently Selected:    0     Selection Criteria     Distinct N Y/N
  _ EMPDEMO.EMPLOYEE
  _ 02 EMP_ID_0415
  s 02 EMP_LAST_NAME_0415
  _ 02 EMP_FIRST_NAME_0415
  _ 02 EMP_STREET_0415
  _ 02 EMP_CITY_0415
  _ 02 EMP_STATE_0415
  _ 02 EMP_ZIP_FIRST_FIVE_0415
  _ 02 EMP_ZIP_LAST_FOUR_0415
  _ 02 EMP_PHONE_0415
  _ 02 EMP_SALARY_AMOUNT_0415
  _ 02 EMP_START_YEAR_0415
  _ 02 EMP_TERMAINATION_DATE_0415
  s 02 EMP_DEPT_ID

Specifying selection criteria

Selection criteria are logical expressions that you use to tell CA OLQ which rows of data to retrieve for your report. You specify your selection criteria in the Selection criteria field of the Column Select screen.

For example, if you only wanted to list the names of those employees in department 4000, you would specify the following:

   Columns Currently Selected:    0     Selection Criteria     Distinct Y Y/N
  _ EMPDEMO.EMPLOYEE
  _ 02 EMP_ID_0415
  s 02 EMP_LAST_NAME_0415
  _ 02 EMP_FIRST_NAME_0415
  _ 02 EMP_STREET_0415
  _ 02 EMP_CITY_0415
  _ 02 EMP_STATE_0415
  _ 02 EMP_ZIP_FIRST_FIVE_0415
  _ 02 EMP_ZIP_LAST_FOUR_0415
  _ 02 EMP_PHONE_0415
  _ 02 EMP_SALARY_AMOUNT_0415
  _ 02 EMP_START_YEAR_0415
  _ 02 EMP_TERMAINATION_DATE_0415
  s 02 EMP_DEPT_ID                          eq 4000

Specifying only unique rows

You do not have to include duplicate rows (detail lines) on your report. By using the Distinct Y/N field on the Column Select screen, you can display a report that contains only unique rows.

Select lastname, state, phone, salary, start year, termination date, and department ID. Specify that you want to want to display only unique rows. Also, specify that you want to view only the list of employees in department 4000.

     CA OLQ Release nn.n                          *** Column Select ***
 ->                                                       Page     1 Of     1
 124000 Select columns, specify selection criteria and press the ENTER key.

    Columns Currently Selected:    0     Selection Criteria     Distinct N Y/N
  _ EMPDEMO.EMPLOYEE
  _ 02 EMP_ID_0415
  s 02 EMP_LAST_NAME_0415  
  _ 02 EMP_FIRST_NAME_0415
  _ 02 EMP_STREET_0415
  _ 02 EMP_CITY_0415
  s 02 EMP_STATE_0415  
  _ 02 EMP_ZIP_FIRST_FIVE_0415
  _ 02 EMP_ZIP_LAST_FOUR_0415
  s 02 EMP_PHONE_0415  
  s 02 EMP_SALARY_AMOUNT_0415  
  s 02 EMP_START_YEAR_0415  
  s 02 EMP_TERMAINATION_DATE_0415  
  s 02 EMP_DEPT_ID .                               eq 4000  

 Additional Selection Criteria:


                                   Proceed to Selection Criteria Screen? N Y/N
 1=HELP          3=QUIT          4=MESSAGE          6=MENU          PA2=REFRESH