Re: SQL*REP - how to reset &Page variable to 1
Date: 11 Nov 92 15:58:09 GMT
Message-ID: <1992Nov11.175809.1_at_cc.helsinki.fi>
In article <fragante.721444309_at_unixg.ubc.ca>, fragante_at_unixg.ubc.ca (Gv Fragante) writes:
> I remember seeing this question posted a few months back but missed reading it.
> So if anyone can remember, how can I reset the page number to 1 after a group
> break ? Also, how can I make SQL*REP pause after each group break ? I need
> the pause to allow the user to insert a special stationary into the laser
> printer.
>
> Thanks.
Here it is again:
Week ago I requested help on resetting page numbers when a record in a master group changes. VSOOD_at_us.oracle.com posted me a solution for SQL*ReportWriter version 1.1 which used an user exit written in Pro*C. Inspired by that I discovered a new way to reset page numbers which does not need any Pro*C, just plain brute force &SQL-kludges. This solution does not affect detail groups in any way; all extra fields are placed at REPORT and master levels.
PLEASE NOTE THAT IN SQL*REPORTWRITER VERSION 2 THERE WILL BE A BUILT-IN WAY TO RESET PAGE NUMBER WHEN RECORD CHANGES! And in version 2 you cannot have fields with source &PAGE any more...
The example below demonstrates that
- this works for details returning no rows
- this works for master - multiple details -reports
I can see no reason why this wouldn't work for master-detail-detail reports; I have not tried it, though.
This is my report like:
QUERY:
dept:
query:
SELECT deptno
FROM dept
emp:
query:
SELECT deptno, ename
FROM emp
WHERE deptno = :deptno
parent query 1: dept
Do NOT enter any records for "Child Columns/Parent 1 Columns"!
manager:
query:
SELECT ename managername
FROM emp
WHERE deptno = :deptno
AND job = 'MANAGER'
parent query 1: dept
Do NOT enter any records for "Child Columns/Parent 1 Columns"!
GROUP:
G_dept:
page break: Always
G_emp:
relative position: Below
G_manager:
relative position: Below
FIELD:
FieldName Source Group DataType Width Skip --------- ------ ----- -------- ----- ---- oldmaster #RWENOP REPORT CHAR 30 realpage &PAGE REPORT NUM 3 virtpage #RWENOP G_dept NUM 3 X firstpage (1) G_dept NUM 3 X DEPTNO dept.DEPTNO G_dept NUM 4 forcecalc (2) G_dept NUM 3 X DEPTNO2 emp.DEPTNO G_emp NUM 4 ENAME ENAME G_emp CHAR 10 MANAGERNAME MANAGERNAME G_manager CHAR 10
where (1) is
&SQL SELECT DECODE(:oldmaster,
:deptno,:firstpage,
:realpage),
:deptno
INTO :firstpage,
:oldmaster
FROM dual
and (2) is
&SQL SELECT :realpage - :firstpage + 1
INTO :virtpage
FROM dual
The fields oldmaster, realpage, virtpage, firstpage and forcecalc were created manually.
SUMMARY:
SummaryName Field Function DataType Width PrintGroup ResetGroup ----------- ----- -------- -------- ----- ---------- ---------- virtpagesum virtpage Last NUM 3 G_dept PAGE
TEXT: place &virtpagesum on the PAGE Header and enjoy!
The "repeat on page overflow" is crossed by default for G_dept objects; I have not tested what happens if it's not crossed.
I tested it by setting Report Page Height to 12 and here is the result: (please note that ^L stands for form feed)
PAGE: 1
Deptno
10
Deptno Ename
- ---------- 10 CLARK 10 KING ^L
PAGE: 2
Deptno
10
Deptno Ename
- ---------- 10 MILLER ^L
PAGE: 3
Deptno
10
Managername
CLARK
^L
PAGE: 1
Deptno
20
Deptno Ename
- ---------- 20 SMITH 20 JONES ^L [stuff deleted] ^L
PAGE: 1
Deptno
40
Deptno Ename
- ----------
Managername
I tried to get rid of the summary, but the virtpage seems to "lag" one page behind! I discovered one solution without a summary, but it worked only if DESTYPE=Screen - if I set DESTYPE=File the page numbers do not reset! My runrep's version is 1.1.12.2.2 on MS-DOS and we have discovered other annoying inconsistencies between File and Screen desformats.
If you have a solution which eliminates the summary, please let me know!
--
Juhani Jaakola jaakola_at_CC.Helsinki.FI
Phone: +358-0-506811 (or +358-0-13431405)
Fax: +358-0-50681300
Address: Sulkapolku 4 B 13 A
00370 Helsinki
Received on Wed Nov 11 1992 - 16:58:09 CET