Jump to content

Shipment Detail Report: Difference between revisions

From PBI Reporting Wiki
← Older edit
VisualWikitext
No edit summary
No edit summary
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:


== Summary ==
== Summary ==
TBC
This is a detailed summary report of all shipments in the system and shows you all the details at a high level. Please see the columns below for their descriptions.


== Filters ==
== Filters ==
Line 9: Line 9:
! style="width: 75%;" |Description
! style="width: 75%;" |Description
|-
|-
|TBC
|Database||If you have access to multiple entity databases, you can filter and select specific ones
|TBC
|-
|Site||Allows you to filter and focus on specific sites within your entity access.
|-
|Carrier||Enables filtering by carriers associated with the sites you have access to.
|-
|Users||Filters quotes/bookings by specific users. Users will be listed if they have access to the sites you can access.
|-
|Origin||Filters jobs based on their origin location.
|-
|Destination||Filters jobs based on their destination location.
|-
|Date Filter||Displays only jobs created within the specified date range
|}
|}


Line 20: Line 31:
!Calculation
!Calculation
!Description
!Description
|-
|
|-
|-
| 1 . Won / Lost = If the quote was won or lost  ||Formula:   
| 1 . Won / Lost = If the quote was won or lost  ||Formula:   


Won/Lost =   
<pre>Won/Lost =   


SWITCH (  
SWITCH (  
Line 31: Line 40:
'quotations'[status] IN {8, 61, 62}, "Lost", 'quotations'[status] IN {0, 1, 2, 20, 6, 60}, "Pending",  
'quotations'[status] IN {8, 61, 62}, "Lost", 'quotations'[status] IN {0, 1, 2, 20, 6, 60}, "Pending",  
'quotations'[status] IN {3, 4, 5, 7, 30, 31, 32, 33, 34, 35, 70, 71}, "Won",  
'quotations'[status] IN {3, 4, 5, 7, 30, 31, 32, 33, 34, 35, 70, 71}, "Won",  
"Unknown")  
"Unknown") </pre>
  || This formula determines the status of a quotation based on its assigned numerical code. The possible statuses are:  
  || This formula determines the status of a quotation based on its assigned numerical code. The possible statuses are:  


Line 43: Line 52:


*Unknown: If the status does not match any of the predefined categories, it defaults to "Unknown" .  
*Unknown: If the status does not match any of the predefined categories, it defaults to "Unknown" .  
|-
|
|-
|-
| 2 . Deleted = If the shipment is deleted ||The formula:  
| 2 . Deleted = If the shipment is deleted ||The formula:  


Deleted = IF (ISBLANK (quotations[deleted_at]),  "X",   
<pre>Deleted = IF (ISBLANK (quotations[deleted_at]),  "X",   
UNICHAR (10003)   
UNICHAR (10003)   


)  
) </pre>
  || This formula checks whether a quotation has been deleted and assigns a corresponding   
  || This formula checks whether a quotation has been deleted and assigns a corresponding   


Line 59: Line 66:


*If the deleted_at field contains a value (i.e., the quotation has been deleted), it shows a ✓ (checkmark) using the UNICHAR (10003) function.  
*If the deleted_at field contains a value (i.e., the quotation has been deleted), it shows a ✓ (checkmark) using the UNICHAR (10003) function.  
|-
|
|-
|-
| 3 . Status = Current status of the shipment in QMS||<pre>The formula:   
| 3 . Status = Current status of the shipment in QMS||<pre>The formula:   
Line 170: Line 175:
Default State:Unknown: If the quotation status does not match any of the predefined values, it is categorized as "Unknown".   
Default State:Unknown: If the quotation status does not match any of the predefined values, it is categorized as "Unknown".   
|-
|-
|
|4. Loss Reasons   || <pre>loss reason = quotation [ loss_reason]</pre>|| The lost reason that was set  
|-
|4. Loss Reasons = The lost reason that was set || || 
|-
|-
|
| 5. Deleted Reasons ||<pre> Delete reason=quatation[delete_reason]</pre>||  The deleted reason that was
|-
| 5. Deleted Reasons = The deleted reason that was set  || || 
|-
|
|-
|-
| 6. Cargo Type = The cargo type selected, currently displayed in the numeric form || The formula:  
| 6. Cargo Type = The cargo type selected, currently displayed in the numeric form || The formula:  


