I have this old program from a client where they will load a payroll for employee the problem is it took 2 hours to display a 36 pages reports. I'm not really pro using crystal reports so i tried asking here.
First it load all of the data from payroll. that's the problem . The solution that i come up is to load only what the client selected in the paramater. It will first select all of the data from the database and after getting the data the parameter will display. How can i display first the parameter and select the payroll that i want to display only?
NOTE: This is sql query is stored in views of database
What I have tried:
THIS IS THE SQL QUERY FROM CRYSTAL REPORTS
SELECT `er`.`employeeid` AS `employeeid`, `er`.`position` AS `positions`, CONCAT(`em`.`lastname`,',',`em`.`firstname`,',',`em`.`middlename`) AS `employeename`,
cast(`tp`.`paydate` AS date) AS `paydate`, `tp`.`payperiod_start` AS `payperiod_start`, `tp`.`payperiod_end` AS `payperiod_end`,
(case `tp`.`payroll_month` when 'January' then cast(concat(year(`tp`.`payperiod_end`),'-01-01') as date)
when 'February' then cast(concat(year(`tp`.`payperiod_start`),'-02-01') as date)
when 'March' then cast(concat(year(`tp`.`payperiod_start`),'-03-01') as date)
when 'April' then cast(concat(year(`tp`.`payperiod_start`),'-04-01') as date)
when 'May' then cast(concat(year(`tp`.`payperiod_start`),'-05-01') as date)
when 'June' then cast(concat(year(`tp`.`payperiod_start`),'-06-01') as date)
when 'July' then cast(concat(year(`tp`.`payperiod_start`),'-07-01') as date)
when 'August' then cast(concat(year(`tp`.`payperiod_start`),'-08-01') as date)
when 'September' then cast(concat(year(`tp`.`payperiod_start`),'-09-01') as date)
when 'October' then cast(concat(year(`tp`.`payperiod_start`),'-10-01') as date)
when 'November' then cast(concat(year(`tp`.`payperiod_start`),'-11-01') as date)
when 'December' then cast(concat(year(`tp`.`payperiod_start`),'-12-01') as date) end) AS `payroll_month`,
`tp`.`total_gross_revenue` AS `total_gross_revenue`,
`tp`.`total_toll_fee` AS `total_toll_fee`,
`tp`.`total_commission` AS `total_commission`,
`tp`.`total_ca` AS `total_ca`,
`tp`.`gross_earnings` AS `gross_earnings`,
`tp`.`other_earnings` AS `other_earnings`,
`tp`.`total_gross` AS `total_gross`,
`tp`.`wtax` AS `wtax`,
`tp`.`sss` AS `sss`,
`tp`.`medicare` AS `medicare`,
`tp`.`pagibig` AS `pagibig`,
`tp`.`total_basic_deductions` AS `total_basic_deductions`,
`tp`.`other_deductions_sss_loan` AS `other_deductions_sss_loan`,
`tp`.`other_deductions_bond` AS `other_deductions_bond`,
`tp`.`other_deductions_pagibig_loan` AS `other_deductions_pagibig_loan`,
`tp`.`other_deductions_pagibig_fund` AS `other_deductions_pagibig_fund`,
`tp`.`other_deductions_loan` AS `other_deductions_loan`,
`tp`.`other_deductions_donation` AS `other_deductions_donation`,
`tp`.`other_deductions_otherloans` AS `other_deductions_otherloans`,
`tp`.`other_deductions_penalty` AS `other_deductions_penalty`,
`tp`.`other_deductions_paybal` AS `other_deductions_paybal`,
`tp`.`other_deductions_otherd` AS `other_deductions_otherd`,
`tp`.`total_other_deductions` AS `total_other_deductions`,
`tp`.`total_net` AS `total_net`,
`tp`.`department` AS `department`,
`tp`.`status` AS `status`,
`tp`.`other_deductions_bond_loan` AS `other_deductions_bond_loan`,
`tp`.`station` AS `Station`,
`tpd`.`bus_number` AS `bus_number`
FROM employee_master em JOIN employee_record er ON er.employeeid = em.employeeid JOIN tor_payroll tp ON tp.employeeid = er.employeeid JOIN tor_payroll_details tpd ON tp.paydate = tpd.payroll_date
GROUP BY employeename