271363
271363 - PA-7XNLH9/ Consumer KPI Report
Copyright OBS Logistics © 2010
The information contained herein is the property of OBS Logistics and is supplied without liability for errors or omissions. No part may be reproduced or used except as authorised by contract or other written permission. The copyright and foregoing restriction on reproduction and use extend to all media in which the information may be embodied
FUNCTIONAL OVERVIEW
Client Requirement
Create a Consumer KPI Report from MTS Data
Create a report in MTS Reports - PDF Format to show: Analysed by Own Fleet, CN Subbed, Non CN DHL subbed, external Subbed & totals where appropriate for: Lifts, Revenue, Distance, Trips, Vehicle Fill (per Trip), Distance per RPE, Revenue per RPE, Subbed Trip Costs, Empty Vehicle Distances, Subbed Percentage Revenue per KM, Delivery Volumes by Region. See Attachment. Allow this report to be run by individual Group Name, Regional Group (Pre-specified) and Complete Network (All) - access controlled by Group parameters for input-controlled Date Range.
Solution
New records in table REP_REPORT and REP_REPORT_PARAM will be added for report ‘CN KPI Report’. The following parameters will be added: Schedule Range (mandatory), Owning Depot (mandatory), Group Name and Regional Group.
A new Oracle Report will be written. This will print in A4 landscape. It will select all trips and orders that are not marked as cancelled for the schedule(s) selected.
The report will print totals in 8 sections, Lifts, Revenue, Distance, Trips, Empty Vehicle Distance, Own Fleet Backload Percentage, Daily Own Fleet Utilisation, Revenue per Kilometre. A ninth section will give a breakdown of the percentage of trips by each Region.
Scope
This change will be applied to system version 10.5
Data
Standard setup of reports menu and associated parameters
FUNCTIONAL DESCRIPTION
A new Oracle report will be created called the ‘CN KPI Report’.
A new entry will be required in the table ‘REP_REPORT’ and the following parameters will be added to the table ‘REP_REPORT_PARAM’ to allow the report to be produced:
- ‘Date From’ – Dropdown list of valid Schedule Dates
- ‘Date To’ – Dropdown list of valid Schedule Dates
- ‘Owning Depot’ – LOV of Depots
- ‘Group Name’ – Select multiple parameters of Groups
The schedule dates and the owning depot will be mandatory parameters whereas the group name will be an optional parameter with the ability to list multiple groups via the ‘Select Multi Parameters’ screen.
The multiple parameters will be present for the user from the table ‘REP_REPORT_LIST’.
The report will be restricted to specified user groups.
The report will be produced in landscape format.
See Appendix A for inserting values.
All trips that have not been cancelled or deleted will be selected for the schedule date range and the owning depot specified, the data will also be selected based on the optional group name if specified.
The schedule date of the trip will be validated against the schedule date range specified, the owning depot of the trip will be validated against the owning depot specified; the group name of the order will be validated against the group name specified.
If a group name has been specified then details for an individual site may be obtained. If multiple group names have been specified then details for multiple sites, e.g. for a region, may be obtained. If a group name has not been specified then details may be obtained nationally.
The report will retrieve the following items for the trips and orders that match the selection criteria:
- ‘Lifts’ – RPE Total
- ‘Revenue’ – Revenue for Trip
- ‘Distance’ – Distance for Trip
- ‘Vehicle Fill’ – Vehicle Fill
- ‘Trips’ – Number of Trips
The items above will then be used to calculate totals for the items below:
- ‘Distance per RPE’
- ‘Vehicle Fill by Trip’
- ‘Subcontracted (%)’
- ‘Revenue per RPE’
- ‘Subbed Trip Costs’
- ‘Empty Vehicle Distance’
- ‘Own Fleet Backload (%)’
- ‘Daily Own Fleet Utilisation’
- ‘Revenue per Kilometre’
- ‘Regional Split’
All of the items displayed in the report will be split into 5 categories:
- ‘Own Fleet’ – Carrier is the home depot (i.e. owning depot)
- ‘Subbed to CN Sites’ – Carrier is in list of locations
- ‘Subbed to Non CN Sites’ – Carrier is DHL/EXEL but not in the list of locations
- ‘Subbed External’ – Non-DHL/EXEL carriers
- ‘Backload’ – Own fleet where ‘PK’ stop follows ‘DL’
The ‘Own Fleet’ will have a carrier that is the owning depot.
A ‘CN’ site will have a carrier code starting with ‘DHL’ or ‘EXEL’ and a carrier group name of ‘fleet’ and a hub location present, it will be for trip stops that are not of type ‘PK’ when it follows ‘DL’.
A ‘Non-CN’ site will have a carrier code starting with ‘DHL’ or ‘EXEL’ but will not have a carrier group name of ‘fleet’ and a hub location.
An ‘External’ site will not have a carrier code starting with ‘DHL’ or ‘EXEL’.
A ‘Backload’ will have a carrier code starting with ‘DHL’ or ‘EXEL’ and a carrier group name of ‘fleet’ and a hub location present, it will only be for a trip stop of type ‘PK’ when it follows ‘DL’.
The ‘Own Fleet’ and ‘Backload’ will thus provide a total for the ‘Own Fleet’ when combined.
Each item will be displayed for the above categories and also a total or an average (where required) will be shown for all the categories.
The data may be obtained as follows where ID refers to the ID in the spreadsheet provided of the proposed layout (note that numbers 49 to 52 were 31 to 34 on the original spreadsheet):
Section 1: Lifts
The total RPE quantity on departure of the trip stops (i.e. RPE_ON_DEPART of SCH_TRIP_STOP)
For example, the ‘RPE on Departure’ on the ‘Trip Detail’ page:
Section 2: Revenue
The revenue of the unique orders on the trips (i.e. ORD_REVENUE of SCH_ORD)
For example, the ‘Revenue’ on the ‘Finance’ page of the order:
Section 3: Subbed Trip Costs
The cost of the trips (i.e. TRIP_COST of SCH_TRIP)
For example, ‘Trip Cost’ on the ‘Finance’ page:
Section 4: Distance
The distance of the orders on the unique trip stops (i.e. DISTANCE_FROM_PRE_STOP of SCH_TRIP_STOP)
For example, the ‘Km from prev stop’ on the ‘Stops’ page
Section 5: Trips
A count of the unique trip IDs
Section 6: Empty Vehicle Distance
The distance when the vehicle is empty between unique trip stops, between consecutive stop types ‘SU’ and ‘PK’, ‘DL’ and ‘PK’ and ‘PK’ and ‘CL’ (i.e. DISTANCE_FROM_PRE_STOP of SCH_TRIP_STOP)
REFERENCES
DOCUMENT HISTORY
| Initial version | ||||
| Completion | ||||
| Change to quoted times to account for removal of regional group, selection of group name via a list of parameters, and extra complexity of the report for development | ||||
| Reviewed and Issued | ||||
| Amended definition of ‘Backload’ and ‘Subbed to CN Sites’ |
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