Cargo type = IF(ISBLANK('quotations'[Cargo_type]), "ANY", 'quotations'[Cargo_type])  
<pre>Cargo type = IF(ISBLANK('quotations'[Cargo_type]), "ANY", 'quotations'[Cargo_type]) </pre>
|| This formula determines the type of cargo for a quotation. It follows these rules:  
|| This formula determines the type of cargo for a quotation. It follows these rules:  


Line 191: Line 190:
*If a Cargo Type is specified, it displays the assigned cargo type. .  
*If a Cargo Type is specified, it displays the assigned cargo type. .  
|-
|-
|
| 7. Gross Weight = Gross weight of the shipment.  ||<pre>WeightBreakChar =
|-
SWITCH(
| 7. Gross Weight = Gross weight of the shipment.  || || The gross weight is determined by summing up the total weight of all items in a quotation. This represents the overall weight, including the packaging and any additional material.   
    TRUE(),
|-
    quotations[chargeable_weight] < 45, "<45",
|
    quotations[chargeable_weight] < 100, "<100",
|-
    quotations[chargeable_weight] < 300, "<300",
| 8. Chargeable Weight = Chargeable weight of the shipment || || .
    quotations[chargeable_weight] < 500, "<500",
|-
    quotations[chargeable_weight] < 1000, "<1000",
|
    quotations[chargeable_weight] < 2000, "<2000",
    quotations[chargeable_weight] < 3000, "<3000",
    quotations[chargeable_weight] >= 3000, ">=3000",
    "Other"
)</pre>
  || The gross weight is determined by summing up the total weight of all items in a quotation. This represents the overall weight, including the packaging and any additional material.   
|-
|-
| 9. Commodity Type = The type of commodity of the shipment || ||  
| 8. Chargeable Weight || <pre>WeightBreakChar =
SWITCH(
    TRUE(),
    quotations[chargeable_weight] < 45, "<45",
    quotations[chargeable_weight] < 100, "<100",
    quotations[chargeable_weight] < 300, "<300",
    quotations[chargeable_weight] < 500, "<500",
    quotations[chargeable_weight] < 1000, "<1000",
    quotations[chargeable_weight] < 2000, "<2000",
    quotations[chargeable_weight] < 3000, "<3000",
    quotations[chargeable_weight] >= 3000, ">=3000",
    "Other"
)</pre>||Chargeable weight of the shipment
|-
|-
|
| 9. Commodity Type  ||<pre>Commodity=quatations[commodity] </pre>||The type of commodity of the shipment 
|-
|-
| 10. Product Type = What type of product was selected  ||The formula:   
| 10. Product Type ||The formula:   
Product Type =   
<pre>Product Type =   


SWITCH (  
SWITCH (  
Line 222: Line 238:
     "Unknown"  
     "Unknown"  


)  
) </pre>
  ||  
  || What type of product was selected 
|-
|
|-
|-
| 11.Promo = If a promotional shipment  ||The formula:  
| 11.Promo = If a promotional shipment  ||The formula:  


Promo = IF (  
<pre>Promo = IF (  


     quotations[tariffs.flag_promo] = TRUE,   
     quotations[tariffs.flag_promo] = TRUE,   
Line 237: Line 251:
     "x"  
     "x"  


)  
) </pre>
  || This formula determines whether a quotation qualifies as "Standard" based on specific conditions:  
  || This formula determines whether a quotation qualifies as "Standard" based on specific conditions:  


Line 243: Line 257:


*If any of these fields do not contain "x", the quotation is marked with "x", meaning it does not qualify as standard.   
*If any of these fields do not contain "x", the quotation is marked with "x", meaning it does not qualify as standard.   
|-
|
|-
|-
| 12. Contract = If a contract shipment  ||The formula:  
| 12. Contract = If a contract shipment  ||The formula:  


Contract = IF (  
<pre>Contract = IF (  


     NOT ISBLANK (quotations[tariffs.customer_id]),   
     NOT ISBLANK (quotations[tariffs.customer_id]),   
Line 256: Line 268:
     "x"  
     "x"  


)  
) </pre>
  || This formula is used to track contracts in a quotation system.  
  || This formula is used to track contracts in a quotation system.  


