Subject Re: Report group headers
From Mervyn Bick <invalid@invalid.invalid>
Date Wed, 3 Jan 2024 11:56:06 +0200
Newsgroups dbase.getting-started
Attachment(s) report_tree1.pdf, collection.rep

On 2023/12/31 23:16, Mervyn Bick wrote:

> I can't run your report without the two tables (collection and category)
> so it would help if you post them here.  If you don't want to make the
> tables generally available perhaps you would email them to me at
 >
> bickmeo at gmail dot com
 >
> I did, however, notice one potential problem.  The rowset must be
> ordered primarily by the field used in the group1 groupBy property. This
> field is 'category'  but the rowset is ordered by ordr,sku   You should
> change that to ... order by category,ordr,sku
I assumed that the grouping was by category in alphabetical order.  Now
that I've seen the tables I realise you have added a field to the
category table to present the categories in priority order.  This means
that the JOINed rowset is correctly ordered by ordr,sku.  In as much as
ordr and category are paired in records it doesn't really matter which
of the two is used in the group's groupBy property.

The "missing rowset" message came about because you used the
reportgroup's footerband onRender event, which is only triggered once at
the end of the report, instead of the group1 footerband onRender event
to trigger the footerband_onRender event handler.

In the case of the group1 footerband, this.parent.parent is
streamsource1 which does have the rowset as a property.  In the case of
the reportgroup footerband, this.parent.parent is the form which does
not have the rowset as a property resulting in oStream.rowset causing an
error.

Attached is report_tree1.pdf which I use to help trace the parentage of
objects when I need to move data from one object to another.

You used the <H3> and </H3> HTML tags to format the text for the
category name.  I couldn't get Ken's code to add (Continued..) to the
category name when the group1 headerband was repeated on a new page.  I
removed the HTML tags and used the text object's fontsize and fontbold
properties to achieve the same "look" and now the (Continued..) works as
expected.  I've added code to display at the bottom of the page if a
group carries over to a new page.

The method of looping through the rowset to count the number of records
in a group had a problem where the group headerband was repeated at the
top of a new page by giving a false value for the next group.  Instead
of trying to fix the code I abandoned it and added a parameter driven
query to count the records.  Apart from being a simpler method, it also
allows the number of items in a category to be easily displayed in the
group headerband.  You can, of course, remove the additional text from
the headerband if you don't want it there.

I've commented out the code in the FOOTERBAND_onRender event handler to
start a group on a new page if there isn't enough space left on a page
for the entire group.  The code increases the report length from 5 to 7
pages and leaves a LOT of white space on some pages.  The code is,
however, clearly marked so you can uncomment it if you wish.

Mervyn.


** END HEADER -- do not remove this line
//
// Generated on 2024-01-03
//
local r
r = new COLLECTIONREPORT()
r.render()

