The following pages show the sequence of panels that appear whenever you choose <PF4> FORMAT REPORT during online execution. The panels pertaining to control breaks and page breaks do not appear if the query does not contain an ORDER BY clause.
This particular set of panels represents the definition of the preceding sample report. Following are the SQL Report Definition Steps.
Select a query for execution.
Display ONLINE EXECUTION SQL QUERY (DQE60) panel.
Press <PF4> FORMAT REPORT.
Complete REPORT TITLE panel as follows.
=> ------------------------------------------------------------------------DQF30 DATAQUERY: REPORT FORMAT - REPORT TITLE QUERY NAME: TESTEXEC ---------------------------------------------------------------------------- Enter up to two lines for a report title: => COLUMNAR REPORT => CONTROL BREAKS, COUNTS, AND SUMS Select the desired report format: X Columnar format: one row per line. _ List format: one row per page. ----------------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> DELETE FORMAT <PF4> CONTINUE
This panel tells CA Dataquery to produce a columnar report with the specified title on each page.
Caution Defining an SQL Report Format causes the current 'ACTIVE' query to be updated in the DQQ if it has been previously saved. This means that any changes to the query on the EDITOR panel made during this session is permanent even though a query update was not requested. This occurs to ensure that the SQL Report Format and SQL query syntax remain in synch with each other.
Press <PF4> CONTINUE to display the following CONTROL BREAKS (DQF40) panel. Complete the panel as shown to permit column functions and page breaks to be reported for all rows having the same Dept data. See Step 1: Plan a Report in SQL Mode for an explanation of control breaks.
=> ------------------------------------------------------------------------DQF40 DATAQUERY: REPORT FORMAT - CONTROL BREAKS QUERY NAME: TEMP ---------------------------------------------------------------------------- The named columns were used to order rows in the table resulting from the execution of this SQL query. Place any character under "CONTROL BREAK" to select that column as a control break. Place any character under "NEW PAGE" for a column selected for control break to get a new report page. ---------------------------------------------------------------------------- |SORT SEQUENCE |CONTROL | NEW COLUMN NAME |ASCEND/DESCEND| BREAK | PAGE ----------------------------------------------------------------------------- DEPT | X | X | X | | | | | | | | | | | | | | | | | | | | | ----------------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> NOT USED <PF4> CONTINUE <PF5> NOT USED <PF6> NOT USED <PF7> BACKWARD <PF8> FORWARD
Press <PF4> CONTINUE to display the COLUMN FUNCTIONS (DQF50) panel. Complete the panel as shown below to:
The SUM function and TOT function allow you to keep a running calculation on the numeric columns. The difference between the two is in how the calculation gets printed. TOT prints control break calculations and resets with every control break. SUM allows you to choose at which control you want intermediate calculations to appear. (Both permit calculation of a grand total even when ORDER BY is not present in the query.)
Press <PF1> for help with defining the report ------------------------------------------------------------------------DQF50 DATAQUERY: REPORT FORMAT - COLUMN FUNCTIONS QUERY NAME: TEMP ---------------------------------------------------------------------------- Specify the order in which the columns are to appear on the report by inserting an order number below. Column functions may be selected by placing any character under the corresponding functions. ------------------------------------------------------------------------------- NUMBER| COLUMN NAME | AVG | MAX | MIN | SUM | CNT | TOT ------------------------------------------------------------------------------- 04 | SALARY | X | | | X | | 01 | DEPT | | | | | X | 02 | NAME | | | | | | 03 | ID | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | - LAST PAGE ------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> NOT USED <PF4> CONTINUE <PF5> NOT USED <PF6> NOT USED <PF7> BACKWARD <PF8> FORWARD
Press <PF4> CONTINUE to display the following COLUMN HEADING (DQF60) panel. Leave the default values. These values create report column headings.
------------------------------------------------------------------------DQF60 DATAQUERY: REPORT FORMAT - COLUMN HEADINGS QUERY NAME: TEMP ---------------------------------------------------------------------------- You may enter alternate headings for any column on the report. Defaults are provided and can be modified. ---------------------------------------------------------------------------- COLUMN: DEPT LINE1: DEPT LINE2: COLUMN: NAME LINE1: NAME LINE2: COLUMN: ID LINE1: ID LINE2: COLUMN: SALARY LINE1: SALARY ----------------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> NOT USED <PF4> CONTINUE <PF5> NOT USED <PF6> NOT USED <PF7> BACKWARD <PF8> FORWARD
Press <PF4> CONTINUE to display the COLUMN FORMAT (DQF70) panel. Leave the default values so that all numeric data will appear in the format labeled Column 2 (without dollar signs).
------------------------------------------------------------------------DQF70 DATAQUERY: REPORT FORMAT - COLUMN FORMAT QUERY NAME: TEMP ----------------------------------------------------------------------------- Select the type of editing desired for each numeric column on your report. Place any character under the number that represents the edit format needed. Defaults are provided and can be modified. Column 1: 12345.678 Column 3: 0,012,345.678 Column 2: 12,345.678 Column 4: $12,345.68 ----------------------------------------------------------------------------- COLUMN NAME | 1 | 2 | 3 | 4 | ---------------------------------------------------------------------------- DEPT | | X | | | ID | | X | | | SALARY | | X | | | | | | | | | | | | | | | | | | ----------------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> NOT USED <PF4> CONTINUE <PF5> NOT USED <PF6> NOT USED <PF7> BACKWARD <PF8> FORWARD
Press <PF4> CONTINUE to display the FUNCTION LEGENDS (DQF80) panel. The legends are the text that will appear on the report each time a column function result is printed. Type over the default legends as follows.
------------------------------------------------------------------------DQF80 DATAQUERY: REPORT FORMAT - FUNCTION LEGENDS QUERY NAME: TEMP ---------------------------------------------------------------------------- Enter a legend to be displayed with the result of the indicated function during control break processing. Defaults are provided and can be modified. ---------------------------------------------------------------------------- FNC | COLUMN NAME | LEGEND ---------------------------------------------------------------------------- CNT | DEPT | NUMBER OF PEOPLE AVG | SALARY | AVERAGE SALARY SUM | SALARY | SUM SALARY | | | | | | | | | | | | | | ----------------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> NOT USED <PF4> CONTINUE <PF5> NOT USED <PF6> NOT USED <PF7> BACKWARD <PF8> FORWARD
Press <PF4> CONTINUE to display the FUNCTION FORMAT (DQF90) panel. Leave the defaults so that the amounts printed for the counts, averages and sums will have the format identified as Column 2 (no dollar signs).
=> ------------------------------------------------------------------------DQF90 DATAQUERY: REPORT FORMAT - FUNCTION FORMAT QUERY NAME: TEMP ---------------------------------------------------------------------------- Select the type of editing desired for each function result. Place any character under the number that represents the edit format needed. Defaults are provided and can be modified. Column 1: 12345.678 Column 3: 0,012,345.678 Column 2: 12,345.678 Column 4: $12,345.68 ---------------------------------------------------------------------------- FCN | COLUMN NAME | 1 | 2 | 3 | 4 | ------------------------------------------------------------------------------- CNT | DEPT | | X | | | AVG | SALARY | | X | | | SUM | SALARY | | X | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | ----------------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> NOT USED <PF4> CONTINUE <PF5> NOT USED <PF6> NOT USED <PF7> BACKWARD <PF8> FORWARD
Press <PF4> CONTINUE to display the FUNCTION CONTROL BREAK (DQFA0) panel for the CNT function associated with the DEPT column. One of these panels appears for each column function chosen on the COLUMN FUNCTIONS (DQF50) panel.
For every panel, select both the DEPT control break and the END OF REPORT options. This causes the results of each function chosen to appear for each department, as well as printing a count, average salary and sum of salaries for all departments at the end of the report.
------------------------------------------------------------------------DQFA0 DATAQUERY: REPORT FORMAT-FUNCTION CONTROL BREAK QUERY NAME: TEMP ----------------------------------------------------------------------------- Select the control break level at which the COUNT function for the DEPT column is to be displayed. The appropriate level may be indicated by placing any character next to the desired names. ---------------------------------------------------------------------------- SEL| CONTROL BREAK COLUMN NAME | ---------------------------------------------------------------------------- X | DEPT | X | END OF REPORT | | | | | | | | | | | | | | | ----------------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> NOT USED <PF4> CONTINUE <PF5> NOT USED <PF6> NOT USED <PF7> BACKWARD <PF8> FORWARD
When all FUNCTION CONTROL BREAK (DQFA0) panels are completed, CA Dataquery redisplays the ONLINE EXECUTION panel with the following message.
DQ125I - THE REPORT FORMAT HAS BEEN SUCCESSFULLY COMPLETED ------------------------------------------------------------------------DQE60 DATAQUERY: ONLINE EXECUTION SQL QUERY ---------------------------------------------------------------------------- EXECUTE QUERY NAMED => ACTIVE QUERY EXECUTE STEP The first query step to execute X SELECTION - Read and collect the data _ REPORTING - Produce the report REPORT DESTINATION The destination for the report _ VIDEO TERMINAL - Produce the report on the terminal _ NETWORK PRINTER ____ - Produce the report on a network printer _ SYSTEM PRINTER - Produce the report on the system printer ----------------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> EXECUTE <PF4> FORMAT REPORT
If you created the query, you have just defined the default report.
If you are not the author of the query, the specifications you entered are good for one execution only. You must reenter specifications every time you want this particular report. Or, edit the query, save it in your library with a different name, and create a different report format.
|
Copyright © 2014 CA.
All rights reserved.
|
|