Subject Re: Totals on Relational Reports
From Mervyn Bick <invalid@invalid.invalid>
Date Mon, 14 May 2018 19:54:33 +0200
Newsgroups dbase.getting-started
Attachment(s) CFP2.repcfp2a.repreport_tree.jpg

On 2018-05-14 4:15 AM, Randy Waldman wrote:
> Hi, Mervyn, all.  You recently offered to help me with a relational report (my cryptonite).  Attached is a simple report.  Please use any of the total methods you desire.
>
> 1.  I had these files in a directory: "c:\try"
> 2.  Requesting a Grand Total, as well.
>
> Many thanks!  Randy
>

The way your tables are structured makes using agSUM() for the Agency
totals a problem with a separate query for each table.  I have,
therefore, gone the DIY route for all totals in your original report.

My preferred way of dealing with related tables is to use one query with
the tables JOINed.  Now all the fields are available for all records and
agSUM() works like a charm.  I have made a second report using this
technique and you will note that it takes far less code to make it work.

To view the reportgroup.footerband where the Grand Total is displayed
set the report designer to show one record.

It is never a good idea to hard-code paths in a report or in any program
or form for that matter.  I've been working with the report and the
tables in the same folder but if you have your tables in a separate
folder (which is good programming practice :-) ) then set up a User BDE
Alias to point to the folder and add a database object to the report.
Refer the query objects to the database object and you're "Up, up and
Away" like Superman with no cryptonite in sight. :-)

I've sprinkled some comment lines around in the code but if there is
anything you don't understand please shout.

