Tailoring your report
The screen displays in this manual show the direct results of the SELECT statement. The displays reflect default formatting options. For example, reports have the name of the source table as a title, fields aren't edited, and groups of data are not separated by blank lines.
You can easily tailor your reports by using options available in CA OLQ's menu facility.
The following series of screens show a few of these options:
Display the report
In command mode, issue a SELECT statement to CA OLQ:
select deptid, lastname, salary, phone from emp order by deptid, lastname ! display
SELECT DEPTID, LASTNAME, SALARY, PHONE FROM EMP ORDER BY DEPTID, LASTNAME ! DISPLAY EMP REPORT mm/dd/yy DEPTID LASTNAME SALARY PHONE 3100 DOUGH 33000.00 6174458155 3100 GALLWAY 33000.00 6173349155 3100 GARFIELD 65000.00 6173321967 3100 GRANGER 34500.00 6173341212 3100 HEAROWITZ 33000.00 6173349634 3100 JACOBI 55000.00 6173348912 3100 JENSEN 37000.00 6172241955 3100 LITERATA 37500.00 6175912323 3100 TYRO 20000.00 6174459191 4000 ANGELO 18000.00 6178870235 4000 BANK 80000.00 6173321933 4000 JACKSON 34000.00 6175346767 4000 MCDOUGALL 18000.00 6178871324 4000 PENMAN 39000.00 6175341199 - 1 -
Go to the menu facility
Swap to CA OLQ's menu facility by pressing [PF9].
Note: You can also move directly to a screen by entering the name of the screen on the command line.
CA OLQ displays the Menu screen:
CA OLQ Release nn.n *** Menu *** -> Page 1 of 3 122000 Select an option and press the ENTER key. Select Command/ Show Pfkey Option Description Screen Name Help ---> Data Source for Report <--- _ Choose data tables TABle _ _ Choose subschema SUBschema _ ---> Retrieval Activity <--- _ Choose records from selected subschema RECord _ _ Choose columns for report COLumn _ _ Retrieve data to build report RETrieve _ _ Alter database access strategy LINkage _ ---> Processing Mode <--- _ Execute or create a predefined routine QFIle _ _ View existing or save current report SAVe _ _ Submit batch report request BATch _ 1=HELP 2=GLOBAL HELP 3=QUIT 4=MESSAGE 8=FWD
Change the column headings
On page 2 of the Menu screen, select the option to change column headers. CA OLQ brings you to the Report Format - Header screen. You can assign new column headings for your report and specify a character to underline the column headings. The default character is a hyphen (-).
CA OLQ Release nn.n *** Report Format - Header *** -> Page 1 of 1 134000 Specify column headers and press the ENTER key. Underline character: - Disp Seq Header EMP X DEPTID 1 department id X LASTNAME 2 employee name X PHONE 4 PHONE X SALARY 3 SALARY
When you press [Enter], CA OLQ displays a revised report:
CA OLQ Release nn.n *** Display Report *** -> Page 1 Line 1 125000 Press the ENTER key to go to the next page of the report. EMP REPORT mm/dd/yy DEPARTMENT EMPLOYEE ID NAME SALARY PHONE ───────── ─────────────── ─────────── ────────── 3100 DOUGH 33000.00 6174458155 3100 GALLWAY 33000.00 6173349155 3100 GARFIELD 65000.00 6173321967 3100 GRANGER 34500.00 6173341212
Change the report title
Press [PF6] to return to the menu options. Choose the option to change page headers and footers. CA OLQ brings you to the Page Header/Footer screen, where you can give the report a meaningful title:
CA OLQ Release nn.n *** Page Header/Footer *** -> Page 1 OF 1 152000 Specify page header(s), footer(s) and press the ENTER key. Format for $DATE: MM/DD/YY Use variables: $DATE, $TIME, $PAGE, $LINE, $USER... Skip lines before heading: 0 Skip lines after heading: 1 Line Page heading text Align 1 employee report by department ENTER 2 $DATE CENTER 3 CENTER
Once you have entered the information, press [Enter]. CA OLQ displays this report:
CA OLQ Release nn.n *** Display Report *** -> Page 1 Line 1 125000 Press the ENTER key to go to the next page of the report. EMPLOYEE REPORT BY DEPARTMENT mm/dd/yy DEPARTMENT EMPLOYEE ID NAME SALARY PHONE ───────── ─────────────── ─────────── ────────── 3100 DOUGH 33000.00 6174458155 3100 GALLWAY 33000.00 6173349155 3100 GARFIELD 65000.00 6173321967 3100 GRANGER 34500.00 6173341212 3100 HEAROWITZ 33000.00 6173349634 3100 JACOBI 55000.00 6173348912
Format the phone and salary data
Return to the Menu screen by pressing [PF6] and choose the option to change the display format of data. CA OLQ brings you to the Report Format - Picture screen where you can:
CA OLQ Release nn.n *** Report Format - Picture *** -> Page 1 of 1 137000 Specify pictures and press the ENTER key. Disp Select Options or Alter Seq $ , 0 Picture EMP X DEPTID 1 _ _ _ 9999 X LASTNAME 2 X(15) X PHONE 4 _ _ _ (999)999-9999 X SALARY 3 x x _ -ZZZZZZ9.99
CA OLQ displays this report once it processes the information entered on the Report Format - Picture screen:
CA OLQ Release nn.n *** Display Report *** -> Page 1 Line 1 125000 Press the ENTER key to go to the next page of the report. EMPLOYEE REPORT BY DEPARTMENT mm/dd/yy DEPARTMENT EMPLOYEE ID NAME SALARY PHONE ───────── ─────────────── ───────────── ───────────── 3100 DOUGH $33,000.00 (617)445-8155 3100 GALLWAY $33,000.00 (617)334-9155 3100 GARFIELD $65,000.00 (617)332-1967 3100 GRANGER $34,500.00 (617)334-1212 3100 HEAROWITZ $33,000.00 (617)334-9634 3100 JACOBI $55,000.00 (617)334-8912 3100 JENSEN $37,000.00 (617)224-1955
Group the rows by department
Press [PF6] to return to the Menu screen and choose the option to sort the sequence of the report. CA OLQ brings you to the Report Format - Sort screen. On this screen, you can indicate:
CA OLQ Release nn.n *** Report Format - Sort *** -> Page 1 of 1 133000 Specify sort or group by request and press the ENTER key. Disp Sort Order Group By Seq Priority (A/D) Level # EMP X DEPTID 1 1 a 1 X LASTNAME 2 2 a _ X PHONE 4 _ _ _ X SALARY 3 _ _ _ Display lines: Detail X and/or Summary X Group by all _ Compute: 1=HELP 3=QUIT 4=MESSAGE 5=DISPLAY 6=MENU 11=HEADER
Total the salaries
Once CA OLQ processes the information provided above, it brings you to the Report Format - Group By screen where you can enter summary information for the report. In this example, the report will display the total salaries for each department. The SKIP LINES and SEPARATOR CHARACTER fields indicate that CA OLQ will:
CA OLQ Release nn.n *** Report Format - Group By *** -> Page 1 OF 1 136000 Specify summary computations and press the ENTER key. Group by: EMP.DEPTID Level# 1 Seq Sum Avg Max Min Count _ EMP X DEPTID 1 _ _ _ _ X LASTNAME 2 _ _ _ _ X PHONE 4 _ _ _ _ X SALARY 3 x _ _ _ Skip lines after group 1 Separator character - Compute: 1=HELP 3=QUIT 4=MESSAGE 5=DISPLAY 6=MENU 10=PICTURE
When you press [Enter], CA OLQ displays this report:
CA OLQ Release nn.n *** Display Report *** -> Page 1 Line 1 105022 Sort successfully completed. 19 records in. 19 records out. 125000 Press the ENTER key to go to the next page of the report. EMPLOYEE REPORT BY DEPARTMENT mm/dd/yy DEPARTMENT EMPLOYEE ID NAME SALARY PHONE ───────── ─────────────── ──────────────────────── ───────────── 3100 DOUGH $33,000.00 (617)445-8155 3100 GALLWAY $33,000.00 (617)334-9155 3100 GARFIELD $65,000.00 (617)332-1967 3100 GRANGER $34,500.00 (617)334-1212 3100 HEAROWITZ $33,000.00 (617)334-9634 3100 JACOBI $55,000.00 (617)334-8912 3100 JENSEN $37,000.00 (617)224-1955 3100 LITERATA $37,500.00 (617)591-2323 3100 TYRO $20,000.00 (617)445-9191 ──────────── $348,000.00 - 1 - 1=HELP 3=QUIT 4=MESSAGE 6=MENU 8=FWD 10=LEFT 11=RIGHT
The end result
The report shown above contains the same data as the original report. However, this report is easier to read and contains summary information for each department.
|
Copyright © 2013 CA.
All rights reserved.
|
|