Report Grouping Pblm using RecordSet Object

[sajith]

Hi,can any one help me

iam generating a payslip Report.i want to group the Report with Employee Name and Department .
iam using Mysql Data Base and RecordSet Object.How can i group the Report using RecordSet.

CODE:

Code: Select all  Expand view

//------------------------------------------------------------------------------
FUNCTION GenEmpPaySlipBill(oRecSet,oDlg)
//------------------------------------------------------------------------------

   LOCAL oReport,oFont1, oFont2, oFont3, oPen1, oPen2
     DEFINE FONT oFont1 NAME "ARIAL" SIZE 0,-10 BOLD
     DEFINE FONT oFont2 NAME "ARIAL" SIZE 0,-10 BOLD
     DEFINE FONT oFont3 NAME "ARIAL" SIZE 0,-12 BOLD
     DEFINE PEN oPen1 WIDTH 1 COLOR 12632256
     DEFINE PEN oPen2 WIDTH 1 COLOR CLR_HMAGENTA

 REPORT oReport ;
   TITLE  " "  ;
   CENTERED;
   FONT oFont1,oFont2,oFont3  ;
   PEN oPen1, ;
   oPen2 ;
   HEADER  Space(40)+"XXXXXXXXX", ;
           Space(100),;
           "NAME                    :" + AllTrim(oRecSet:Fields( 'EmpName' ):Value) + Space(Len(oRecSet:Fields( 'EmpName' ):Value)+20)     +"DESIGNATION        :" + oRecSet:Fields( 'Department' ):Value ,;
            Space(100),;
           "MONTH\YEAR       :" + AllTrim(Str(oApp:nFnYear))+ "-" + AllTrim(Str( oRecSet:Fields( 'MonthOfTransaction' ):Value)),;
            Space(100),;
           "BASIC SALARY    :" + AllTrim(str(oRecSet:Fields('BasicSalary' ):Value)) + Space(Len(oRecSet:Fields( 'EmpName' ):Value)+20)      + "MOBILE ALLOWENCE  :" ,;
            Space(100),;
           "LEAVE DAYS        :" + AllTrim(str(oRecSet:Fields('NoOfLeaves' ):Value)) + Space(Len(oRecSet:Fields( 'EmpName' ):Value)+25)     + "MOBILE BILL       :" + AllTrim(Str(oRecSet:Fields( 'MobileBill' ):Value)),;
            Space(100),;
           "LEAVE DDN          :" + AllTrim(str(oRecSet:Fields('LossOffPayAmt' ):Value)) + Space(Len(oRecSet:Fields( 'EmpName' ):Value)+25) + "ADDL MOBILE BILL  :" + AllTrim(Str(oRecSet:Fields( 'MobileRecovery' ):Value));
                  Left;
    GROUP ON oRecSet:Fields( 'EmpName' ):Value ;//Here Iam getting Error
   FOOTER  "Page Number: "+str(oReport:nPage,3);
   CENTER ;
   PREVIEW

 COLUMN TITLE 'PARTICULARS'     DATA "" CENTER  SIZE 30   GRID

 COLUMN TITLE 'EARNINGS'        DATA oRecSet:Fields('BasicSalary' ):Value,;
                                     oRecSet:Fields( 'Incentives' ):Value,;
                                     oRecSet:Fields( 'TAAmt' ):Value ,;
                                       "-",;
                                       "-",;
                                       "-",;
                                       "-",;
                                       "-"  TOTAL SIZE 15 CENTER    GRID
 COLUMN TITLE 'DEDUCTIONS'     DATA  "-" ,;
                                     "-",;
                                     "-",;
                                     oRecSet:Fields( 'TaxAmount' ):Value,;
                                     oRecSet:Fields( 'PFAmount' ):Value,;
                                     oRecSet:Fields( 'PfArrers' ):Value,;
                                     oRecSet:Fields( 'SalaryAdvance' ):Value,;
                                     oRecSet:Fields( 'MobileRecovery' ):Value TOTAL SIZE 15 CENTER GRID

  // COLUMN TITLE 'TOAL SALARY'  DATA oRecSet:Fields( 'TotalSalary' ):Value SIZE 10 CENTER  GRID
          oReport:SetTxtColor(nRGB(0,0,0),1)
          oReport:lSeparator := .T.
          oReport:CELLVIEW()
           oReport:nTotalLine := RPT_SINGLELINE
          oReport:oTITLE:afont[1] :={||3}