Being able to see how various objects in a report relate to each other
takes a bit of learning.  I've attached the little diagram I used to
find my way about the object jungle.  (It's much like doing Sums in
Grade 1 by counting on one's fingers. :-) )

Mervyn.






clear
** END HEADER -- do not remove this line
//
// Generated on 2018-05-14
//
local r
r = new CFP2REPORT()
r.render()

class CFP2REPORT of REPORT
   with (this)
      metric = 3        // Inches
      autoSort = false
   endwith

   this.JOB_HDR1 = new QUERY(this)
   with (this.JOB_HDR1)
      sql = 'select * from "job_hdr.dbf" ORDER BY agency'
      requestLive = false
      active = true
   endwith

   this.JOBITEMSX1 = new QUERY(this)
   with (this.JOBITEMSX1)
      sql = 'select i.*,price*qty as cost from "jobitemsx.dbf" i'
      requestLive = false
      active = true
   endwith

   with (this.JOBITEMSX1.rowset)
      indexName = "IDLINK"
      masterRowset = form.form.job_hdr1.rowset
      masterFields = "idLink1"
   endwith

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

   this.PAGETEMPLATE1.STREAMFRAME1 = new STREAMFRAME(this.PAGETEMPLATE1)
   with (this.PAGETEMPLATE1.STREAMFRAME1)
      height = 8.0521
      left = 0.25
      top = 0.9479
      width = 6.5
      form.STREAMFRAME1 = form.pagetemplate1.streamframe1
   endwith

   this.PAGETEMPLATE1.TEXT1 = new TEXT(this.PAGETEMPLATE1)
   with (this.PAGETEMPLATE1.TEXT1)
      height = 0.3021
      left = 0.4271
      top = 0.2396
      width = 2.1354
      prefixEnable = false
      fontSize = 14.0
      text = "Randy Sample Report"
      form.TEXT1 = form.pagetemplate1.text1
   endwith

   this.STREAMSOURCE1 = new STREAMSOURCE(this)
   this.STREAMSOURCE1.GROUP1 = new GROUP(this.STREAMSOURCE1)
   with (this.STREAMSOURCE1.GROUP1)
      groupBy = "Agency"
   endwith

   with (this.STREAMSOURCE1.GROUP1.footerBand)
      height = 0.25
   endwith

   this.STREAMSOURCE1.GROUP1.footerBand.TEXT1 = new TEXT(this.STREAMSOURCE1.GROUP1.footerBand)
   with (this.STREAMSOURCE1.GROUP1.footerBand.TEXT1)
      height = 0.1979
      left = 4.3125
      top = 0.0313
      width = 1.1563
      prefixEnable = false
      alignHorizontal = 2        // Right
      text = "Total This Agency"
   endwith

   this.STREAMSOURCE1.GROUP1.footerBand.TEXT2 = new TEXT(this.STREAMSOURCE1.GROUP1.footerBand)
   with (this.STREAMSOURCE1.GROUP1.footerBand.TEXT2)
      canRender = class::TEXT2_CANRENDER
      onRender = class::TEXT2_ONRENDER
      height = 0.1771
      left = 5.6563
      top = 0.0417
      width = 0.7292
      prefixEnable = false
      alignHorizontal = 2        // Right
      text = 0
   endwith

   with (this.STREAMSOURCE1.GROUP1.headerBand)
      onRender = class::HEADERBAND_ONRENDER1
      height = 0.25
   endwith

   this.STREAMSOURCE1.GROUP1.headerBand.TEXTAGENCY1 = new TEXT(this.STREAMSOURCE1.GROUP1.headerBand)
   with (this.STREAMSOURCE1.GROUP1.headerBand.TEXTAGENCY1)
      height = 0.2083
      left = 0.2396
      top = 0.0035
      width = 1.875
      variableHeight = true
      prefixEnable = false
      fontSize = 12.0
      fontBold = true
      text = {||this.form.job_hdr1.rowset.fields["agency"].value}
   endwith

   this.STREAMSOURCE1.GROUP2 = new GROUP(this.STREAMSOURCE1)
   with (this.STREAMSOURCE1.GROUP2)
      groupBy = "idLink1"
   endwith

   with (this.STREAMSOURCE1.GROUP2.footerBand)
      height = 0.25
   endwith

   this.STREAMSOURCE1.GROUP2.footerBand.TEXT1 = new TEXT(this.STREAMSOURCE1.GROUP2.footerBand)
   with (this.STREAMSOURCE1.GROUP2.footerBand.TEXT1)
      canRender = class::TEXT1_CANRENDER
      onRender = class::TEXT1_ONRENDER
      height = 0.1667
      left = 5.5937
      top = 0.0313
      width = 0.8021
      prefixEnable = false
      alignHorizontal = 2        // Right
      text = 0
   endwith

   this.STREAMSOURCE1.GROUP2.footerBand.TEXT2 = new TEXT(this.STREAMSOURCE1.GROUP2.footerBand)
   with (this.STREAMSOURCE1.GROUP2.footerBand.TEXT2)
      height = 0.1771
      left = 4.2917
      top = 0.0208
      width = 1.2188
      prefixEnable = false
      alignHorizontal = 2        // Right
      text = "Total this ticket"
   endwith

   with (this.STREAMSOURCE1.GROUP2.headerBand)
      height = 0.25
   endwith

   this.STREAMSOURCE1.GROUP2.headerBand.TEXTNAME11 = new TEXT(this.STREAMSOURCE1.GROUP2.headerBand)
   with (this.STREAMSOURCE1.GROUP2.headerBand.TEXTNAME11)
      height = 0.1354
      left = 0.5521
      top = 0.0347
      width = 2.5104
      variableHeight = true
      prefixEnable = false
      text = {||this.form.job_hdr1.rowset.fields["name1"].value}
   endwith

   this.STREAMSOURCE1.GROUP2.headerBand.TEXTIDLINK1 = new TEXT(this.STREAMSOURCE1.GROUP2.headerBand)
   with (this.STREAMSOURCE1.GROUP2.headerBand.TEXTIDLINK1)
      height = 0.1458
      left = 3.2396
      top = 0.0243
      width = 0.8229
      variableHeight = true
      prefixEnable = false
      text = {||this.form.jobitemsx1.rowset.fields["idlink"].value}
   endwith

   this.STREAMSOURCE1.detailBand.TEXTTITLE1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TEXTTITLE1)
      height = 0.1354
      left = 0.9584
      top = 0.0014
      width = 3.1979
      variableHeight = true
      prefixEnable = false
      text = {||this.form.jobitemsx1.rowset.fields["title"].value}
   endwith

   this.STREAMSOURCE1.detailBand.TEXTQTY1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TEXTQTY1)
      height = 0.1667
      left = 4.3437
      top = 0.0
      width = 0.3438
      variableHeight = true
      prefixEnable = false
      alignHorizontal = 2        // Right
      picture = "9999"
      text = {||this.form.jobitemsx1.rowset.fields["qty"].value}
   endwith

   this.STREAMSOURCE1.detailBand.TEXTPRICE1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TEXTPRICE1)
      height = 0.1979
      left = 4.7604
      top = 0.0
      width = 0.6771
      variableHeight = true
      prefixEnable = false
      alignHorizontal = 2        // Right
      picture = "999999.99"
      text = {||this.form.jobitemsx1.rowset.fields["price"].value}
   endwith

   this.STREAMSOURCE1.detailBand.TEXTIDLINK1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TEXTIDLINK1)
      height = 0.1771
      left = 0.2604
      top = 0.0
      width = 0.5417
      variableHeight = true
      prefixEnable = false
      text = {||this.form.jobitemsx1.rowset.fields["idlink"].value}
   endwith

   this.STREAMSOURCE1.detailBand.TEXTCOST1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TEXTCOST1)
      onRender = class::TEXTCOST1_ONRENDER
      height = 0.2035
      left = 5.5625
      top = 0.0
      width = 0.83
      variableHeight = true
      prefixEnable = false
      alignHorizontal = 2        // Right
      text = {||this.form.jobitemsx1.rowset.fields["cost"].value}
   endwith

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

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

   this.reportGroup.footerBand.TEXT1 = new TEXT(this.reportGroup.footerBand)
   with (this.reportGroup.footerBand.TEXT1)
      canRender = class::TEXT1_CANRENDER1
      onRender = class::TEXT1_ONRENDER1
      height = 0.2083
      left = 5.6146
      top = 0.4479
      width = 0.75
      prefixEnable = false
      alignHorizontal = 2        // Right
      text = 0.0
   endwith

   this.reportGroup.footerBand.TEXT2 = new TEXT(this.reportGroup.footerBand)
   with (this.reportGroup.footerBand.TEXT2)
      height = 0.2083
      left = 2.0938
      top = 0.4479
      width = 2.9479
      prefixEnable = false
      alignHorizontal = 2        // Right
      text = "Grand Total for this report."
   endwith

   with (this.reportGroup.headerBand)
      onRender = class::HEADERBAND_ONRENDER
      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.job_hdr1.rowset

   function HEADERBAND_onRender()
      //create properties of the reportgroup, group1 and group
      //to act as accumulators for totals.
      this.parent.grandtotal = 0
      this.parent.parent.streamsource1.group1.agency_total = 0
      this.parent.parent.streamsource1.group2.ticket_total = 0
      return

   function HEADERBAND_onRender1()
      //group1.headerband
      //reset after new page
      this.beginNewFrame = false
      return

   function TEXT1_canRender()
      // get ticket_total
      this.text = this.parent.parent.ticket_total
      return true

   function TEXT1_canRender1()
      //get grandtotal
      this.text = this.parent.parent.grandtotal
      return true

   function TEXT1_onRender()
       //on group2 footerband
      //reset ticket_total
      this.parent.parent.ticket_total = 0
      return

   function TEXT1_onRender1()
      //on reportgroup footerband
      //make sure group1headerband.beginNewFrame is reset
      //at end of report
      this.parent.parent.parent.streamsource1.group1.headerband.beginNewFrame = false
      return

   function TEXT2_canRender()
      // get agency_total
      this.text = this.parent.parent.agency_total
      return true

   function TEXT2_onRender()
       //on group1 footerband
      //reset agency_total
      this.parent.parent.agency_total = 0
      //check for space
      if this.parent.renderOffset > this.form.pagetemplate1.streamFrame1.height - 3
         this.parent.parent.headerband.beginNewFrame = true
      endif
      return

   function TEXTCOST1_onRender()
      // add this value to the accumulators.
      this.parent.parent.parent.reportgroup.grandtotal += this.text()
      this.parent.parent.group1.agency_total += this.text()
      this.parent.parent.group2.ticket_total += this.text()
      // contents of this.text is a codeblock.  
      // the brackets in this.text() are required to evaluate the codeblock
      return