class COLLECTIONREPORT of REPORT
   set procedure to :ReportControls:REPORT.Co additive
   with (this)
      title = "Collection"
      metric = 3        // Inches
      autoSort = false
   endwith

   this.COINDATA1 = new DATABASE(this)
   with (this.COINDATA1)
      databaseName = "COINDATA"
      active= true
   endwith

   this.COUNT_CAT1 = new QUERY(this)
   with (this.COUNT_CAT1)
      left = 4.0
      database = form.coindata1
      sql = "select count(*) from collection where category = :category"
      params["category"] = ""
      active = true
   endwith

   this.CATEGORY1 = new QUERY(this)
   with (this.CATEGORY1)
      left = 4.0
      database = form.coindata1
      sql = "select * from collection inner join category on collection.category = category.cat order by ordr,sku"
      requestLive = false
      active = true
   endwith

   this.STREAMSOURCE1 = new STREAMSOURCE(this)
   this.STREAMSOURCE1.GROUP1 = new GROUP(this.STREAMSOURCE1)
   with (this.STREAMSOURCE1.GROUP1)
      groupBy = "ORDR"
      headerEveryFrame = true
   endwith

   with (this.STREAMSOURCE1.GROUP1.footerBand)
      onRender = class::FOOTERBAND_ONRENDER
   endwith

   with (this.STREAMSOURCE1.GROUP1.headerBand)
      preRender = class::HEADERBAND_PRERENDER
      onRender = class::HEADERBAND_ONRENDER
      height = 0.35
   endwith

   this.STREAMSOURCE1.GROUP1.headerBand.LINE1 = new LINE(this.STREAMSOURCE1.GROUP1.headerBand)
   with (this.STREAMSOURCE1.GROUP1.headerBand.LINE1)
      canRender = {||this.parent.context == 0}
      left = 0.0
      right = 6.5
      top = 0.0729
      bottom = 0.0729
      width = 1
   endwith

   this.STREAMSOURCE1.GROUP1.headerBand.TEXT1 = new TEXT(this.STREAMSOURCE1.GROUP1.headerBand)
   with (this.STREAMSOURCE1.GROUP1.headerBand.TEXT1)
      height = 0.2083
      left = 0.0
      top = 0.1354
      width = 0.8438
      prefixEnable = false
      colorNormal = "b+"
      text = "<H3>Category :</H3>"
   endwith

   this.STREAMSOURCE1.GROUP1.headerBand.TEXT2 = new TEXT(this.STREAMSOURCE1.GROUP1.headerBand)
   with (this.STREAMSOURCE1.GROUP1.headerBand.TEXT2)
      height = 0.2083
      left = 0.8958
      top = 0.1354
      width = 2.7813
      prefixEnable = false
      colorNormal = "b+"
      fontSize = 11.0
      fontBold = true
      text = {||this.parent.parent.parent.rowset.fields["CATEGORY"].value}
   endwith

   this.STREAMSOURCE1.GROUP1.headerBand.TEXT3 = new TEXT(this.STREAMSOURCE1.GROUP1.headerBand)
   with (this.STREAMSOURCE1.GROUP1.headerBand.TEXT3)
      canRender = class::TEXT3_CANRENDER
      height = 0.2083
      left = 3.8229
      top = 0.1354
      width = 0.5
      prefixEnable = false
      colorNormal = "b+"
      fontSize = 11.0
      text = "Items :"
   endwith

   this.STREAMSOURCE1.GROUP1.headerBand.TEXT4 = new TEXT(this.STREAMSOURCE1.GROUP1.headerBand)
   with (this.STREAMSOURCE1.GROUP1.headerBand.TEXT4)
      canRender = class::TEXT4_CANRENDER
      height = 0.2083
      left = 4.3438
      top = 0.1354
      width = 0.75
      prefixEnable = false
      colorNormal = "b+"
      fontSize = 11.0
      text = "Text4"
   endwith

   with (this.STREAMSOURCE1.detailBand)
      height = 0.1729
   endwith

   this.STREAMSOURCE1.detailBand.TITLETEXTID1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TITLETEXTID1)
      canRender = {||this.parent.firstOnFrame}
      height = 0.22
      left = 0.0
      top = 0.0
      width = 0.6146
      prefixEnable = false
      suppressIfBlank = true
      fontSize = 9.0
      text = "<H3>Date/Id</H3>"
   endwith

   this.STREAMSOURCE1.detailBand.TEXTID1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TEXTID1)
      height = 0.0
      left = 0.0
      top = 0.22
      width = 0.85
      variableHeight = true
      prefixEnable = false
      fontSize = 9.0
      text = {||this.form.CATEGORY1.rowset.fields["ID"].value}
   endwith

   this.STREAMSOURCE1.detailBand.TITLETEXTGRADE1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TITLETEXTGRADE1)
      canRender = {||this.parent.firstOnFrame}
      height = 0.22
      left = 0.91
      top = 0.0
      width = 0.6146
      prefixEnable = false
      suppressIfBlank = true
      fontSize = 9.0
      text = "<H3>Grade</H3>"
   endwith

   this.STREAMSOURCE1.detailBand.TEXTGRADE1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TEXTGRADE1)
      height = 0.0
      left = 0.91
      top = 0.22
      width = 0.6146
      variableHeight = true
      prefixEnable = false
      fontSize = 9.0
      text = {||this.form.CATEGORY1.rowset.fields["GRADE"].value}
   endwith

   this.STREAMSOURCE1.detailBand.TITLETEXTBDATE1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TITLETEXTBDATE1)
      canRender = {||this.parent.firstOnFrame}
      height = 0.22
      left = 1.73
      top = 0.0
      width = 0.9375
      prefixEnable = false
      suppressIfBlank = true
      fontSize = 9.0
      text = "<H3>Bdate</H3>"
   endwith

   this.STREAMSOURCE1.detailBand.TEXTBDATE1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TEXTBDATE1)
      height = 0.0
      left = 1.73
      top = 0.22
      width = 0.9375
      variableHeight = true
      prefixEnable = false
      fontSize = 9.0
      text = {||this.form.CATEGORY1.rowset.fields["BDATE"].value}
   endwith

   this.STREAMSOURCE1.detailBand.TITLETEXTQTY1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TITLETEXTQTY1)
      canRender = {||this.parent.firstOnFrame}
      height = 0.22
      left = 2.8
      top = 0.0
      width = 0.4375
      prefixEnable = false
      alignHorizontal = 1        // Center
      suppressIfBlank = true
      fontSize = 9.0
      text = "<H3>Qty</H3>"
   endwith

   this.STREAMSOURCE1.detailBand.TEXTQTY1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TEXTQTY1)
      height = 0.0
      left = 2.8
      top = 0.22
      width = 0.4896
      variableHeight = true
      prefixEnable = false
      fontSize = 9.0
      text = {||this.form.CATEGORY1.rowset.fields["QTY"].value}
   endwith

   this.STREAMSOURCE1.detailBand.TITLETEXTESTV1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TITLETEXTESTV1)
      canRender = {||this.parent.firstOnFrame}
      height = 0.22
      left = 3.43
      top = 0.0
      width = 0.625
      prefixEnable = false
      alignHorizontal = 2        // Right
      suppressIfBlank = true
      fontSize = 9.0
      text = "<h3>Est Val</h3>"
   endwith

   this.STREAMSOURCE1.detailBand.TEXTESTV1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TEXTESTV1)
      height = 0.0
      left = 3.43
      top = 0.22
      width = 0.6875
      variableHeight = true
      prefixEnable = false
      alignHorizontal = 2        // Right
      fontSize = 9.0
      text = {||this.form.CATEGORY1.rowset.fields["ESTV"].value}
   endwith

   this.STREAMSOURCE1.detailBand.TITLETEXTDETAILS1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TITLETEXTDETAILS1)
      canRender = {||this.parent.firstOnFrame}
      height = 0.22
      left = 4.2
      top = 0.0
      width = 1.0
      prefixEnable = false
      suppressIfBlank = true
      fontSize = 9.0
      text = "<H3>Notes</H3>"
   endwith

   this.STREAMSOURCE1.detailBand.TEXTDETAILS1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TEXTDETAILS1)
      height = 0.0
      left = 4.2
      top = 0.22
      width = 3.25
      variableHeight = true
      prefixEnable = false
      fontSize = 9.0
      text = {||this.form.CATEGORY1.rowset.fields["DETAILS"].value}
   endwith

   with (this.printer)
      duplex = 1        // None
      orientation = 1        // Portrait
      paperSource = 15
      paperSize = 1
      resolution = 0        // Default
      color = 2        // Color
      trueTypeFonts = 3        // Substitute
   endwith

   this.PAGETEMPLATE1 = new PAGETEMPLATE(this)
   with (this.PAGETEMPLATE1)
      height = 11.0
      width = 8.5
      marginTop = 0.1
      marginLeft = 0.0
      marginBottom = 0.1
      marginRight = 2.7875
      gridLineWidth = 0
   endwith

   this.PAGETEMPLATE1.STREAMFRAME1 = new STREAMFRAME(this.PAGETEMPLATE1)
   with (this.PAGETEMPLATE1.STREAMFRAME1)
      height = 9.0
      left = 0.3958
      top = 0.8958
      width = 7.5
      form.STREAMFRAME1 = form.pagetemplate1.streamframe1
   endwith

   this.PAGETEMPLATE1.TEXT1 = new TEXT(this.PAGETEMPLATE1)
   with (this.PAGETEMPLATE1.TEXT1)
      height = 0.3646
      left = 0.6146
      top = 0.1354
      width = 2.9896
      prefixEnable = false
      text = "<h1>My Collection</h1>"
      form.TEXT1 = form.pagetemplate1.text1
   endwith

   this.PAGETEMPLATE1.TEXT2 = new TEXT(this.PAGETEMPLATE1)
   with (this.PAGETEMPLATE1.TEXT2)
      height = 0.1563
      left = 0.61
      top = 0.57
      width = 2.1458
      prefixEnable = false
      text = {||Date()}
      form.TEXT2 = form.pagetemplate1.text2
   endwith

   this.PAGETEMPLATE1.PAGENUMBER1 = new PAGENUMBER(this.PAGETEMPLATE1)
   with (this.PAGETEMPLATE1.PAGENUMBER1)
      height = 0.3958
      left = 4.0624
      top = 10.198
      width = 0.3854
      alignVertical = 0        // Top
      alignHorizontal = 1        // Center
      fontSize = 10.0
      form.PAGENUMBER1 = form.pagetemplate1.pagenumber1
   endwith

   this.PAGETEMPLATE1.TEXT3 = new TEXT(this.PAGETEMPLATE1)
   with (this.PAGETEMPLATE1.TEXT3)
      height = 0.2083
      left = 0.6979
      top = 10.0417
      width = 3.7396
      prefixEnable = false
      colorNormal = "b+"
      text = "Text3"
      form.TEXT3 = form.pagetemplate1.text3
   endwith

   with (this.reportGroup.footerBand)
      height = 0.0
   endwith

   with (this.reportGroup.headerBand)
      expandable = true
      height = 0.0
      beginNewFrame = true
   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.category1.rowset

   function FOOTERBAND_onRender()  
           local oStream, nCount, nSpaceRemaining, nSpaceNeeded,cNextCategory
      oStream = this.parent.parent
      oStream.rowset.next() //Next record so we can get category for next group
      cNextCategory = oStream.rowset.fields["category"].value
      nCount = 0
      if not oStream.rowset.endofset
         this.parent.parent.parent.count_cat1.params['category'] = oStream.rowset.fields["category"].value
         this.parent.parent.parent.count_cat1.requery()
         nCount = this.parent.parent.parent.count_cat1.rowset.fields[1].value
      endif
      oStream.rowset.next(-1) //Back to last record of previous group so that report can continue where it left off      
      nSpaceRemaining = this.streamframe.height - this.renderOffset
      nSpaceNeeded =  0.39+(nCount*0.21)  //space for header, footer (which is 0) and rows

      //Uncomment the following section to force groups that are too long to fit in the
      //space available to start on a new page
      *--------------------