END REPORT

  oReport:bSkip := { || oRecSet:MoveNext() }
  oRecSet:MoveFirst()
  ACTIVATE REPORT oReport WHILE ( ! oRecSet:Eof() )                                                           ;
           ON init(oReport:Say(0,"SALARY",,,oReport:nRow),;
                        oReport:Say(0,"INCENTIVE",,,oReport:nRow+100),;
           oReport:Say(10,"TRAVELLING ALLOWENCE",,,oReport:nRow+200),oReport:Say(10,"INCOME TAX",,,oReport:nRow+300),;
           oReport:Say(10,"PROVIDENT FUND",,,oReport:nRow+400),oReport:Say(10,"PROVIDENT FUND ARRERS",,,oReport:nRow+500),;
           oReport:Say(10,"SALARY ADVANCE",,,oReport:nRow+600),oReport:Say(10,"MOBILE RECOVERY",,,oReport:nRow+700))
  oRecSet:MoveFirst()
  //ON STARTPAGE (StartPage(oReport),  oReport:Say(10,"ghhhj",,,1060))
  oDlg:END()
  oFont1:END()
  oFont2:END()
  oFont3:END()


RETURN Nil


 

[James Bott]

Sajith,

The GROUP ON definition needs to be on a separate line, just like the column definitions. It cannot be part of the REPORT... definition (as you have it).

Also, I think you want it grouped on dept but not on employee. If you group on employee also, then you are going to get a new group for each employee.

I hope you are not confusing grouping with sorting. Grouping will break the report after each group.

You will need to have the recordset sorted by Dept if you want to group on it. You may want to sort it by Dept + employee if you want the employees in alpha order within the group.

James

[sajith]

Dear James,many thanks for ur suppot

I hope you are not confusing grouping with sorting. Grouping will break the report after each group.

i want to do grouping on Empname and Department .prblm is how can i assign column name
to the group. In DBF we can give like :: GROUP ON PayRoll->EmpName .but iam using Mysql tere
every thing is RecordSet .

Modified Code::

Code: Select all  Expand view

COLUMN TITLE 'PARTICULARS'     DATA "" CENTER  SIZE 30   GRID

 COLUMN TITLE 'EARNINGS'        DATA oRecSet:Fields('BasicSalary' ):Value,;
                                     oRecSet:Fields('Incentives' ):Value,;
                                     oRecSet:Fields('TAAmt' ):Value ,;
                                       "-",;
                                       "-",;
                                       "-",;
                                       "-",;
                                       "-"  TOTAL SIZE 15 CENTER    GRID
 COLUMN TITLE 'DEDUCTIONS'     DATA  "-" ,;
                                     "-",;
                                     "-",;
                                     oRecSet:Fields( 'TaxAmount' ):Value,;
                                     oRecSet:Fields( 'PFAmount' ):Value,;
                                     oRecSet:Fields( 'PfArrers' ):Value,;
                                     oRecSet:Fields( 'SalaryAdvance' ):Value,;
                                     oRecSet:Fields( 'MobileRecovery' ):Value TOTAL SIZE 15 CENTER GRID

  GROUP ON  oRecSet:Fields("EmpName"):value //Group by


  // COLUMN TITLE 'TOAL SALARY'  DATA oRecSet:Fields( 'TotalSalary' ):Value SIZE 10 CENTER  GRID
          oReport:SetTxtColor(nRGB(0,0,0),1)
          oReport:lSeparator := .T.
          oReport:CELLVIEW()
           oReport:nTotalLine := RPT_SINGLELINE
          oReport:oTITLE:afont[1] :={||3}


END REPORT

  oReport:bSkip := { || oRecSet:MoveNext() }
  oRecSet:MoveFirst()
   //
  ACTIVATE REPORT oReport WHILE ( ! oRecSet:Eof() )    
 

Regards,
sajith

[James Bott]

Sajith

>GROUP ON oRecSet:Fields("EmpName"):value

Are you saying that the above line is not working? Is the recordset sorted by empName?

>i want to do grouping on Empname and Department

I am still not clear. Do you have multiple records for each empolyee? If not you are going to get a new group for each record.

If you do have multiple records per employee, then you need to sort by department + employee, then you need two group statements.

group on oRecSet:Fields("Department"):value
group on oRecSet:Fields("EmpName"):value

James

[James Bott]

>In DBF we can give like :: GROUP ON PayRoll->EmpName .but iam using Mysql tere
every thing is RecordSet .

It is the same with a recordset except for how you specify the field.

James

[sajith]

James,Many Many thanks for ur great support

I am still not clear. Do you have multiple records for each empolyee? If not you are going to get a new group for each record.

