Subject Re: Report group headers From charlie <tm@tc.com> Date Wed, 03 Jan 2024 09:37:17 -0500 Newsgroups dbase.getting-started Hi Mervyn...  Wow I would have never figured that out.  Thanks so much!!!!!!! Mervyn Bick Wrote: > 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 >