//            if nSpaceRemaining < nSpaceNeeded
//               this.parent.headerband.beginNewframe := true
//            else
//               this.parent.headerband.beginNewframe := false
//            endif
      *--------------------
                return


   function HEADERBAND_onRender()
      local nSpaceRemaining,nSpaceNeeded
      //Copy & paste direct from Ken's dBASE Reports book.
      *--------
         f = this.parent.parent.rowset.fields
         this.BreakField = f["category"].value.rightTrim()
         this.text2.text = {||this.parent.parent.parent.rowset.fields["CATEGORY"].value }
      *-------
      //Display notice at bottom of page if group continues on next page
      nSpaceRemaining = this.streamframe.height - this.renderOffset
      nSpaceNeeded =  this.count*0.21  //header has already been rendered  so space for footer (which is 0) and rows
      if nSpaceNeeded > nSpaceRemaining
         this.parent.parent.parent.pagetemplate1.text3.text = ;
            this.BreakField + ' continued on next page'
      else
         this.parent.parent.parent.pagetemplate1.text3.text = ''
       endif  
      return

   function HEADERBAND_preRender()
      local oStream
      //Count records in group for display on headerband
      oStream = this.parent.parent
      this.parent.parent.parent.count_cat1.params['category'] = oStream.rowset.fields["category"].value
      this.parent.parent.parent.count_cat1.requery()
      this.count = this.parent.parent.parent.count_cat1.rowset.fields[1].value
      this.text4.text = ltrim(this.count+'')  //Convert to text then left trim for better alignment
      
      //Copy & paste direct from Ken's dBASE Reports book to add (Continued..) to categrory name
      // check to see if custom property exists, if not create it:
      if type( "this.BreakField" ) == "U"
      this.BreakField = "XXX" // value not in table
      endif
      // this = headerBand
      // parent = group1
      // parent = streamSource1
      f = this.parent.parent.rowset.fields
      this.Text2.text := f["category"].value.rightTrim()
      if this.BreakField == f["category"].value.rightTrim()
      // add extra text: the word<
      // "(Continued ...)" in italics
      this.Text2.text += " <i>(Continued ...)</i>"
      this.count = 0  //Set to 0 so that if group is continued on next page
                             //the onRender event handler doesn't set set the Continued
                            //notice at the bottom of the new page.                            
      endif
      return

   function TEXT3_canRender()
      local lRet
      if 'Continued'$this.parent.text2.text
         lRet = false
      else
         lRet = true
      endif
      return lRet


   function TEXT4_canRender()
      local lRet
      if 'Continued'$this.parent.text2.text
         lRet = false
      else
         lRet = true
      endif
      return lRet

endclass