Jump to content

Shipment Detail Report

From PBI Reporting Wiki

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 = The lost reason that was set
5. Deleted Reasons = The deleted reason that was set
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. 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 = Chargeable weight of the shipment .
9. Commodity Type = The type of commodity of the shipment
10. Product Type = What type of product was selected The formula:

Product Type =

SWITCH ( 

   quotations [Product], 

   0, "Airport to Airport", 

   1, "Airport to Door", 

   2, "Door to Airport", 

   3, "Door to Door", 

   "Unknown"

)

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=90"