Let me Explane: i have a Form for selecting Report. Form have a combo box to select corresponding Employee.ComboBox has a Provision to select single or All Employees( "Select All" Which Means to select all Employees Report). i need to show employee with name and Department wise for that i use Grouping . i tried the below code still error .can u pls send a sample code for grouping using RecordSet.

Code: Select all  Expand view

 COLUMN TITLE 'PARTICULARS'     DATA "" CENTER  SIZE 30   GRID

 COLUMN TITLE 'EARNINGS'        DATA oRecSet:Fields('BasicSalary' ):Value,;
                                     oRecSet:Fields('Incentives' ):Value,;
                                     oRecSet:Fields('TAAmt' ):Value ,;
                                       "-",;
                                       "-",;
                                       "-",;
                                       "-",;
                                       "-"  TOTAL SIZE 15 CENTER    GRID
 COLUMN TITLE 'DEDUCTIONS'     DATA  "-" ,;
                                     "-",;
                                     "-",;
                                     oRecSet:Fields( 'TaxAmount' ):Value,;
                                     oRecSet:Fields( 'PFAmount' ):Value,;
                                     oRecSet:Fields( 'PfArrers' ):Value,;
                                     oRecSet:Fields( 'SalaryAdvance' ):Value,;
                                     oRecSet:Fields( 'MobileRecovery' ):Value TOTAL SIZE 15 CENTER GRID


    GROUP ON  oRecSet:Fields("EmpName"):value   //Grouping
    GROUP ON  oRecSet:Fields( "Department" ):Value


          oReport:SetTxtColor(nRGB(0,0,0),1)
          oReport:lSeparator := .T.
          oReport:CELLVIEW()
           oReport:nTotalLine := RPT_SINGLELINE
          oReport:oTITLE:afont[1] :={||3}


END REPORT

  oReport:bSkip := { || oRecSet:MoveNext() }
  oRecSet:MoveFirst()
  MsgInfo("eee")
  ACTIVATE REPORT oReport WHILE ( ! oRecSet:Eof() )
           //ON init(oReport:Say(0,"SALARY",,,oReport:nRow),;
                       // oReport:Say(0,"INCENTIVE",,,oReport:nRow+100),;
          // oReport:Say(10,"TRAVELLING ALLOWENCE",,,oReport:nRow+200),oReport:Say(10,"INCOME TAX",,,oReport:nRow+300),;
           //oReport:Say(10,"PROVIDENT FUND",,,oReport:nRow+400),oReport:Say(10,"PROVIDENT FUND ARRERS",,,oReport:nRow+500),;
           //oReport:Say(10,"SALARY ADVANCE",,,oReport:nRow+600),oReport:Say(10,"MOBILE RECOVERY",,,oReport:nRow+700))
  oRecSet:MoveFirst()

  oDlg:END()
  oFont1:END()
  oFont2:END()
  oFont3:END()


RETURN Nil

 

Regards,
sajith

[James Bott]

Sajith,

First, what is the error you are getting? Is it a compile time error or a runtime error.

Next I suggest creating the most simple report possible to show the problem. Make a copy of the current report and remove every line that is not needed. Just make a COLUMN definition with say EmpName another with Department, and only one grouping on Department.

I don't have MySQL installed here to test it on, so you will need to do it. The syntax that you are now using for the GROUP looks correct to me.

Let us know how this goes and exactly what errors you get, if any.

Also, please, do you have multiple records for each employee in you recordset, or just one record for each emplyee?

Regards,
James

[sajith]

Dear James,many many thanks for ur superb Support

Error is traced it is a Run time Error(Record Set Pblm).

Next I suggest creating the most simple report possible to show the problem. Make a copy of the current report and remove every line that is not needed. Just make a COLUMN definition with say EmpName another with Department, and only one grouping on Department.

Modified Code ::

Code: Select all  Expand view

  REPORT oRep;
   HEADER  Space(40)+"jooo";
  Preview
COLUMN TITLE 'Name' DATA oRecSet:Fields( 'EmpName' ):Value
COLUMN TITLE 'Code'  DATA oRecSet:Fields( 'Department ' ):Value

END REPORT

oRep:bSkip := { || oRecSet:MoveNext() }
oRecSet:MoveFirst()

ACTIVATE REPORT oRep WHILE ( ! oRecSet:Eof())
GROUP ON oRecSet:Fields('Department'):Value; //Grouping with Department
      HEADER      "NAME                    :" + AllTrim(oRecSet:Fields( 'Department' ):Value) //Group Header

oRecSet:MoveFirst()
 

