Carrier Statistic Report

From UG

(Difference between revisions)
Jump to: navigation, search
m (MOT: FCL: correct misprints)
(History)
 
(30 intermediate revisions not shown)
Line 19: Line 19:
=== SD: Summary ===
=== SD: Summary ===
* This report is one of a family of statistics reports. It is related to [[Client Statistics Report]].
* 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. Main Menu item | Figure 0]].
+
* Carrier Statistics Report is available from the main menu on the next path: "Reports -> Statistical -> Carriers Statistic". See [[#Figure 0. Main Menu item | Figure 0]].
** Also it can be created from Save/Scheduled Reports under Report Class drop-down menu.  
** 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:
* Purpose of Carrier Statistics Report is to provide the ability to view statistic numbers for Carriers of:
Line 32: Line 32:
==== Totals ====
==== Totals ====
-
* Totals are per month based on Actual Collection Date:
+
* Totals are per month based on '''Actual Pick Up Date''':
-
:* [[Container | Actual Collection Date]] for MOT = {OCEAN - FCL, Trucking-Ocean}.
+
:* [[CT_bo#Actual_Export_Pick_Up_Date | Actual Export Pick-Up Date]] for MOT = TRUCKING.
-
:* [[CT_bo#Actual_Export_Pick_Up_Date | Actual Export Pick-Up Date]] for MOT = {OCEAN - LCL, AIR, Trucking-Domestic, Trucking-Air}.
+
:* [[CT_bo#Airport_Of_Departure_Actual_Date | Airport Of Departure Actual Date]] for MOT = AIR.
 +
:* [[CT_bo#Port_Of_Loading_Actual_Date | Port Of Loading Actual Date]] for MOT = OCEAN.
*This means that all totals of report should be calculated on the basis of dates from above.
*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.
+
:* ''In other words'', to add a some CT totals to the report for any month, Actual Pickup Date of CT should be in range of this month.
 +
* All weight totals displays in KG, CBM total (for MOT=LCL) displays in CBM.
 +
* Show Grand Total (sum for all ttl value) for each and all sections after the total line.
'''MOT = FCL'''
'''MOT = FCL'''
Line 44: Line 47:
** 40 hq (40 hq only)
** 40 hq (40 hq only)
** 45 ft (include all types)
** 45 ft (include all types)
-
** ''NOTE: 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.'' --[[User:Andrei|Andrei]] 06:51, 22 June 2010 (EDT)
+
* T.E.U ([[TEU | Twenty Equivalent Unit]]: 20 ft = 1 Equivalent, 40 ft/hq or 45 ft = 2 TEU)
-
* T.E.U ([[TEU | Twenty Equivalent Unit]]: 20 ft = 1 Equivalent)
+
** For automatic calculation of TEU should be used values from Container Sizes table: ''Admin - Transportation - Container Sizes''.
-
** NOTE: 1 40 ft/hq or 45 ft = 2 TEU
+
*** NOTE: ''(Not implemented yet, see mantis [http://mantis.jaguarfreight.com/mantis/view.php?id=2383 2383])''.
* Total of each column
* Total of each column
Line 72: Line 75:
=== SD: Filters ===
=== SD: Filters ===
-
* On the report template are filters to configure the report generation. See [[#Figure 1. Carrier Statistics Report Template  | Figure 1]].
+
* On the report template are [[Filters]] to configure the report generation. See [[#Figure 1. Carrier Statistics Report Template  | Figure 1]].
-
* List of filters:
+
==== List of filters ====
-
** '''Time Frame''' - located on the template as two pairs "Month & Year" dropdowns: From and To.  
+
* '''Actual Pickup Date''' - located on the template as two pairs "Month & Year" dropdowns: From and To.  
-
*** By default: "From" pair set to first month of current year, "To" pair set to current month of current year.
+
** 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"''.
-
** '''Transport Modes''' - located on the template as dropdown list, which include MOT = {Ocean LCL, Ocean FCL, Air, Truck}.
+
** <span style="color:#008000">By default:</span> all dropdowns are blank.
-
** '''Carriers''' - located on the template as dropdown list, which is changed dynamically depending on the selected mode:
+
* '''Transport Modes''' - located on the template as dropdown list, which include MOT = {OCEAN-FCL, OCEAN-LCL, AIR, TRUCKING}.
-
*** if Mode is Ocean, carriers dropdown list will be mapped to Admin > Carriers > Steamshiplines.
+
* '''Client Companies''' - located on the template as dropdown list, which mapped from Client Companies ''(Admin > Client Companies)'' to [[CT_bo#E0_Client]].
-
*** if Mode is Air, carriers dropdown list will be mapped to Admin > Carriers > Airlines.
+
** Mapping of "Client Companies" should include also visibility fields [[CT_bo#E1]], [[CT_bo#E2]], [[CT_bo#E3]].
-
*** if Mode is Truck, carriers dropdown list will be mapped to Admin > Addressbook > Vendors > Trucking Company (v3).
+
* '''Shippers''' - located on the template as client specific dropdown lists, which include only shippers that are associated with client companies.
-
** '''Origin/Destination Regions''' - located on the template as client specific dropdown lists, which include only countries that are associated with clients. Mapped to Admin->Geography->Regions.
+
** Mapped to [[CT_bo#Shipper]] (''Admin > Addressbook > Transportation > Shipper (T1)'').
-
** '''Origin/Destination Terminals''' - located on the template as client specific dropdown lists, which include only terminals that are associated with clients. Mapped to:
+
* '''Consignees''' - located on the template as client specific dropdown lists, which include only consignees that are associated with client companies.  
-
*** [[CT_bo#Origin_Terminal]] / [[CT_bo#Destination_Terminal]] for Ocean CTs.
+
** Mapped to [[CT_bo#Consignee]] (''Admin > Addressbook > Transportation > Consignee (T2)'').
-
*** [[CT_bo#Airport_Of_Departure]] / [[CT_bo#Airport_Of_Destination]] for Air CTs.
+
* '''Origin Countries''' (''Admin->Geography->Countries'') - located on the template as dropdown list, which mapped to:
-
*** address info derived from [[CT_bo#Shipper_Address]] / [[CT_bo#Consignee_Address]] for Truck CTs.
+
** country from [[CT_bo#Export Pick Up Address]] for MOT=TRUCKING.
 +
** country from [[CT_bo#Airport Of Departure]] for MOT=AIR.
 +
** country from [[CT_bo#Port Of Loading]] for MOT=OCEAN.
 +
* '''Destination Countries''' (''Admin->Geography->Countries'') - located on the template as dropdown list, which mapped to:
 +
** country from [[CT_bo#Export Delivery To Address]] for MOT=TRUCKING.
 +
** country from [[CT_bo#Airport Of Destination]] for MOT=AIR.
 +
** country from [[CT_bo#Port Of Discharge]] for MOT=OCEAN.
 +
* '''Origin Regions''' ''(Admin->Geography->Regions)'' - located on the template as dropdown list, which mapped to:
 +
** region of country from [[CT_bo#Export Pick Up Address]] for MOT=TRUCKING.
 +
** region of country from [[CT_bo#Airport Of Departure]] for MOT=AIR.
 +
** region of country from [[CT_bo#Port Of Loading]] for MOT=OCEAN.
 +
* '''Destination Regions''' (''Admin->Geography->Regions'') - located on the template as dropdown list, which mapped to:
 +
** region of country from [[CT_bo#Export Delivery To Address]] for MOT=TRUCKING.
 +
** region of country from [[CT_bo#Airport Of Destination]] for MOT=AIR.
 +
** region of country from [[CT_bo#Port Of Discharge]] for MOT=OCEAN.
 +
* '''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'').
 +
* '''Origin Ports/Terminals''' - located on the template as a dropdown list which becomes 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 a dropdown list which becomes visible when "Transport Modes" filter is blank or contains any OCEAN value.
 +
** Mapped to [[CT_bo#Destination_Terminal]] for Ocean CTs.
 +
* '''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'').
 +
* '''Origin Airports''' - located on the template as a dropdown list which becomes 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 a dropdown list which becomes visible when "Transport Modes" filter is blank or contains AIR value.
 +
** Mapped to [[CT_bo#Airport_Of_Destination]] for Air CTs.
 +
* '''Pick Up Truckers''' - 1st 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]] (''Admin > Addressbook > Vendors > Trucking Company (v3)'').
 +
* '''Delivery Truckers''' - 2nd Carriers dropdown list for CTs with MOT=TRUCKING.
 +
** Visible when "Transport Modes" filter is blank or contains TRUCKING value.
 +
** Mapped to [[CT_bo#Delivery Trucker]] (''Admin > Addressbook > Vendors > Trucking Company (v3)'').
 +
* '''Pickup Location Cities''' - located on the template as a dropdown list which becomes visible when "Transport Modes" filter is blank or contains TRUCKING value.
 +
** Mapped to name of City derived from [[CT_bo#Export Pick Up Address]] for Truck CTs.
 +
* '''Delivery Location Cities''' - located on the template as a dropdown list which becomes visible when "Transport Modes" filter is blank or contains TRUCKING value.
 +
** Mapped to name of City derived from [[CT_bo#Export Delivery To Address]] for Truck CTs.
 +
 
 +
==== Output Tab ====
 +
* This tab contains text line "NOTE: Report only includes 20, 40, 45 ft containers". See on [[#Figure 2. Carrier Statistics Report Output Tab | Figure 2]].
 +
 
 +
==== Action Controls ====
 +
* ''Reset Form '' button:
 +
: to clear form
 +
* ''Generate Result'' button:
 +
: to generate report
 +
* These buttons located both at the top and bottom of template.
 +
 
 +
==== Notes ====
* Each filter has its own checkbox to the left of the name.  
* 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 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.
** If filter checkbox is not checked, that means filter is not set and does not affect the report.
-
* All filters are "multi select", exclude "Time Frame".  
+
* All filters are [http://mantis.jaguarfreight.com/wiki/Reports_Filters#Multiselect 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 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 all values of some filter are cleared, then checkbox of this filter should be set to "unchecked" state automatically.
Line 100: Line 153:
* Output spreadsheet contains information divided by tabs, each of which corresponds to one selected MOT from the "Transport Modes" filter on template.  
* 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:
* Spreadsheet consists of Header and Body:
-
** Header: Report name, Date time stamp, Criteria(list of filters selected), Jaguar Logo.
+
** Header: Report name, Date time stamp, Criteria(list of filters selected), Transport Mode, Jaguar Logo.
** Body(general info):
** Body(general info):
-
*** consists of sections: separately by from country to country
+
*** consists of sections: separately by each Carrier
-
*** each section label from country -> country
+
*** each section top label Carrier Name (Steamshipline for of MOT=Ocean, Airline for MOT=Air, Company-City-Remark form for MOT=Truck)
-
*** in case there is sub-section, list subsection such as Airline,...etc
+
**** each Carrier section consists of sub-sections: separately by from country to country
 +
***** each sub-section consists set of lines that reflect specific summary info for each pair of "Origin/Destination Terminal" per each month from Actual Pick Up filter
 +
**** each sub-section finishes with sub-total line that contains the total values for each columns that summed in this sub-section.
 +
*** each Carrier section finishes with grand total line that contains the total values for each columns that summed in this section by sub-totals.
*** many sections should be separated by one row
*** 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
+
* '''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-"'''.
-
***:''as example of sub-section may be considered grouping by Airline in case of MOT=Air (or Steamshipline in case of MOT=Ocean).''
+
** These fields are (mapping in [[#SD: Filters |Filters]]):
-
*** each (sub-)section finishes with (sub-)total line that contains the total values for each columns that summed in this (sub-)section.
+
**: # Origin Countries.
-
 
+
**: # Destination Countries.
 +
**: # Origin Regions.
 +
**: # Destination Regions.
 +
**: # Steamshiplines.
 +
**: # Origin Ports/Terminals.
 +
**: # Destination Ports/Terminals.
 +
**: # Airlines.
 +
**: # Origin Airports.
 +
**: # Destination Airports.
 +
**: # Pick Up Truckers.
 +
**: # Delivery Truckers.
 +
**: # Pickup Location Cities.
 +
**: # Delivery Location Cities.
 +
** There is '''special logic''' for using of ''Pick Up / Delivery Truckers'':
 +
*** If both ''Truckers'' are empty
 +
***: Then list date under ''-Undefined-'' carrier
 +
*** If ''Pick Up Trucker'' = company A and ''Delivery Trucker'' = blank
 +
***: Then list under company A and show Destination Terminal cell blank
 +
*** If ''Pick Up Trucker'' = blank and ''Delivery Trucker'' = company A
 +
***: Then list under company A and show Origin Terminal cell blank
 +
*** If ''Pick Up Trucker'' = company A and ''Delivery Trucker'' = company B
 +
***: Then list under company A and company B and show both Terminals
==== SD: Columns Definitions ====
==== SD: Columns Definitions ====
* For every MOT there is a set of columns that should be shown.
* 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.
+
* Summary monthly totals shows for a month specified in the header of ''Month'' section.
* Examples of layouts for the report output shown here:
* Examples of layouts for the report output shown here:
Line 118: Line 195:
:''Origin Terminal'' - location that shipment is moved from (port)
:''Origin Terminal'' - location that shipment is moved from (port)
:''Destination Terminal'' - location that shipment is moved to (port)
:''Destination Terminal'' - location that shipment is moved to (port)
-
:'''''Steamshipline''''' sub-section (make it ''Italic'' & <span style="color:Blue">''blue''</span>) - 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:
:''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).
:*''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'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)
+
:*''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)
+
:*''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")
+
:*''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"
+
:'''Total line''' with label (country -> country) - sum up result per column per "location country from" to "location country to".
'''Example 1. Mode FCL'''
'''Example 1. Mode FCL'''
-
:[[File:CarrierFCLStats.JPG|FCL]]
+
 
 +
[[File:CarrierFCLStats.JPG|FCL]]
===== MOT: LCL =====
===== MOT: LCL =====
:''Origin Terminal'' - location that shipment is moved from (port)
:''Origin Terminal'' - location that shipment is moved from (port)
:''Destination Terminal'' - location that shipment is moved to (port)
:''Destination Terminal'' - location that shipment is moved to (port)
-
:'''''Steamshipline''''' sub-section (make it ''Italic'' & <span style="color:Blue">''blue''</span>) - 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:
:''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().
:*''CT'' - summary shipments count().
-
:*''Gross weight'' - summary gross weight count().
+
:*''Gross Weight'' - summary Gross Weight count().
-
:*''cbm'' - summary cbm count().
+
:*''CBM'' - summary CBM count().
-
:*''Avg weight'' - summary calculation. Total of gross weight/number of CT shipments 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.
:'''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'''
'''Example 2. Mode LCL'''
-
:[[File:CarrierLCLStats.JPG|LCL]]
 
 +
[[File:CarrierLCLStats.JPG|LCL]]
===== MOT: AIR =====
===== MOT: AIR =====
-
:''Origin Terminal'' - location that shipment is moved from (port)
+
:''Origin Terminal'' - location that shipment is moved from (airport).
-
:''Destination Terminal'' - location that shipment is moved to (port)
+
:''Destination Terminal'' - location that shipment is moved to (airport).
-
:'''''Airline''''' sub-section (make it ''Italic'' & <span style="color:Blue">''blue''</span>) - Airlines 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:
:''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()  
+
:*''CT'' - summary shipments count().
-
:*''Chg weight'' - summary chargeable weight count()
+
:*''Chg Weight'' - summary Chargeable Weight count().
-
:*''Avg weight'' - summary calculation: Total of chargeable weight/number of CT shipments 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.
-
:'''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'''
'''Example 3. Mode Air'''
-
:[[File:CarrierAirStats.JPG|Air]]
 
 +
[[File:CarrierAirStats.JPG|Air]]
===== MOT: TRUCK =====
===== MOT: TRUCK =====
-
:''Origin Terminal'' - location that shipment is moved from (port)
+
:''Origin Terminal'' - location that shipment is moved from (terminal).
-
:''Destination Terminal'' - location that shipment is moved to (port)
+
:''Destination Terminal'' - location that shipment is moved to (terminal).
:''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:
:''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()  
+
:*''CT'' - summary shipments count().
-
:*''Gross weight'' - summary gross weight count()
+
:*''Gross Weight'' - summary Gross Weight count().
-
:*''Avg weight'' - summary calculation: Total of gross weight/number of CT shipments 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
+
:'''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'''
'''Example 4. Mode Truck'''
-
:[[File:CarrierTruck.JPG|Truck]]
 
 +
[[File:CarrierTruck.JPG|Truck]]
=== SD: Figures ===
=== SD: Figures ===
Line 175: Line 252:
==== Figure 1. Carrier Statistics Report Template ====
==== Figure 1. Carrier Statistics Report Template ====
[[File:CarrierStatisticsReportTemplate.gif]]
[[File:CarrierStatisticsReportTemplate.gif]]
 +
==== Figure 2. Carrier Statistics Report Output Tab ====
 +
[[File:CarrierStatisticsReportOutputTab.gif]]
</div>
</div>
Line 197: Line 276:
==== SA 1573 ====
==== SA 1573 ====
* See [[#SD: Systems Design]]
* See [[#SD: Systems Design]]
 +
 +
=== 0002447: (*ph1)(Carrier Statistics) Tweak: show Grand Total(sum for all ttl value) for each and all sections after the total line ===

Current revision as of 20:21, 14 December 2010


Contents

[edit] Parent Mantis

1599

[edit] 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

[edit] SD: Systems Design

This wiki section is managed by: Systems Analyst

[edit] 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 -> Carriers Statistic". 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?

[edit] 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.

[edit] Totals

  • Totals are per month based on Actual Pick Up Date:
  • 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 Pickup Date of CT should be in range of this month.
  • All weight totals displays in KG, CBM total (for MOT=LCL) displays in CBM.
  • Show Grand Total (sum for all ttl value) for each and all sections after the total line.

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, 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

[edit] SD: Filters

  • On the report template are Filters to configure the report generation. See Figure 1.

[edit] 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: all dropdowns are blank.
  • Transport Modes - located on the template as dropdown list, which include MOT = {OCEAN-FCL, OCEAN-LCL, AIR, TRUCKING}.
  • Client Companies - located on the template as dropdown list, which mapped from Client Companies (Admin > Client Companies) to CT_bo#E0_Client.
  • Shippers - located on the template as client specific dropdown lists, which include only shippers that are associated with client companies.
    • Mapped to CT_bo#Shipper (Admin > Addressbook > Transportation > Shipper (T1)).
  • Consignees - located on the template as client specific dropdown lists, which include only consignees that are associated with client companies.
    • Mapped to CT_bo#Consignee (Admin > Addressbook > Transportation > Consignee (T2)).
  • Origin Countries (Admin->Geography->Countries) - located on the template as dropdown list, which mapped to:
  • Destination Countries (Admin->Geography->Countries) - located on the template as dropdown list, which mapped to:
  • Origin Regions (Admin->Geography->Regions) - located on the template as dropdown list, which mapped to:
  • Destination Regions (Admin->Geography->Regions) - located on the template as dropdown list, which mapped to:
  • 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).
  • Origin Ports/Terminals - located on the template as a dropdown list which becomes visible when "Transport Modes" filter is blank or contains any OCEAN value.
  • Destination Ports/Terminals - located on the template as a dropdown list which becomes visible when "Transport Modes" filter is blank or contains any OCEAN value.
  • 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).
  • Origin Airports - located on the template as a dropdown list which becomes visible when "Transport Modes" filter is blank or contains AIR value.
  • Destination Airports - located on the template as a dropdown list which becomes visible when "Transport Modes" filter is blank or contains AIR value.
  • Pick Up Truckers - 1st 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 (Admin > Addressbook > Vendors > Trucking Company (v3)).
  • Delivery Truckers - 2nd Carriers dropdown list for CTs with MOT=TRUCKING.
    • Visible when "Transport Modes" filter is blank or contains TRUCKING value.
    • Mapped to CT_bo#Delivery Trucker (Admin > Addressbook > Vendors > Trucking Company (v3)).
  • Pickup Location Cities - located on the template as a dropdown list which becomes visible when "Transport Modes" filter is blank or contains TRUCKING value.
  • Delivery Location Cities - located on the template as a dropdown list which becomes visible when "Transport Modes" filter is blank or contains TRUCKING value.

[edit] Output Tab

  • This tab contains text line "NOTE: Report only includes 20, 40, 45 ft containers". See on Figure 2.

[edit] Action Controls

  • Reset Form button:
to clear form
  • Generate Result button:
to generate report
  • These buttons located both at the top and bottom of template.

[edit] 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)

[edit] 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), Transport Mode, Jaguar Logo.
    • Body(general info):
      • consists of sections: separately by each Carrier
      • each section top label Carrier Name (Steamshipline for of MOT=Ocean, Airline for MOT=Air, Company-City-Remark form for MOT=Truck)
        • each Carrier section consists of sub-sections: separately by from country to country
          • each sub-section consists set of lines that reflect specific summary info for each pair of "Origin/Destination Terminal" per each month from Actual Pick Up filter
        • each sub-section finishes with sub-total line that contains the total values for each columns that summed in this sub-section.
      • each Carrier section finishes with grand total line that contains the total values for each columns that summed in this section by sub-totals.
      • many sections should be separated by one row
  • 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):
      # Origin Countries.
      # Destination Countries.
      # Origin Regions.
      # Destination Regions.
      # Steamshiplines.
      # Origin Ports/Terminals.
      # Destination Ports/Terminals.
      # Airlines.
      # Origin Airports.
      # Destination Airports.
      # Pick Up Truckers.
      # Delivery Truckers.
      # Pickup Location Cities.
      # Delivery Location Cities.
    • There is special logic for using of Pick Up / Delivery Truckers:
      • If both Truckers are empty
        Then list date under -Undefined- carrier
      • If Pick Up Trucker = company A and Delivery Trucker = blank
        Then list under company A and show Destination Terminal cell blank
      • If Pick Up Trucker = blank and Delivery Trucker = company A
        Then list under company A and show Origin Terminal cell blank
      • If Pick Up Trucker = company A and Delivery Trucker = company B
        Then list under company A and company B and show both Terminals

[edit] SD: Columns Definitions

  • For every MOT there is a set of columns that should be shown.
  • Summary monthly totals shows for a month specified in the header of Month section.
  • Examples of layouts for the report output shown here:
[edit] MOT: FCL
Origin Terminal - location that shipment is moved from (port)
Destination Terminal - location that shipment is moved to (port)
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

FCL

[edit] MOT: LCL
Origin Terminal - location that shipment is moved from (port)
Destination Terminal - location that shipment is moved to (port)
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

LCL

[edit] MOT: AIR
Origin Terminal - location that shipment is moved from (airport).
Destination Terminal - location that shipment is moved to (airport).
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().
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

Air

[edit] MOT: TRUCK
Origin Terminal - location that shipment is moved from (terminal).
Destination Terminal - location that shipment is moved to (terminal).
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

Truck

[edit] SD: Figures

[edit] Figure 0. Main Menu item

File:Mainmenu_statreports_carrier.gif

[edit] Figure 1. Carrier Statistics Report Template

File:CarrierStatisticsReportTemplate.gif

[edit] Figure 2. Carrier Statistics Report Output Tab

File:CarrierStatisticsReportOutputTab.gif

[edit] History

[edit] m1573

last revision with BR - http://mantis.jaguarfreight.com/mediawiki-1.15.0/index.php?title=Carrier_Statistic_Report&oldid=11494

last revision - http://mantis.jaguarfreight.com/mediawiki-1.15.0/index.php?title=Carrier_Statistic_Report&oldid=12755

[edit] BA 1573

See #BR: Business Requirements section

[edit] 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.

[edit] SA 1573

[edit] 0002447: (*ph1)(Carrier Statistics) Tweak: show Grand Total(sum for all ttl value) for each and all sections after the total line

Personal tools