Home » Other » Client Tools » Generating Header and Footer information in a Report (Oracel PL/SQL 9i Microsoft 2K3)
| Generating Header and Footer information in a Report [message #431809] |
Thu, 19 November 2009 11:12  |
dnfrantum
Messages: 11
Registered: November 2009
Location: California
|
Junior Member |
|
|
I am trying to create a report out of a query that I wrote, but I am not having the success I was hoping for. Essentially, the header should include a 'BOF' on line one, the system date and time on line two, a record count on line three, and 'EOF' on the last line. I have not been successful in getting any of these to print correctly and I don't even know how to get the record count. I suspect it is a COMPUTE and BREAK command, but I don't know how to effectively use them. I put 'BOF' and 'EOF' in both REPHEADER and REPFOOTER and TTITLE and BTITLE clauses and didn't get the output that I desire. Here is my code below:
SET FEEDBACK OFF
sET PAGESIZE 60
SET NEWPAGE 0
SET ECHO OFF
SET TRIMSPOOL ON
SET HEADING OFF
SPOOL C:\DF_KAPLAN_TEST_a.txt;
COLUMN current_date NEW_VALUE report_date
SELECT TO_CHAR(SYSDATE,'YYYYMMDD') current_date FROM DUAL;
TTITLE LEFT 'BOF'-
TTITEL LEFT report_date
BTITLE LEFT 'EOF'
SELECT
T0.OBJECT_NAME||'|'||T0.Title||'|'||CASE WHEN (T0.R_VERSION >= 1.0) THEN T0.R_VERSION ELSE '' END
||'|'||T0.R_Object_Type||'|'||T0.R_EffectiveDate
||'|'||T0.R_EffectiveDate
FROM
QM_Document_Base T0
WHERE
T0.R_LIFECYCLESTATE IN (1229,1231,1232) AND T0.R_ISSYSTEM_COPY = 0 AND T0.I_LATEST_FLAG = 1 and T0.I_Is_Deleted = 0
AND T0.R_Object_Type IN ('qu_policy_doctype','qu_sop_doctype','qu_workinstruction_doctype') ORDER BY
T0.R_Object_Type, T0.Object_Name ;
SPOOL OFF;
Any help would be greatly appreciated.
Donald
[Updated on: Thu, 19 November 2009 11:24] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Generating Header and Footer information in a Report [message #431834 is a reply to message #431831] |
Thu, 19 November 2009 11:50  |
dnfrantum
Messages: 11
Registered: November 2009
Location: California
|
Junior Member |
|
|
It should have read TTITLE, but here is a an example of what I am trying to achieve:
BOF
02-26-2009 14:19
633
"EOP105"|"Internal"|"Engineering Variance"|"6"|"0"|"CICS"|"SOP"|"Rev. F"|"General"|"01/02/2009"|"01/02/2009"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\EOP105F.PDF"
"EOP203"|"Internal"|"Engineering and Quality Test Data"|"4"|"0"|"CICS"|"SOP"|"Rev. D"|"General"|"07/26/2006"|"07/26/2006"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\EOP203D.PDF"
"EOP209"|"Internal"|"Machine Shop Work Order Request"|"2"|"0"|"CICS"|"SOP"|"Rev. B"|"General"|"10/08/2007"|"10/08/2007"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\EOP209B.PDF"
"FORM120"|"Internal"|"Document Change Form"|"17"|"0"|"CICS"|"SOP"|"Rev. U"|"General"|"01/15/2009"|"01/15/2009"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\FORM120U.PDF"
"FORM121"|"Internal"|"Label, 30-day Hold for Latex"|"2"|"0"|"CICS"|"SOP"|"Rev. B"|"General"|"11/09/2007"|"11/09/2007"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\FORM121B.PDF"
"FORM123"|"Internal"|"510(k) Change - Main Flowchart"|"2"|"0"|"CICS"|"SOP"|"Rev. B"|"General"|"05/11/2000"|"05/11/2000"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\FORM123B.PDF"
"FORM124"|"Internal"|"510(k) Change - Flowchart A - Labeling Change"|"2"|"0"|"CICS"|"SOP"|"Rev. B"|"General"|"05/11/2000"|"05/11/2000"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\FORM124B.PDF"
"FORM125"|"Internal"|"510(k) Change - Flowchart B - Technology or Performance Change"|"2"|"0"|"CICS"|"SOP"|"Rev. B"|"General"|"05/11/2000"|"05/11/2000"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\FORM125B.PDF"
"FORM126"|"Internal"|"510(k) Change - Flowchart C - Materials Change"|"2"|"0"|"CICS"|"SOP"|"Rev. B"|"General"|"05/11/2000"|"05/11/2000"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\FORM126B.PDF"
"FORM134"|"Internal"|"English to Spanish Translations Form"|"4"|"0"|"CICS"|"SOP"|"Rev. D"|"General"|"02/26/2008"|"02/26/2008"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\FORM134D.PDF"
"FORM145"|"Internal"|"Labeling Specification Form"|"7"|"0"|"CICS"|"SOP"|"Rev. G"|"General"|"06/13/2008"|"06/13/2008"|""|"1"|""|""|""|"1"|""|"0"|""|""|""|""|""|""|""|""|""|""|""|"14"|"dd"|""|""|""|""|"1"|"\\lfdcontrol\working\PDF_Active\FORM145G.PDF"
EOF
|
|
|
|
|
|
| Re: Generating Header and Footer information in a Report [message #431840 is a reply to message #431835] |
Thu, 19 November 2009 12:05 |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
If the only part that is missing is count number, you can't have it at the beginning of each page.
COMPUTE and BREAK print it at the end of the report.
You can do it in SQL but only for the first page:
SQL> select decode(grouping(v),1,to_char(count(*)),v) val
2 from (select empno||','||ename||','||sal v
3 from emp)
4 group by rollup(v)
5 order by grouping(v) desc
6 /
VAL
-----------------------------------------------------------
14
7521,WARD,1250
7566,JONES,2975
7654,MARTIN,1250
7698,BLAKE,2850
7782,CLARK,2450
7788,SCOTT,3000
7839,KING,5000
7844,TURNER,1500
7876,ADAMS,1100
7900,JAMES,950
7902,FORD,3000
7934,MILLER,1300
7369,SMITH,800
7499,ALLEN,1600
15 rows selected.
Regards
Michel
|
|
|
|
| Re: Generating Header and Footer information in a Report [message #431854 is a reply to message #431840] |
Thu, 19 November 2009 12:32 |
dnfrantum
Messages: 11
Registered: November 2009
Location: California
|
Junior Member |
|
|
That was definitely helpful for the count, but this was the output of the rest of the file. Notice the first two lines...how do I stop the date from printing at the top, as well as, eliminate the two control characters?
11-19-2009 01:37
BOF
11-19-2009 01:37
56
POL-GN-00001|Policy on Policies and Procedural Documents|1.0|qu_policy_doctype|2
2-FEB-09|22-FEB-09
...purposely truncated to eliminate too much detail...
SOP-IT-00001|Computerized System LifeCycle Management|1.0|qu_sop_doctype|22-FEB-
09|22-FEB-09
EOF
Again, here is the code for the report:
SET FEEDBACK OFF
sET PAGESIZE 60
SET NEWPAGE 0
SET ECHO OFF
SET TRIMSPOOL ON
SET HEADING OFF
SPOOL C:\DF_KAPLAN_TEST_a.txt;
COLUMN current_date NEW_VALUE report_date
SELECT TO_CHAR(SYSDATE,'mm-dd-yyyy hh:SS') current_date FROM DUAL;
REPH LEFT 'BOF'SKIP 1 -
report_date
REPF LEFT 'EOF'
select decode(grouping(v),1,to_char(count(*)),v) val
FROM(SELECT
T0.OBJECT_NAME||'|'||T0.Title||'|'||CASE WHEN (T0.R_VERSION >= 1.0) THEN T0.R_VERSION ELSE '' END
||'|'||T0.R_Object_Type||'|'||T0.R_EffectiveDate
||'|'||T0.R_EffectiveDate v
FROM
QM_Document_Base T0
WHERE
T0.R_LIFECYCLESTATE IN (1229,1231,1232) AND T0.R_ISSYSTEM_COPY = 0 AND T0.I_LATEST_FLAG = 1 and T0.I_Is_Deleted = 0
AND T0.R_Object_Type IN ('qu_policy_doctype','qu_sop_doctype','qu_workinstruction_doctype') ORDER BY
T0.R_Object_Type, T0.Object_Name)
group by rollup(v)
order by grouping(v) desc;
SPOOL OFF;
Thanks again,
Donald
|
|
|
|
|
|
| Re: Generating Header and Footer information in a Report [message #431862 is a reply to message #431857] |
Thu, 19 November 2009 12:46 |
dnfrantum
Messages: 11
Registered: November 2009
Location: California
|
Junior Member |
|
|
Okay, got rid of the control characters, but I am still left with a leading line...I don't understand where this is coming from. Here is the output and the code once more.
BOF
11-19-2009 01:44
56
POL-GN-00001|Policy on Policies and Procedural Documents|1.0|qu_policy_doctype|2
2-FEB-09|22-FEB-09
EOF
Code
SET FEEDBACK OFF
SET PAGESIZE 59
SET NEWPAGE NONE
SET ECHO OFF
SET TRIMSPOOL ON
SET HEADING OFF
SPOOL C:\DF_KAPLAN_TEST_a.txt;
BREAK ON REPORT
COLUMN current_date NOPRINT NEW_VALUE report_date
SELECT TO_CHAR(SYSDATE,'mm-dd-yyyy hh:SS') current_date FROM DUAL;
BREAK ON REPORT
REPH LEFT 'BOF'SKIP 1 -
report_date
REPF LEFT 'EOF'
select decode(grouping(v),1,to_char(count(*)),v) val
FROM(SELECT
T0.OBJECT_NAME||'|'||T0.Title||'|'||CASE WHEN (T0.R_VERSION >= 1.0) THEN T0.R_VERSION ELSE '' END
||'|'||T0.R_Object_Type||'|'||T0.R_EffectiveDate
||'|'||T0.R_EffectiveDate v
FROM
QM_Document_Base T0
WHERE
T0.R_LIFECYCLESTATE IN (1229,1231,1232) AND T0.R_ISSYSTEM_COPY = 0 AND T0.I_LATEST_FLAG = 1 and T0.I_Is_Deleted = 0
AND T0.R_Object_Type IN ('qu_policy_doctype','qu_sop_doctype','qu_workinstruction_doctype') ORDER BY
T0.R_Object_Type, T0.Object_Name)
group by rollup(v)
order by grouping(v) desc;
SPOOL OFF;
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 17 21:02:30 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
