Subject Re: REPORT GROUPING ON FIELDS
From Mervyn Bick <invalid@invalid.invalid>
Date Fri, 23 Jun 2017 08:14:47 +0200
Newsgroups dbase.getting-started
Attachment(s) test_groupby1.rep

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