Carrier Statistic Report
From UG
Contents |
Parent Mantis
CBN: Core Business Need
This report should help in defining the amount of shipments handled specific Carrier (Vendor) .
This report is related to Client Statistics Report
BR: Business Requirements
This report is a statistic report that user can generate in order to view statistic numbers of:
- FCL: How many containers with different types and sizes with T.E.U. per month from each origin terminal to each destination terminal?
- LCL: How many CT shipments and how much total of gross weight and total in cbm per month for each LCL shipment from each origin terminal to each destination terminal each month?
- Air: How many CT shipments and how much chargeable weight and average weight per month for each airline from origin terminal to destination terminal?
- Truck: How many CT shipments and how much total Gross weight per month for each trucking company from origin terminal to destination terminal?
This report allows users to create carrier statistics reports. It can be created from Report Home under Statistical Reports OR from Save/Scheduled Reports under Report Class drop-down menu.
User Interface
- Statistical Reports Menu
Carrier Statistic Report
1. Tabs
- General Filters Tab -> see figure under User Defined Conditions
- Output Tab -> dynamically list of headings depend on MODE. See figure below and detail under Output
Hard Coded Conditions
- Total per month consider Actual Collection Date
- Per carrier company
- Per Mode in case of many modes selected (when Mode filter is blank; consider all modes)
MOT = FCL
- Total containers with each combination between types and sizes listed below
- 20 ft (include all types)
- 40 ft (include all types except HQ)
- 40 hq (40 hq only)
- 45 ft (include all types)
- T.E.U (Twenty Equivalent Unit: 20 ft = 1 Equivalent)
- NOTE: 1 40 ft/hq or 45 ft = 2 T.E.U
- Total of each column
- Mapped to ContTab.size and ContTab.Type (15 cases to fit in 4 categories)
MOT = LCL
- Total CT shipments
- Total Gross weight
- Total in cbm
- Average weight
- Total of weight/number of CT shipments
- Total of each column
- Mapped to ContTab.Table A. GrandTotal.Ttl G kg (2 decimal places)
- Mapped to ContTab.Table B. Total (in cbm)
MOT = AIR
- Total CT shipments
- Total chargeable weight
- Average weight
- Total of weight/number of CT shipments
- Total of each column
- Mapped to ExpTab.17b: Chargeable weight
MOT = Truck
- Total CT shipments
- Total Gross weight
- Average weight
- Total of weight/number of CT shipments
- Total of each column
- Mapped to ContTab.Table A. GrandTotal.Ttl G kg
User Defined Conditions
- See UI for General Filters tab below
- Most filters are "multi select"
- Check box in front of each filter. If filter is set, check box MUST be automatically checked vise versa.
- Filters consists of several groups
- Every filter is connected by AND and sub selections inside each filter combo-box are connected by OR
- For example: (Year to Report = 2009) AND (Mode = Ocean) AND (Carrier = APL OR Carrier = COSCO)
Note:If a filter is not set, that means "all". For ex, if Mode is not selected, that means Mode = all modes
Output
Figure 1. Mode FCL
Figure 2. Mode LCL
Figure 3. Mode Air
Figure 4. Mode Truck
Summary
- Output is a spreadsheet
- Spreadsheet tabs: there is one tab per mode
- Spreadsheet consists of Header, Body, and footer
- Header:
- Report name
- Date time stamp
- Criteria(list of filters selected)
- Jaguar Logo
- Body:(general info)
- consists of sections: separately by from country to country
- Each section label from country -> country
- In case there is sub-section, list subsection such as Airline,...etc
- Figure 1: refer an example of one section
- Many sections should be separated by one row
- Table consists of columns. See #Column Definitions below
- Footer:
- There is no footer
Columns Definitions
MOT: FCL
Origin Terminal
- location that shipment is moved from (port)
Destination Terminal
- location that shipment is moved to (port)
Month
- Report by monthly (Jan - Dec); depend on Month filter is selected
20'ft
- summary count() for that particular size and types: See #Hard Coded Conditions
40'ft
- summary count() for that particular size and types: See #Hard Coded Conditions
40'hq
- summary count() for that particular size and types: See #Hard Coded Conditions
T.E.U
- summary calculation for that particular size and types: See #Hard Coded Conditions
Total with label (country -> country)
- sum up result per column per "location country from" to "location country to"
MOT: LCL
Origin Terminal
- location that shipment is moved from (port)
Destination Terminal
- location that shipment is moved to (port)
Month
- Report by monthly (Jan - Dec); depend on Month filter is selected
CT
- summary shipments count()
Gross weight
- summary gross weight count()
cbm
- summary cbm count()
Avg weight
- summary calculation: Total of gross weight/number of CT shipments count()
Total with label (country -> country)
- sum up result per column per location country from to location country to
MOT: AIR
Origin Terminal
- location that shipment is moved from (port)
Destination Terminal
- location that shipment is moved to (port)
Month
- Report by monthly (Jan - Dec); depend on Month filter is selected
Airline (make it Italic)
- Airlines that moves shipment from one location to the other location within from (port) to (port) country
CT
- summary shipments count()
Chg weight
- summary chargeable weight count()
Avg weight
- summary calculation: Total of chargeable weight/number of CT shipments count()
Total (make it Italic& blue)
- sum up result per column for each airline
Total with label (country -> country)
- sum up result per column per location country from to location country to
MOT: TRUCK
Origin Terminal
- location that shipment is moved from (port)
Destination Terminal
- location that shipment is moved to (port)
Month
- Report by monthly (Jan - Dec); depend on Month filter is selected
CT
- summary shipments count()
Gross weight
- summary gross weight count()
Avg weight
- summary calculation: Total of gross weight/number of CT shipments count()
Total
- sum up result per column per location country from to location country to
SD: Systems Design
This wiki section is managed by: Systems Analyst
SD: Summary
- This report is one of a family of statistics reports. It is related to Client Statistics Report.
- Carrier Statistics Report is available from the main menu on the next path: "Reports -> Statistical -> Carrier Statistics". See Figure 0.
- Also it can be created from Save/Scheduled Reports under Report Class drop-down menu.
- Purpose of Carrier Statistics Report is to provide the ability to view statistic numbers for Carriers of:
- FCL: How many containers with different types and sizes with T.E.U. per month from each origin terminal to each destination terminal?
- LCL: How many CT shipments and how much total of gross weight and total in cbm per month for each LCL shipment from each origin terminal to each destination terminal each month?
- Air: How many CT shipments and how much chargeable weight and average weight per month for each airline from origin terminal to destination terminal?
- Truck: How many CT shipments and how much total Gross weight per month for each trucking company from origin terminal to destination terminal?
SD: Details Per Mode
- Report should be contain monthly information for one or several Carriers for a specified period.
- Information that should be reflected in the report is grouped by MOT( Mode Of Transport) and shows shipment totals from each Origin Terminal to each Destination Terminal, which in turn are grouped and summed by Country.
Totals
- Totals are per month based on Actual Collection Date:
- Actual Collection Date for MOT = {OCEAN - FCL, Trucking-Ocean}.
- Actual Export Pick-Up Date for MOT = {OCEAN - LCL, AIR, Trucking-Domestic, Trucking-Air}.
- This means that all totals of report should be calculated on the basis of dates from above.
- In other words, to add a some CT totals to the report for any month, Actual Collection Date of CT should be in range of this month.
MOT = FCL
- Total containers with each combination between types and sizes listed below. Mapped to ContTab.Size and ContTab.Type (15 cases to fit in 4 categories):
- 20 ft (include all types)
- 40 ft (include all types except HQ)
- 40 hq (40 hq only)
- 45 ft (include all types)
- T.E.U ( Twenty Equivalent Unit: 20 ft = 1 Equivalent)
- NOTE: 1 40 ft/hq or 45 ft = 2 TEU
- Total of each column
MOT = LCL
- Total CT shipments
- Total Gross weight. Mapped to ContTab.Table A. GrandTotal.Ttl GW kg (2 decimal places).
- Total in cbm. Mapped to ContTab.Table B. Total (in cbm).
- Average weight. Calculated by formula: Total Gross weight / Total CT shipments.
- Total of each column
MOT = AIR
NOTE: for this Transport Mode there is also grouping by Airlines
- Total CT shipments
- Total chargeable weight. Mapped to ExpTab.17b: Chargeable weight.
- Average weight. Calculated by formula: Total chargeable weight / Total CT shipments.
- Total of each column
MOT = Truck
- Total CT shipments
- Total Gross weight. Mapped to ContTab.Table A. GrandTotal.Ttl GW kg.
- Average weight. Calculated by formula: Total Gross weight / Total CT shipments.
- Total of each column
SD: Filters
- On the report template are filters to configure the report generation. See Figure 1.
- Most filters are "multi select", exclude "Year to report" and "Month From ... To ...".
- A detailed description of these fields, see #User Defined Conditions.
- Note 1: Origin/Destination Regions dropdown lists mapped to Admin->Geography->Regions.
- Note 2: Origin/Destination Terminals dropdown lists mapped to:
- CT_bo#Origin_Terminal / CT_bo#Destination_Terminal for Ocean CTs.
- CT_bo#Airport_Of_Departure / CT_bo#Airport_Of_Destination for Air CTs.
- address info derived from CT_bo#Shipper_Address / CT_bo#Consignee_Address for Truck CTs.
- Every filter is connected by AND and sub selections inside each filter combo-box are connected by OR.
- For example: (Year to Report = 2009) AND (Mode = Ocean OR Mode = Air) AND (Carrier = APL OR Carrier = COSCO)
- If a filter is not set, that means "all". For example, if Mode filter is blank, then report will include all modes info.
SD: Output
- Output form of this report is carried out only in spreadsheet (HTML form is not required).
- Output spreadsheet contains information divided by tabs, each of which corresponds to one selected MOT from the "Transport Modes" filter on template.
- For details, see #Summary.
- For every MOT there is a set of columns that should be shown. See #Columns Definitions.
- Examples of layouts for the report output shown in #Output:
SD: Figures
Figure 0. Main Menu item
Figure 1. Carrier Statistics Report Template
History
m1573
last revision - http://mantis.jaguarfreight.com/mediawiki-1.15.0/index.php?title=Carrier_Statistic_Report&oldid=10849
BA 1573
See #BR: Business Requirements section
Note from Sys Architect 1573
- There is one misleading moment here: #BR: Business Requirements are very detailed but can not be considered as Systems design - there are many questions remaining.
Design that developer must follow is defined as usual in #SD: Systems Design.