Client Statistics Report
From UG
(→SD: Output: changed Output options) |
(→SD: Systems Design: rearranged pictures of examples) |
||
Line 82: | Line 82: | ||
*** NOTE: ''(Not implemented yet, see mantis [http://mantis.jaguarfreight.com/mantis/view.php?id=2383 2383])''. | *** NOTE: ''(Not implemented yet, see mantis [http://mantis.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 92: | Line 90: | ||
* 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 except Average weight wich should be calculated by the formula that specified above. | * 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 102: | Line 98: | ||
* 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 except Average weight wich should be calculated by the formula that specified above. | * 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 111: | Line 105: | ||
* 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 except Average weight wich should be calculated by the formula that specified above. | * Total of each column except Average weight wich should be calculated by the formula that specified above. | ||
- | + | * ''Example of layout'': [[#Example 4. Mode Truck]] | |
- | + | ||
- | + | ||
=== SD: Filters === | === SD: Filters === | ||
Line 212: | Line 204: | ||
==== SD: Columns Definitions ==== | ==== SD: Columns Definitions ==== | ||
* For every MOT there is a set of columns that should be shown in the body of spreadsheet. | * For every MOT there is a set of columns that should be shown in the body of spreadsheet. | ||
- | * Summary monthly totals shows for a | + | * 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''' | '''MOT: FCL''' | ||
Line 224: | Line 217: | ||
:*''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 ===== | ||
+ | [[File:ClientFCLStats.JPG|FCL]] | ||
'''MOT: LCL''' | '''MOT: LCL''' | ||
Line 234: | Line 230: | ||
:*''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. | ||
+ | |||
+ | ===== Example 2. Mode LCL ===== | ||
+ | [[File:ClientLCLStats.JPG]] | ||
'''MOT: AIR''' | '''MOT: AIR''' | ||
Line 243: | Line 242: | ||
:*''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() | ||
:'''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 3. Mode Air ===== | ||
+ | [[File:ClientAir.JPG]] | ||
'''MOT: TRUCK''' | '''MOT: TRUCK''' | ||
Line 253: | Line 255: | ||
:'''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 | ||
- | ==== | + | ===== Example 4. Mode Truck ===== |
- | + | [[File:ClientTruckStat.JPG]] | |
- | + | ||
- | : | + | |
- | + | ||
- | + | ||
=== SD: Figures === | === SD: Figures === |
Revision as of 10:20, 30 September 2010
Contents |
Info
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
SD: Systems Design
This wiki section is managed by: Systems Analyst
SD: Summary
This report is one of a family of statistics reports. It is related to 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?
- How many containers with different types and sizes with TEU per 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?
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.
Totals
- Totals are per month based on Actual Pick Up Date:
- Actual Export Pick-Up Date for MOT = TRUCKING.
- Airport Of Departure Actual Date for MOT = AIR.
- 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.
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).
- For automatic calculation of TEU should be used values from Container Sizes table: Admin - Transportation - Container Sizes.
- Total of each column
- Example of layout: #Example 1. Mode FCL
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
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
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
SD: Filters
- On the report template are filters to configure the report generation. See Figure 1.
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 clients.
- 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 clients.
- 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 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.
- 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.
Output Tab
- This tab contains text line "NOTE: Report only includes 20, 40, 45 ft containers". See on 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.
- 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 "Time Frame".
- 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)
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 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 Client Company section consists of sub-sections: separately by from country to country
- 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.
- # Origin Terminals.
- # Destination Terminals.
- These fields are (mapping in Filters):
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
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
MOT: AIR
- 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()
- 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
MOT: TRUCK
- 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()
- 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
SD: Figures
Figure 0. Main Menu item
Figure 1. Client Statistics Report Template
Figure 2. Client Statistics Report Output Tab
History
m1511
last revision with BR - http://mantis.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=12756