Re: End-of-file on SELECT

[Kristy Engelkes <kristy_engelkes@RSMI.COM>]

I think you should be able to use a before procedure for printing the
page break on support_team_mbr.  You would need a flag to know
not to page break initially.  But otherwise, it would close-out the
previous
support_team_mbr.





Denise White <dewhite@VICR.COM>@list.iex.net> on 04/02/2002 04:55:43 PM

Please respond to sqr-users@list.iex.net

Sent by:  "Discussion of SQR, Brio Software's database reporting language"
      <SQR-USERS@list.iex.net>


To:   SQR-USERS@list.iex.net
cc:
Subject:  End-of-file on SELECT


Hi,

I am trying to write a report that has several ON-BREAK levels.  I need to
be
able to detect when I am at the end of the select (the equivalent of an
end-of-file condition on a flat file).  I will try to explain what I am
trying
to do and hopefully it will not be too confusing!

On the top two levels (region_cd & support_team_mbr), I need to page break.
On
the major level, region_cd, I want to do the page break every time - no
problem.
 However, on the support_team_mbr, if it is the last in the region_cd, I
only
want to skip a line and then print the region_cd totals; the page break
will
come after the region_cd totals.  If the support_team_mbr is not the last
in the
region_cd, I do want to do a page break.  Something like this:

[detail lines]
support_team_mbr 1 totals

region_cd 1 totals

[page break]

[detail lines]
support_team_mbr 2 totals

[page break]

[detail lines]
support_team_mbr 3 totals

region_cd 2 totals  (includes support_team_mbr 2 + 3)

So, here are my on-break clauses:

v.region_cd             () on-break print=never after=Region-Brk
                                save=$hold_region      level=1
m.support_team_mbr () on-break print=never after=Team-Mbr-Brk
                                save=$hold_team_mbr level=2

In my Team-Mbr-Brk procedure, I have the following:

      if &v.region_cd = $hold_region and not isnull(&v.region_cd)
            new-page
         else
            position (+1)
         end-if

This works fine, until I hit the end of the select.  There, it prints the
last
support_team_mbr totals, does a page break, and prints the region_cd
totals!  I
added the not isnull() condition, thinking that at the end, null values
would
have been returned to signal the program that it was finished, but
apparently it
does not return a null row, but retains the last values.  So, at the end,
&v.region_cd does equal $hold_region.  Is there anything I can add to the
condition so that at the end of the select, it falls into the 'else'?  It
would
also help me in my header, so I could blank out some values I am printing
in the
header when I print the grand totals on a final page, and would prefer to
not
see the last values still printing.

TIA!

Denise White
Sr. Software Engineer
Vicor