Employee Mileage Report
Executive Summary
Employee Mileage Report estimates how far employees drive between their home and their client visits each day. Supervisors start a report for a date range, optionally limited to one or more groups or a single employee. The system retrieves the visits from AlayaCare, plans a driving route for each employee and day (home, then each client in visit-time order, then home), and produces an Excel workbook with visit-level detail and a daily kilometre summary. Each report is kept as a job in the list so the workbook can be downloaded again later.
Key Features
- Start a new mileage report for a date range
- Limit the report to selected groups, one employee, or leave both blank for all employees
- Choose which visit statuses are left out of the report (in the page settings, when permitted)
- Estimate daily driving distance per employee from home, through each client, and back home
- Get an Excel workbook with the report criteria, a visit-level detail sheet, and a daily summary with a 20 km allowance column
- Review earlier report jobs with their status, run times, and number of included visits, and download their workbooks again
Typical Workflow
- Click Start A New Report.
- Choose a date range in Choose a Range. Optionally select Groups or an Employee, or leave both blank for all employees. Click Ok.
- Watch the processing panel while visits are retrieved and routes are calculated. You can cancel from the panel.
- When the job completes, the Excel workbook downloads automatically and the job appears in the list with status Completed.
- To get the workbook again later, click Download Excel on the job row.
Report Output
An Excel workbook (.xlsx) named EmployeeMileage_ followed by the date and time of the run. It downloads automatically when the report finishes and is saved on the job so it can be downloaded again with Download Excel. The workbook has three sheets:
- Report: the criteria used (date from, date to, groups, employee, and the visit statuses excluded).
- DailyDtl_{end date}: one block per employee and day, with a row for the employee's start location, one row per visit, and a row for the employee's end location.
- Daily_{end date}: one summary row per employee and day.
Report Contents
Daily detail sheet
| Content | How it is determined |
|---|---|
| employee_group | The employee's AlayaCare groups, comma separated. |
| employee_jobtitle, employee, employee_firstname, employee_lastname | Employee details from AlayaCare. |
| date, time | Visit start date and time in the tenant time zone. |
| client, client_firstname, client_lastname, client_address_line1, client_city, client_zipcode, client_country | Client profile from AlayaCare. Visits whose client cannot be loaded from AlayaCare are left off the sheet. |
| client_latitude, client_longitude | The client's geocoded location. Blank when the client's address is not geocoded. |
| Processing Message | Employee Added As Start Location, Client Added As Waypoint, Employee Added As End Location, Client Address not geocoded, or Employee Address not geocoded (when the employee has no geocoded address). |
| service_code | Service code name of the visit. |
| visit_time_int, visit_date_int | Visit start time (HHmm) and date (ddMMyyyy) as numbers. |
| cancel_code | For cancelled visits, the cancel code (or the AlayaCare cancel reason number if the code is not set up). |
| approval_status | cancelled, approved, or not approved. |
| hold_code | For visits on hold, the hold reason description from AlayaCare. |
| actual_duration | For approved visits, hours from the first clock-in to the last clock-out of the completed work sessions. 0 otherwise. |
| end_date, end_time | Visit end date and time. |
| employee_address, employee_city, employee_zipcode | Employee home address. |
| branch_id, branch_name | The employee's branch. |
| employee_designation, employee_role | Employee designation and roles, comma separated. |
Other columns on this sheet (for example payroll ID, group 2, and flag sequence) are written blank.
Daily summary sheet
| Content | How it is determined |
|---|---|
| Employee ID, Employee Name, Date | The employee and service day. |
| Total KMs driven | Driving distance of the route from the employee's home, through that day's clients in visit-time order, and back home. |
| Total KMs driven - 20 KM | Total KMs minus 20 km; 0 when the total is under 20 km. |
| Number of Visits | Visits included for that employee and day. |
| Message | Employee Address not geocoded. Route Calculation not possible when the employee's home has no geocoded location. |
Configurable Settings
Open View Settings or Edit Settings from this screen (when permitted) to change these options.
| Group | Setting | Type | Purpose |
|---|---|---|---|
| General | Visit Statuses To Exclude | Multi-select dropdown (checkboxes, with Select All) | Visit statuses that are left out of the report. The statuses offered come from the configuration list. |
The label Visit Statuses To Exclude is taken from the earlier documentation because the label itself is stored in a configuration table.
Available Permissions
The following permissions are available to be assigned to AlayaCare Roles:
| Attribute | What It Controls |
|---|---|
| View | Open the page |
| Settings View | Open View Settings to see the page configuration |
| Settings Edit | Open Edit Settings and change the page configuration |
Main Grid
The grid lists the mileage report jobs, newest first (14 rows per page).
| Column | Description |
|---|---|
| ID | Job number. |
| Added | Date and time the job was created, in the tenant time zone. |
| Added By | User who started the job. |
| From Date | Start of the reporting period. |
| To Date | End of the reporting period. |
| Processing Start | When processing began. |
| Processing End | When processing finished. |
| Duration | How long processing took. |
| Included Visits | Number of visits included in the report. |
| Status | In Progress, Completed, or Cancelled. |
| Manage Records | Contains the Download Excel button. |
Actions
| Button | Description |
|---|---|
| Start A New Report | Opens the Start A New Report dialog. |
| View Settings | Opens the page settings in read-only mode (when settings view is permitted). |
| Edit Settings | Opens the page settings for editing (when settings edit is permitted). |
| Download Excel (row button) | Downloads the saved workbook for that job. Does nothing for jobs that have no workbook (for example cancelled jobs). |
Dialog Components
Start A New Report
Opens from Start A New Report. Choose the dates and who to include, then click Ok.
| Field | Purpose |
|---|---|
| Choose a Range | From and to dates for the visits to include. Both dates are required; otherwise a warning asks you to select a valid date range. |
| Groups | Optional. One or more employee groups (disabled groups are not offered), with Select All. Choosing groups clears and locks the employee box. |
| Employee | Optional. Search for a single employee. Locked while groups are selected. |
| Ok | Starts the report. |
Groups and employee are alternatives: leave both blank to include all employees.
Dependencies
The following tables must be configured for lookups on this view to work:
| Table | Description |
|---|---|
| Branches | Branch name shown for each employee on the detail sheet. |
| Cancel Codes | Cancel code shown for cancelled visits on the detail sheet. |
| Groups | Group list offered in the Groups drop-down. |
Processing Overviews
This section describes what happens after you confirm a processing action.
Start A New Report
- The AlayaCare login is confirmed. If it fails, a warning is shown and nothing is started.
- A job is created with status In Progress, recording who started it, the date range, and the start time.
- Visits for the date range (midnight on the first day to 11:59:59 PM on the last day) are retrieved from AlayaCare: for the employees in the selected groups, for the selected employee, or for all employees (the vacant-visit placeholder employee is excluded). When one employee is selected, that employee's time zone is used; otherwise the tenant time zone is used.
- Visits with no client name, and visits whose status is in Visit Statuses To Exclude, are removed. If Scheduled is among the excluded statuses, every visit is removed.
- For each employee (sorted by last name) and each service day, the employee's details are loaded and a start-location row is written. Then, for each visit in time order, the visit details (cancel or hold reason, clock times), and the client profile are loaded and a row is written. Clients with a geocoded address are added to the route; clients without one are noted as not geocoded. An end-location row follows.
- If the employee has a geocoded home address, a driving route is requested from the mapping service and its total distance is recorded for that employee and day. Without a geocoded home address, the route is skipped and the summary notes it.
- If you cancel from the processing panel, the job is marked Cancelled and stops.
- The daily summary sheet is written, the job is marked Completed with its end time, duration, and visit count, the workbook is saved on the job and downloaded, and Exported Successfully is shown.