now i want to place Employee Personal details in Group Header and salary details in Detail part.
the above code does not work .in the group header nothing is displayed .can u pls correct my code.

do you have multiple records for each employee in you recordset, or just one record for each emplyee?

i have only single record for Employee

Regards,
sajith

[James Bott]

Sajith,

OK, I setup a test report to test recordset grouping. The error is generated because the report uses a feature of DBF RDDs that errors out in recordsets. That is when a DBF is at the EOF you can still retrieve a value for any field (it is always blank). No so with recordsets, when at EOF, if you try to retrieve a value, you will get an error.

The error actually occurs in the Check() method of the TRGroup class which is used by the TReport class. Each time the report skips it checks to see if the group has ended (before it checks for EOF).

I thought about it for awhile an no immediate solution comes to me (but I have been quite sick and am not thinking all that well). A workaround is to move all the data to a temp DBF then run the report on the DBF instead.

If a solution comes to me I will let you know. Maybe someone else has an idea?

Regards,
James

[James Bott]

Sajith,

OK, I think I have a working solution. Below is my test report which you can run using the sample Northwind.mdb that comes with many MS products.

You will need to modify the FW TRGROUP.PRG and change the Check() method from INLINE to the method shown below.

James

Code: Select all  Expand view

Program : Test02.prg
Purpose : Test report using RecordSet and groups
App     : none
Author  : James Bott, jbott@compuserve.com
Date    : 08/19/09 12:12:41
Language: Fivewin Harbour/xHarbour
Notes   : Using the Northwind.mdb sample Access table
          Note we are using field 'city' for department since there is no
          Department field.

Found that TRGroup:Check() is causing an error when trying to read data
while at EOF (which is allowed in DBF RDDs). This only happens when
groups are used.

Requires: TRGroup class modification (FWH v9.05)
*/

#include "fivewin.ch"
#include "report.ch"


FUNCTION MAIN()

    LOCAL oRS, oCat

    oRS = TOLEAuto():new( "ADODB.Recordset" )

    oRS:Open( "SELECT * FROM Employees order by city + LastName + FirstName", "Provider=Microsoft.Jet.OleDB.4.0;Data Source=Northwind.mdb",0,3,0 )

    myReport( oRS )

    oRS:Close()

RETURN NIL


Function MyReport( oRecSet )
   local oRep

   REPORT oRep;
      HEADER  Space(40)+"jooo";
      Preview

   COLUMN TITLE 'Name       ' ;
      DATA "  "+oRecSet:Fields( 'LastName' ):Value +", "+;
      oRecSet:Fields( 'FirstName'):Value

   GROUP ON oRecSet:Fields('City'):Value;
      HEADER "Department: "  + oRecSet:Fields( 'City' ):Value;
      FOOTER oRep:aGroups[1]:cValue+" Total..."

   GROUP ON oRecSet:Fields('LastName'):Value + oRecSet:Fields("FirstName"):Value;
      FOOTER "  Total..."

   END REPORT

   oRep:bSkip := { || oRecSet:MoveNext() }

   oRep:bPostGroup:={|| oRep:newLine() } // skip a line after each group

   ACTIVATE REPORT oRep WHILE ! oRecSet:Eof()

   oRecSet:MoveFirst()

return nil

Code: Select all  Expand view

method Check() class TRGROUP
  local lEndGrp
  if ! Eval( ::oReport:bWhile )
     lEndGrp:= .t.
  else
     lEndGrp:= !(::cOldValue := cValToChar(Eval(::bGroup)) , ::cOldValue == ::cValue )
  endif
return lEndGrp

eof

[sajith]

Dear james,ManyMany thanks for ur sample Program and Support

i will try it out and send the feedback.


Regards,
sajith

[Antonio Linares]

James,

Thanks!

[sajith]

Dear James,Many Many thanks for Ur Support

Grouping Pblm solved.i want my report in the format given below

Code: Select all  Expand view

      EmpName:SAJI       Department:BDD        Joining Date:05/09/2009   //Group Header
     ---------------------------------------------------------------------------------
     Particulars                        Earnings              Deductions  
    --------------------------------------------------------------------------------
      Basic Salary                     3000.00                    -
      Incentive                        500.00                     -
     Salary Advance                 -                           200.00
      PF                                  -                          1800.00
  --------------------------------------------------------------------------------
       Total                         3500.00                       2000.00
    ---------------------------------------------------------------------------------

    EmpName:XXXX    Department:ZCC        Joining Date:05/09/2009   //Group Header
     ----------------------------------------------------------------------------
     Particulars                    Earnings                Deductions  
    ---------------------------------------------------------------------------
      Basic Salary                  4000.00                    -
      Incentive                      500.00                     -
     Salary Advance               -                           200.00
      PF                                 -                          800.00
  ----------------------------------------------------------------------------
       Total                         4500.00                  1000.00
    -------------------------------------------------------------------------
