Subject Re: Report group headers From charlie <tm@tc.com> Date Fri, 15 Dec 2023 09:36:54 -0500 Newsgroups dbase.getting-started Hi Mervyn, Ken... Thanks so much for the info.  Looks doable.  Actually I am sort of amazed I figured out the sql  for the report in the first place!  Without asking questions!!! I do have Ken's books and will refer to that also. Merry Christmas and thanks again!! Mervyn Bick Wrote: > On 2023/12/14 23:47, Charlie wrote: > > I have report where I am using group headers.  I am finding that on some pages a group header will appear at the bottom of the page, then the details on the next page.  Is there a way of preventing this from happening?  Thanks > > Short answer, yes.  In practice, as Ken point's out, it's complicated. > It is, however, not particularly difficult once one gets one's head > around it. > > The report designer is extremely powerful but using it isn't as > intuitive as using the form designer.  Ken's book is invaluable when one > needs more than just a plain vanilla report.  Believe me, it will be > money well spent. > > Basically, one needs to determine how much space is needed for a > specific group's header, its footer and how ever many records are in the > group before one actually renders the group. > > As each band is prepared for rendering it updates its renderOffset > property which gives the distance from the top of the streamframe to the > bottom of the band.  The renderOffset value is saved when the band is > rendered.  This allows one to use the group's FOOTERBAND_onRender() > event handler to calculate how much of the streamframe is still > available after a group has been rendered.  If there's enough room for > the next group with it's header, records and footer, carry on.  If there > isn't enough room, set the group headerband's beginNewFrame property > true which will move to the next page. > > The attached example (which was done as a learning exercise back in > 2014) is based on code from Ken's dBASE Reports book for counting the > records in a group.  Ken's code in the book and in his examples is fully > commented.  My code, which was created for learning rather than > teaching, has very few comments.  I understood what needed to be done > and I was more interested in getting it working.  You should, however, > be able to follow the code which is actually quite straightforward. > > A few comments about the example.  I have no idea why I used twips as > the metric as I normally use centimetres.  The principle is, however, > the same no matter what metric is used. > > Although the detailband height is shown as 250 this was just a starting > point before any controls were added.  The detailband's variableHeight > property is set true and one gets its actual height for use in the > calculation by summing the top and height properties of an appropriate > control on the detailband.  In this case all controls are aligned so any > control will do. > > The example was created while I was still learning.  Nowadays, as there > is nothing on the group footerband, I would set its height to 0.  The > footerband's onRender event will still be triggered even if there are no > controls on the footerband.  It would, however, affect the spacing on > the report. > > The example only works where each control on the detailband occupies one > line.  It is also "all or nothing" when it comes to deciding whether > there is enough room for a group or not.  With groups containing many > records this can lead to unacceptably long blank spaces at the the > bottom of pages.  Ken's book deals with splitting long groups over pages > with the appropriate "continuation" text in the header on the new page > while at the same time ensuring that one doesn't wind up with a single > "orphan" line at the bottom of a page or at the top of the new page. > > If one of the controls displays data from a memofield, which may need > several lines and the number of lines can vary from record to record, > then simply counting records is not enough.  One also needs to calculate > how many lines the memofield will need for each record.  This is a far > more complicated exercise.  I do, however, have an example for this so > just ask if you need it. > > Mervyn. > > > > > > > clear > > ** END HEADER -- do not remove this line > // > // Generated on 2014-10-15 > // > local r > r = new test_countReport() > r.render() > > class test_countReport of REPORT >    with (this) >       autoSort = false >    endwith > >    this.DBASESAMPLES1 = new DATABASE(this) >    with (this.DBASESAMPLES1) >       left = 1080.0 >       top = 1080.0 >       width = 360.0 >       height = 360.0 >       databaseName = "DBASESAMPLES" >       active = true >    endwith > >    this.EMPLOYEES1 = new QUERY(this) >    with (this.EMPLOYEES1) >       left = 1080.0 >       top = 1080.0 >       width = 360.0 >       height = 360.0 >       database = form.form.dbasesamples1 >       sql = "select e.*,substring(lastname from 1 for 1) as intial from employees e order by lastname" >       requestLive = false >       active = true >    endwith > >    this.PAGETEMPLATE1 = new PAGETEMPLATE(this) >    with (this.PAGETEMPLATE1) >       height = 16837.0 >       width = 11905.0 >       marginTop = 1080.0 >       marginLeft = 1080.0 >       marginBottom = 1080.0 >       marginRight = 1080.0 >       gridLineWidth = 0 >    endwith > >    this.PAGETEMPLATE1.STREAMFRAME1 = new STREAMFRAME(this.PAGETEMPLATE1) >    with (this.PAGETEMPLATE1.STREAMFRAME1) >       height = 11592.0 >       left = 360.0 >       top = 1365.0 >       width = 9360.0 >       form.STREAMFRAME1 = form.pagetemplate1.streamframe1 >    endwith > >    with (this.printer) >       duplex = 1        // None >       orientation = 1        // Portrait >       paperSource = 268 >       paperSize = 9 >       resolution = 3        // Medium >       color = 2        // Color >       trueTypeFonts = 1        // Bitmap >    endwith > >    this.STREAMSOURCE1 = new STREAMSOURCE(this) >    this.STREAMSOURCE1.GROUP1 = new GROUP(this.STREAMSOURCE1) >    with (this.STREAMSOURCE1.GROUP1) >       groupBy = "intial" >    endwith > >    with (this.STREAMSOURCE1.GROUP1.footerBand) >       onRender = class::FOOTERBAND_ONRENDER >       height = 250.0 >    endwith > >    with (this.STREAMSOURCE1.GROUP1.headerBand) >       height = 250.0 >    endwith > >    this.STREAMSOURCE1.GROUP1.headerBand.TEXTINTIAL1 = new TEXT(this.STREAMSOURCE1.GROUP1.headerBand) >    with (this.STREAMSOURCE1.GROUP1.headerBand.TEXTINTIAL1) >       height = 300.0 >       left = 390.0 >       top = 35.0 >       width = 1530.0 >       variableHeight = true >       prefixEnable = false >       text = {||this.form.employees1.rowset.fields["intial"].value} >    endwith > >    with (this.STREAMSOURCE1.detailBand) >       height = 250.0 >    endwith > >    this.STREAMSOURCE1.detailBand.TEXTEMPLOYEEID1 = new TEXT(this.STREAMSOURCE1.detailBand) >    with (this.STREAMSOURCE1.detailBand.TEXTEMPLOYEEID1) >       height = 293.0 >       left = 525.0 >       top = 348.0 >       width = 1350.0 >       variableHeight = true >       prefixEnable = false >       alignHorizontal = 2        // Right >       text = {||this.form.employees1.rowset.fields["employeeid"].value} >    endwith > >    this.STREAMSOURCE1.detailBand.TEXTFIRSTNAME1 = new TEXT(this.STREAMSOURCE1.detailBand) >    with (this.STREAMSOURCE1.detailBand.TEXTFIRSTNAME1) >       height = 293.0 >       left = 2115.0 >       top = 348.0 >       width = 1530.0 >       variableHeight = true >       prefixEnable = false >       text = {||this.form.employees1.rowset.fields["firstname"].value} >    endwith > >    this.STREAMSOURCE1.detailBand.TEXTLASTNAME1 = new TEXT(this.STREAMSOURCE1.detailBand) >    with (this.STREAMSOURCE1.detailBand.TEXTLASTNAME1) >       height = 293.0 >       left = 3990.0 >       top = 348.0 >       width = 1530.0 >       variableHeight = true >       prefixEnable = false >       text = {||this.form.employees1.rowset.fields["lastname"].value} >    endwith > >    this.STREAMSOURCE1.detailBand.TEXTHIREDATE1 = new TEXT(this.STREAMSOURCE1.detailBand) >    with (this.STREAMSOURCE1.detailBand.TEXTHIREDATE1) >       height = 293.0 >       left = 6015.0 >       top = 333.0 >       width = 1080.0 >       variableHeight = true >       prefixEnable = false >       text = {||this.form.employees1.rowset.fields["hiredate"].value} >    endwith > >    with (this.reportGroup.footerBand) >       height = 250.0 >    endwith > >    with (this.reportGroup.headerBand) >       height = 0.0 >    endwith > >    this.firstPageTemplate = this.form.pagetemplate1 >    this.form.pagetemplate1.nextPageTemplate = this.form.pagetemplate1 >    this.form.pagetemplate1.streamframe1.streamSource = this.form.streamsource1 >    this.form.streamsource1.rowset = this.form.employees1.rowset > >    function FOOTERBAND_onRender >       local oStream, nCount, nSpaceRemaining, nSpaceNeeded,cInitial >       oStream = this.parent.parent >       oStream.bookMark = oStream.rowset.bookMark() >       if not oStream.rowset.endofset >          oStream.rowset.next() >          nCount = 0 >          cInitial = substr(oStream.rowset.fields["lastname"].value,1,1) >          do while  oStream.rowset.fields["lastname"].value = cInitial  ; >             and not oStream.rowset.endofset >             oStream.rowset.next() >             nCount ++ >          enddo >          oStream.rowset.goto(oStream.bookmark) >       endif >       nSpaceRemaining = this.streamframe.height - this.renderOffset >       nSpaceNeeded =  250+250+(nCount*641)  //space for header, footer and rows >       if nSpaceRemaining < nSpaceNeeded >          this.parent.headerband.beginNewframe := true >       else >          this.parent.headerband.beginNewframe := false >       endif >       return > > endclass >