Sergey,
The query can be seen here:
SELECT b.branchname, u.wholename, SUM(COALESCE(TotalDials,0)+COALESCE(TotalNCAFDials,0)+COALESCE(TotalNCAMDials,0)+COALESCE(TotalNCPMDials,0)) ALLDials,
SUM(hrs.hours) AllHours,
SUM(t.TotalDemos) TotalDemos,
SUM(TotalSales) TotalSales,
SUM(TotalCancels) TotalCancels,
SUM(TotalRejects) TotalRejects,
SUM(TotalSaved) TotalSaved,
SUM(TotalOneLeg) TotalOneLeg,
SUM(TotalResetToClose) TotalResetToClose,
SUM(TotalLeads) TotalLeads,
SUM(TotalApptsIn) TotalApptsIn,
SUM(TotalApptsOut) TotalApptsOut,
SUM(TotalRepTied) TotalRepTied,
SUM(TotalNoShow) TotalNoShow,
SUM(TotalAdjustment) TotalAdjustment,
SUM(TotalIssues) TotalIssues,
SUM(t.TotalTMKRate) TotalTMKRate,
SUM(t.TotalTMKMinutes) TotalTMKMinutes,
SUM(TotalDead) TotalDead,
SUM(TotalResched) TotalResched,
SUM(NetAmount) NetAmount,
SUM(TotalAppts) TotalAppts,
SUM(TotalContacts) TotalContacts,
SUM(TotalDials) TotalTranDials
FROM (
Select branch_id, t.tmk_id, SUM(t.demos) TotalDemos,
SUM(sales) TotalSales,
SUM(cancels) TotalCancels,
SUM(reject) TotalRejects,
SUM(saved) TotalSaved,
SUM(oneleg) TotalOneLeg,
SUM(broken) TotalResetToClose,
SUM(count) TotalLeads,
SUM(CASE WHEN t.type = 'ENL' THEN appts ELSE 0 END) TotalApptsIn,
SUM(CASE WHEN t.type <> 'ENL' THEN appts ELSE 0 END) TotalApptsOut,
SUM(reptied) TotalRepTied,
SUM(noshow) TotalNoShow,
SUM(adjustment) TotalAdjustment,
SUM(issues) TotalIssues,
SUM(t.tmkrate) TotalTMKRate,
SUM(t.tmkminute) TotalTMKMinutes,
SUM(dead) TotalDead,
SUM(resched) TotalResched,
SUM(amount) NetAmount,
SUM(appts) TotalAppts,
SUM(COALESCE(t.cncts_pm,0) + COALESCE(t.cncts_af,0) + COALESCE(t.cncts_am,0)) TotalContacts,
SUM(COALESCE(t.dials_pm,0) + COALESCE(t.dials_af,0) + COALESCE(t.dials_am,0)) TotalDials
FROM leadtrans t LEFT JOIN tmk ON t.tmk_id = tmk.user_id
WHERE t.activitydate >= $P(startDate) and t.activitydate <= $P(endDate) and t.type <> 'CFM' and tmk.inactive = FALSE and t.branch_id <> 3 AND t.branch_id > 0 and t.branch_id IS NOT NULL AND $P(limitBranch) AND $P(limitStatus) AND $P(limitTMK) AND $P(limitTeam)
group by branch_id, tmk_id
) as t
LEFT JOIN LATERAL
(
SELECT sum(nc.dials_am) TotalNCAMDials,
sum(nc.dials_af) TotalNCAFDials,
sum(nc.dials_pm) TotalNCPMDials
FROM nocont nc
WHERE nc.activitydate >= $P(startDate) and nc.activitydate <= $P(endDate) and nc.tmk_id = t.tmk_id and nc.branch_id = t.branch_id AND nc.branch_id <> 3 and nc.branch_id > 0 and nc.branch_id IS NOT NULL AND $P(limitBranch)
) as nc ON TRUE
LEFT JOIN LATERAL
(
SELECT sum(hourstowork) hours
FROM user_timeclock tc
WHERE tc.login >= $P(startDate) and tc.login <= $P(endDate) and tc.user_id = t.tmk_id
) as hrs ON TRUE
JOIN usuarios u on t.tmk_id = u.id
JOIN branches b on t.branch_id = b.id
GROUP BY b.branchname, u.wholename
ORDER BY b.branchname, u.wholename
Expressions appear rather simple to me:
$E(totaltmkminutes / 60)
$E(totaltmkminutes / 60 / allhours)
$E(alldials / allhours)
Now the JS has a little bit more code but I assumed it would be quite fast:
Report body is doing some math for GRAND Totals as well as group totals:
report.sum_hours += report.cursor.columnValue( 'allhours' );
report.sum_dials += report.cursor.columnValue( 'alldials' );
report.sum_cont += report.cursor.columnValue( 'totalcontacts' );
report.sum_appts += report.cursor.columnValue( 'totalappts' );
report.sum_inappts += report.cursor.columnValue( 'totalapptsin' );
report.sum_outappts += report.cursor.columnValue( 'totalapptsout');
report.sum_resets += report.cursor.columnValue( 'totalresched' );
report.sum_issues += report.cursor.columnValue( 'totalissues' );
report.sum_demos += report.cursor.columnValue( 'totaldemos' );
report.sum_sales += report.cursor.columnValue( 'totalsales' );
report.sum_cns += report.cursor.columnValue( 'totalnoshow' );
report.sum_rns += report.cursor.columnValue( 'totalreptied' );
report.sum_oneleg += report.cursor.columnValue( 'totaloneleg' );
report.sum_rtc += report.cursor.columnValue( 'totalresettoclose' );
report.sum_tmkmins += report.cursor.columnValue( 'totaltmkminutes' );
report.hours[report.cursor.columnValue('wholename')] = parseInt(report.cursor.columnValue( 'allhours' ));
report.h_sum_hours += report.cursor.columnValue( 'allhours' );
report.h_sum_dials += report.cursor.columnValue( 'alldials' );
report.h_sum_cont += report.cursor.columnValue( 'totalcontacts' );
report.h_sum_appts += report.cursor.columnValue( 'totalappts' );
report.h_sum_inappts += report.cursor.columnValue( 'totalapptsin' );
report.h_sum_outappts += report.cursor.columnValue( 'totalapptsout');
report.h_sum_resets += report.cursor.columnValue( 'totalresched' );
report.h_sum_issues += report.cursor.columnValue( 'totalissues' );
report.h_sum_demos += report.cursor.columnValue( 'totaldemos' );
report.h_sum_sales += report.cursor.columnValue( 'totalsales' );
report.h_sum_cns += report.cursor.columnValue( 'totalnoshow' );
report.h_sum_rns += report.cursor.columnValue( 'totalreptied' );
report.h_sum_oneleg += report.cursor.columnValue( 'totaloneleg' );
report.h_sum_rtc += report.cursor.columnValue( 'totalresettoclose' );
report.h_sum_tmkmins += report.cursor.columnValue( 'totaltmkminutes' );
report.h_hours[report.cursor.columnValue('wholename')] = parseInt(report.cursor.columnValue( 'allhours' ));
And several expressions are blank with something like this in the pre_place for JS:
if (report.cursor.columnValue( 'totalissues' )==0) {
report.controls.demoIssues.label = 0;
} else {
report.controls.demoIssues.label = (report.cursor.columnValue( 'totaldemos' ) / report.cursor.columnValue( 'totalissues' ));
}
Ben