Carrier Statistic Report

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)
  • NOTE1: The list of combinations of types and sizes of containers for the output of the report is basic. To add or change these values need a special editor of columns of the report. Location of this editor on the template of the report would be more comfortable and flexible than in other place of the system. --Andrei 06:51, 22 June 2010 (EDT)
  • NOTE2: Set of columns to output of FCL-information is defined on the Output Tab of the template. --Andrei 13:22, 24 September 2010 (EDT)
• T.E.U ( Twenty Equivalent Unit: 20 ft = 1 Equivalent, 40 ft/hq or 45 ft = 2 TEU)
  • For automatic calculation of TEU should be used values from Container Sizes table: Admin - Transportation - Container Sizes.
    • NOTE: (Not implemented yet, see mantis 2383).
• 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 or [ZUL-prototype].

List of filters

• Actual Pickup Date - located on the template as two pairs "Month & Year" dropdowns: From and To.
  • This filter is required to be filled on the all fields. If even one field is blank then system shows warning message "You have not set Actual Pickup Date filter".
  • By default: "From" pair set to first month of current year, "To" pair set to current month of current year.
• Transport Modes - located on the template as dropdown list, which include MOT = {OCEAN-FCL, OCEAN-LCL, AIR, TRUCKING}.
• Steamshiplines - Carriers dropdown list for CTs with MOT=OCEAN.
  • Visible when "Transport Modes" filter is blank or contains any OCEAN value.
  • Mapped to CT_bo#Steamshipline (Admin > Carriers > Steamshiplines).
• Airlines - Carriers dropdown list for CTs with MOT=AIR.
  • Visible when "Transport Modes" filter is blank or contains AIR value.
  • Mapped to CT_bo#Airline (Admin > Carriers > Airlines).
• Delivery Truckers - Carriers dropdown list for CTs with MOT=TRUCKING.
  • Visible when "Transport Modes" filter is blank or contains TRUCKING value.
  • Mapped to CT_bo#Export_Pick_Up_Trucker and CT_bo#Delivery_Trucker (Admin > Addressbook > Vendors > Trucking Company (v3)).
    • Only non-empty values of these CT fields should be taken for report.
• Origin Regions - located on the template as client specific dropdown lists, which include only regions that are associated with clients.
  • Mapped to Admin->Geography->Regions.
• Destination Regions - located on the template as client specific dropdown lists, which include only regions that are associated with clients.
  • Mapped to Admin->Geography->Regions.
• Origin Ports/Terminals - located on the template as client specific dropdown lists, which include only terminals that are associated with clients.
  • Visible when "Transport Modes" filter is blank or contains any OCEAN value.
  • Mapped to CT_bo#Origin_Terminal for Ocean CTs.
• Destination Ports/Terminals - located on the template as client specific dropdown lists, which include only terminals that are associated with clients.
  • Visible when "Transport Modes" filter is blank or contains any OCEAN value.
  • Mapped to CT_bo#Destination_Terminal for Ocean CTs.
• Origin Airports - located on the template as client specific dropdown lists, which include only terminals that are associated with clients.
  • Visible when "Transport Modes" filter is blank or contains AIR value.
  • Mapped to CT_bo#Airport_Of_Departure for Air CTs.
• Destination Airports - located on the template as client specific dropdown lists, which include only terminals that are associated with clients.
  • Visible when "Transport Modes" filter is blank or contains AIR value.
  • Mapped to CT_bo#Airport_Of_Destination for Air CTs.
• Origin Terminals - located on the template as client specific dropdown lists, which include only terminals that are associated with clients.
  • Visible when "Transport Modes" filter is blank or contains TRUCKING value.
  • Mapped to address info derived from CT_bo#Shipper_Address for Truck CTs.
• Destination Terminals - located on the template as client specific dropdown lists, which include only terminals that are associated with clients.
  • Visible when "Transport Modes" filter is blank or contains TRUCKING value.
  • Mapped to address info derived from CT_bo#Consignee_Address for Truck CTs.
• No Archived.
  • Checkbox that defines the presence of the "archived" CTs in the result of the Report.

Output Tab

• This tab shows the set of columns to output of FCL-information.
• Each column corresponds to the checkbox with label which describes a type of container. See on Figure 2.
• Checkbox with "etc." label is used to include in the report of containers, types of which are not described in other checkboxes. By default it is unchecked.

Action Controls

• Reset Form button:

to clear form

• Generate Result button:

to generate report

Notes

• Each filter has its own checkbox to the left of the name.
  • If filter checkbox is checked, that means filter is set and affects the reports.
  • If filter checkbox is not checked, that means filter is not set and does not affect the report.
