| Subject | Re: REPORT GROUPING ON FIELDS |
| From | Mustansir Ghor <mustan31@hotmail.com> |
| Date | Sat, 24 Jun 2017 05:13:09 -0400 |
| Newsgroups | dbase.getting-started |
Dear all I wonder how ag.sum,ag.count and other work on group. There was tutorial report which was group on supplier and from different table it gave it its inventory total in the footer of the group. So how the total was done based on supplier (I understand the tables were joined by masterrowset and masterfields) . Can this be done on the designer or we only need to add as codeblock. Best Regards Mustansir Mervyn Bick Wrote: > On 2017-06-22 3:02 PM, Mustansir Ghor wrote: > > Dear all > > > > how Can we make grouping on calculated fields in reports. > > > > Example we want to create a new stream frame as group for Customer listing based on the first character of Name. eg group based on LEFT(name,1) > > You need to make sure the calculated field is in the rowset. You can > then use it to set the group's groupBy property. > > There are two ways of adding a calculated field to a rowset. The first > way was used by Akshat although there is no need to actually create a > datamodule although you can add a new query to the datamodule if you are > using one for the rest of your application. > > The other way, which I prefer as it is easier to implement, is to create > it in the query's sql string. > > sql = "select c.*,substring(company from 1 for 1) as initial from > CUSTOMERS.DBF c order by company" > > Note that instead of substr() or left() which are dBASE functions you > need to use substring() which is a localSQL function. Where you have > the * wildcard and a calculated field in a select statement you need to > qualify the wild card by using the tablename or an alias. Here I've > used c as the alias. > > You will find the localSQL help file at > C:\Program Files (x86)\Common Files\Borland\BDE\localsql.hlp > > Mervyn. > > ** END HEADER -- do not remove this line > // > // Generated on 2017-06-23 > // > local r > r = new test_groupby1Report() > r.render() > > class test_groupby1Report of REPORT > with (this) > autoSort = false > endwith > > this.DBASESAMPLES1 = new DATABASE(this) > with (this.DBASESAMPLES1) > left = 1935.0 > top = 165.0 > width = 360.0 > height = 360.0 > databaseName = "DBASESAMPLES" > active = true > endwith > > this.CUSTOMERS1 = new QUERY(this) > with (this.CUSTOMERS1) > left = 540.0 > top = 120.0 > width = 360.0 > height = 360.0 > database = form.form.dbasesamples1 > sql = "select c.*,substring(company from 1 for 1) as initial from CUSTOMERS.DBF c order by company" > requestLive = false > active = true > endwith > > this.STREAMSOURCE1 = new STREAMSOURCE(this) > this.STREAMSOURCE1.GROUP1 = new GROUP(this.STREAMSOURCE1) > with (this.STREAMSOURCE1.GROUP1) > groupBy = "initial" > endwith > > with (this.STREAMSOURCE1.GROUP1.footerBand) > height = 250.0 > endwith > > with (this.STREAMSOURCE1.GROUP1.headerBand) > height = 250.0 > endwith > > with (this.STREAMSOURCE1.detailBand) > height = 250.0 > endwith > > this.STREAMSOURCE1.detailBand.TITLETEXTCOMPANY1 = new TEXT(this.STREAMSOURCE1.detailBand) > with (this.STREAMSOURCE1.detailBand.TITLETEXTCOMPANY1) > canRender = {||this.parent.firstOnFrame} > height = 300.0 > left = 150.0 > top = 40.0 > width = 3780.0 > prefixEnable = false > suppressIfBlank = true > text = "<H3>Company</H3>" > endwith > > this.STREAMSOURCE1.detailBand.TEXTCOMPANY1 = new TEXT(this.STREAMSOURCE1.detailBand) > with (this.STREAMSOURCE1.detailBand.TEXTCOMPANY1) > height = 293.0 > left = 150.0 > top = 333.0 > width = 3780.0 > variableHeight = true > prefixEnable = false > text = {||this.form.customers1.rowset.fields["company"].value} > endwith > > this.STREAMSOURCE1.detailBand.TITLETEXTFIRSTNAME1 = new TEXT(this.STREAMSOURCE1.detailBand) > with (this.STREAMSOURCE1.detailBand.TITLETEXTFIRSTNAME1) > canRender = {||this.parent.firstOnFrame} > height = 300.0 > left = 4290.0 > top = 55.0 > width = 1530.0 > prefixEnable = false > suppressIfBlank = true > text = "<H3>Firstname</H3>" > endwith > > this.STREAMSOURCE1.detailBand.TEXTFIRSTNAME1 = new TEXT(this.STREAMSOURCE1.detailBand) > with (this.STREAMSOURCE1.detailBand.TEXTFIRSTNAME1) > height = 293.0 > left = 4290.0 > top = 348.0 > width = 1530.0 > variableHeight = true > prefixEnable = false > text = {||this.form.customers1.rowset.fields["firstname"].value} > endwith > > this.STREAMSOURCE1.detailBand.TITLETEXTLASTNAME1 = new TEXT(this.STREAMSOURCE1.detailBand) > with (this.STREAMSOURCE1.detailBand.TITLETEXTLASTNAME1) > canRender = {||this.parent.firstOnFrame} > height = 300.0 > left = 6180.0 > top = 40.0 > width = 1530.0 > prefixEnable = false > suppressIfBlank = true > text = "<H3>Lastname</H3>" > endwith > > this.STREAMSOURCE1.detailBand.TEXTLASTNAME1 = new TEXT(this.STREAMSOURCE1.detailBand) > with (this.STREAMSOURCE1.detailBand.TEXTLASTNAME1) > height = 293.0 > left = 6180.0 > top = 333.0 > width = 1530.0 > variableHeight = true > prefixEnable = false > text = {||this.form.customers1.rowset.fields["lastname"].value} > endwith > > with (this.printer) > duplex = 1 // None > orientation = 1 // Portrait > paperSource = 7 > paperSize = 1 > resolution = 3 // Medium > color = 2 // Color > trueTypeFonts = 1 // Bitmap > endwith > > this.PAGETEMPLATE1 = new PAGETEMPLATE(this) > with (this.PAGETEMPLATE1) > height = 15840.0 > width = 12240.0 > marginTop = 1080.0 > marginLeft = 1080.0 > marginBottom = 1080.0 > marginRight = 1080.0 > gridLineWidth = 0 > endwith > > this.PAGETEMPLATE1.STREAMFRAME1 = new STREAMFRAME(this.PAGETEMPLATE1) > with (this.PAGETEMPLATE1.STREAMFRAME1) > height = 11592.0 > left = 360.0 > top = 1365.0 > width = 9360.0 > form.STREAMFRAME1 = form.pagetemplate1.streamframe1 > endwith > > with (this.reportGroup.footerBand) > height = 250.0 > endwith > > with (this.reportGroup.headerBand) > height = 250.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.customers1.rowset > > endclass > | |