From: jaakola@cc.helsinki.fi
Newsgroups: comp.databases.oracle
Subject: Re: SQL*REP - how to reset &Page variable to 1
Date: 11 Nov 92 15:58:09 GMT
Organization: University of Helsinki

In article <fragante.721444309@unixg.ubc.ca>, fragante@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@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@CC.Helsinki.FI
Phone: +358-0-506811  (or +358-0-13431405)
Fax:   +358-0-50681300
Address: Sulkapolku 4 B 13 A
         00370 Helsinki
Newsgroups: comp.databases.oracle