Line 262: Line 274:


*The checkmark (✓) and cross (x) provide an intuitive, visual representation instead of displaying raw data.  
*The checkmark (✓) and cross (x) provide an intuitive, visual representation instead of displaying raw data.  
|-
|
|-
|-
| 13. Spot Rate = If a spot rate shipment  || The formula:  
| 13. Spot Rate = If a spot rate shipment  || The formula:  


Spot Rate = IF (  
<pre>Spot Rate = IF (  


     quotations[spotrate] = 1,   
     quotations[spotrate] = 1,   
Line 275: Line 285:
     "x"  
     "x"  


)  
) </pre>
  || This formula helps users quickly identify whether a quotation is categorized as a spot rate.  
  || This formula helps users quickly identify whether a quotation is categorized as a spot rate.  


*The checkmark (✓) and cross (x) provide an easy-to-read visual representation.  
*The checkmark (✓) and cross (x) provide an easy-to-read visual representation.  
|-
|
|-
|-
| 14. DG = If there are dangerous goods in the shipment  || The formula:  
| 14. DG = If there are dangerous goods in the shipment  || The formula:  
 
<pre>DG = IF (  
DG = IF (  
quotations[hazardous_goods] = TRUE,   
 
"✓",   "x"  
    quotations[hazardous_goods] = TRUE,   
) </pre>
 
    "✓",
 
    "x"  
 
)  
|| This formula helps users quickly identify whether a shipment or quotation involves dangerous (hazardous) goods.  The checkmark (✓) and cross (x) provide a clear and intuitive visual representation.  
|| This formula helps users quickly identify whether a shipment or quotation involves dangerous (hazardous) goods.  The checkmark (✓) and cross (x) provide a clear and intuitive visual representation.  
|-
|
|-
|-
| 15. CAO = If the shipment is cargo aircraft only  
| 15. CAO = If the shipment is cargo aircraft only  
Line 301: Line 301:
   || The formula  
   || The formula  


CAO = IF (  
<pre>CAO = IF (  


     quotations[cargo_only] = TRUE,   
     quotations[cargo_only] = TRUE,   
Line 309: Line 309:
     "x"  
     "x"  


)  
) </pre>
|| This formula helps users quickly identify whether a shipment is classified as Cargo Aircraft Only (CAO), ensuring compliance with aviation safety regulations. The checkmark (✓) and cross (x) provide a clear and intuitive visual representation.
|| This formula helps users quickly identify whether a shipment is classified as Cargo Aircraft Only (CAO), ensuring compliance with aviation safety regulations. The checkmark (✓) and cross (x) provide a clear and intuitive visual representation.
|}
|}

Latest revision as of 13:31, 17 June 2025

Summary

This is a detailed summary report of all shipments in the system and shows you all the details at a high level. Please see the columns below for their descriptions.

Filters

Filter Description
Database If you have access to multiple entity databases, you can filter and select specific ones
Site Allows you to filter and focus on specific sites within your entity access.
Carrier Enables filtering by carriers associated with the sites you have access to.
Users Filters quotes/bookings by specific users. Users will be listed if they have access to the sites you can access.
Origin Filters jobs based on their origin location.
Destination Filters jobs based on their destination location.
Date Filter Displays only jobs created within the specified date range

Calculations

Reference Calculation Description
1 . Won / Lost = If the quote was won or lost Formula: 
Won/Lost =  

SWITCH ( 
TRUE(), 
'quotations'[status] IN {8, 61, 62}, "Lost", 'quotations'[status] IN {0, 1, 2, 20, 6, 60}, "Pending", 
'quotations'[status] IN {3, 4, 5, 7, 30, 31, 32, 33, 34, 35, 70, 71}, "Won", 
"Unknown")
This formula determines the status of a quotation based on its assigned numerical code. The possible statuses are:


  • Lost: If the quotation status is 8, 61, or 62, it is categorized as "Lost".
  • Pending: If the status is 0, 1, 2, 20, 6, or 60, the quotation is still "Pending".
  • Won: If the status is 3, 4, 5, 7, 30, 31, 32, 33, 34, 35, 70, or 71, the quotation is considered "Won".
  • Unknown: If the status does not match any of the predefined categories, it defaults to "Unknown" .