endclass



/*
      The designer streams the sql property as one long line which is hard to follow.
      This copy is for easy reference
      
      sql = 'select j.agency,j.idLink1,i.title,j.name1,i.qty,i.Price,i.Price * i.qty as cost '
      sql += 'from job_hdr j inner Join jobitemsx i On j.idLink1 = i.idLink '
      sql += 'order by j.agency,j.idLink1'
*/
** END HEADER -- do not remove this line
//
// Generated on 2018-05-14
//
local r
r = new CFP2AREPORT()
r.render()

class CFP2AREPORT of REPORT
   with (this)
      metric = 3        // Inches
      autoSort = false
   endwith

   this.JOB_HDR1 = new QUERY(this)
   with (this.JOB_HDR1)
      sql = "select j.agency,j.idLink1,i.title,j.name1,i.qty,i.Price,i.Price * i.qty as cost from job_hdr j inner Join jobitemsx i On j.idLink1 = i.idLink order by j.agency,j.idLink1"
      requestLive = false
      active = true
   endwith

   this.STREAMSOURCE1 = new STREAMSOURCE(this)
   this.STREAMSOURCE1.GROUP1 = new GROUP(this.STREAMSOURCE1)
   with (this.STREAMSOURCE1.GROUP1)
      groupBy = "Agency"
   endwith

   with (this.STREAMSOURCE1.GROUP1.footerBand)
      height = 0.25
   endwith

   this.STREAMSOURCE1.GROUP1.footerBand.TEXT1 = new TEXT(this.STREAMSOURCE1.GROUP1.footerBand)
   with (this.STREAMSOURCE1.GROUP1.footerBand.TEXT1)
      height = 0.1979
      left = 4.3125
      top = 0.0313
      width = 1.1563
      prefixEnable = false
      alignHorizontal = 2        // Right
      text = "Total This Agency"
   endwith

   this.STREAMSOURCE1.GROUP1.footerBand.TEXT2 = new TEXT(this.STREAMSOURCE1.GROUP1.footerBand)
   with (this.STREAMSOURCE1.GROUP1.footerBand.TEXT2)
      onRender = class::TEXT2_ONRENDER
      height = 0.1771
      left = 5.6563
      top = 0.0417
      width = 0.7292
      prefixEnable = false
      alignHorizontal = 2        // Right
      text = {||this.parent.parent.agSum({||this.parent.rowset.fields[ "cost" ].value})}
   endwith

   with (this.STREAMSOURCE1.GROUP1.headerBand)
      onRender = class::HEADERBAND_ONRENDER
   endwith

   this.STREAMSOURCE1.GROUP1.headerBand.TEXTAGENCY1 = new TEXT(this.STREAMSOURCE1.GROUP1.headerBand)
   with (this.STREAMSOURCE1.GROUP1.headerBand.TEXTAGENCY1)
      height = 0.2083
      left = 0.1875
      top = 0.0465
      width = 2.1771
      variableHeight = true
      prefixEnable = false
      fontSize = 12.0
      fontBold = true
      text = {||this.form.job_hdr1.rowset.fields["agency"].value}
   endwith

   this.STREAMSOURCE1.GROUP2 = new GROUP(this.STREAMSOURCE1)
   with (this.STREAMSOURCE1.GROUP2)
      groupBy = "idLink1"
   endwith

   with (this.STREAMSOURCE1.GROUP2.footerBand)
      height = 0.25
   endwith

   this.STREAMSOURCE1.GROUP2.footerBand.TEXT1 = new TEXT(this.STREAMSOURCE1.GROUP2.footerBand)
   with (this.STREAMSOURCE1.GROUP2.footerBand.TEXT1)
      height = 0.1667
      left = 5.5937
      top = 0.0313
      width = 0.8021
      prefixEnable = false
      alignHorizontal = 2        // Right
      text = {||this.parent.parent.agSum({||this.parent.rowset.fields[ "cost" ].value})}
   endwith

   this.STREAMSOURCE1.GROUP2.footerBand.TEXT2 = new TEXT(this.STREAMSOURCE1.GROUP2.footerBand)
   with (this.STREAMSOURCE1.GROUP2.footerBand.TEXT2)
      height = 0.1771
      left = 4.2917
      top = 0.0208
      width = 1.2188
      prefixEnable = false
      alignHorizontal = 2        // Right
      text = "Total this ticket"
   endwith

   with (this.STREAMSOURCE1.GROUP2.headerBand)
      height = 0.25
   endwith

   this.STREAMSOURCE1.GROUP2.headerBand.TEXTNAME1 = new TEXT(this.STREAMSOURCE1.GROUP2.headerBand)
   with (this.STREAMSOURCE1.GROUP2.headerBand.TEXTNAME1)
      height = 0.2083
      left = 0.5938
      top = 0.0007
      width = 2.3125
      variableHeight = true
      prefixEnable = false
      text = {||this.form.job_hdr1.rowset.fields["name1"].value}
   endwith

   this.STREAMSOURCE1.GROUP2.headerBand.TEXTIDLINK1 = new TEXT(this.STREAMSOURCE1.GROUP2.headerBand)
   with (this.STREAMSOURCE1.GROUP2.headerBand.TEXTIDLINK1)
      height = 0.2083
      left = 3.2813
      top = 0.0007
      width = 0.5625
      variableHeight = true
      prefixEnable = false
      text = {||this.form.job_hdr1.rowset.fields["idlink1"].value}
   endwith

   this.STREAMSOURCE1.detailBand.TEXTTITLE1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TEXTTITLE1)
      height = 0.1354
      left = 0.9584
      top = 0.0014
      width = 3.1979
      variableHeight = true
      prefixEnable = false
      text = {||this.form.job_hdr1.rowset.fields["title"].value}
   endwith

   this.STREAMSOURCE1.detailBand.TEXTQTY1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TEXTQTY1)
      height = 0.1667
      left = 4.3437
      top = 0.0
      width = 0.3438
      variableHeight = true
      prefixEnable = false
      alignHorizontal = 2        // Right
      picture = "9999"
      text = {||this.form.job_hdr1.rowset.fields["qty"].value}
   endwith

   this.STREAMSOURCE1.detailBand.TEXTPRICE1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TEXTPRICE1)
      height = 0.1979
      left = 4.7604
      top = 0.0
      width = 0.6771
      variableHeight = true
      prefixEnable = false
      alignHorizontal = 2        // Right
      picture = "999999.99"
      text = {||this.form.job_hdr1.rowset.fields["price"].value}
   endwith

   this.STREAMSOURCE1.detailBand.TEXTCOST1 = new TEXT(this.STREAMSOURCE1.detailBand)
   with (this.STREAMSOURCE1.detailBand.TEXTCOST1)
      height = 0.2035
      left = 5.5625
      top = 0.0
      width = 0.83
      variableHeight = true
      prefixEnable = false
      alignHorizontal = 2        // Right
      text = {||this.form.job_hdr1.rowset.fields["cost"].value}
   endwith

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

   this.PAGETEMPLATE1.STREAMFRAME1 = new STREAMFRAME(this.PAGETEMPLATE1)
   with (this.PAGETEMPLATE1.STREAMFRAME1)
      height = 8.0521
      left = 0.25
      top = 0.9479
      width = 6.5
      form.STREAMFRAME1 = form.pagetemplate1.streamframe1
   endwith

   this.PAGETEMPLATE1.TEXT1 = new TEXT(this.PAGETEMPLATE1)
   with (this.PAGETEMPLATE1.TEXT1)
      height = 0.3021
      left = 0.4271
      top = 0.2396
      width = 2.1354
      prefixEnable = false
      fontSize = 14.0
      text = "Randy Sample Report"
      form.TEXT1 = form.pagetemplate1.text1
   endwith

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

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

   this.reportGroup.footerBand.TEXT1 = new TEXT(this.reportGroup.footerBand)
   with (this.reportGroup.footerBand.TEXT1)
      onRender = class::TEXT1_ONRENDER
      height = 0.2083
      left = 5.6146
      top = 0.4479
      width = 0.75
      prefixEnable = false
      alignHorizontal = 2        // Right
      text = {||this.parent.parent.agSum({||this.parent.streamsource1.rowset.fields[ "cost" ].value})}
   endwith

   this.reportGroup.footerBand.TEXT2 = new TEXT(this.reportGroup.footerBand)
   with (this.reportGroup.footerBand.TEXT2)
      height = 0.2083
      left = 2.0938
      top = 0.4479
      width = 2.9479
      prefixEnable = false
      alignHorizontal = 2        // Right
      text = "Grand Total for this report."
   endwith

   with (this.reportGroup.headerBand)
      height = 0.1736
   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.job_hdr1.rowset

   function HEADERBAND_onRender()
      //group1.headerband
      //reset after new page
      this.beginNewFrame = false
      return

   function TEXT1_onRender()
      //on reportgroup footerband
      //make sure group1headerband.beginNewFrame is reset
      //at end of report
      this.parent.parent.parent.streamsource1.group1.headerband.beginNewFrame = false
      return

   function TEXT2_onRender()
       //on group1 footerband
      //check for space
      if this.parent.renderOffset > this.form.pagetemplate1.streamFrame1.height - 3
         this.parent.parent.headerband.beginNewFrame = true
      endif
      return

endclass