My pblm is iam not getting my report in this format.
 

Code: Select all  Expand view

  PREVIEW
   COLUMN TITLE 'PARTICULARS'     DATA "" CENTER  SIZE 30   GRID
   COLUMN TITLE 'EARNINGS'        DATA oRecSet:Fields('BasicSalary' ):Value,;
                                     oRecSet:Fields('Incentives' ):Value,;
                                     oRecSet:Fields('TAAmt' ):Value ,;
                                       "-",;
                                       "-",;
                                       "-",;
                                       "-",;
                                       "-"  //TOTAL SIZE 15 CENTER    GRID
  COLUMN TITLE 'DEDUCTIONS'     DATA  "-" ,;
                                     "-",;
                                     "-",;
                                     oRecSet:Fields( 'TaxAmount' ):Value,;
                                     oRecSet:Fields( 'PFAmount' ):Value,;
                                     oRecSet:Fields( 'PfArrers' ):Value,;
                                     oRecSet:Fields( 'SalaryAdvance' ):Value,;
                                     oRecSet:Fields( 'MobileRecovery' ):Value
  GROUP ON oRecSet:Fields("Department" ):Value;
    HEADER   "NAME           :" + AllTrim(oRecSet:Fields( 'EmpName' ):Value) + Space(Len(oRecSet:Fields( 'EmpName' ):Value)+20)     +"DESIGNATION        :" + oRecSet:Fields( 'Department' ):Value+ ;
           "MONTH\YEAR       :" + AllTrim(Str(oApp:nFnYear))+ "-" + AllTrim(Str( oRecSet:Fields( 'MonthOfTransaction' ):Value))+;
           "MONTH\YEAR       :" + AllTrim(Str(oApp:nFnYear))+ "-" + AllTrim(Str( oRecSet:Fields( 'MonthOfTransaction' ):Value))+;
           "BASIC SALARY     :" + AllTrim(str(oRecSet:Fields('BasicSalary' ):Value)) + Space(Len(oRecSet:Fields( 'EmpName' ):Value)+20)      + "MOBILE ALLOWENCE  :" +;
           "LEAVE DAYS       :" + AllTrim(str(oRecSet:Fields('NoOfLeaves' ):Value)) + Space(Len(oRecSet:Fields( 'EmpName' ):Value)+25)     + "MOBILE BILL       :" + AllTrim(Str(oRecSet:Fields( 'MobileBill' ):Value))+;
           "LEAVE DDN        :" + AllTrim(str(oRecSet:Fields('LossOffPayAmt' ):Value)) + Space(Len(oRecSet:Fields( 'EmpName' ):Value)+25) + "ADDL MOBILE BILL  :" + AllTrim(Str(oRecSet:Fields( 'MobileRecovery' ):Value));
 FOOTER oReport:aGroups[1]:cValue
  END REPORT
 

I want to keep group header apart frm the column .
now group header comes inside the column .how can i get my report in the format given above

Regards,
sajith

[James Bott]

Sajith,

>how can i get my report in the format given above

This issue is why I kept asking what you we trying to do. TReport is designed only to build column style reports, and what you want is not a column style report.

First let me make a few comments on reports. The report style you are asking for is a very inefficent use of space, and if it is being printed, then a very big waste of paper.

What reports are supposed to do is provide information in a clear and consise format and make it easy to find. All the information that you have in a group (which is 12 lines long) could be provided in a column style report in 1 line. Not only is this a better use of space but the information will be much easier to find since the report will be 1/12th as many pages. This will also require 1/12th as much paper if printed.

I don't know if the design is yours, your employer's or client's, but a new design is my first suggestion.

If you still want to stick with the original design, then there are three options. First you can try to trick the TReport class into creating your format (not simple but maybe possible), you can reformat the data by preprocessing it, then use TReport. Or, you can write a free-form report from scratch (not using TReport).

Regards,
James

[sajith]

Dear james,Many Many thanks for ur support

sorry for troubling u again.
Iam doing a Pay Roll S/w .i want to generate a Payslip bill which is in the format given in last post .
only the Payslip bill is in that format rest Report r in column format as u said .
In Crystal Report it is very easy to make a Report in that format.Iam not familiar with Five Win reports it is Little bit difficult for me .ur support is really helping me thanks,

Regards,
sajith