Subject Re: REPORT GROUPING ON FIELDS From Mustansir Ghor <mustan31@hotmail.com> Date Fri, 23 Jun 2017 04:38:36 -0400 Newsgroups dbase.getting-started Dear Mervyn Your assistance is a great help. Thank you. There is some confusion with me to understand below code. Here "this" refers for classname whose base class is REPORT. then can you explain what this statement means "form.STREAMFRAME1 = form.pagetemplate1.streamframe1". Also same with last 4 statements. I mean what does "form" represent. 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 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 >