Client Statistics Report

From UG

(Difference between revisions)
Jump to: navigation, search
(corrected links to wiki and mantis)
 
(67 intermediate revisions not shown)
Line 3: Line 3:
== Info ==
== Info ==
-
[http://mantis.jaguarfreight.com/mantis/view.php?id=1598 1598]
+
[http://ct.jaguarfreight.com/mantis/view.php?id=1598 1598]
Line 23: Line 23:
</div>
</div>
-
== BR: Business Requirements and Design Ideas ==
+
== Requirements ==
-
''This wiki section is managed by: [[Business Analyst]]''
+
=== Client version of this report ===
 +
==== Requirements ====
 +
* The client application report is virtually identical to the internal client statistic report.  In that it should still answer the same three basic questions, have the same filters & output.
-
<div style="background-color:Moccasin;">
+
* Only difference is that the client company should be according to the [[Client_visibility_rule]].
-
=== BR: Summary ===
+
* It would be accessible on the client application under Cybertrax >> Reports.
-
 
+
-
For specific Client Company (or group) this report should help to answer these basic questions:
+
-
 
+
-
* '''FCL''':
+
-
** How many containers with different types and sizes with [[TEU]] per month from each origin terminal to each destination terminal?
+
-
 
+
-
* '''LCL''':
+
-
** How many CT shipments
+
-
** How much total of gross weight
+
-
** Total in cbm per month for each LCL shipment from each origin terminal to each destination terminal each month
+
-
 
+
-
* '''Air''':
+
-
** How many CT shipments
+
-
** 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?
+
-
 
+
-
=== BR: Details Per Mode ===
+
-
 
+
-
* Totals are per month based on Actual Collection Date
+
-
 
+
-
'''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
+
-
 
+
-
 
+
-
=== BR: Output ===
+
-
*  spreadsheet only
+
-
* Spreadsheet tabs:  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 [[#BR: Columns Definitions | Columns Definitions]] below
+
-
* Footer:
+
-
** There is no footer
+
-
 
+
-
==== BR: 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'' & ''blue'')
+
-
:* 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'')
+
-
:* 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
+
-
 
+
-
=== BR: Figures ===
+
-
 
+
-
==== Figure 0. Filters ====
+
-
 
+
-
[[File:ClientStatsReport.JPG]]
+
-
 
+
-
==== Figure 1. Mode FCL ====
+
-
 
+
-
[[File:ClientFCLStats.JPG|FCL]]
+
-
 
+
-
==== Figure 2. Mode LCL ====
+
-
[[File:ClientLCLStats.JPG]]
+
-
 
+
-
==== Figure 3. Mode Air ====
+
-
[[File:ClientAir.JPG]]
+
-
 
+
-
==== Figure 4. Mode Truck ====
+
-
[[File:ClientTruckStat.JPG]]
+
-
 
+
-
</div>
+
== SD: Systems Design ==
== SD: Systems Design ==
Line 219: Line 41:
=== SD: Summary ===
=== SD: Summary ===
-
This report is one of a family of statistics reports. Client Statistics Report is available from the main menu on the next path: "Reports -> Statistical -> Client Statistics". See [[#Figure 0. Main Menu item | Figure 0]].
+
This report is one of a family of statistics reports. It is related to [[Carrier Statistic Report]].
 +
 
 +
Client Statistics Report is available from the main menu on the next path: "Reports -> Statistical -> Clients Statistic". See [[#Figure 0. Main Menu item | Figure 0]].
Client Statistics Report should help to answer these basic questions for specific Client Company:
Client Statistics Report should help to answer these basic questions for specific Client Company:
* '''FCL''':  
* '''FCL''':  
-
** How many containers with different types and sizes with [[TEU]] per month from each origin terminal to each destination terminal?
+
** How many containers with different types and sizes with [[TEU]] per month
 +
*** from each origin terminal to each destination terminal each month?
* '''LCL''':  
* '''LCL''':  
** How many CT shipments  
** How many CT shipments  
** How much total of gross weight  
** How much total of gross weight  
-
** Total in cbm per month for each LCL shipment from each origin terminal to each destination terminal each month
+
** Total in CBM
 +
** and average weight per month  
 +
*** from each origin terminal to each destination terminal each month?
* '''Air''':  
* '''Air''':  
Line 235: Line 62:
** How much chargeable weight  
** How much chargeable weight  
** and average weight per month  
** and average weight per month  
-
*** for each airline
+
*** from origin airport to destination airport each month?
-
**** 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?
+
* '''Truck''':
 +
** How many CT shipments
 +
** How much total Gross weight
 +
** and average weight per month  
 +
*** from origin terminal to destination terminal each month?
=== SD: Details Per Mode ===
=== SD: Details Per Mode ===
-
Report should be contain monthly information for one or several Client Companies for a specified period.  
+
* Report should be contain monthly information for one or several Client Companies for a specified period.
 +
* Information that should be reflected in the report is grouped by MOT([[CT_bo#Mode_Of_Transport | Mode Of Transport]]) and shows shipment totals from each Origin Terminal to each Destination Terminal, which in turn are grouped and summed by Country.
-
Information that should be reflected in the report is grouped by MOT([[CT_bo#Mode_Of_Transport | 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 Pick Up Date''':
 +
:* [[CT_bo#Actual_Export_Pick_Up_Date | Actual Export Pick-Up Date]] for MOT = TRUCKING.
 +
:* [[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.
 +
:* ''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 252: Line 91:
** 40 hq (40 hq only)
** 40 hq (40 hq only)
** 45 ft (include all types)
** 45 ft (include all types)
-
* T.E.U ([[TEU | Twenty Equivalent Unit]]: 20 ft = 1 TEU) for each type from above.
+
* T.E.U ([[TEU | Twenty Equivalent Unit]]: 20 ft = 1 Equivalent, 40 ft/hq or 45 ft = 2 TEU)
-
** NOTE: one 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 [http://ct.jaguarfreight.com/mantis/view.php?id=2383 2383])''.
* Total of each column
* Total of each column
 +
* ''Example of layout'': [[#Example 1. Mode FCL]]
==== MOT = LCL ====
==== MOT = LCL ====
Line 261: Line 102:
* Total in cbm. Mapped to ContTab.Table B. Total (in cbm).
* Total in cbm. Mapped to ContTab.Table B. Total (in cbm).
* Average weight. Calculated by formula: ''Total Gross weight / Total CT shipments''.
* Average weight. Calculated by formula: ''Total Gross weight / Total CT shipments''.
-
* Total of each column
+
* Total of each column except Average weight wich should be calculated by the formula that specified above.
 +
* ''Example of layout'': [[#Example 2. Mode LCL]]
==== MOT = AIR ====
==== MOT = AIR ====
Line 268: Line 110:
* Total chargeable weight. Mapped to ExpTab.17b: Chargeable weight.
* Total chargeable weight. Mapped to ExpTab.17b: Chargeable weight.
* Average weight. Calculated by formula: ''Total chargeable weight / Total CT shipments.''
* Average weight. Calculated by formula: ''Total chargeable weight / Total CT shipments.''
-
* Total of each column
+
* Total of each column except Average weight wich should be calculated by the formula that specified above.
 +
* ''Example of layout'': [[#Example 3. Mode Air]]
==== MOT = Truck ====
==== MOT = Truck ====
Line 274: Line 117:
* Total Gross weight. Mapped to ContTab.Table A. GrandTotal.Ttl GW kg.
* Total Gross weight. Mapped to ContTab.Table A. GrandTotal.Ttl GW kg.
* Average weight. Calculated by formula: ''Total Gross weight / Total CT shipments''.
* Average weight. Calculated by formula: ''Total Gross weight / Total CT shipments''.
-
* Total of each column
+
* Total of each column except Average weight wich should be calculated by the formula that specified above.
-
 
+
* ''Example of layout'': [[#Example 4. Mode Truck]]
-
==== Totals ====
+
-
Totals are per month based on Actual Collection 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 = {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.
+
=== SD: Filters ===
=== SD: Filters ===
-
* On the report template are filters to configure the report generation. See [[#Figure 1. Client Statistics Report Template  | Figure 1]].
+
* On the report template are [[Filters]] to configure the report generation. See [[#Figure 1. Client Statistics Report Template  | Figure 1]].
-
* Most filters are "multi select", exclude "Year to report" and "Month From ... To ...".  
+
==== List of filters ====
-
** A detailed description of these fields, see [[#Figure 0. Filters]].
+
* '''Actual Pickup Date''' - located on the template as two pairs "Month & Year" dropdowns: From and To.
-
* 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.
+
** 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"''.
 +
** <span style="color:#008000">By default:</span> 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]].
 +
** Mapping of "Client Companies" should include also visibility fields [[CT_bo#E1]], [[CT_bo#E2]], [[CT_bo#E3]].
 +
* '''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:
 +
** 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 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 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 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 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 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 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. Client Statistics Report Output Tab | Figure 2]].
 +
 
 +
==== 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 [http://ct.jaguarfreight.com/wiki/Reports_Filters#Multiselect multi-select], exclude "Actual Pickup Date".
** 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 293: Line 193:
* Every filter is connected by AND and sub selections inside each filter combo-box are connected by OR.
* 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'''  (Shipper = EUROSTOCK '''OR''' Shipper = VALOIS)
** For example: (Year to Report = 2009) '''AND''' (Mode = Ocean '''OR''' Mode = Air) '''AND'''  (Shipper = EUROSTOCK '''OR''' Shipper = VALOIS)
 +
* Some filters should be '''client-specific''':
 +
** Meaning: ''is needed to clarify''.
 +
** Implementation: ''not implemented''.
=== SD: Output ===
=== SD: Output ===
* Output form of this report is carried out only in spreadsheet (HTML form is not required).
* 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.  
* 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 [[#BR: Output]].
+
* Spreadsheet consists of Header and Body:
-
* For every MOT there is a set of columns that should be shown. See [[#BR: Columns Definitions]].
+
** Header: Report name, Date time stamp, Criteria(list of filters selected), Transport Mode, Jaguar Logo.
-
* Examples of layouts for the report output shown in [[#BR: Figures]]:
+
** Body(general info):
-
:* [[#Figure 1. Mode FCL]]
+
*** consists of sections: separately by each Client Company.
-
:* [[#Figure 2. Mode LCL]]
+
*** each section top label Client Company Name.
-
:* [[#Figure 3. Mode Air]]
+
**** each Client Company section consists of sub-sections: separately by from country to country
-
:* [[#Figure 4. Mode Truck]]
+
***** each sub-section starts with sub-total line that contains the total values for each columns that summed in this sub-section ''(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 Client Company 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 "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 [[#SD: Filters |Filters]]):
 +
**: # Origin Countries.
 +
**: # Destination Countries.
 +
**: # Origin Regions.
 +
**: # Destination Regions.
 +
**: # Origin Ports/Terminals.
 +
**: # Destination Ports/Terminals.
 +
**: # Origin Airports.
 +
**: # Destination Airports.
 +
**: # Pickup Location Cities.
 +
**: # Delivery Location Cities.
 +
* Report Results are '''sorted''' as follows:
 +
** First sorting is by Client Company Name in ascending order.
 +
** Then (inside of the Client section) data is sorted by the pair Origin Country - Destination Country in ascending order.
 +
*** At first, output data are sorted by Origin Country and then data are sorted by Destination Country.
 +
*** If exist "-Undefined-" values of Countries in the report then they placed first in this section.
 +
** Then (inside of the Countries sub-section) data is sorted by the pair Origin Terminal - Destination Terminal in ascending order.
 +
*** At first, output data are sorted by Origin Terminals and then data are sorted by Destination Terminals.
 +
*** If exist "-Undefined-" values of Terminals in the report then they placed first in the sub-section.
 +
* [http://ct.jaguarfreight.com/wiki/List_CTs#Archived_Tab "Archived"] shipments included in the results of the Report.
 +
 
 +
==== SD: Columns Definitions ====
 +
* For every MOT there is a set of columns that should be shown in the body of spreadsheet.
 +
* Summary monthly totals shows for a month specified 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)
 +
:''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)(include all types)
 +
:*''45'ft'' - the total number of containers for that particular size and types: 40 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 =====
 +
[[File:ClientFCLStats.JPG|FCL]]
 +
 
 +
'''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.
 +
 
 +
===== Example 2. Mode LCL =====
 +
[[File:ClientLCLStats.JPG]]
 +
 
 +
'''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
 +
 
 +
===== Example 3. Mode Air =====
 +
[[File:ClientAir.JPG]]
 +
 
 +
'''MOT: TRUCK'''
 +
:''Origin Terminal'' - location that shipment is moved from (origin terminal)
 +
:''Destination Terminal'' - location that shipment is moved to (destination 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
 +
 
 +
===== Example 4. Mode Truck =====
 +
[[File:ClientTruckStat.JPG]]
=== SD: Figures ===
=== SD: Figures ===
Line 311: Line 294:
==== Figure 1. Client Statistics Report Template ====
==== Figure 1. Client Statistics Report Template ====
[[File:ClientStatisticsReportTemplate.gif]]
[[File:ClientStatisticsReportTemplate.gif]]
 +
==== Figure 2. Client Statistics Report Output Tab ====
 +
[[File:ClientsStatisticsReportOutputTab.gif]]
</div>
</div>
Line 317: Line 302:
=== m1511 ===
=== m1511 ===
-
* http://mantis.jaguarfreight.com/mantis/view.php?id=1511
+
* http://ct.jaguarfreight.com/mantis/view.php?id=1511
-
'''last revision with BR''' - http://mantis.jaguarfreight.com/mediawiki-1.15.0/index.php?title=Client_Statistics_Report&oldid=11112
+
'''last revision with BR''' - http://ct.jaguarfreight.com/mediawiki-1.15.0/index.php?title=Client_Statistics_Report&oldid=11112
-
'''last revision''' - http://mantis.jaguarfreight.com/mediawiki-1.15.0/index.php?title=Client_Statistics_Report&oldid=11114
+
'''last revision''' - http://ct.jaguarfreight.com/mediawiki-1.15.0/index.php?title=Client_Statistics_Report&oldid=12756
==== BA 1511 ====
==== BA 1511 ====
Line 328: Line 313:
==== SA 1511 ====
==== SA 1511 ====
* See [[#SD: Systems Design]]
* See [[#SD: Systems Design]]
 +
 +
==== m2450 ====
 +
http://ct.jaguarfreight.com/mantis/view.php?id=2450
 +
 +
=== 0002446: (*ph1)(Client Statistics) Tweak: show Grand Total(sum for all ttl value) for each and all sections after the total line ===

Current revision as of 12:11, 25 October 2011


Contents

[edit] Info

1598


[edit] CBN: Core Business Need

This wiki section is managed by: Module Owner


This report should help in defining the volume of shipments handled for specific Client Company (or group).

Low or medium or high volume will trigger certain business decision.

This report is related to Carrier Statistic Report


[edit] Requirements

[edit] Client version of this report

[edit] Requirements

  • The client application report is virtually identical to the internal client statistic report. In that it should still answer the same three basic questions, have the same filters & output.
  • It would be accessible on the client application under Cybertrax >> Reports.

[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 Carrier Statistic Report.

Client Statistics Report is available from the main menu on the next path: "Reports -> Statistical -> Clients Statistic". See Figure 0.

Client Statistics Report should help to answer these basic questions for specific Client Company:

  • FCL:
    • How many containers with different types and sizes with TEU per month
      • from each origin terminal to each destination terminal each month?
  • LCL:
    • How many CT shipments
    • How much total of gross weight
    • Total in CBM
    • and average weight per month
      • from each origin terminal to each destination terminal each month?
  • Air:
    • How many CT shipments
    • How much chargeable weight
    • and average weight per month
      • from origin airport to destination airport each month?
  • Truck:
    • How many CT shipments
    • How much total Gross weight
    • and average weight per month
      • from origin terminal to destination terminal each month?

[edit] SD: Details Per Mode

  • Report should be contain monthly information for one or several Client Companies 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

[edit] 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
  • Example of layout: #Example 1. Mode FCL

[edit] 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 except Average weight wich should be calculated by the formula that specified above.
  • Example of layout: #Example 2. Mode LCL

[edit] 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 except Average weight wich should be calculated by the formula that specified above.
  • Example of layout: #Example 3. Mode Air

[edit] 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 except Average weight wich should be calculated by the formula that specified above.
  • Example of layout: #Example 4. Mode Truck

[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 dropdown list, which becomes visible when "Transport Modes" filter is blank or contains any OCEAN value.
  • Destination Ports/Terminals - located on the template as 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 dropdown list, which becomes visible when "Transport Modes" filter is blank or contains AIR value.
  • Destination Airports - located on the template as 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 dropdown list, which becomes visible when "Transport Modes" filter is blank or contains TRUCKING value.
  • Delivery Location Cities - located on the template as 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

[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".
    • 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 (Shipper = EUROSTOCK OR Shipper = VALOIS)
  • Some filters should be client-specific:
    • Meaning: is needed to clarify.
    • Implementation: not implemented.

[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 Client Company.
      • each section top label Client Company Name.
        • each Client Company section consists of sub-sections: separately by from country to country
          • each sub-section starts with sub-total line that contains the total values for each columns that summed in this sub-section (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 Client Company 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 "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.
      # Origin Ports/Terminals.
      # Destination Ports/Terminals.
      # Origin Airports.
      # Destination Airports.
      # Pickup Location Cities.
      # Delivery Location Cities.
  • Report Results are sorted as follows:
    • First sorting is by Client Company Name in ascending order.
    • Then (inside of the Client section) data is sorted by the pair Origin Country - Destination Country in ascending order.
      • At first, output data are sorted by Origin Country and then data are sorted by Destination Country.
      • If exist "-Undefined-" values of Countries in the report then they placed first in this section.
    • Then (inside of the Countries sub-section) data is sorted by the pair Origin Terminal - Destination Terminal in ascending order.
      • At first, output data are sorted by Origin Terminals and then data are sorted by Destination Terminals.
      • If exist "-Undefined-" values of Terminals in the report then they placed first in the sub-section.
  • "Archived" shipments included in the results of the Report.

[edit] SD: Columns Definitions

  • For every MOT there is a set of columns that should be shown in the body of spreadsheet.
  • Summary monthly totals shows for a month specified 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)
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)(include all types)
  • 45'ft - the total number of containers for that particular size and types: 40 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"
[edit] Example 1. Mode FCL

FCL

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.
[edit] Example 2. Mode LCL

File:ClientLCLStats.JPG

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
[edit] Example 3. Mode Air

File:ClientAir.JPG

MOT: TRUCK

Origin Terminal - location that shipment is moved from (origin terminal)
Destination Terminal - location that shipment is moved to (destination 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
[edit] Example 4. Mode Truck

File:ClientTruckStat.JPG

[edit] SD: Figures

[edit] Figure 0. Main Menu item

File:Maimenu_statreports_client.gif

[edit] Figure 1. Client Statistics Report Template

File:ClientStatisticsReportTemplate.gif

[edit] Figure 2. Client Statistics Report Output Tab

File:ClientsStatisticsReportOutputTab.gif

[edit] History

[edit] m1511

last revision with BR - http://ct.jaguarfreight.com/mediawiki-1.15.0/index.php?title=Client_Statistics_Report&oldid=11112

last revision - http://ct.jaguarfreight.com/mediawiki-1.15.0/index.php?title=Client_Statistics_Report&oldid=12756

[edit] BA 1511

[edit] SA 1511

[edit] m2450

http://ct.jaguarfreight.com/mantis/view.php?id=2450

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

Retrieved from "https://wiki.jaguarfreight.com/Client_Statistics_Report"
Personal tools