2 . Deleted = If the shipment is deleted The formula: 
Deleted = IF (ISBLANK (quotations[deleted_at]),  "X",  
UNICHAR (10003)  

)
This formula checks whether a quotation has been deleted and assigns a corresponding

symbol:

  • If the deleted_at field is blank (i.e., the quotation has not been deleted), it displays "X".
  • If the deleted_at field contains a value (i.e., the quotation has been deleted), it shows a ✓ (checkmark) using the UNICHAR (10003) function.
3 . Status = Current status of the shipment in QMS 
The formula:  
State =  

    SWITCH ( 

    TRUE(), 

    VALUE('quotations'[status]) = 0, "Search - routes", 

    VALUE('quotations'[status]) = 1, "Search - confirm", 

    VALUE('quotations'[status]) = 2, "Quote", 

    VALUE('quotations'[status]) = 20, "Quote - requote request", 

    VALUE('quotations'[status]) = 6, "Quote - spot rate", 

    VALUE('quotations'[status]) = 60, "Quote - spot rate review required", 

    VALUE('quotations'[status]) = 5, "Booking - cancellation request", 

    VALUE('quotations'[status]) = 31, "Booking - cancelled with charges", 

    VALUE('quotations'[status]) = 30, "Booking - acceptance review required", 

    VALUE('quotations'[status]) = 33, "Booking - confirmed review required", 

    VALUE('quotations'[status]) = 34, "Booking - sync review required", 

    VALUE('quotations'[status]) = 35, "Booking - schedule confirmed review required", 

    VALUE('quotations'[status]) = 4, "Booking - pending credit", 

    VALUE('quotations'[status]) = 3, "Booking - carrier confirmation required", 

    VALUE('quotations'[status]) = 32, "Booking - pending", 

    VALUE('quotations'[status]) = 70, "Booking - rebooking request", 

    VALUE('quotations'[status]) = 7, "Booking - confirmed", 

    VALUE('quotations'[status]) = 71, "Booking - airline confirmed", 

    VALUE('quotations'[status]) = 8, "Lost", 

    VALUE('quotations'[status]) = 61, "Lost - carrier declined", 

    VALUE('quotations'[status]) = 62, "Lost - customer declined", 

     

    "UnKnown" 

)
This formula categorizes the status of a quotation based on its numerical value and assigns a corresponding description. The possible states are:


Search & Quotation States:

  • Search - Routes (0): The user is searching for available routes.
  • Search - Confirm (1): The user is confirming route details.
  • Quote (2): A quotation has been generated.
  • Quote - Requote Request (20): A request for a revised quotation has been made.
  • Quote - Spot Rate (6): A special pricing (spot rate) has been requested.
  • Quote - Spot Rate Review Required (60): The spot rate needs review.

Booking States:

  • Booking - Carrier Confirmation Required (3): Waiting for carrier confirmation.
  • Booking - Pending Credit (4): Booking is pending due to credit approval.
  • Booking - Cancellation Request (5): A request to cancel the booking has been made.
  • Booking - Acceptance Review Required (30): The booking needs review before acceptance.
  • Booking - Cancelled with Charges (31): The booking was canceled with applicable charges.
  • Booking - Pending (32): The booking is pending approval or confirmation.
  • Booking - Confirmed Review Required (33): The confirmed booking needs further review.
  • Booking - Sync Review Required (34): A synchronization issue requires review.
  • Booking - Schedule Confirmed Review Required (35): The schedule confirmation needs review.
  • Booking - Rebooking Request (70): A request to rebook the shipment has been made.
  • Booking - Confirmed (7): The booking has been successfully confirmed.
  • Booking - Airline Confirmed (71): The airline has confirmed the booking.

Lost States:

  • Lost (8): The quotation was lost.
  • Lost - Carrier Declined (61): The carrier rejected the booking request.
  • Lost - Customer Declined (62): The customer rejected the quotation.

Default State:Unknown: If the quotation status does not match any of the predefined values, it is categorized as "Unknown".

4. Loss Reasons 
loss reason = quotation [ loss_reason]
 The lost reason that was set
5. Deleted Reasons 
 Delete reason=quatation[delete_reason]
 The deleted reason that was
