Jump to content

Shipment Detail Report

From PBI Reporting Wiki

Summary

TBC

Filters

Filter Description
TBC TBC

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