NCR - Staff Logs - Revision history

Hendry.mwamburi at 12:11, 27 January 2026

2026-01-27T12:11:19Z

← Older revision		Revision as of 14:11, 27 January 2026
Line 16:		Line 16:
	\|Filters data by there entities		\|Filters data by there entities
	\|-		\|-
	\|~~Entity~~		\|Region
	\|Filters data by there Region		\|Filters data by there Region
	\|}		\|}

Hendry.mwamburi: /* Filters */

2026-01-27T12:11:02Z

Filters

← Older revision		Revision as of 14:11, 27 January 2026
Line 15:		Line 15:
	\|Entity		\|Entity
	\|Filters data by there entities		\|Filters data by there entities
			\|-
			\|Entity
			\|Filters data by there Region
	\|}		\|}

Hendry.mwamburi at 12:00, 27 January 2026

2026-01-27T12:00:22Z

← Older revision		Revision as of 14:00, 27 January 2026
Line 51:		Line 51:
	\|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.		\|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.
	\|-		\|-

	\|-
	~~\|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		\|4 - Staffed Rostered

Hendry.mwamburi at 11:59, 27 January 2026

2026-01-27T11:59:08Z

← Older revision		Revision as of 13:59, 27 January 2026
Line 51:		Line 51:
	\|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.		\|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		\|3 - Total NCR's

Hendry.mwamburi: /* Data Transformation Applied */

2026-01-27T11:19:53Z

Data Transformation Applied

← Older revision		Revision as of 13:19, 27 January 2026
Line 23:		Line 23:
	!Transformations		!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/		\|SharePoint Tables =https://dcbiaero.sharepoint.com/sites/NCRDatabase/

Hendry.mwamburi at 11:17, 27 January 2026

2026-01-27T11:17:47Z

← Older revision		Revision as of 13:17, 27 January 2026
Line 1:		Line 1:
	== Summary ==		== 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~~.		This page forms part of the Non-Conformance Report for Datacair. It consolidates data from both the NCR Database.

	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.		The report provides a unified view of non-conformance data across entities. It offers a high-level overview of staff attendance and work rates, enabling quick and easy analysis across all entities.

	== Filters ==		== Filters ==

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..."

2026-01-26T13:25:12Z

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..."

New page

== 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
|}