Example
The following query produces the data wanted for the report. It also contains an ORDER BY clause that permits control breaks to be specified.
=> ------------------------------------------------------------------------DQD10 DATAQUERY: EDITOR ---------------------------------------------------------------------------- NAME: TEMP TYPE: QUERY STATUS: PRIVATE DESCRIPTION: ____________________________________________________________ ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+. .. ================================ T O P ===================================== 01 SELECT SALARY, 02 DEPT, 03 NAME, 04 ID 05 FROM STAFF 06 ORDER BY DEPT .. =========================== B O T T O M ================================== ------------------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> EXECUTE <PF4> SAVE <PF5> DIALOG DEF <PF6> DELETE <PF7> BACKWARD <PF8> FORWARD <PF9> UPDATE <PF10> VALIDATE <PF11> RIGHT/LEFT <PF12> CREATE MODE
Result
Note that the following default report sorts the found rows by DEPT as a result of the ORDER BY clause in the query.
03/17/11 12:16:15 SEE UNSPECIFIED REPORT FORMAT SALARY DEPT NAME ID --------- ------- -------- ------- 017654.50 7 SMITH 220 002010.00 10 LU 210 022959.20 10 MOLINARE 160 019260.25 10 DANIELS 240 021234.25 10 JONES 260 020659.80 15 HANES 50 012258.50 15 KERMISCH 170 012508.20 15 NGAN 110 016502.83 15 ROTHMAN 70 018357.50 20 SANDERS 10 018171.25 20 PERNAL 20 014252.75 20 SNEIDER 190
Options
The ORDER BY clause is important in creating the sample report. When ORDER BY is in a query, a panel appears in the Report Format sequence that allows the person executing a query to specify control breaks and page breaks based on the columns listed in the ORDER BY clause.
The ORDER BY clause tells CA Dataquery to group rows together according to data found in one or more named columns. A control break can be assigned to any ORDER BY column. A control break refers to a point in processing where CA Dataquery finds a different data value than one it has just read, meaning the current group of rows is complete. If directed, CA Dataquery performs a specified function at the control break. At a control break, CA Dataquery can perform mathematical functions, calculating results for the rows just processed. You still have to tell CA Dataquery which columns to perform the control break process on, but you do not do that using this panel.
Example
The following example shows how CA Dataquery sees the rows found by a query after sorting them by LAST-NAME. Since the query report format specified LAST-NAME as a control-break column, CA Dataquery sees the rows containing identical values in that column as logical groups and takes a control break after each group. If subtotals were specified on another panel for the CONTRIBUTIONS column, CA Dataquery would total the CONTRIBUTIONS values for each group at the control break.
|
LAST-NAME |
FIRST-NAME |
CLASS |
CONTRIBUTIONS |
|---|---|---|---|
|
Adams |
James |
A |
45345.89 |
|
Adams |
Arlene |
C |
98765.21 |
|
Adams |
Robert |
C |
23456.90 |
|
>>>>>>>>>> Control Break <<<<<<<<<< |
|||
|
Burns |
John |
A |
7897.32 |
|
Burns |
Jeff |
C |
198.21 |
|
Burns |
Alice |
B |
99321.42 |
|
>>>>>>>>>> Control Break <<<<<<<<<< |
|||
|
Crosby |
Fred |
A |
278.13 |
|
Crosby |
Mary |
C |
9978.13 |
Use control breaks to organize the selected data, grouping it to provide more easily understood results. Control breaks also provide a way to summarize large volumes of data.
Example
03/15/11 DATAQUERY DEVELOPMENT 15:04:21 SORTED COLUMNAR REPORT WITH CONTROL BREAKS, COUNTS, AVERAGES, PAGE BREAKS AND SUM DEPT NAME ID SALARY ------- --------- ------- ---------- 10 LU 210 20,010.00 MOLINARE 160 22,959.20 DANIELS 240 19,260.25 JONES 260 21,234.25 NUMBER OF PEOPLE 4 AVERAGE SALARY 20,865.92 SUM SALARY 83,463.70
03/15/11 DATAQUERY DEVELOPMENT 15:04:21 SORTED COLUMNAR REPORT CONTROL BREAKS, COUNTS, AVERAGES, SUMS, PAGE B NUMBER OF PEOPLE 37 AVERAGE SALARY 16,174.57 SUM SALARY 598,459.13
The new report contains the same data. However, the order of columns, the mathematical data that appears for each department and at the end of the report, and the paging have all been supplied by the report format panels. The next section describes the report format panels and the selections that define the sample report.
|
Copyright © 2014 CA.
All rights reserved.
|
|