6. Cargo Type = The cargo type selected, currently displayed in the numeric form The formula: 
Cargo type = IF(ISBLANK('quotations'[Cargo_type]), "ANY", 'quotations'[Cargo_type])
This formula determines the type of cargo for a quotation. It follows these rules:


  • If the Cargo Type field is blank, it defaults to "ANY", meaning no specific cargo type is assigned.
  • If a Cargo Type is specified, it displays the assigned cargo type. .
7. Gross Weight = Gross weight of the shipment. 
WeightBreakChar =
SWITCH(
    TRUE(),
    quotations[chargeable_weight] < 45, "<45",
    quotations[chargeable_weight] < 100, "<100",
    quotations[chargeable_weight] < 300, "<300",
    quotations[chargeable_weight] < 500, "<500",
    quotations[chargeable_weight] < 1000, "<1000",
    quotations[chargeable_weight] < 2000, "<2000",
    quotations[chargeable_weight] < 3000, "<3000",
    quotations[chargeable_weight] >= 3000, ">=3000",
    "Other"
)
 The gross weight is determined by summing up the total weight of all items in a quotation. This represents the overall weight, including the packaging and any additional material.
8. Chargeable Weight 
WeightBreakChar =
SWITCH(
    TRUE(),
    quotations[chargeable_weight] < 45, "<45",
    quotations[chargeable_weight] < 100, "<100",
    quotations[chargeable_weight] < 300, "<300",
    quotations[chargeable_weight] < 500, "<500",
    quotations[chargeable_weight] < 1000, "<1000",
    quotations[chargeable_weight] < 2000, "<2000",
    quotations[chargeable_weight] < 3000, "<3000",
    quotations[chargeable_weight] >= 3000, ">=3000",
    "Other"
)
 Chargeable weight of the shipment
9. Commodity Type 
Commodity=quatations[commodity]
The type of commodity of the shipment
10. Product Type The formula: 
Product Type =  

SWITCH ( 

    quotations [Product], 

    0, "Airport to Airport", 

    1, "Airport to Door", 

    2, "Door to Airport", 

    3, "Door to Door", 

    "Unknown" 

)
What type of product was selected
11.Promo = If a promotional shipment The formula: 
Promo = IF ( 

    quotations[tariffs.flag_promo] = TRUE,  

    "✓",  

    "x" 

)
This formula determines whether a quotation qualifies as "Standard" based on specific conditions:
  • If Promo, Contract, and Spot Rate fields all contain "x", the quotation is marked with ✓ (checkmark), indicating it meets the standard criteria.
  • If any of these fields do not contain "x", the quotation is marked with "x", meaning it does not qualify as standard.
12. Contract = If a contract shipment The formula: 
Contract = IF ( 

    NOT ISBLANK (quotations[tariffs.customer_id]),  

    "✓",  

    "x" 

)
This formula is used to track contracts in a quotation system.
  • Helps users quickly identify which quotations have an associated customer contract.
  • The checkmark (✓) and cross (x) provide an intuitive, visual representation instead of displaying raw data.
13. Spot Rate = If a spot rate shipment The formula: 
Spot Rate = IF ( 

    quotations[spotrate] = 1,  

    "✓",  

    "x" 

)
This formula helps users quickly identify whether a quotation is categorized as a spot rate.
  • The checkmark (✓) and cross (x) provide an easy-to-read visual representation.
14. DG = If there are dangerous goods in the shipment The formula: 
DG = IF ( 
quotations[hazardous_goods] = TRUE,  
 "✓",   "x" 
)
This formula helps users quickly identify whether a shipment or quotation involves dangerous (hazardous) goods. The checkmark (✓) and cross (x) provide a clear and intuitive visual representation.
15. CAO = If the shipment is cargo aircraft only The formula 
CAO = IF ( 

    quotations[cargo_only] = TRUE,  

    "✓",  

    "x" 

)
This formula helps users quickly identify whether a shipment is classified as Cargo Aircraft Only (CAO), ensuring compliance with aviation safety regulations. The checkmark (✓) and cross (x) provide a clear and intuitive visual representation.
Retrieved from "https://wiki-reporting.qms-portal.aero/index.php?title=Shipment_Detail_Report&oldid=95"