Page 1 of 1
Report Grouping Pblm using RecordSet Object
Posted: Tue Aug 18, 2009 6:47 am
by 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
//------------------------------------------------------------------------------
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
Re: Report Grouping Pblm using RecordSet Object
Posted: Tue Aug 18, 2009 9:48 am
by 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
Re: Report Grouping Pblm using RecordSet Object
Posted: Tue Aug 18, 2009 11:13 am
by 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
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
Re: Report Grouping Pblm using RecordSet Object
Posted: Tue Aug 18, 2009 11:34 am
by 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
Re: Report Grouping Pblm using RecordSet Object
Posted: Tue Aug 18, 2009 11:41 am
by 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
Re: Report Grouping Pblm using RecordSet Object
Posted: Tue Aug 18, 2009 12:39 pm
by 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
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
Re: Report Grouping Pblm using RecordSet Object
Posted: Tue Aug 18, 2009 7:13 pm
by 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
Re: Report Grouping Pblm using RecordSet Object
Posted: Wed Aug 19, 2009 1:03 pm
by 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
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
Re: Report Grouping Pblm using RecordSet Object
Posted: Wed Aug 19, 2009 10:00 pm
by 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
Re: Report Grouping Pblm using RecordSet Object
Posted: Wed Aug 19, 2009 11:32 pm
by 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
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
method Check() class TRGROUP
local lEndGrp
if ! Eval( ::oReport:bWhile )
lEndGrp:= .t.
else
lEndGrp:= !(::cOldValue := cValToChar(Eval(::bGroup)) , ::cOldValue == ::cValue )
endif
return lEndGrp
eof
Re: Report Grouping Pblm using RecordSet Object
Posted: Thu Aug 20, 2009 6:17 am
by sajith
Dear james,ManyMany thanks for ur sample Program and Support
i will try it out and send the feedback.
Regards,
sajith
Re: Report Grouping Pblm using RecordSet Object
Posted: Thu Aug 20, 2009 7:21 am
by Antonio Linares
James,
Thanks!
Re: Report Grouping Pblm using RecordSet Object
Posted: Fri Aug 21, 2009 7:46 am
by sajith
Dear James,Many Many thanks for Ur Support
Grouping Pblm solved.i want my report in the format given below
Code: Select all
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
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
Re: Report Grouping Pblm using RecordSet Object
Posted: Fri Aug 21, 2009 12:58 pm
by 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
Re: Report Grouping Pblm using RecordSet Object
Posted: Fri Aug 21, 2009 1:17 pm
by 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