• All filters are "multi select", exclude "Actual Pickup Date" and "No Archived".
  • If a value of some filter is selected, then checkbox of this filter should be set to "checked" state automatically.
  • If all values of some filter are cleared, then checkbox of this filter should be set to "unchecked" state automatically.
• If no filter value is selected, that means "all". For example, if Mode filter is blank (and checked!), then report will include info by all its modes.
• 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)

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.
• Spreadsheet consists of Header and Body:
  • 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
    • many sections should be separated by one row
    • each (sub-)section consists set of lines that reflect specific summary info for each pair of "Origin/Destination Terminal" per each month from Time Frame filter

      as example of sub-section may be considered grouping by Airline in case of MOT=Air (Steamshipline for of MOT=Ocean, Trucker for MOT=Truck).

    • each (sub-)section finishes with (sub-)total line that contains the total values for each columns that summed in this (sub-)section.
• NOTE. If some Carrier field or "geografic" field does not filled (is blank) in CT that included in the report then it will be reflected in the report as "-Undefined-".
  • These fields are (mapping in Filters):

    # Steamshiplines.

    # Airlines.

    # Delivery Truckers.

    # Origin Regions.

    # Destination Regions.

    # Origin Ports/Terminals.

    # Destination Ports/Terminals.

    # Origin Airports.

    # Destination Airports.

    # Origin Terminals.

    # Destination Terminals.

SD: Columns Definitions

• For every MOT there is a set of columns that should be shown.
• Summary monthly totals shows for a specified month in the header of Month section.
• Examples of layouts for the report output shown here:

MOT: FCL

Origin Terminal - location that shipment is moved from (port)

Destination Terminal - location that shipment is moved to (port)

Steamshipline sub-section (make it Italic & blue) - Steamshiplines that moves shipment from one location to the other location within from (port) to (port) country

Month section - Report by monthly (Jan - Dec); depend on Month filter is selected. Header of section as label in format "MMM-yy", which includes the following columns:

• 20'ft - the total number of containers for that particular size and types: 20 ft (include all types).
• 40'ft - the total number of containers for that particular size and types: 40 ft (include all types except HQ).
• 40'hq - the total number of containers for that particular size and types: 40 hq (40 HQ only).
• 45'ft - the total number of containers for that particular size and types: 45 ft (include all types).
• T.E.U - summary calculation of TEU for all container size and types of current line (for "location country from" to "location country to").

Total line with label (country -> country) - sum up result per column per "location country from" to "location country to".

Example 1. Mode FCL

MOT: LCL

Origin Terminal - location that shipment is moved from (port)

Destination Terminal - location that shipment is moved to (port)

Steamshipline sub-section (make it Italic & blue) - Steamshiplines that moves shipment from one location to the other location within from (port) to (port) country

Month section - Report by monthly (Jan - Dec); depend on Month filter is selected. Header of section as label in format "MMM-yy", which includes the following columns:

• 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 line with label (country -> country) - sum up result per column per location country from to location country to.

• Avg weight in the total line should be calculated as usual by formula (not summing!): Total gross weight / total number of CTs.

Example 2. Mode LCL

MOT: AIR

Origin Terminal - location that shipment is moved from (airport).

Destination Terminal - location that shipment is moved to (airport).

Airline sub-section (make it Italic & blue) - Airlines that moves shipment from one location to the other location within from (airport) to (airport) country.

Month section - Report by monthly (Jan - Dec); depend on Month filter is selected. Header of section as label in format "MMM-yy", which includes the following columns:

• CT - summary shipments count().
• Chg weight - summary chargeable weight count().
• Avg weight - summary calculation: Total of chargeable weight/number of CT shipments count().

Sub-Total line (make it Italic) - sum up result per column for each airline.

Total line with label (country -> country) - sum up result per column per location country from to location country to.

• Avg weight in the total line should be calculated as usual by formula (not summing!): Total chargeable weight / total number of CTs.

Example 3. Mode Air

MOT: TRUCK

Origin Terminal - location that shipment is moved from (terminal).

Destination Terminal - location that shipment is moved to (terminal).

Trucker sub-section (make it Italic & blue) - Truckers (Pickup and Delivery) that moves shipment from one location to the other location within from (port) to (port) country

Month section - Report by monthly (Jan - Dec); depend on Month filter is selected. Header of section as label in format "MMM-yy", which includes the following columns:

• CT - summary shipments count().
• Gross weight - summary gross weight count().
• Avg weight - summary calculation: Total of gross weight/number of CT shipments count().

Total line - sum up result per column per location country from to location country to.

• Avg weight in the total line should be calculated as usual by formula (not summing!): Total gross weight / total number of CTs.

Example 4. Mode Truck

SD: Figures

Figure 0. Main Menu item

Figure 1. Carrier Statistics Report Template

Figure 2. Carrier Statistics Report Output Tab