Weekly Technician Productivity Report and OnDemand SSRS Report providing extensive call status and availability.
The Tech Productivity Report stack ranks all your techs based on those standard metrics like gross calls per day, net calls, hours worked etc. It is designed as a snap shot to see who is working effectively. Knowing who is working a full day, who is fixing a call correctly the first time, and has the parts they need available is key information about the effectiveness of your service team. We send it on a Monday for the previous week but include a link that lets you change the date range and other options.
Here are some industry benchmarks: In the MFP world reschedules should be 8%, callbacks should be 10%, total work time should be 7-7.2 hours and FCE% should be 82%.
Run Schedule: Monday morning, reports for the previous week data
Type of Output: Email and OnDemand SSRS Report
* * *
* * *
VariableW: Set to send consolidate to deliver company wide report (10.16.19 this Variable function is currently broken)
VariableX - specify OnHold codes (in addition to PA/Parts Available) as indicating the tech has the parts he was waiting on. This is used to calculate the column [Parts Available Tech to Close]
VariableY - The number of hours per day you expect the tech to be accountable for based upon time card entries, call times, unavailable times. If you do not require your techs to create a timecard entry for lunch, then you would use 8 hours per day. If you have your techs create a time card entry for lunch, then you would use 9 hours for accountable time. (decimal ok - i.e. 7.5)
VariableZ - Types of calls excluded from the Net Calls calculation - this will effect the filter used for Monday AM email sent containing report
* * *
1. Emails generated to those listed in to/cc/bcc will receive separate email by Tech Manager.
2. This report does not send to the individual technician, only to each technician manager. The 'ToTechnician' checkbox does not have any functionality in this case. Only the 'ToTech Manager' checkbox will work for this alert.
3. eAuto Set Up Requirements:
a. Be sure both techs and tech managers have an email address on eAuto Employee Record
b. Be sure your techs have the correct manager assigned to them
4. Report is only built to look back 6 months max, it is not built to run history beyond 6 months from date you are running. Consider using our ID28 Power BI Tech Productivity for more data and better performance.
5. This alert shows key metrics (from both completed and invoiced service calls) that you should be focused on to ensure your technicians are being effective.
6. Column Explanations:
**In gray (10%, 8%, 82%) we show Target Percentage and Info button takes you to documentation details/overview link**
1. Gross Work Days - per your date filter settings on report, total number of work days set in eAuto SLA Code / Company Hours (details on where/how set HERE)
Unavailable Days - per Time Card and/or Unavailable Entries for Tech (please use SSRS ID205 Tech Time Log to evaluate details)
2. Net Days in Period - Difference between Gross Work Days and Unavailable Days
3. Total Calls - total calls in period based on Call Category filter
All CM Calls - total of call corrective maintenance category calls
PM Calls - total of preventative maintenance category calls
Shop Calls - total of shop category calls
Other Calls - total of all other category calls not set as CM, PM or shop calls
Missing information here? Verify you're using needed Call Categories in report filter settings.
Call categories seen via Lists & Codes / Call types / Call type code:
4. Net Calls - based on your formula selected in Net Calls filter on report (filter for email generated each week is set in VariableZ of your ID204 subscription)
5. Gross CPD - Gross Calls per Day = Total Calls / (Gross Work Days - Unavailable Days)
6. Callback Calls - callbacks being charged to that technician for that time period. In other words, a call the tech worked previously generated a callback, so the tech that did the original call is charged with a callback. (per settings found HERE in eAuto, we do not determine if considered callback)
% of Net Calls = Callback calls / Net Calls
7. Callbacks Worked - Number of callbacks worked in this reporting period, whether this tech's or somebody else's. Remember while the first call indicates who is responsible for the callback, anybody can work the second call (and that second call(s) is what this column reflects).
**Please note, the CallBacks Generated column could change for a reporting period due to the nature of the logic. For example, when you run the report for last week, you might get a count of 3, but if you come back 4 weeks from now and rerun the report for the same time frame...other calls might have triggered a callback!! Therefore you should not use this CallBacks Generated column for any key performance indicator since it's not a 100% stable historical number, whereas 'CallBack Calls' is (Callbacks are the calls that are the callback call and that flag does not change unless you void a service call for the period)
8. Incomplete/Resch - Total of all calls for period that are considered incomplete calls and had to be rescheduled. *Does not reflect rescheduled calls, just the first call that was incomplete.
% of Gross Calls = Qty of IncompleteResch Calls / Total Calls. Look at this number over a few months to see who has a high number then see what their inventory levels are like. An incomplete is typically because a part was needed. Ask Juice for help setting up your auto mins and maxs levels for that tech.
9. Calls from Incompletes (Reschedules) - Total of all calls for period that are considered rescheduled calls. These are the follow up calls, not the call that was incompleted.
% of Gross Calls = Qty of Incompletes (Reschedules) / Net Calls (please note Column Label is incorrect, the formula is % of Net Calls, not Gross Calls)
10. Gross FCFR (TC-(IC+CB))/TC - Gross First Call Fix Rate = Qty of Total Calls - Incompletes+Callbacks / Total Calls. (The number of calls completed without a Reschedule against the 'Total Calls' column.)
11. Net CM CPD - Net Calls per Day, formula = Net Calls / Total Days. (Please note column lable is incorrect and is not Corrective Maintenance/CM calls only).
12. Net First Call Fix Rate =100 - (Call Back Calls % + Incomplete Calls %)
FCFR Net is number of calls completed without a reschedule against the 'Net Calls' Column. The NetCalls formula is set in VariableZ (Types of calls excluded from the NetCalls calculation) also filter option on the manual report.
1. Installed Parts Cost - total average cost of any/all items (of any type, report considers any item used on service call) used on service calls reporting for period filter of report (based on service call invoiced date)
2. Average Installed Parts per Call - average of installed parts based on gross calls (Parts Cost / Gross Calls)
3. Previous Call Parts Cost - the materials average cost on the service call BEFORE the current ones shown (per your period filter on report) - how much in parts you put in device last time.
4. Previous Parts CPC - value of Previous Call Parts Cost divided by the number of clicks between previous call and reported call
5. Repair Time - Total Work Hours/Actual Labor Time (time between arrival and departure) - does not include travel time
6. Average RT Per Call - Average Repair Time per Call = Total Work Hours / Total Number of Gross Calls
7. Parts Available - the average time (in hours) from a call being released from final hold until call is closed, whether PartsAvailable (PA) or another OnHoldCode as specified in the VariableX on your alert subscription. (consider alert ID715/Parts received for service call not worked/completed in Y days)
8. Travel Time (excl PF calls) - Travel Time for period filter, excludes only Phone Fix calls
Average TT per Call - average Travel Time per call - per travel time entries on service calls, excludes only Phone Fix Calls
9. CM Resp Time - average response time for all corrective maintenance call types during period reporting (excludes rescheduled calls). This is Open Time to Arrival or Dispatch Time
1. Total Work + Travel Time - total repair and travel time for period reporting
2. Mileage - total miles reported for period reporting
3. Survey Rank Technician - average rating for question "rank your technician overall" on service surveys submitted for period reporting (based on service call completion date, not date survey submitted)
4. Calls on Equipment Not Assigned to Tech - calls for the reporting period which were on equipment not showing the tech completing the call to be the tech assigned to the equipment record in eAuto. (If you drill down under tech's service call detail, you will see which of the service calls this applies to.)
5. See ID205 Tech Time Log for details (if you're missing details for any tech, ensure the Time Card Export box is checked on techs eAuto Employee Record)
6. Pages Serviced is broken down based on your ID295 Model Category Mapping App. If you aren't subscribed to ID295, please consider! Otherwise all stats in MFP column and no breakdown available.
See ID207 Pages Serviced & Parts Variance Details for details.
Parts variance for period is where we want to see how effective the parts use is by the tech so we do that by cpc. Use ID207 Pages Serviced to see details
For any call closed during the week, we find the prior tech on the machine and see how effective he was on that call. The prior call parts cost and copies produced by that call gives us the parts cpc. Then we compare his prior call to the avg cpc for that model segment. This gives us the variance for the call in dollars.
To better explain the parts target; let’s say I ran this report for a model segment (digital segment 3) for the last 365 days, something every technician has in their territory (at least for most dealers). We provide this report as a custom report you can run from eAuto.
When looking the company total parts variance, we should see a number near zero*. This is because our parts used equaled our target for the last 12 months. However, looking at each technician, we see variances. –(and the managers totals as well; see example below.) Positive numbers mean they used more parts than the previous period and negative means they used less.
Let’s say, you think being average as a company is not good enough. Maybe you would like to drive the team to using 3% less parts for the next year. Then set variable adjustment to = .97
Run this as a rolling six month period every month, and take the copies and parts Var divided by 6. As a bonus program, pay per click earned and deduct/credit 10% of the parts variance from the total. -The six month rolling average is a very telling number, -good for some, bad for others.
Example for a full 12 months, all models; - total at the bottom, one manager on the group and each technician on a line.
* * *
OnDemand Report Filters
OnDemand Report Filters
Manager & Tech Must Be Selected in order to move through remaining filters
- Select only for Make(s) - use drop down filter to see report for selected Make(s).
When using this filter, please make sure to UNCHECK ALL; otherwise you will see error like:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'MainData'. (rsErrorExecutingCommand)
Violation of PRIMARY KEY constraint 'PK__#EquipFi__344A31F1EFA7D12A'. Cannot insert duplicate key in object 'dbo.#EquipFilter'. The duplicate key value is (678). inside else The 'rpt_ZCJ_SCServiceActivity' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
- Call Category - we highly recommend leaving this filter wide open and selecting ALL. If not, then stats will be skewed (i.e. total calls)
- Formula for Net Calls - select from drop downs available to determine what formula used for Net Calls Column calculations (e.g. Total CMCalls - Incompletes - CallBacks). See **VariableZ for formula used when alert triggers email each week.**
Top Margin of SSRS Report:
- Use these tools to save as, refresh or print report as needed Note: Default Print Size is 11x17
- Report header shows selected date range, working days in period (per EA Company Hours set), selected managers and techs. Also shows what formula set for Net Calls.
* * *
Best Practices & Tips
Best Practices & Tips
1. Tech Assists- Assisting techs should also show their time under the call in their section. We look at the labor records, so primary tech should get hours credit for their labor records and same for the assisting tech
2. Technician's Employee Record must be set as Tech and have Time Card Export Box checked off
3. Tech Missing from Report? Make sure tech is set up per these instructions:
Frequently Asked Questions:
Q: The email task isn't including two techs even though they are assigned to Tech Mgr and have ZCJFieldTech set to yes. They are showing in drop down when I run SSRS manually for the tech manager.
A: Techs that have not closed a non-cancelled call for the report period on email generated will not be listed on the report.
Q: When I access the drill down within the report taking me to a sub-report (i.e. ID205/Tech Time Log or ID206/Call Backs), it wipes out my ID204 Tech Productivity Report and using the back button makes me have to rerun it.
A: You will need to go to “compatibility view settings” in Internet Explorer, Internet Options, and add your SSRS Reports URL to the compatibility view sites as below:
Q: Can ID204 only email out one report or is it always going to send a report for tech manager AND company full report/or branch report (per VariableW)?
A: As currently designed it's one report per manager and one report per branch (or one or company if no branches). We expect to add some options around sending soon but wanted to wait for feedback to see what the client base wants. We could, for instance, take TO/CC of copy to the manager report, allowing you to 'turn it off' by unchecking the manager box. We could also allow you to enter a dummy value for branch to turn off that side of it. Trouble is, every time we make one client happy three others want something different, so we're hoping we'd see a trend or consensus about how it needs to work. Let us know at email@example.com!
* * *
ID28 - Power BI Tech Productivity
ID205 -Tech Productivity Report Time Sheets - Tech Time Log
ID206 - Tech Productivity Callback Report
ID207 - Tech Productivity Copy Details Report - Pages Serviced & Parts Variance Details
* * *