Subject Re: Totaling fields in a report From charlie <tm@tc.com> Date Thu, 22 Dec 2022 08:05:50 -0500 Newsgroups dbase.getting-started 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. > >