PBI Reporting Wiki - User contributions [en-gb]

Reporting DB

2026-05-29T15:59:28Z

David.weller:

= Raw Tables, Columns and Suggested Modelling =
To access the reporting database, you will need an account creating. Please follow the correct process and raise a ticket via the [https://helpdesk.dcbi.aero/ helpdesk]. They will provide you account, address and access.

The information below has the assumption that you understand databases, and have knowledge in PBI development and data management.

Currently we have only added the tables / columns for the information we know is required.

=== Every Table ===
Within every table there is 2 consistent columns, to save repeating data - these columns will be shared here.

'''Columns'''
{| class="wikitable"
!Column Name
!Description
!Notes
|-
|id
|Database id
|
|-
|portal
|Which QMS instance the data is from.
|The portal is the instance of the QMS system, not the entity, that is the site.
|}

=== Table: dim_quotations ===
'''Columns:'''
{| class="wikitable"
!Column Name
!Type
!Description
!Notes
|-
|quotation_id
|bigint
|quotation id within qms
|
|-
|hos_id
|bigint
|hos id associated with the job
|
|-
|site_id
|bigint
|the site (entity) that the job is saved for
|
|-
|booked_at
|datetime
|the date / time the job was booked
|
|-
|confirmed_at
|datetime
|the date / time the job was confirmed with airline
|
|-
|quoted_at
|datetime
|the date / time the job was quoted
|
|-
|pre_quoted_at
|datetime
|the date / time the job was pre_quoted
|
|-
|user_id
|bigint
|the user who created the job
|
|-
|group_type
|varchar
|the group type of shipment
|
|-
|booking_user_id
|bigint
|the id of the user who confirmed the job
|
|-
|lost_reason
|varchar
|the reason the job was lost
|
|-
|customer_id
|bigint
|the id of the customer
|what you use to merge with customer table
|-
|main_carrier_id
|bigint
|the id of the carrier for the job
|what you use to merge with the carrier table
|-
|source_created_at
|timestamp
|if the job came from an other system
|
|-
|deleted_at
|timestamp
|the date the job was deleted
|null if not deleted
|-
|shipper_status
|varchar
|n/a
|
|-
|lost_at
|timestamp
|the date the job was lost
|null if not lost
|-
|delete_reasons
|text
|the reason for deletion
|
|-
|chargeable_weight
|decimal
|the chargeable weight for the job
|
|-
|weight_unit
|varchar
|the weight unit used
|we support both kg and lb, so you need to convert the lb weights to kg. (looking into making it always converting into kg in the db)
|-
|gross_weight
|decimal
|the gross weight for the job
|
|-
|spotrate
|tinyint
|if the job is a spot rate or not
|
|-
|type
|varchar
|the type of job
|standard, consol, brand masters, ground handling, import, customs etc.
|-
|schedule_auto_update
|tinyint
|the automation schedule updater ran on this job
|
|-
|automation_created_count
|int
|how many times the automation ran on the job to sync into a carrier system
|used to show if it has ran at least once, and how many times after that
|-
|schedule_auto_amount
|decimal
|how many times the schedule automation ran on job
|
|-
|status
|varchar
|status of the job
|need to provide list of status, but not needed for the reports by WFC
|-
|booking_sync_state
|varchar
|the status of automation, if there's been an error
|
|-
|cargo_type
|json
|the cargo type of the job
|this can be multiple and links back to the cargo types table
|-
|house_airwaybill
|varchar
|the house airway bill on the job if there is one
|
|-
|product
|varchar
|the product of the job
|airport to airport, door to airport, airport to door, door to door, first mile only, final mile only.
|-
|origin
|varchar
|origin airport
|
|-
|destination
|varchar
|destination airport
|
|-
|master_airwaybill
|varchar
|the master airway bill on the job if there is one
|
|-
|hazardous_goods
|tinytint
|if the job is marked as dangerous goods
|
|-
|commodity_description
|text
|the commodity
|
|-
|cargo_only
|tinyint
|if job is cargo aircraft only
|
|-
|depart_date
|datetime
|the depart date of the job
|
|-
|created_at
|timestamp
|when the job was created
|
|-
|updated_at
|timestamp
|last time the job was updated
|
|}

=== Table: dim_cargo_type ===
'''Columns:'''
{| class="wikitable"
!Column Name
!Type
!Description
!Notes
|-
|cargo_type_id
|bigint
|cargo type id
|used when referenced in other tables.
|-
|description
|varchar
|description
|
|-
|tariff_service_code
|json
|service code that can be linked to the cargo type
|
|}

=== Table: dim_carriers ===
'''Columns:'''
{| class="wikitable"
!Column Name
!Type
!Description
!Notes
|-
|carrier_id
|bigint
|id for the carrier
|
|-
|name
|varchar
|name of the carrier / supplier
|
|-
|site_id
|bigint
|the site (entity) the carrier / supplier is on
|
|-
|is_gsa
|tinyint
|the carrier / supplier is gsa
|
|-
|parent_id
|bigint
|the parent carrier / supplier for this carrier
|this will be null if the carrier is a parent.
|-
|carrier_class
|tinyint
|supplier class
|airline, truck, sea freight etc..
|-
|carrier_subclass
|tinytint
|subclass for the carrier
|
|-
|created_at
|timestamp
|when the carrier was created
|
|-
|updated_at
|timestamp
|the last time the carrier was updated
|
|}
=== Table: dim_customers ===
'''Columns:'''
{| class="wikitable"
!Column Name
!Type
!Description
!Notes
|-
|customer_id
|bigint
|id for the customer
|
|-
|name
|varchar
|name of the customer
|
|-
|hos_code
|varchar
|hos id for the customer if they have one
|
|-
|account_number
|varchar
|account number for the customer
|
|-
|site_id
|bigint
|the site (entity) the customer is on
|
|-
|created_at
|timestamp
|when the customer was created
|
|-
|updated_at
|timestamp
|the last time the carrier was updated
|
|}
=== Table: dim_sites ===
'''Columns:'''
{| class="wikitable"
!Column Name
!Type
!Description
!Notes
|-
|site_id
|bigint
|id for the site (entity)
|
|-
|name
|varchar
|name of the site (entity)
|
|-
|created_at
|timestamp
|when the site (entity) was created
|
|-
|updated_at
|timestamp
|when the site (entity) was updated last
|
|}

=== Coming Soon ===
'''quotation_totals'''

Allow for a breakdown of cost and sell in customer invoice currency, entity currency and all exchange rates to EU at point of quote, book and confirmation for exact conversion numbers into EU.

= Helpers =
N/A - Will update as questions come through.

Reporting DB

2026-05-29T15:48:23Z

David.weller: /* Table: dim_quotations */

= Raw Tables, Columns and Suggested Modelling =
To access the reporting database, you will need an account creating. Please follow the correct process and raise a ticket via the [https://helpdesk.dcbi.aero/ helpdesk]. They will provide you account, address and access.

The information below has the assumption that you understand databases, and have knowledge in PBI development and data management.

Currently we have only added the tables / columns for the information we know is required.

=== Every Table ===
Within every table there is 2 consistent columns, to save repeating data - these columns will be shared here.

'''Columns'''
{| class="wikitable"
!Column Name
!Description
!Notes
|-
|id
|Database id
|
|-
|portal
|Which QMS instance the data is from.
|The portal is the instance of the QMS system, not the entity, that is the site.
|}

=== Table: dim_quotations ===
'''Columns:'''
{| class="wikitable"
!Column Name
!Type
!Description
!Notes
|-
|quotation_id
|bigint
|quotation id within qms
|
|-
|hos_id
|bigint
|hos id associated with the job
|
|-
|site_id
|bigint
|the site (entity) that the job is saved for
|
|-
|booked_at
|datetime
|the date / time the job was booked
|
|-
|confirmed_at
|datetime
|the date / time the job was confirmed with airline
|
|-
|quoted_at
|datetime
|the date / time the job was quoted
|
|-
|pre_quoted_at
|datetime
|the date / time the job was pre_quoted
|
|-
|user_id
|bigint
|the user who created the job
|
|-
|group_type
|varchar
|the group type of shipment
|
|-
|booking_user_id
|bigint
|the id of the user who confirmed the job
|
|-
|lost_reason
|varchar
|the reason the job was lost
|
|-
|customer_id
|bigint
|
|
|-
|main_carrier_id
|bigint
|
|
|-
|source_created_at
|timestamp
|
|
|-
|deleted_at
|timestamp
|
|
|-
|shipper_status
|varchar
|
|
|-
|lost_at
|timestamp
|
|
|-
|delete_reasons
|text
|
|
|-
|chargeable_weight
|decimal
|
|
|-
|weight_unit
|varchar
|
|
|-
|gross_weight
|decimal
|
|
|-
|spotrate
|tinyint
|
|
|-
|type
|varchar
|
|
|-
|schedule_auto_update
|tinyint
|
|
|-
|automation_created_count
|int
|
|
|-
|schedule_auto_amount
|decimal
|
|
|-
|status
|varchar
|
|
|-
|booking_sync_state
|varchar
|
|
|-
|cargo_type
|json
|
|
|-
|house_airwaybill
|varchar
|
|
|-
|product
|varchar
|
|
|-
|origin
|varchar
|
|
|-
|destination
|varchar
|
|
|-
|master_airwaybill
|varchar
|
|
|-
|hazardous_goods
|tinytint
|
|
|-
|commodity_description
|text
|
|
|-
|cargo_only
|tinyint
|
|
|-
|depart_date
|datetime
|
|
|-
|created_at
|timestamp
|
|
|-
|updated_at
|timestamp
|
|
|}

=== Table: dim_cargo_type ===
'''Columns:'''
{| class="wikitable"
!Column Name
!Type
!Description
!Notes
|-
|cargo_type_id
|bigint
|cargo type id
|used when referenced in other tables.
|-
|description
|varchar
|description
|
|-
|tariff_service_code
|json
|service code that can be linked to the cargo type
|
|}

=== Table: dim_carriers ===
'''Columns:'''
{| class="wikitable"
!Column Name
!Type
!Description
!Notes
|-
|carrier_id
|bigint
|id for the carrier
|
|-
|name
|varchar
|name of the carrier / supplier
|
|-
|site_id
|bigint
|the site (entity) the carrier / supplier is on
|
|-
|is_gsa
|tinyint
|the carrier / supplier is gsa
|
|-
|parent_id
|bigint
|the parent carrier / supplier for this carrier
|this will be null if the carrier is a parent.
|-
|carrier_class
|tinyint
|supplier class
|airline, truck, sea freight etc..
|-
|carrier_subclass
|tinytint
|subclass for the carrier
|
|-
|created_at
|timestamp
|when the carrier was created
|
|-
|updated_at
|timestamp
|the last time the carrier was updated
|
|}
=== Table: dim_customers ===
'''Columns:'''
{| class="wikitable"
!Column Name
!Type
!Description
!Notes
|-
|customer_id
|bigint
|id for the customer
|
|-
|name
|varchar
|name of the customer
|
|-
|hos_code
|varchar
|hos id for the customer if they have one
|
|-
|account_number
|varchar
|account number for the customer
|
|-
|site_id
|bigint
|the site (entity) the customer is on
|
|-
|created_at
|timestamp
|when the customer was created
|
|-
|updated_at
|timestamp
|the last time the carrier was updated
|
|}
=== Table: dim_sites ===
'''Columns:'''
{| class="wikitable"
!Column Name
!Type
!Description
!Notes
|-
|site_id
|bigint
|id for the site (entity)
|
|-
|name
|varchar
|name of the site (entity)
|
|-
|created_at
|timestamp
|when the site (entity) was created
|
|-
|updated_at
|timestamp
|when the site (entity) was updated last
|
|}

=== Coming Soon ===
'''quotation_totals'''

Allow for a breakdown of cost and sell in customer invoice currency, entity currency and all exchange rates to EU at point of quote, book and confirmation for exact conversion numbers into EU.

= Helpers =
N/A - Will update as questions come through.

Reporting DB

2026-05-29T15:45:10Z

David.weller: /* Raw Tables, Columns and Suggested Modelling */

Reporting DB

2026-05-29T15:44:05Z

David.weller:

Reporting DB

2026-05-29T14:38:57Z

David.weller: /* Raw Tables, Columns and Suggested Modelling */

Main Page

2026-05-29T14:26:35Z

David.weller: /* QMS: PBI Reporting */

== QMS: PBI Reporting ==

This wiki serves as the central hub for all information related to Power BI and reporting for QMS. It provides comprehensive documentation, including detailed user guides for all active reports, as well as guidance for users working with the data lake.

===Wiki Contents===

* [[Report User Guides]] – Instructions on how to interpret, and utilize each Power BI report effectively.
* [[Reporting DB]] - Breakdown of all tables and columns, with suggested hints for data modelling for external sources who want to model our reporting data.

* [[Error Reporting]] – Guidelines on how to report issues or discrepancies in existing reports to ensure data accuracy and reliability.

* [[New Requests]] – Instructions on how to submit requests for new reports, including required details to streamline the development process and how to request access to reports.

This wiki will be continuously updated to reflect new reports, feature enhancements, and any changes to reporting processes. If you have any suggestions for improvement, feel free to contribute or reach out to the appropriate support team.

Reporting DB

2026-05-29T14:26:06Z

David.weller:

Reporting DB

2026-05-29T14:24:38Z

David.weller:

Reporting DB

2026-05-29T11:54:31Z

David.weller: Created page with "= Raw Tables, Columns and Suggested Modelling = To access the reporting database, you will need an account creating. Please follow the correct process and raise a ticket via the [https://helpdesk.dcbi.aero/ helpdesk]. They will provide you account, address and access. The information below has the assumption that you understand databases, and have knowledge in PBI development and data management. === Every Table === Within every table there is 2 consistent columns, t..."

Main Page

2026-05-29T08:49:20Z

David.weller:

== QMS: PBI Reporting ==

This wiki serves as the central hub for all information related to Power BI and reporting for QMS. It provides comprehensive documentation, including detailed user guides for all active reports, as well as guidance for users working with the data lake.

===Wiki Contents===

* [[Report User Guides]] – Instructions on how to interpret, and utilize each Power BI report effectively.
* [[Reporting DB]] - Breakdown of all tables and columns, with suggested hints for data modelling.

* [[Error Reporting]] – Guidelines on how to report issues or discrepancies in existing reports to ensure data accuracy and reliability.

* [[New Requests]] – Instructions on how to submit requests for new reports, including required details to streamline the development process and how to request access to reports.

This wiki will be continuously updated to reflect new reports, feature enhancements, and any changes to reporting processes. If you have any suggestions for improvement, feel free to contribute or reach out to the appropriate support team.

Trouble Shooting & FAQs

2026-04-10T11:10:41Z

David.weller:

== FAQs ==
TBC

== Troubleshooting ==

Trouble Shooting & FAQs

2026-04-10T11:09:46Z

David.weller: Created page with "[To be update by Collins]"

[To be update by Collins]

Report User Guides

2026-04-10T11:09:23Z

David.weller: /* Row Level Security */

== Overview ==
This guide provides detailed instructions on how to navigate and understand various reports. Each report is broken down into individual pages, with a comprehensive explanation of the available filters, report elements, and the calculations used to generate the displayed data.

== User Guides ==

=== Row Level Security ===

* [[Row Level Security Setup Guide]]
* [[Trouble Shooting & FAQs]]

=== M & W Booking Summary Dashboard ===
* [[Quote & Bookings Summary Dashboard]]
* [[Quote & Bookings Summary Dashboard 2]]
* [[Teams Dashboard]]
* [[Shipment Detail Report]]
* [[Pre-quote Report]]
* [[Spot Rate Report]]

=== M & W Automation Summary Dashboard ===

* Automation Booking (Weekly)
* Automation Booking (Monthly)
* Automation Booking Insights (Weekly)
* Automation Booking Insights (Monthly)
* Automation Sync Error Report
* Automated Progress Scraper Report
* Automated Invoice Payable Report

=== M & W Email Summary Dashboard ===

* Received Emails Report
* Sent Emails Report
* Email Category Report

=== Global Solution Dashboard ===

* [[Overall YTD]]
* [[Detail (W M)|Detail (W+M)]]
* [[Quotes Overview (W M)|Quotes Overview (W+M)]]
* [[Quotes Summary (W M)|Quotes Summary (W+M)]]
* [[Quarterly]]
* [[Customer Overall TY]]
* [[Aldo Report]]

=== Weight, Yield & Revenue GSA Report ===

* [[Yield - Group Overview]]
* [[Yield Week-On-Week]]
* [[Weight v Revenue Week-On-Week]]
* [[Weight By Carrier]]
* [[Site Summary]]
* [[Job Finder]]

=== Master Carrier Report ===

* [[Revenue & Booking Overview]]
* [[Quotes]]
* [[Sales Performance]]

=== Datacair Non-Conformance Report ===

* [[NCR - Staff Logs]]
* [[NCR - DataCair Tickets]]
* [[Performance Page]]
* [[Root Cause Analysis (Resolved) Page]]
* [[Entity Breakdown Page]]

=== Datacair NCR Analysis ===

* [[NCR Report Analysis]]

=== HAE Customer Invoice Report ===

* [[ET Customer Invoice Details]]

=== Datacair EY Performance Report ===

* [[Datacair EY Performance Report - Dashboard]]
* [[Datacair EY Performance Report - Detail]]
* [[Datacair EY Performance Report - Airway Bills]]

=== TSA Report ===

* [[TSA Dashboard]]

== Additional Information ==
* Reports may be updated periodically, so refer to the latest version of the guide for the most accurate details.
* If you encounter any discrepancies or require additional assistance, please reach out to the [https://helpdesk.dcbi.aero/ helpdesk].

Row Level Security Setup Guide

2026-04-02T08:43:25Z

David.weller: Created blank page

TSA Dashboard

2026-04-02T08:43:00Z

David.weller: Created page with " == Summary == == Tables & Columns Included == {| class="wikitable" |+ !Database !Table !Columns |- | | | |} == Filters == {| class="wikitable" |+ ! style="width: 25%;" |Filter ! style="width: 75%;" |Description |- | | |} == Data Transformation Applied == [To be added] {| class="wikitable" |+ !Query !Calculations !Description |- | | | |} == Calculations == {| class="wikitable" ! style="width: 20%;" |Reference ! style="width: 40%;" |Calculation ! style="width: 40%;" |..."

== Summary ==

== Tables & Columns Included ==
{| class="wikitable"
|+
!Database
!Table
!Columns
|-
|
|
|
|}

== Filters ==
{| class="wikitable"
|+
! style="width: 25%;" |Filter
! style="width: 75%;" |Description
|-
|
|
|}

== Data Transformation Applied ==
[To be added]
{| class="wikitable"
|+
!Query
!Calculations
!Description
|-
|
|
|
|}

== Calculations ==
{| class="wikitable"
! style="width: 20%;" |Reference
! style="width: 40%;" |Calculation
! style="width: 40%;" |Description
|-
|
|
|
|}

Report User Guides

2026-04-02T08:42:57Z

David.weller: /* TSA Report */

== Overview ==
This guide provides detailed instructions on how to navigate and understand various reports. Each report is broken down into individual pages, with a comprehensive explanation of the available filters, report elements, and the calculations used to generate the displayed data.

== User Guides ==

=== Row Level Security ===

* [[Row Level Security Setup Guide]]

=== M & W Booking Summary Dashboard ===
* [[Quote & Bookings Summary Dashboard]]
* [[Quote & Bookings Summary Dashboard 2]]
* [[Teams Dashboard]]
* [[Shipment Detail Report]]
* [[Pre-quote Report]]
* [[Spot Rate Report]]

=== M & W Automation Summary Dashboard ===

* Automation Booking (Weekly)
* Automation Booking (Monthly)
* Automation Booking Insights (Weekly)
* Automation Booking Insights (Monthly)
* Automation Sync Error Report
* Automated Progress Scraper Report
* Automated Invoice Payable Report

=== M & W Email Summary Dashboard ===

* Received Emails Report
* Sent Emails Report
* Email Category Report

=== Global Solution Dashboard ===

* [[Overall YTD]]
* [[Detail (W M)|Detail (W+M)]]
* [[Quotes Overview (W M)|Quotes Overview (W+M)]]
* [[Quotes Summary (W M)|Quotes Summary (W+M)]]
* [[Quarterly]]
* [[Customer Overall TY]]
* [[Aldo Report]]

=== Weight, Yield & Revenue GSA Report ===

* [[Yield - Group Overview]]
* [[Yield Week-On-Week]]
* [[Weight v Revenue Week-On-Week]]
* [[Weight By Carrier]]
* [[Site Summary]]
* [[Job Finder]]

=== Master Carrier Report ===

* [[Revenue & Booking Overview]]
* [[Quotes]]
* [[Sales Performance]]

=== Datacair Non-Conformance Report ===

* [[NCR - Staff Logs]]
* [[NCR - DataCair Tickets]]
* [[Performance Page]]
* [[Root Cause Analysis (Resolved) Page]]
* [[Entity Breakdown Page]]

=== Datacair NCR Analysis ===

* [[NCR Report Analysis]]

=== HAE Customer Invoice Report ===

* [[ET Customer Invoice Details]]

=== Datacair EY Performance Report ===

* [[Datacair EY Performance Report - Dashboard]]
* [[Datacair EY Performance Report - Detail]]
* [[Datacair EY Performance Report - Airway Bills]]

=== TSA Report ===

* [[TSA Dashboard]]

== Additional Information ==
* Reports may be updated periodically, so refer to the latest version of the guide for the most accurate details.
* If you encounter any discrepancies or require additional assistance, please reach out to the [https://helpdesk.dcbi.aero/ helpdesk].

Report User Guides

2026-04-01T13:20:21Z

David.weller: /* Row Level Security */

== Overview ==
This guide provides detailed instructions on how to navigate and understand various reports. Each report is broken down into individual pages, with a comprehensive explanation of the available filters, report elements, and the calculations used to generate the displayed data.

== User Guides ==

=== Row Level Security ===

* [[Row Level Security Setup Guide]]

=== M & W Booking Summary Dashboard ===
* [[Quote & Bookings Summary Dashboard]]
* [[Quote & Bookings Summary Dashboard 2]]
* [[Teams Dashboard]]
* [[Shipment Detail Report]]
* [[Pre-quote Report]]
* [[Spot Rate Report]]

=== M & W Automation Summary Dashboard ===

* Automation Booking (Weekly)
* Automation Booking (Monthly)
* Automation Booking Insights (Weekly)
* Automation Booking Insights (Monthly)
* Automation Sync Error Report
* Automated Progress Scraper Report
* Automated Invoice Payable Report

=== M & W Email Summary Dashboard ===

* Received Emails Report
* Sent Emails Report
* Email Category Report

=== Global Solution Dashboard ===

* [[Overall YTD]]
* [[Detail (W M)|Detail (W+M)]]
* [[Quotes Overview (W M)|Quotes Overview (W+M)]]
* [[Quotes Summary (W M)|Quotes Summary (W+M)]]
* [[Quarterly]]
* [[Customer Overall TY]]
* [[Aldo Report]]

=== Weight, Yield & Revenue GSA Report ===

* [[Yield - Group Overview]]
* [[Yield Week-On-Week]]
* [[Weight v Revenue Week-On-Week]]
* [[Weight By Carrier]]
* [[Site Summary]]
* [[Job Finder]]

=== Master Carrier Report ===

* [[Revenue & Booking Overview]]
* [[Quotes]]
* [[Sales Performance]]

=== Datacair Non-Conformance Report ===

* [[NCR - Staff Logs]]
* [[NCR - DataCair Tickets]]
* [[Performance Page]]
* [[Root Cause Analysis (Resolved) Page]]
* [[Entity Breakdown Page]]

=== Datacair NCR Analysis ===

* [[NCR Report Analysis]]

=== HAE Customer Invoice Report ===

* [[ET Customer Invoice Details]]

=== Datacair EY Performance Report ===

* [[Datacair EY Performance Report - Dashboard]]
* [[Datacair EY Performance Report - Detail]]
* [[Datacair EY Performance Report - Airway Bills]]

=== TSA Report ===

* [[Dashboard]]

== Additional Information ==
* Reports may be updated periodically, so refer to the latest version of the guide for the most accurate details.
* If you encounter any discrepancies or require additional assistance, please reach out to the [https://helpdesk.dcbi.aero/ helpdesk].

Report User Guides

2026-04-01T13:19:57Z

David.weller: /* User Guides */

== Overview ==
This guide provides detailed instructions on how to navigate and understand various reports. Each report is broken down into individual pages, with a comprehensive explanation of the available filters, report elements, and the calculations used to generate the displayed data.

== User Guides ==

=== Row Level Security ===

* Row Level Security Setup Guide

=== M & W Booking Summary Dashboard ===
* [[Quote & Bookings Summary Dashboard]]
* [[Quote & Bookings Summary Dashboard 2]]
* [[Teams Dashboard]]
* [[Shipment Detail Report]]
* [[Pre-quote Report]]
* [[Spot Rate Report]]

=== M & W Automation Summary Dashboard ===

* Automation Booking (Weekly)
* Automation Booking (Monthly)
* Automation Booking Insights (Weekly)
* Automation Booking Insights (Monthly)
* Automation Sync Error Report
* Automated Progress Scraper Report
* Automated Invoice Payable Report

=== M & W Email Summary Dashboard ===

* Received Emails Report
* Sent Emails Report
* Email Category Report

=== Global Solution Dashboard ===

* [[Overall YTD]]
* [[Detail (W M)|Detail (W+M)]]
* [[Quotes Overview (W M)|Quotes Overview (W+M)]]
* [[Quotes Summary (W M)|Quotes Summary (W+M)]]
* [[Quarterly]]
* [[Customer Overall TY]]
* [[Aldo Report]]

=== Weight, Yield & Revenue GSA Report ===

* [[Yield - Group Overview]]
* [[Yield Week-On-Week]]
* [[Weight v Revenue Week-On-Week]]
* [[Weight By Carrier]]
* [[Site Summary]]
* [[Job Finder]]

=== Master Carrier Report ===

* [[Revenue & Booking Overview]]
* [[Quotes]]
* [[Sales Performance]]

=== Datacair Non-Conformance Report ===

* [[NCR - Staff Logs]]
* [[NCR - DataCair Tickets]]
* [[Performance Page]]
* [[Root Cause Analysis (Resolved) Page]]
* [[Entity Breakdown Page]]

=== Datacair NCR Analysis ===

* [[NCR Report Analysis]]

=== HAE Customer Invoice Report ===

* [[ET Customer Invoice Details]]

=== Datacair EY Performance Report ===

* [[Datacair EY Performance Report - Dashboard]]
* [[Datacair EY Performance Report - Detail]]
* [[Datacair EY Performance Report - Airway Bills]]

=== TSA Report ===

* Dashboard

== Additional Information ==
* Reports may be updated periodically, so refer to the latest version of the guide for the most accurate details.
* If you encounter any discrepancies or require additional assistance, please reach out to the [https://helpdesk.dcbi.aero/ helpdesk].

Datacair EY Performance Report - Airway Bills

2026-01-29T10:58:38Z

David.weller: Created page with "== Summary == TBC == Filters == {| class="wikitable" |+ !Filter !Description |- |TBC |TBC |- |} == Data Transformation Applied == {| class="wikitable" |+ !Query !Transformations |- |TBC |TBC |- |} == Calculations == {| class="wikitable" |+ !Columns !Calculation !Description |- |TBC |TBC |TBC |- |}"

== Summary ==

TBC

== Filters ==
{| class="wikitable"
|+
!Filter
!Description
|-
|TBC
|TBC
|-
|}

== Data Transformation Applied ==
{| class="wikitable"
|+
!Query
!Transformations
|-
|TBC
|TBC
|-
|}

== Calculations ==
{| class="wikitable"
|+
!Columns
!Calculation
!Description
|-
|TBC
|TBC
|TBC
|-
|}

Datacair EY Performance Report - Detail

2026-01-29T10:58:26Z

Report User Guides

2026-01-29T10:26:38Z

David.weller:

== Overview ==
This guide provides detailed instructions on how to navigate and understand various reports. Each report is broken down into individual pages, with a comprehensive explanation of the available filters, report elements, and the calculations used to generate the displayed data.

== User Guides ==
=== M & W Booking Summary Dashboard ===
* [[Quote & Bookings Summary Dashboard]]
* [[Quote & Bookings Summary Dashboard 2]]
* [[Teams Dashboard]]
* [[Shipment Detail Report]]
* [[Pre-quote Report]]
* [[Spot Rate Report]]

=== M & W Automation Summary Dashboard ===

* Automation Booking (Weekly)
* Automation Booking (Monthly)
* Automation Booking Insights (Weekly)
* Automation Booking Insights (Monthly)
* Automation Sync Error Report
* Automated Progress Scraper Report
* Automated Invoice Payable Report

=== M & W Email Summary Dashboard ===

* Received Emails Report
* Sent Emails Report
* Email Category Report

=== Global Solution Dashboard ===

* [[Overall YTD]]
* [[Detail (W M)|Detail (W+M)]]
* [[Quotes Overview (W M)|Quotes Overview (W+M)]]
* [[Quotes Summary (W M)|Quotes Summary (W+M)]]
* [[Quarterly]]
* [[Customer Overall TY]]
* [[Aldo Report]]

=== Weight, Yield & Revenue GSA Report ===

* [[Yield - Group Overview]]
* [[Yield Week-On-Week]]
* [[Weight v Revenue Week-On-Week]]
* [[Weight By Carrier]]
* [[Site Summary]]
* [[Job Finder]]

=== Master Carrier Report ===

* [[Revenue & Booking Overview]]
* [[Quotes]]
* [[Sales Performance]]

=== Datacair Non-Conformance Report ===

* [[NCR - Staff Logs]]
* [[NCR - DataCair Tickets]]
* [[Performance Page]]
* [[Root Cause Analysis (Resolved) Page]]
* [[Entity Breakdown Page]]

=== Datacair NCR Analysis ===

* [[NCR Report Analysis]]

=== HAE Customer Invoice Report ===

* [[ET Customer Invoice Details]]

=== Datacair EY Performance Report ===

* [[Datacair EY Performance Report - Dashboard]]
* [[Datacair EY Performance Report - Detail]]
* [[Datacair EY Performance Report - Airway Bills]]

== Additional Information ==
* Reports may be updated periodically, so refer to the latest version of the guide for the most accurate details.
* If you encounter any discrepancies or require additional assistance, please reach out to the [https://helpdesk.dcbi.aero/ helpdesk].

Datacair EY Performance Report - Dashboard

2026-01-29T10:25:44Z

David.weller: Created page with "== Summary == This report provides a weekly overview of operational performance by tracking AWBs, execution status, pending workload, customer issues (CAMS), and closure rates. Using simple metrics, filters by station and date, and clear percentage changes, it helps teams quickly identify performance trends, growth areas, and operational risks to support timely and data-driven decisions. == Filters == {| class="wikitable" |+ !Filter !Description |- |Station |Filters th..."

== Summary ==

This report provides a weekly overview of operational performance by tracking AWBs, execution status, pending workload, customer issues (CAMS), and closure rates. Using simple metrics, filters by station and date, and clear percentage changes, it helps teams quickly identify performance trends, growth areas, and operational risks to support timely and data-driven decisions.

== Filters ==
{| class="wikitable"
|+
!Filter
!Description
|-
|Station
|Filters the report to show data for a specific operational station
|-
|Status
|Filters records based on issue resolution status:
'''Resolved''' (issues that have been closed) and '''Unresolved''' (issues still open and pending action).
|-
|Date
|Filters the data to a selected date range for time-based analysis
|}

== Data Transformation Applied ==
{| class="wikitable"
|+
!Query
!Transformations
|-
|1. EY Performance Report
|Cleaned the dataset by correcting column data types (dates, numbers, text) to ensure accurate calculations and filtering.
|-
|2.Pending AWBs
|Standardized data types and prepared the table for relationship mapping with Stations.
|-
|3.Reopened AWBs
|Corrected data types and validated station identifiers for consistent joins.
|-
|4.Stations
|Used as a master reference table to provide station details for all operational datasets.
|-
|5.Calendar
|Created and linked to date fields to support time-based analysis (weekly and monthly trends).
|}

== Calculations ==
{| class="wikitable"
|+
!Columns
!Calculation
!Description
|-
|1. Total AWBs
|Sum of all AWBs
|Total number of Air Waybills created in the selected period
|-
|2. Total Executed AWBs
|Sum of executed AWBs
|Number of AWBs that were successfully processed or completed
|-
|3.Total Pending AWBs
|Sum of pending AWBs
|Number of AWBs still awaiting execution
|-
|4.Total CAMS Raised
|Sum of CAMS cases
|Total number of customer or operational issues raised
|-
|5.Total CR
|Average of Completion Rate
|Overall closure rate showing operational efficiency
|-
|6.Total CR (EY)
|Average of Completion Rate EY Fault Removed
|Closure rate based on EY-specific business rules
|}

Report User Guides

2026-01-29T10:25:08Z

David.weller: /* Datacair EY Performance Report */

== Overview ==
This guide provides detailed instructions on how to navigate and understand various reports. Each report is broken down into individual pages, with a comprehensive explanation of the available filters, report elements, and the calculations used to generate the displayed data.

== User Guides ==
=== M & W Booking Summary Dashboard ===
* [[Quote & Bookings Summary Dashboard]]
* [[Quote & Bookings Summary Dashboard 2]]
* [[Teams Dashboard]]
* [[Shipment Detail Report]]
* [[Pre-quote Report]]
* [[Spot Rate Report]]

=== M & W Automation Summary Dashboard ===

* Automation Booking (Weekly)
* Automation Booking (Monthly)
* Automation Booking Insights (Weekly)
* Automation Booking Insights (Monthly)
* Automation Sync Error Report
* Automated Progress Scraper Report
* Automated Invoice Payable Report

=== M & W Email Summary Dashboard ===

* Received Emails Report
* Sent Emails Report
* Email Category Report

=== Global Solution Dashboard ===

* [[Overall YTD]]
* [[Detail (W M)|Detail (W+M)]]
* [[Quotes Overview (W M)|Quotes Overview (W+M)]]
* [[Quotes Summary (W M)|Quotes Summary (W+M)]]
* [[Quarterly]]
* [[Customer Overall TY]]
* [[Aldo Report]]

=== Weight, Yield & Revenue GSA Report ===

* [[Yield - Group Overview]]
* [[Yield Week-On-Week]]
* [[Weight v Revenue Week-On-Week]]
* [[Weight By Carrier]]
* [[Site Summary]]
* [[Job Finder]]

=== Master Carrier Report ===

* [[Revenue & Booking Overview]]
* [[Quotes]]
* [[Sales Performance]]

=== Datacair Non-Conformance Report ===

* [[NCR - Staff Logs]]
* [[NCR - DataCair Tickets]]
* [[Performance Page]]
* [[Root Cause Analysis (Resolved) Page]]
* [[Entity Breakdown Page]]

=== Datacair NCR Analysis ===

* [[NCR Report Analysis]]

=== HAE Customer Invoice Report ===

* [[ET Customer Invoice Details]]

=== Datacair EY Performance Report ===

* [[Datacair EY Performance Report]]
* [[Datacair EY Performance Report - Dashboard]]
* [[Datacair EY Performance Report - Detail]]
* [[Datacair EY Performance Report - Airway Bills]]

== Additional Information ==
* Reports may be updated periodically, so refer to the latest version of the guide for the most accurate details.
* If you encounter any discrepancies or require additional assistance, please reach out to the [https://helpdesk.dcbi.aero/ helpdesk].

NCR - DataCair Tickets

2026-01-26T13:28:47Z

David.weller: Created page with "== Summary == TBC == Filters == {| class="wikitable" |+ !Filter !Description |- |Example filter |Filter description |} == Data Transformation Applied == {| class="wikitable" |+ !Query !Transformations |- |Example Query | Example transformations |} == Calculations == {| class="wikitable" |+ !Columns !Calculation !Description |- |Example |Example calc |Example description |}"

== Summary ==
TBC

== Filters ==
{| class="wikitable"
|+
!Filter
!Description
|-
|Example filter
|Filter description
|}

== Data Transformation Applied ==
{| class="wikitable"
|+
!Query
!Transformations
|-
|Example Query
|
Example transformations
|}

== Calculations ==
{| class="wikitable"
|+
!Columns
!Calculation
!Description
|-
|Example
|Example calc
|Example description
|}

Report User Guides

2026-01-26T13:25:52Z

David.weller: /* Datacair Non-Conformance Report */

== Overview ==
This guide provides detailed instructions on how to navigate and understand various reports. Each report is broken down into individual pages, with a comprehensive explanation of the available filters, report elements, and the calculations used to generate the displayed data.

== User Guides ==
=== M & W Booking Summary Dashboard ===
* [[Quote & Bookings Summary Dashboard]]
* [[Quote & Bookings Summary Dashboard 2]]
* [[Teams Dashboard]]
* [[Shipment Detail Report]]
* [[Pre-quote Report]]
* [[Spot Rate Report]]

=== M & W Automation Summary Dashboard ===

* Automation Booking (Weekly)
* Automation Booking (Monthly)
* Automation Booking Insights (Weekly)
* Automation Booking Insights (Monthly)
* Automation Sync Error Report
* Automated Progress Scraper Report
* Automated Invoice Payable Report

=== M & W Email Summary Dashboard ===

* Received Emails Report
* Sent Emails Report
* Email Category Report

=== Global Solution Dashboard ===

* [[Overall YTD]]
* [[Detail (W M)|Detail (W+M)]]
* [[Quotes Overview (W M)|Quotes Overview (W+M)]]
* [[Quotes Summary (W M)|Quotes Summary (W+M)]]
* [[Quarterly]]
* [[Customer Overall TY]]
* [[Aldo Report]]

=== Weight, Yield & Revenue GSA Report ===

* [[Yield - Group Overview]]
* [[Yield Week-On-Week]]
* [[Weight v Revenue Week-On-Week]]
* [[Weight By Carrier]]
* [[Site Summary]]
* [[Job Finder]]

=== Master Carrier Report ===

* [[Revenue & Booking Overview]]
* [[Quotes]]
* [[Sales Performance]]

=== Datacair Non-Conformance Report ===

* [[NCR - Staff Logs]]
* [[NCR - DataCair Tickets]]
* [[Performance Page]]
* [[Root Cause Analysis (Resolved) Page]]
* [[Entity Breakdown Page]]

=== Datacair NCR Analysis ===

* [[NCR Report Analysis]]

=== HAE Customer Invoice Report ===

* [[ET Customer Invoice Details]]

=== Datacair EY Performance Report ===

* [[Datacair EY Performance Report]]

== Additional Information ==
* Reports may be updated periodically, so refer to the latest version of the guide for the most accurate details.
* If you encounter any discrepancies or require additional assistance, please reach out to the [https://helpdesk.dcbi.aero/ helpdesk].

NCR - Staff Logs

2026-01-26T13:25:12Z

David.weller: Created page with "== Summary == This page forms part of the Non-Conformance Report for Datacair. It consolidates data from both the NCR Database and the SupportPal Non-Conformance Tickets. The report provides a unified view of non-conformance data across entities, combining records from both sources. It offers a high-level overview of staff attendance and work rates, enabling quick and easy analysis across all entities. == Filters == {| class="wikitable" |+ !Filter !Description |- |Date..."

== Summary ==
This page forms part of the Non-Conformance Report for Datacair. It consolidates data from both the NCR Database and the SupportPal Non-Conformance Tickets.

The report provides a unified view of non-conformance data across entities, combining records from both sources. It offers a high-level overview of staff attendance and work rates, enabling quick and easy analysis across all entities.

== Filters ==
{| class="wikitable"
|+
!Filter
!Description
|-
|Date
|Filters data by there creation dates
|-
|Entity
|Filters data by there entities
|}

== Data Transformation Applied ==
{| class="wikitable"
|+
!Query
!Transformations
|-
|Datacair tickects table API link =<nowiki>https://customer-service.datacair.com/api/ticket/ticket?department=4&limit=1000000000</nowiki>
|
* '''API Endpoint''': <code><nowiki>https://customer-service.datacair.com/api/ticket/ticket?department=4&limit=1000000000</nowiki></code>

* '''Purpose''': Retrieves comprehensive ticket information for department ID '''4''', including metadata and key lifecycle fields. The <code>limit</code> parameter is explicitly set to a very high value to ensure all relevant records are returned in a single call, bypassing default pagination limits.
|-
|SharePoint Tables =https://dcbiaero.sharepoint.com/sites/NCRDatabase/
|'''Purpose:''' Connects to the NCR Database stored on SharePoint and retrieves all available tables and lists.
'''Details:''' Uses SharePoint API version 2.0 and loads all views in the site. Enables access to structured NCR (Non-Conformance Report) data for further transformation and reporting in Power BI.

'''Usage:''' This source is used to supplement other datasets with NCR-related metadata or additional ticket details not available in the primary API.
|}

== Calculations ==
[[File:Report-ncrpage-overview.png|none|thumb|800x800px]]
{| class="wikitable"
|+
!Columns
!Calculation
!Description
|-
|1 - Total Transactions
|Total Transactions =
COALESCE([Number of Bookings], 0) +

COALESCE([Number of Email], 0) +

COALESCE([Number of FDC], 0) +

COALESCE([Number of Pre_Quotes], 0)
|This represents the total number of all transaction types completed. It is calculated by adding together the number of bookings, emails, FDC , and pre-quotes. If any of these values is missing, it is treated as zero to ensure accurate counting.
|-
|2 - Total Transactions Affected
|Total Transactions Affected = SUM('NCR_page_datacaire_tickets'[39_Trasanction_Affected]) +0
|This represents the total number of transactions impacted, calculated by summing the values in the '39_Transaction_Affected' column from the 'NCR_page_datacaire_tickets' dataset. A "+0" ensures that the result is always a number, even if the dataset is empty or no transactions are affected.
|-
|3 - Total NCR's
|Total NCR's =
COALESCE(DISTINCTCOUNT('NCR_page_datacaire_tickets'[id]), 0)
|This represents the total number of unique Non-Conformance Reports (NCRs). It is calculated by counting the distinct values in the 'id' column of the 'NCR_page_datacaire_tickets' dataset. If no unique IDs are found, the result is set to zero to ensure an accurate count.
|-
|4 - Staffed Rostered
|Staffed Rostered = COALESCE(SUM('NCR Database Combined'[Staff Rostered]), 0)
|This represents the total number of staff scheduled to work, calculated by summing the values in the 'Staff Rostered' column of the 'NCR Database Combined' dataset. If no data is available or the sum is empty, the result is set to zero for accuracy.
|-
|5 - Staff Attended
|Staff Attended = COALESCE(SUM('NCR Database Combined'[Staff Attended]), 0)
|This represents the total number of staff who actually attended work, calculated by summing the values in the 'Staff Attended' column of the 'NCR Database Combined' dataset. If no data is available or the sum is empty, the result is set to zero to ensure an accurate count.
|-
|6 - Accuracy
|Accuracy = IFERROR(
1 - DIVIDE(

CALCULATE([Total Transactions Affected]),

CALCULATE([Total Transactions])

),

0

)
|This measures the accuracy of transactions by calculating the proportion of transactions that were not affected. It is determined by dividing the Total Transactions Affected by the Total Transactions and subtracting the result from 1. If there’s an error (e.g., division by zero), the result is set to zero to ensure a valid output.
|-
|7 - Attendance %
|Attendance % =
DIVIDE(

[Staff Attended],

[Staffed Rostered],

0

)
|This represents the percentage of rostered staff who actually attended work. It is calculated by dividing the total number of Staff Attended by the total number of Staffed Rostered. If the calculation cannot be performed (e.g., if Staffed Rostered is zero), the result is set to zero to ensure a valid output
|-
|8 - Accuracy by Date
|Accuracy = IFERROR(
1 - DIVIDE(

CALCULATE([Total Transactions Affected]),

CALCULATE([Total Transactions])

),

0

)
|This chart visually displays the Accuracy percentage over time, with each point on the line representing the Accuracy for a specific date. Accuracy is calculated as 1 minus the ratio of Total Transactions Affected to Total Transactions, expressed as a percentage. If the calculation encounters an error (e.g., no transactions), it defaults to 0%. The x-axis shows the dates, while the y-axis shows the Accuracy percentage (0% to 100%). This chart helps users track how transaction accuracy trends over time, identifying patterns or fluctuations in performance.
|-
|9 - Staff by Date
|Staffed Rostered = COALESCE(SUM('NCR Database Combined'[Staff Rostered]), 0)
Staff Sickness = COALESCE(SUM('NCR Database Combined'[Staff Sickness]), 0)

Staff on Leave = COALESCE(SUM('NCR Database Combined'[Staff on Leave]), 0)

Staff Attended = COALESCE(SUM('NCR Database Combined'[Staff Attended]), 0)"
|This line chart visualizes four key staff metrics over time: '''Staffed Rostered''', '''Staff Sickness''', '''Staff on Leave''', and '''Staff Attended'''.

* '''Staffed Rostered''': The total number of staff scheduled to work, calculated by summing the 'Staff Rostered' column in the 'NCR Database Combined' dataset, defaulting to 0 if no data is available.
* '''Staff Sickness''': The total number of staff absent due to sickness, calculated by summing the 'Staff Sickness' column, defaulting to 0 if no data exists.
* '''Staff on Leave''': The total number of staff on leave, calculated by summing the 'Staff on Leave' column, defaulting to 0 if no data is present.
* '''Staff Attended''': The total number of staff who actually attended work, calculated by summing the 'Staff Attended' column, defaulting to 0 if no data is available.
|-
|10 - Total Transactions by Month
|Total Transactions =
COALESCE([Number of Bookings], 0) +

COALESCE([Number of Email], 0) +

COALESCE([Number of FDC], 0) +

COALESCE([Number of Pre_Quotes], 0)
|This column chart displays the Total Transactions for each month, providing a clear view of transaction volume over time. Total Transactions is calculated by summing the number of bookings, emails, FDC (Final Delivery Confirmations), and pre-quotes, with each component defaulting to 0 if no data is available. The x-axis lists the months, while the y-axis shows the total count of transactions. Each column represents the combined transaction activity for a given month, helping users identify trends, peak periods, or fluctuations in transaction volume.
|-
|11 -
|i. Date
ii. Staffed Rostered
iii. Staff Attended
iv. Staff on Leave
v. Staff Sickness
vi. Number of Bookings
vii. Number of Emails
viii. Number of Prequotes
ix. Number of FDC
x. Total NCRs
xi.Total Transactions
xii. Total Transactions Affected
xiii. Accuracy
|The table presents a comprehensive overview of key metrics related to transactions and staffing, visualized in a clear, tabular format for easy analysis
|}

Report User Guides

2026-01-26T13:24:55Z

David.weller: /* Datacair Non-Conformance Report */

== Overview ==
This guide provides detailed instructions on how to navigate and understand various reports. Each report is broken down into individual pages, with a comprehensive explanation of the available filters, report elements, and the calculations used to generate the displayed data.

== User Guides ==
=== M & W Booking Summary Dashboard ===
* [[Quote & Bookings Summary Dashboard]]
* [[Quote & Bookings Summary Dashboard 2]]
* [[Teams Dashboard]]
* [[Shipment Detail Report]]
* [[Pre-quote Report]]
* [[Spot Rate Report]]

=== M & W Automation Summary Dashboard ===

* Automation Booking (Weekly)
* Automation Booking (Monthly)
* Automation Booking Insights (Weekly)
* Automation Booking Insights (Monthly)
* Automation Sync Error Report
* Automated Progress Scraper Report
* Automated Invoice Payable Report

=== M & W Email Summary Dashboard ===

* Received Emails Report
* Sent Emails Report
* Email Category Report

=== Global Solution Dashboard ===

* [[Overall YTD]]
* [[Detail (W M)|Detail (W+M)]]
* [[Quotes Overview (W M)|Quotes Overview (W+M)]]
* [[Quotes Summary (W M)|Quotes Summary (W+M)]]
* [[Quarterly]]
* [[Customer Overall TY]]
* [[Aldo Report]]

=== Weight, Yield & Revenue GSA Report ===

* [[Yield - Group Overview]]
* [[Yield Week-On-Week]]
* [[Weight v Revenue Week-On-Week]]
* [[Weight By Carrier]]
* [[Site Summary]]
* [[Job Finder]]

=== Master Carrier Report ===

* [[Revenue & Booking Overview]]
* [[Quotes]]
* [[Sales Performance]]

=== Datacair Non-Conformance Report ===

* [[NCR Page]]
* [[NCR - Staff Logs]]
* [[NCR - DataCair Tickets]]
* [[Performance Page]]
* [[Root Cause Analysis (Resolved) Page]]
* [[Entity Breakdown Page]]

=== Datacair NCR Analysis ===

* [[NCR Report Analysis]]

=== HAE Customer Invoice Report ===

* [[ET Customer Invoice Details]]

=== Datacair EY Performance Report ===

* [[Datacair EY Performance Report]]

== Additional Information ==
* Reports may be updated periodically, so refer to the latest version of the guide for the most accurate details.
* If you encounter any discrepancies or require additional assistance, please reach out to the [https://helpdesk.dcbi.aero/ helpdesk].

Report User Guides

2026-01-20T15:29:24Z

David.weller:

== Overview ==
This guide provides detailed instructions on how to navigate and understand various reports. Each report is broken down into individual pages, with a comprehensive explanation of the available filters, report elements, and the calculations used to generate the displayed data.

== User Guides ==
=== M & W Booking Summary Dashboard ===
* [[Quote & Bookings Summary Dashboard]]
* [[Quote & Bookings Summary Dashboard 2]]
* [[Teams Dashboard]]
* [[Shipment Detail Report]]
* [[Pre-quote Report]]
* [[Spot Rate Report]]

=== M & W Automation Summary Dashboard ===

* Automation Booking (Weekly)
* Automation Booking (Monthly)
* Automation Booking Insights (Weekly)
* Automation Booking Insights (Monthly)
* Automation Sync Error Report
* Automated Progress Scraper Report
* Automated Invoice Payable Report

=== M & W Email Summary Dashboard ===

* Received Emails Report
* Sent Emails Report
* Email Category Report

=== Global Solution Dashboard ===

* [[Overall YTD]]
* [[Detail (W M)|Detail (W+M)]]
* [[Quotes Overview (W M)|Quotes Overview (W+M)]]
* [[Quotes Summary (W M)|Quotes Summary (W+M)]]
* [[Quarterly]]
* [[Customer Overall TY]]
* [[Aldo Report]]

=== Weight, Yield & Revenue GSA Report ===

* [[Yield - Group Overview]]
* [[Yield Week-On-Week]]
* [[Weight v Revenue Week-On-Week]]
* [[Weight By Carrier]]
* [[Site Summary]]
* [[Job Finder]]

=== Master Carrier Report ===

* [[Revenue & Booking Overview]]
* [[Quotes]]
* [[Sales Performance]]

=== Datacair Non-Conformance Report ===

* [[NCR Page]]
* [[Performance Page]]
* [[Root Cause Analysis (Resolved) Page]]
* [[Entity Breakdown Page]]

=== Datacair NCR Analysis ===

* [[NCR Report Analysis]]

=== HAE Customer Invoice Report ===

* [[ET Customer Invoice Details]]

=== Datacair EY Performance Report ===

* [[Datacair EY Performance Report]]

== Additional Information ==
* Reports may be updated periodically, so refer to the latest version of the guide for the most accurate details.
* If you encounter any discrepancies or require additional assistance, please reach out to the [https://helpdesk.dcbi.aero/ helpdesk].

Aldo Report

2025-11-21T13:26:34Z

David.weller: Created page with "==Summary== ==Filters== {| class="wikitable" |+ ! style="width: 25%;" | Filter !! style="width: 75%;" | Description |- | TBC|| TBC |} == Data Transformation Applied == [To be added] {| class="wikitable" |+ !Query !Calculations !Description |- | | | |} ==Calculations== {| class="wikitable" |- ! style="width: 20%;" | Reference !! style="width: 40%;" | Calculation !! style="width: 40%;" | Description |- | TBC ||<pre>TBC</pre>|| TBC |}"

==Summary==

==Filters==
{| class="wikitable"
|+
! style="width: 25%;" | Filter !! style="width: 75%;" | Description
|-
| TBC|| TBC
|}

== Data Transformation Applied ==
[To be added]
{| class="wikitable"
|+
!Query
!Calculations
!Description
|-
|
|
|
|}

==Calculations==

{| class="wikitable"
|-
! style="width: 20%;" | Reference !! style="width: 40%;" | Calculation !! style="width: 40%;" | Description
|-
| TBC ||<pre>TBC</pre>|| TBC
|}

Customer Overall TY

2025-11-21T13:26:26Z

Quarterly

2025-11-21T13:26:14Z

Quotes Summary (W+M)

2025-11-21T13:26:06Z

Quotes Overview (W+M)

2025-11-21T13:25:56Z

Detail (W+M)

2025-11-21T13:25:45Z

Report User Guides

2025-11-21T13:25:32Z

David.weller: /* Global Solution Dashboard */

== Overview ==
This guide provides detailed instructions on how to navigate and understand various reports. Each report is broken down into individual pages, with a comprehensive explanation of the available filters, report elements, and the calculations used to generate the displayed data.

== User Guides ==
=== M & W Booking Summary Dashboard ===
* [[Quote & Bookings Summary Dashboard]]
* [[Quote & Bookings Summary Dashboard 2]]
* [[Teams Dashboard]]
* [[Shipment Detail Report]]
* [[Pre-quote Report]]
* [[Spot Rate Report]]

=== M & W Automation Summary Dashboard ===

* Automation Booking (Weekly)
* Automation Booking (Monthly)
* Automation Booking Insights (Weekly)
* Automation Booking Insights (Monthly)
* Automation Sync Error Report
* Automated Progress Scraper Report
* Automated Invoice Payable Report

=== M & W Email Summary Dashboard ===

* Received Emails Report
* Sent Emails Report
* Email Category Report

=== Global Solution Dashboard ===

* [[Overall YTD]]
* [[Detail (W+M)]]
* [[Quotes Overview (W+M)]]
* [[Quotes Summary (W+M)]]
* [[Quarterly]]
* [[Customer Overall TY]]
* [[Aldo Report]]

=== Weight, Yield & Revenue GSA Report ===

* [[Yield - Group Overview]]
* [[Yield Week-On-Week]]
* [[Weight v Revenue Week-On-Week]]
* [[Weight By Carrier]]
* [[Site Summary]]
* [[Job Finder]]

=== Master Carrier Report ===

* [[Revenue & Booking Overview]]
* [[Quotes]]
* [[Sales Performance]]

=== Datacair Non-Conformance Report ===

* [[NCR Page]]
* [[Performance Page]]
* [[Root Cause Analysis (Resolved) Page]]
* [[Entity Breakdown Page]]

=== Datacair NCR Analysis ===

* [[NCR Report Analysis]]

=== HAE Customer Invoice Report ===

* [[ET Customer Invoice Details]]

== Additional Information ==
* Reports may be updated periodically, so refer to the latest version of the guide for the most accurate details.
* If you encounter any discrepancies or require additional assistance, please reach out to the [https://helpdesk.dcbi.aero/ helpdesk].

Overall YTD

2025-11-21T13:24:43Z

Report User Guides

2025-11-21T13:23:04Z

David.weller: /* Global Solution Dashboard */

== Overview ==
This guide provides detailed instructions on how to navigate and understand various reports. Each report is broken down into individual pages, with a comprehensive explanation of the available filters, report elements, and the calculations used to generate the displayed data.

== User Guides ==
=== M & W Booking Summary Dashboard ===
* [[Quote & Bookings Summary Dashboard]]
* [[Quote & Bookings Summary Dashboard 2]]
* [[Teams Dashboard]]
* [[Shipment Detail Report]]
* [[Pre-quote Report]]
* [[Spot Rate Report]]

=== M & W Automation Summary Dashboard ===

* Automation Booking (Weekly)
* Automation Booking (Monthly)
* Automation Booking Insights (Weekly)
* Automation Booking Insights (Monthly)
* Automation Sync Error Report
* Automated Progress Scraper Report
* Automated Invoice Payable Report

=== M & W Email Summary Dashboard ===

* Received Emails Report
* Sent Emails Report
* Email Category Report

=== Global Solution Dashboard ===

* [[Overall YTD]]
* Detail (W+M)
* Quotes Overview (W+M)
* Quotes Summary (W+M)
* Quarterly
* Customer Overall TY
* Aldo Report

=== Weight, Yield & Revenue GSA Report ===

* [[Yield - Group Overview]]
* [[Yield Week-On-Week]]
* [[Weight v Revenue Week-On-Week]]
* [[Weight By Carrier]]
* [[Site Summary]]
* [[Job Finder]]

=== Master Carrier Report ===

* [[Revenue & Booking Overview]]
* [[Quotes]]
* [[Sales Performance]]

=== Datacair Non-Conformance Report ===

* [[NCR Page]]
* [[Performance Page]]
* [[Root Cause Analysis (Resolved) Page]]
* [[Entity Breakdown Page]]

=== Datacair NCR Analysis ===

* [[NCR Report Analysis]]

=== HAE Customer Invoice Report ===

* [[ET Customer Invoice Details]]

== Additional Information ==
* Reports may be updated periodically, so refer to the latest version of the guide for the most accurate details.
* If you encounter any discrepancies or require additional assistance, please reach out to the [https://helpdesk.dcbi.aero/ helpdesk].

Report User Guides

2025-11-21T13:22:30Z

David.weller:

== Overview ==
This guide provides detailed instructions on how to navigate and understand various reports. Each report is broken down into individual pages, with a comprehensive explanation of the available filters, report elements, and the calculations used to generate the displayed data.

== User Guides ==
=== M & W Booking Summary Dashboard ===
* [[Quote & Bookings Summary Dashboard]]
* [[Quote & Bookings Summary Dashboard 2]]
* [[Teams Dashboard]]
* [[Shipment Detail Report]]
* [[Pre-quote Report]]
* [[Spot Rate Report]]

=== M & W Automation Summary Dashboard ===

* Automation Booking (Weekly)
* Automation Booking (Monthly)
* Automation Booking Insights (Weekly)
* Automation Booking Insights (Monthly)
* Automation Sync Error Report
* Automated Progress Scraper Report
* Automated Invoice Payable Report

=== M & W Email Summary Dashboard ===

* Received Emails Report
* Sent Emails Report
* Email Category Report

=== Global Solution Dashboard ===

* Overall YTD
* Detail (W+M)
* Quotes Overview (W+M)
* Quotes Summary (W+M)
* Quarterly
* Customer Overall TY
* Aldo Report

=== Weight, Yield & Revenue GSA Report ===

* [[Yield - Group Overview]]
* [[Yield Week-On-Week]]
* [[Weight v Revenue Week-On-Week]]
* [[Weight By Carrier]]
* [[Site Summary]]
* [[Job Finder]]

=== Master Carrier Report ===

* [[Revenue & Booking Overview]]
* [[Quotes]]
* [[Sales Performance]]

=== Datacair Non-Conformance Report ===

* [[NCR Page]]
* [[Performance Page]]
* [[Root Cause Analysis (Resolved) Page]]
* [[Entity Breakdown Page]]

=== Datacair NCR Analysis ===

* [[NCR Report Analysis]]

=== HAE Customer Invoice Report ===

* [[ET Customer Invoice Details]]

== Additional Information ==
* Reports may be updated periodically, so refer to the latest version of the guide for the most accurate details.
* If you encounter any discrepancies or require additional assistance, please reach out to the [https://helpdesk.dcbi.aero/ helpdesk].

Report User Guides

2025-11-21T13:20:23Z

David.weller: /* Datacair Non-Conformance Report */

== Overview ==
This guide provides detailed instructions on how to navigate and understand various reports. Each report is broken down into individual pages, with a comprehensive explanation of the available filters, report elements, and the calculations used to generate the displayed data.

== User Guides ==
=== M & W Booking Summary Dashboard ===
* [[Quote & Bookings Summary Dashboard]]
* [[Quote & Bookings Summary Dashboard 2]]
* [[Teams Dashboard]]
* [[Shipment Detail Report]]
* [[Pre-quote Report]]
* [[Spot Rate Report]]

=== M & W Automation Summary Dashboard ===

* Automation Booking (Weekly)
* Automation Booking (Monthly)
* Automation Booking Insights (Weekly)
* Automation Booking Insights (Monthly)
* Automation Sync Error Report
* Automated Progress Scraper Report
* Automated Invoice Payable Report

=== M & W Email Summary Dashboard ===

* Received Emails Report
* Sent Emails Report
* Email Category Report

=== Weight, Yield & Revenue GSA Report ===

* [[Yield - Group Overview]]
* [[Yield Week-On-Week]]
* [[Weight v Revenue Week-On-Week]]
* [[Weight By Carrier]]
* [[Site Summary]]
* [[Job Finder]]

=== Master Carrier Report ===

* [[Revenue & Booking Overview]]
* [[Quotes]]
* [[Sales Performance]]

=== Datacair Non-Conformance Report ===

* [[NCR Page]]
* [[Performance Page]]
* [[Root Cause Analysis (Resolved) Page]]
* [[Entity Breakdown Page]]

=== Datacair NCR Analysis ===

* [[NCR Report Analysis]]

=== HAE Customer Invoice Report ===

* [[ET Customer Invoice Details]]

== Additional Information ==
* Reports may be updated periodically, so refer to the latest version of the guide for the most accurate details.
* If you encounter any discrepancies or require additional assistance, please reach out to the [https://helpdesk.dcbi.aero/ helpdesk].

Job Finder

2025-09-30T10:20:22Z

David.weller: Created page with " == Summary == This is for the page "Job Finder" page that is a part of the Yield, Weight and Revenue GSA Report. == Filters == {| class="wikitable" |+ !Filter !Description |- | | |} == Data Transformation Applied == {| class="wikitable" |+ !Query !Transformations |- | | |} == Calculations == {| class="wikitable" |+ !Columns !Calculation !Description |- | | | |}"

== Summary ==
This is for the page "Job Finder" page that is a part of the Yield, Weight and Revenue GSA Report.

== Filters ==
{| class="wikitable"
|+
!Filter
!Description
|-
|
|
|}

== Data Transformation Applied ==
{| class="wikitable"
|+
!Query
!Transformations
|-
|
|
|}

== Calculations ==
{| class="wikitable"
|+
!Columns
!Calculation
!Description
|-
|
|
|
|}

Site Summary

2025-09-30T10:20:10Z

David.weller: Created page with " == Summary == This is for the page "Site Summary" page that is a part of the Yield, Weight and Revenue GSA Report. == Filters == {| class="wikitable" |+ !Filter !Description |- | | |} == Data Transformation Applied == {| class="wikitable" |+ !Query !Transformations |- | | |} == Calculations == {| class="wikitable" |+ !Columns !Calculation !Description |- | | | |}"

== Summary ==
This is for the page "Site Summary" page that is a part of the Yield, Weight and Revenue GSA Report.

== Filters ==
{| class="wikitable"
|+
!Filter
!Description
|-
|
|
|}

== Data Transformation Applied ==
{| class="wikitable"
|+
!Query
!Transformations
|-
|
|
|}

== Calculations ==
{| class="wikitable"
|+
!Columns
!Calculation
!Description
|-
|
|
|
|}

Weight By Carrier

2025-09-30T10:19:57Z

David.weller: Created page with " == Summary == This is for the page "Weight By Carrier" page that is a part of the Yield, Weight and Revenue GSA Report. == Filters == {| class="wikitable" |+ !Filter !Description |- | | |} == Data Transformation Applied == {| class="wikitable" |+ !Query !Transformations |- | | |} == Calculations == {| class="wikitable" |+ !Columns !Calculation !Description |- | | | |}"

== Summary ==
This is for the page "Weight By Carrier" page that is a part of the Yield, Weight and Revenue GSA Report.

== Filters ==
{| class="wikitable"
|+
!Filter
!Description
|-
|
|
|}

== Data Transformation Applied ==
{| class="wikitable"
|+
!Query
!Transformations
|-
|
|
|}

== Calculations ==
{| class="wikitable"
|+
!Columns
!Calculation
!Description
|-
|
|
|
|}

Weight v Revenue Week-On-Week

2025-09-30T10:19:33Z

David.weller: Created page with " == Summary == This is for the page "Weight v Revenue Week-On-Week" page that is a part of the Yield, Weight and Revenue GSA Report. == Filters == {| class="wikitable" |+ !Filter !Description |- | | |} == Data Transformation Applied == {| class="wikitable" |+ !Query !Transformations |- | | |} == Calculations == {| class="wikitable" |+ !Columns !Calculation !Description |- | | | |}"

== Summary ==
This is for the page "Weight v Revenue Week-On-Week" page that is a part of the Yield, Weight and Revenue GSA Report.

== Filters ==
{| class="wikitable"
|+
!Filter
!Description
|-
|
|
|}

== Data Transformation Applied ==
{| class="wikitable"
|+
!Query
!Transformations
|-
|
|
|}

== Calculations ==
{| class="wikitable"
|+
!Columns
!Calculation
!Description
|-
|
|
|
|}

Yield Week-On-Week

2025-09-30T10:19:18Z

David.weller: Created page with " == Summary == This is for the page "Yield - Week-On-Week" page that is a part of the Yield, Weight and Revenue GSA Report. == Filters == {| class="wikitable" |+ !Filter !Description |- | | |} == Data Transformation Applied == {| class="wikitable" |+ !Query !Transformations |- | | |} == Calculations == {| class="wikitable" |+ !Columns !Calculation !Description |- | | | |}"

== Summary ==
This is for the page "Yield - Week-On-Week" page that is a part of the Yield, Weight and Revenue GSA Report.

== Filters ==
{| class="wikitable"
|+
!Filter
!Description
|-
|
|
|}

== Data Transformation Applied ==
{| class="wikitable"
|+
!Query
!Transformations
|-
|
|
|}

== Calculations ==
{| class="wikitable"
|+
!Columns
!Calculation
!Description
|-
|
|
|
|}

Yield - Group Overview

2025-09-30T10:18:41Z

David.weller: Created page with " == Summary == This is for the page "Yield - Group Overview" page that is a part of the Yield, Weight and Revenue GSA Report. == Filters == {| class="wikitable" |+ !Filter !Description |- | | |} == Data Transformation Applied == {| class="wikitable" |+ !Query !Transformations |- | | |} == Calculations == {| class="wikitable" |+ !Columns !Calculation !Description |- | | | |}"

== Summary ==
This is for the page "Yield - Group Overview" page that is a part of the Yield, Weight and Revenue GSA Report.

== Filters ==
{| class="wikitable"
|+
!Filter
!Description
|-
|
|
|}

== Data Transformation Applied ==
{| class="wikitable"
|+
!Query
!Transformations
|-
|
|
|}

== Calculations ==
{| class="wikitable"
|+
!Columns
!Calculation
!Description
|-
|
|
|
|}

Report User Guides

2025-09-30T10:16:48Z

David.weller:

== Overview ==
This guide provides detailed instructions on how to navigate and understand various reports. Each report is broken down into individual pages, with a comprehensive explanation of the available filters, report elements, and the calculations used to generate the displayed data.

== User Guides ==
=== M & W Booking Summary Dashboard ===
* [[Quote & Bookings Summary Dashboard]]
* [[Quote & Bookings Summary Dashboard 2]]
* [[Teams Dashboard]]
* [[Shipment Detail Report]]
* [[Pre-quote Report]]
* [[Spot Rate Report]]

=== M & W Automation Summary Dashboard ===

* Automation Booking (Weekly)
* Automation Booking (Monthly)
* Automation Booking Insights (Weekly)
* Automation Booking Insights (Monthly)
* Automation Sync Error Report
* Automated Progress Scraper Report
* Automated Invoice Payable Report

=== M & W Email Summary Dashboard ===

* Received Emails Report
* Sent Emails Report
* Email Category Report

=== Weight, Yield & Revenue GSA Report ===

* [[Yield - Group Overview]]
* [[Yield Week-On-Week]]
* [[Weight v Revenue Week-On-Week]]
* [[Weight By Carrier]]
* [[Site Summary]]
* [[Job Finder]]

=== Master Carrier Report ===

* [[Revenue & Booking Overview]]
* [[Quotes]]
* [[Sales Performance]]

=== Datacair Non-Conformance Report ===

* [[NCR Page]]
* [[Performance Page]]
* [[Root Cause Analysis (Resolved) Page]]
* [[Entity Breakdown Page]]
* [[NCR Report Analysis]]

=== HAE Customer Invoice Report ===

* [[ET Customer Invoice Details]]

== Additional Information ==
* Reports may be updated periodically, so refer to the latest version of the guide for the most accurate details.
* If you encounter any discrepancies or require additional assistance, please reach out to the [https://helpdesk.dcbi.aero/ helpdesk].

ET Customer Invoice Details

2025-09-26T12:56:23Z

David.weller:

== Summary ==
This report was developed as a custom request from the Brazil-based Operations team to enable them to quickly generate an invoice report for sharing with carrier ET.

It forms part of an ongoing effort to streamline their workflow and support fast, efficient invoice generation and communication with the carrier.

== Filters ==
{| class="wikitable"
|+
!Filter
!Description
|-
|Site
|This is locked to the Brazil site
|-
|Carrier
|This is locked to ET Brazil
|-
|Customer
|Allows the user to specifically select the customer they want to view the booking details for. Can only select a single customer, or show all.
|-
|Date Range
|The date range is linked to the depart date.
|}

== Data Transformation Applied ==
{| class="wikitable"
|+
!Query
!Transformations
|-
|quotations
|
* '''Changed Type''': '''created_at''', '''depart_date''' to '''date'''
* '''Merged Queries''': Joined with '''quotation_carriers''' on '''id''', '''main_carrier_id''' and '''quotation_id''', '''carrier_id'''
* '''Expanded Columns''': '''carrier_id''', '''Carrier''', '''Site'''

* '''Filtered Rows''': Kept only rows where '''quotation_carriers.carrier_id = 250'''
|-
|hae_booking_db carriers
|
* '''Merged Queries''': Joined with '''sites''' on '''site_id''' and '''id'''
* '''Expanded Column''': '''name''' from '''sites'''

* '''Renamed Column''': '''hae_booking_db sites.name''' to '''Site'''
|-
|quotation_totals_local
|
* '''Renamed Columns''': '''total_cost''' → '''ET Refund (COST)''' '''total_sell''' → '''HAE Invoice Customer Total (Sales BRL)'''

* '''Added Custom Column''': '''HAE Over''' = '''total_sell_rate - total_cost_rate'''
|-
|quotation_charges
|
* '''Changed Type''': '''created_at''' to '''date'''
* '''Duplicated Column''': '''sell_rate''' → '''AWB Rate'''
* '''Renamed Column''': '''currency''' → '''Due Carrier Currency'''
* '''Duplicated Column Again''': '''Due Carrier Currency''' → '''HAE Commission Currency (USD)'''
|-
|quotation_routes
|
* '''Merged Queries''': Joined with '''quotation_charges''' on '''id''' and '''quotation_route_id'''
* '''Expanded Columns''': '''cost_rate''', '''sell_rate''', '''currency''', '''currency_sell'''
* '''Renamed Columns''': '''cost_rate''' → '''Buying Rate''' '''sell_rate''' → '''Selling Rate''' '''currency''' → '''Buying Currency''' '''currency_sell''' → '''Sell Rate Currency'''

* '''Changed Type''': '''created_at''' to '''date'''
|-
|quotation_fields (Mode of Payment)
|
* '''Renamed Column''': '''field_value''' → '''Mode of Payment'''

* '''Changed Type''': '''created_at''' to '''date'''
|-
|quotation_fields (AWB Issue Date)
|
* '''Renamed Column''': '''field_value''' → '''AWB Issue Date'''

* '''Added Custom Column''': '''AWB Issue Date Final''' = if '''AWB Issue Date = ""''' then '''"NODATA"''' else '''AWB Issue Date'''
|-
|quotation_fields (Due Agent)
|
* '''Renamed Column''': '''field_value''' → '''Due Agent'''

* '''Changed Type''': '''created_at''' to '''date'''
|-
|quotation_exchange_rates

|
* '''Changed Type''': '''created_at''' to '''date'''
* '''Grouped Rows''': by '''quotation_id''', retained max of '''created_at'''
* '''Merged Queries''': Joined with '''quotation_exchange_Original''' on '''quotation_id''' and '''created_at'''
* '''Expanded Columns''': '''exchange_rate''', '''is_fixed'''
* '''Renamed Columns''': '''quotation_exchange_Original.exchange_rate''' → '''exchange_rate''' '''quotation_exchange_Original.is_fixed''' → '''is_fixed'''
|-
|quotation_exchange_Original
|'''Changed Type''': '''created_at''' to '''date'''
|-
|quotation_chargesHAE_TAX
|
* '''Filtered Rows''': Initial filter kept all, then second filter kept rows where '''HAE Tax (charge currency) = USD'''

* '''Renamed Columns''': '''sell_min''' → '''HAE Tax USD''' '''currency_sell''' → '''HAE Tax (charge currency)'''
|-
|quotation_route_partsOriginal
|
* '''Merged Queries''': Joined with '''quotation_routes_flight_number''' on '''quotation_route_id'''
* '''Expanded Column''': '''quotation_id'''

* '''Renamed Column''': '''quotation_routes_flight_number.quotation_id''' → '''quotation_id'''
|-
|quotation_flight_number
|
* '''Merged Queries''': Joined with '''quotation_routes_flight_number''' on '''quotation_route_id'''
* '''Expanded Column''': '''quotation_id'''
* '''Grouped Rows''': by '''quotation_id''', selected '''Min(id)'''
* '''Merged Queries''': Joined with '''quotation_route_partsOriginal'''
* '''Expanded Columns''': '''flight_number''', '''carrier_code'''

* '''Renamed Columns''': '''quotation_route_partsOriginal.flight_number''' → '''flight_number''' '''quotation_route_partsOriginal.carrier_code''' → '''carrier_code'''
|-
|QuotationCombinedTableTotalRevenue
|To create a table that combines quotation data with revenue-related charge details. This helps in calculating '''Total Revenue'''.
'''How it works:'''

* It '''joins''' the quotations table with the quotations_total_revenue table using matching quotation IDs.
* From the joined data, it '''selects''' only these columns:
** '''ID''' – the quotation ID
** '''Chargeable Weight''' – used in revenue calculation
** '''Code''' – type of charge (e.g., LINEHAUL, DTC)
** '''Sell Rate''' – rate per weight unit
** '''Sell Min''' – minimum selling value

This table is then used in the revenue formula that applies different logic depending on the charge code.
|-
|QuotationCombinedTableDueCarrierUSD
|'''Purpose:'''
To create a clean table for calculating Due Carrier USD, excluding rows where the charge code is LINEHAUL.

'''Steps:'''

* Use three tables: '''quotations''', '''quotation_chargesDueCarrierUSD''' (a filtered version of quotation_charges), and '''quotation_exchange_rates'''.
* Join '''quotations''' with '''quotation_chargesDueCarrierUSD''' using a natural inner join.
* Then join the result with '''quotation_exchange_rates''' using another natural inner join.
* Select the following columns:
** '''ID''' from quotations
** '''Chargeable Weight''' from quotations
** '''Cost Rate''', '''Code''', '''Cost Min''', '''On''' from quotation_chargesDueCarrierUSD
** '''Exchange Rate''' from quotation_exchange_rates
* Use '''DISTINCT''' to remove any duplicate rows.
|-
|QuotationCombinedTableDueCarrierTotal
|To create a unified table that includes '''all carrier charges''' (including '''LINEHAUL''') for calculating '''Total Due Carrier USD''' values.
'''Steps:'''

* Start with the '''quotations''', '''quotation_chargesDueCarrierTotal''', and '''quotation_exchange_rates''' tables.
* Perform a '''natural inner join''' between '''quotations''' and '''quotation_chargesDueCarrierTotal'''.
* Then perform another '''natural inner join''' with '''quotation_exchange_rates'''.
* Use '''SELECTCOLUMNS''' to extract the following fields:
** '''ID''' from quotations
** '''Chargeable Weight''' from quotations
** '''Cost Rate''', '''Code''', '''Cost Min''', '''On''' from quotation_chargesDueCarrierTotal
** '''Exchange Rate''' from quotation_exchange_rates
*
|}

== Calculations ==
{| class="wikitable"
|+
!Columns
!Calculation
!Description
|-
|id
|'''id''' — sourced directly from the quotations table.
|The '''id''' is a unique identifier for each record and is retrieved directly from the '''quotations''' table.
|-
|master_airwaybill
|'''master_airwaybill''' — sourced directly from the quotations table.
|The '''master_airwaybill''' field contains the main airway bill number for the shipment. It is pulled directly from the '''quotations''' table for reference and tracking.
|-
|Customer
|'''Customer Name''' — pulled from the customer table via customer_id and linked to the quotations table.
|Join the '''customer''' table to the '''quotations''' table using '''customer_id''' as the key.
From the joined customer record, retrieve the '''name''' field to identify the customer associated with the quotation.
|-
|Flight_Number
|'''flight_number''' — retrieved from hae_route_parts, filtered to the minimum quotation_route_id per quotation_id, and joined back to quotations via quotation_id
|Retrieve the '''flight_number''' associated with each quotation by linking route data. This is done by identifying the first (minimum) '''quotation_route_id''' per '''quotation_id''', then using it to pull the corresponding '''flight_number''' from the route details.
|-
|Depart Date
|'''depart_date''' — sourced directly from the quotations table.
|The '''depart_date''' is obtained directly from the quotations table.
|-
|AWB Issue Date
|AWB Issue Date = if [AWB Issue Date] = null then "NO DATA" else [AWB Issue Date]
|Retrieve the '''AWB Issue Date''' from the '''quotation_fields''' table where '''carrier_quotation_field_id''' equals '''200''', which corresponds to the AWB Issue Date field.
Then, join this data to the '''quotations''' table using '''quotation_id'''.

Finally, check if the '''AWB Issue Date''' is null; if so, return '''"NO DATA"'''. Otherwise, return the '''AWB Issue Date'''.
|-
|Exchange_rate
|'''exchange_rate''' — retrieved from the exchange_rate table as the most recent entry (by created_at) for each quotation_id.
|Join the '''exchange_rate''' table with the '''quotations''' table using the '''quotation_id''' key.
From the '''exchange_rate''' table, retrieve the '''exchange_rate''' field that has the '''most recent created_at''' timestamp '''per quotation_id'''.

This ensures that each quotation is matched with the latest available exchange rate record
|-
|Fixed_exRate
|Fixed_exRate =
IF(

quotation_exchange_rates[is_fixed] = TRUE(),

"✓",

"×"

)
|Join the '''exchange_rate''' table with the '''quotations''' table using the '''quotation_id''' key.
From the exchange_rate table, retrieve the '''is_fixed''' field that with the most recent created_at per quotation_id

which indicates whether the exchange rate is fixed.

Then evaluate the value:

* If '''is_fixed''' is '''TRUE''', return '''"✓"'''
* If '''is_fixed''' is '''FALSE''', return '''"×"'''
|-
|origin
|'''origin''' — sourced directly from the quotations table.
|It is retrieved directly from the '''quotations''' table.
|-
|Destination
|'''destination''' — sourced directly from the quotations table.
|It is retrieved directly from the '''quotations''' table.
|-
|gross_weight
|'''gross_weight''' — sourced directly from the quotations table.
|It is retrieved directly from the '''quotations''' table.
|-
|chargeable_weight
|'''chargeable_weight''' — sourced directly from the quotations table.
|It is retrieved directly from the '''quotations''' table.
|-
|Buying Rate
|Buying Rate = quotation_routes[cost_rate]
|'''Join''' the '''quotation_routes''' table (filtered where '''carrier_id = 12''') with the '''quotation_charges''' table, '''filtered where code = "LINEHAUL"'''.
From this joined data, '''extract the cost_rate''' field and rename it as '''Buying Rate'''.
|-
|Buying Rate Total
|'''Buying Rate Total''' =
quotation_routes[Buying Rate] × RELATED(quotations[chargeable_weight])
|Join the '''routes''' table (filtered to '''carrier_id = 12''') with the '''quotation_charges''' table, where the '''Airfreight rate''' is '''LINEHAUL'''.
From this join, extract the '''cost_rate''' field.

Next, join this data with the '''quotations''' table to access the '''chargeable_weight'''.

Then, calculate the '''Buying Rate Total''' by multiplying the '''LINEHAUL cost_rate''' by the '''chargeable_weight'''.
|-
|Buying Currency
|'''Buying Currency = currency''' from quotation_charges, joined via quotation_id.
|Join the '''quotation_charges''' table with the '''quotations''' table using the '''quotation_id''' key.
From the '''quotation_charges''' table, retrieve the '''currency''' field.
|-
|AWB Rate
|'''AWB Rate = sell_rate''' from quotation_charges where code = 'LINEHAUL', joined via quotation_id.
|Join the '''quotation_charges''' table with the '''quotations''' table using the '''quotation_id''' key.
From the '''quotation_charges''' table, filter to only include the charge where '''code = 'LINEHAUL''''.

Retrieve the '''sell_rate''' field for this charge, which represents the '''AWB Rate'''.
|-
|Due Agent
|Due Agent = value from quotation_fields (field ID 204), joined to quotations via quotation_id
|Retrieve the '''Due Agent''' value from the '''quotation_fields''' table by filtering for '''carrier_quotation_field_id = 204''', which corresponds to the Due Agent field.
Then, join this data with the '''quotations''' table using the '''quotation_id'''.
|-
|HAE Over
|HAE Over = [Total Sell Rate] - [Total Cost Rate]
|Retrieve '''sell_rate''' and '''cost_rate''' from the '''hae_quotations_local''' table as '''Total Sell Rate''' and '''Total Cost Rate''', respectively.
Then, join this data with the '''quotations''' table using the '''id''' field.

Finally, calculate the '''HAE Over''' by subtracting the '''Total Cost Rate''' from the '''Total Sell Rate'''.
|-
|HAE Tax USD
|'''HAE Tax USD = sell_min''' from quotation_charges where code = 'DTC', linked to quotations by quotation_id.
|Retrieve '''sell_min''' from the '''quotation_charges''' table where the '''code = 'DTC'.'''
Join this table with the '''quotations''' table using '''quotation_id''' to match the correct quotation. This value represents the '''HAE Tax USD''', as the charge is stored in USD by default.
|-
|HAE Tax (charge currency)
|'''HAE Tax (charge currency) = currency_sell''' from quotation_charges, joined via quotation_id.
|Join the '''quotation_charges''' table with the '''quotations''' table using the '''quotation_id''' key.
From the '''quotation_charges''' table, retrieve the '''currency_sell''' field, which indicates the currency of the HAE Tax.
|-
|HAE Tax BRL
|HAE Tax BRL = [HAE Tax USD] * [Exchange_rate]
|convert the '''HAE Tax USD''' amount to '''BRL''' by multiplying it with the '''exchange_rate'''.
|-
|Due Carrier (USD)
|'''Due Carrier USD''' =
SWITCH(

TRUE(),

QuotationCombinedTableDueCarrierUSD[on] = "CH", QuotationCombinedTableDueCarrierUSD[cost_rate] * QuotationCombinedTableDueCarrierUSD[chargeable_weight],

QuotationCombinedTableDueCarrierUSD[on] = "FL", QuotationCombinedTableDueCarrierUSD[cost_min],

0

)
|Grab '''quotation_charges''' table and rename it to '''quotation_chargesDueCarrierUSD'''.
From this duplicated table, '''exclude all rows where code = 'LINEHAUL'''', keeping only relevant carrier charges.

Then, join '''quotation_chargesDueCarrierUSD''' with the '''quotations''' table using '''quotation_id''' to create '''QuotationCombinedTableDueCarrierUSD'''.

Also, join the '''quotation_exchange_rates''' table to bring in the most recent '''exchange_rate''' per quotation.

From '''QuotationCombinedTableDueCarrierUSD''', retrieve the following fields:

* '''chargeable_weight''' from quotations
* '''cost_rate''', '''code''', '''cost_min''', and '''on''' from quotation_chargesDueCarrierUSD
* '''exchange_rate''' from quotation_exchange_rates

Finally, calculate the '''Due Carrier USD''' using the logic based on the '''on''' field:

* If '''on = "CH"''', multiply '''cost rate''' by '''chargeable weight'''
* If '''on = "FL"''', use '''cost_min'''
* Otherwise, return 0
|-
|Due Carrier Total BRL
|'''Total Due Carrier BRL ='''
QuotationCombinedTableDueCarrierTotal[Due Carrier Total] × QuotationCombinedTableDueCarrierTotal[Exchange Rate]
|After calculating '''Due Carrier Total''' (which includes all carrier charges, including '''LINEHAUL'''), convert the amount into '''BRL''' by multiplying it with the relevant '''exchange rate'''.
Ensure that '''QuotationCombinedTableDueCarrierTotal''' includes both the '''Due Carrier Total''' and the correct '''Exchange Rate''', retrieved as the most recent value per '''quotation_id''' from the '''quotation_exchange_rates''' table.
*
|-
|Due Carrier (BRL)
|Due Carrier BRL =
QuotationCombinedTableDueCarrierUSD[Due Carrier USD] * QuotationCombinedTableDueCarrierUSD[Exchange Rate]
|After computing '''Due Carrier USD''', convert the value into '''BRL''' by applying the corresponding '''exchange rate'''.
Ensure that '''QuotationCombinedTableDueCarrierUSD''' already contains the '''Due Carrier USD''' and the applicable '''Exchange Rate''' (retrieved from the most recent entry in the '''quotation_exchange_rates''' table, joined by '''quotation_id''').
|-
|Total Revenue
|'''Total Revenue ='''
CALCULATE (

SUMX (

QuotationCombinedTableTotalRevenue,

SWITCH (

TRUE(),

[Code] = "LINEHAUL", [Sell Rate] * [Chargeable Weight],

[Code] = "DTC", [Sell Min],

0

)

)

)
|Grab the quotation charges table, rename it to:
'''quotation_charges_Total Revenue'''.
This version includes all relevant charge codes for calculating total revenue—specifically '''LINEHAUL''' and '''DTC'''.

Next, perform a '''NATURALINNERJOIN''' between the '''quotations''' table and '''quotation_charges_Total Revenue''' to create '''QuotationCombinedTableTotalRevenue'''. From this joined table, retrieve the following fields:

* '''quotation_id''' as ID
* '''chargeable_weight''' from '''quotations'''
* '''code''', '''sell_rate''', and '''sell_min''' from '''quotation_charges_Total Revenue'''

Finally, compute '''Total Revenue''' using a conditional logic:

* If '''code = "LINEHAUL"''', multiply '''sell_rate''' by '''chargeable_weight'''
* If '''code = "DTC"''', use '''sell_min'''
* Otherwise, return 0
|-
|Total Revenue Currency
|'''Total Revenue Currency ='''
quotation_charges[currency_sell]
|Join the '''quotation_charges''' table with the '''quotations''' table using the '''quotation_id''' key.
From the '''quotation_charges''' table, retrieve the '''currency_sell''' field, which indicates the currency in which the revenue is quoted.

|-
|HAE Invoice Customer Total (Sales BRL)
|Join the '''quotation_totals_local''' table with the '''quotations''' table using the '''quotation_id''' key.
From the '''quotation_totals_local''' table, retrieve the '''total_sell''' field.
|'''HAE Invoice Customer Total (Sales) = total_sell''' from quotation_totals_local, joined via quotation_id.
|-
|HAE Commision (Margin) BRL
|HAE Margin BRL =
if ([Exchange_rate] = null or [Exchange_rate] = 0) then null

else [HAE Commission] * [Exchange_rate]
|After retrieving the '''HAE Commission''' and the applicable '''Exchange_rate''' (ensuring it's the most recent per quotation_id), calculate the '''HAE Margin in BRL''' by converting the commission using the exchange rate.
If the '''Exchange_rate''' is null or zero, return null to avoid invalid conversion.
|-
|HAE Margin BRL
|HAE Margin BRL =
if ([Exchange_rate] = null or [Exchange_rate] = 0) then null

else [HAE Commission] * [Exchange_rate]
|This formula calculates the HAE Margin amount in BRL by multiplying [HAE Commission 2] by the [Exchange_rate].
If the [Exchange_rate] is null or zero, the formula returns null to prevent an invalid calculation.

Otherwise, it performs the conversion from the commission value to BRL.
|-
|ET Refund (Cost BRL)
|'''ET Refund (COST) = total_cost''' from quotation_totals_local, joined via quotation_id.
|Join the '''quotation_totals_local''' table with the '''quotations''' table using the '''quotation_id''' key.
From the joined data, retrieve the '''total_cost''' field.

This value represents the '''ET Refund (COST)''' associated with the quotation.
|-
|Selling Rate
|'''Sell Rate = sell rate''' from quotation_charges, joined via quotation_id.
|Join the '''quotation_charges''' table with the '''quotations''' table using the '''quotation_id''' key.
From the '''quotation_charges''' table, retrieve the '''sell_rate''' field.
|-
|Selling Rate Currency
|'''Sell Rate Currency = currency sell''' from quotation_charges, joined via quotation_id
|Join the '''quotation_charges''' table with the '''quotations''' table using the '''quotation_id''' key.
From the '''quotation_charges''' table, retrieve the '''currency sell''' field.
|-
|Mode Of Payment
|'''Mode of Payment = field_value''' from quotation_fields where carrier_quotation_field_id = 199, joined to quotations via quotation_id.
|Retrieve the '''Mode of Payment''' from the '''quotation_fields''' table by filtering records where '''carrier_quotation_field_id = 199''', which corresponds to this customer field.
Join the '''quotation_fields''' table with the '''quotations''' table using '''quotation_id''' to ensure the value is linked to the correct quotation.

Only values associated with '''carrier_quotation_field_id = 199''' are displayed.
|-
|Due Carrier Total
|'''Due Carrier Total ='''
SWITCH(

TRUE(),

QuotationCombinedTableDueCarrierTotal[on] = "CH", QuotationCombinedTableDueCarrierTotal[cost_rate] * QuotationCombinedTableDueCarrierTotal[chargeable_weight],

QuotationCombinedTableDueCarrierTotal[on] = "FL", QuotationCombinedTableDueCarrierTotal[cost_min],

0

)
|Grab the '''quotation_charges''' table and renamed it to '''quotation_chargesDueCarrierTotal'''.
From this duplicated table, '''include all rows''', including those where '''code = 'LINEHAUL'''', to capture the full set of carrier charges.

Then, '''join''' '''quotation_chargesDueCarrierTotal''' with the '''quotations''' table using '''quotation_id''' to create '''QuotationCombinedTableDueCarrierTotal'''.

Also, '''join''' the '''quotation_exchange_rates''' table to bring in the '''most recent exchange_rate''' per quotation.

From '''QuotationCombinedTableDueCarrierTotal''', retrieve the following fields:

* '''chargeable_weight''' from '''quotations'''
* '''cost_rate''', '''code''', '''cost_min''', and '''on''' from '''quotation_chargesDueCarrierTotal'''
* '''exchange_rate''' from '''quotation_exchange_rates'''

Finally, calculate the '''Due Carrier Total''' using the logic based on the '''on''' field:

* If '''on = "CH"''', multiply '''cost_rate''' by '''chargeable weight'''
* If '''on = "FL"''', use '''cost_min'''
* Otherwise, return '''0'''
|}

Sales Performance

2025-08-15T12:29:29Z

David.weller:

== Summary ==

== Filters ==

{| class="wikitable"
|+
!Filter
!Description
|-
|
|
|}

== Data Transformation Applied ==
{| class="wikitable"
|+
!Query
!Transformations
|-
|
|
|}

== Calculations ==
[[File:Report-performance-mastercarrier.png|none|thumb|800x800px]]
{| class="wikitable"
|+
!Columns
!Calculation
!Description
|-
|
|
|}

File:Report-performance-mastercarrier.png

2025-08-15T12:29:12Z

David.weller:

Performance Master Carrier

Quotes

2025-08-15T12:28:47Z

David.weller:

== Summary ==

== Filters ==

{| class="wikitable"
|+
!Filter
!Description
|-
|
|
|}

== Data Transformation Applied ==
{| class="wikitable"
|+
!Query
!Transformations
|-
|
|
|}

== Calculations ==
[[File:Report-quote-mastercarrier.png|none|thumb|800x800px]]
{| class="wikitable"
|+
!Columns
!Calculation
!Description
|-
|
|
|}