Subject Re: Totaling fields in a report From Charlie <tm@tc.com> Date Sat, 24 Dec 2022 14:55:19 -0500 Newsgroups dbase.getting-started Thanks very much for the help.  Learned a lot! Happy holidays! charlie Wrote: > Thanks...   Got this error > > Database Engine Error:  Type mismatch in expression. > > sure enough qty is text and price is numeric. > > Would I use cast to fix this? > > > Mervyn Bick Wrote: > > > On 2022/12/22 11:26, Charlie wrote: > > > Thanks for the help. > > > > > > I didn't realize you could group and total in layout.  Very helpful.  So.... > > > > > > this.reportGroup.footerBand.TEXT4 = new TEXT(this.reportGroup.footerBand) > > ......... > > >        text = {||this.parent.parent.agSum({||this.parent.STREAMSOURCE1.rowset.fields["PRICE"].value})} > > >     endwith > > > > > > But I want to get a grand sum of qty*price.  I tried this > > > > > > {||this.parent.parent.agSum({||this.parent.STREAMSOURCE1.rowset.fields["PRICE"].value*this.parent.STREAMSOURCE1.rowset.fields["QTY"].value})} > > > > > > But that does not work.  Produces a blank.  Is there a way to express this so I get a value? > > > > The codeblock in agSum() doesn't seem to accept the product of two fields. > > > > The answer is to create a calculated field in the report's query. > > > > sql = "select i.*,qty*price as cost from imp_spd.dbf i ORDER BY MFG,SKU" > > > > Note that where you include the * wildcard as well as a calculated field > > in a SELECT statement you need to use a table correlation name (alias) > > to qualify the wildcard.  I've used i above because it is the first > > character of the table name but you could just as easily use any other > > character. > > > > You can now use the following in the text object on the reportgroup > > footerband to show the grand total. > > > > > > text = > > {||this.parent.parent.agSum({||this.parent.STREAMSOURCE1.rowset.fields["cost"].value})} > >     endwith > > > > > > Instead of using the canRender event handler of a standard text object > > to show the value of qty*price you can drag cost from the Field palette > > onto the detail band. > > > > Mervyn. > > > > >