Laboratory Information System KPIs & Reporting

KPI Summary

Note: Some fields (e.g., auto_verified, hie_submission_status, first_attempt_flag, antibiogram metadata) are assumed to exist or can be added as part of the LIS data model extension.

KPI Definitions

KPI-LIS-001: Lab Turnaround Time (TAT) — Routine

Definition

Average time from specimen receipt in the lab to result verification for routine-priority tests. Monitors operational efficiency and supports DOH/DHA quality expectations for timely reporting.

Calculation Formula

Example for chemistry routine tests:

SELECT
    lot.lab_section,
    DATE(lr.verified_datetime) AS tat_date,
    AVG(TIMESTAMPDIFF(
        MINUTE,
        ls.received_datetime,
        lr.verified_datetime
    )) AS avg_tat_minutes
FROM lab_results lr
JOIN lab_order_tests lot
    ON lr.order_test_id = lot.order_test_id
JOIN lab_orders lo
    ON lot.order_id = lo.order_id
JOIN lab_specimens ls
    ON ls.order_id = lo.order_id
WHERE
    lo.priority = 'Routine'
    AND lot.lab_section = 'Chemistry'
    AND lr.result_status = 'Final'
    AND ls.received_datetime IS NOT NULL
    AND lr.verified_datetime IS NOT NULL
    AND lr.verified_datetime BETWEEN :start_datetime AND :end_datetime
GROUP BY
    lot.lab_section,
    DATE(lr.verified_datetime);

To compute SLA compliance (% within target):

SELECT
    lot.lab_section,
    COUNT(*) AS total_tests,
    COUNT(CASE
        WHEN TIMESTAMPDIFF(
            MINUTE,
            ls.received_datetime,
            lr.verified_datetime
        ) <= CASE
                WHEN lot.lab_section = 'Chemistry' THEN 240  -- 4h
                WHEN lot.lab_section = 'Hematology' THEN 120 -- 2h
                ELSE 240
             END
        THEN 1 END
    ) AS within_target,
    COUNT(CASE
        WHEN TIMESTAMPDIFF(
            MINUTE,
            ls.received_datetime,
            lr.verified_datetime
        ) <= CASE
                WHEN lot.lab_section = 'Chemistry' THEN 240
                WHEN lot.lab_section = 'Hematology' THEN 120
                ELSE 240
             END
        THEN 1 END
    ) * 100.0 / COUNT(*) AS tat_compliance_pct
FROM lab_results lr
JOIN lab_order_tests lot
    ON lr.order_test_id = lot.order_test_id
JOIN lab_orders lo
    ON lot.order_id = lo.order_id
JOIN lab_specimens ls
    ON ls.order_id = lo.order_id
WHERE
    lo.priority = 'Routine'
    AND lr.result_status = 'Final'
    AND lr.verified_datetime BETWEEN :start_datetime AND :end_datetime
GROUP BY
    lot.lab_section;

Target / Benchmark

Data Source

• lab_orders(order_id, priority, facility_id, encounter_id, ordering_provider_id)
• lab_order_tests(order_test_id, order_id, lab_section, status)
• lab_specimens(order_id, specimen_id, received_datetime, specimen_type, condition)
• lab_results(result_id, order_test_id, result_status, verified_datetime, verified_by)

Dimensions / Filters

• Time: date, week, month, quarter, year
• Facility (lab_orders.facility_id)
• Department / location (via encounter/location from scheduling module)
• Lab section (lab_order_tests.lab_section)
• Test (lab_order_tests.test_code_loinc, test_name)
• Priority (Routine only, but can compare with STAT)
• Patient type (inpatient/outpatient/ED via encounter)

Visualization

• Line chart: average TAT (minutes) per day by lab section.
• Bar chart: SLA compliance % by lab section.
• Box plot (optional): distribution of TAT for selected test/section.

Alert Thresholds

• Warning:
• Chemistry: average TAT > 210 minutes (3.5 h) for 2 consecutive hours.
• Hematology: average TAT > 105 minutes (1.75 h) for 2 consecutive hours.
• Critical:
• Chemistry: average TAT > 240 minutes (4 h) for 2 consecutive hours.
• Hematology: average TAT > 120 minutes (2 h) for 2 consecutive hours.
• Notification recipients:
• Lab Supervisor, Lab Director
• Nursing supervisor for affected departments (summary email/daily report)

KPI-LIS-002: Lab Turnaround Time (TAT) — STAT

Definition

Average time from specimen receipt to result verification for STAT-priority tests. Critical for emergency and ICU care.

Calculation Formula

SELECT
    lot.lab_section,
    DATE(lr.verified_datetime) AS tat_date,
    AVG(TIMESTAMPDIFF(
        MINUTE,
        ls.received_datetime,
        lr.verified_datetime
    )) AS avg_tat_minutes
FROM lab_results lr
JOIN lab_order_tests lot
    ON lr.order_test_id = lot.order_test_id
JOIN lab_orders lo
    ON lot.order_id = lo.order_id
JOIN lab_specimens ls
    ON ls.order_id = lo.order_id
WHERE
    lo.priority = 'STAT'
    AND lr.result_status = 'Final'
    AND ls.received_datetime IS NOT NULL
    AND lr.verified_datetime IS NOT NULL
    AND lr.verified_datetime BETWEEN :start_datetime AND :end_datetime
GROUP BY
    lot.lab_section,
    DATE(lr.verified_datetime);

SLA compliance (% within 60 minutes):

SELECT
    lot.lab_section,
    COUNT(*) AS total_stat_tests,
    COUNT(CASE
        WHEN TIMESTAMPDIFF(
            MINUTE,
            ls.received_datetime,
            lr.verified_datetime
        ) <= 60
        THEN 1 END
    ) AS within_60min,
    COUNT(CASE
        WHEN TIMESTAMPDIFF(
            MINUTE,
            ls.received_datetime,
            lr.verified_datetime
        ) <= 60
        THEN 1 END
    ) * 100.0 / COUNT(*) AS stat_tat_compliance_pct
FROM lab_results lr
JOIN lab_order_tests lot
    ON lr.order_test_id = lot.order_test_id
JOIN lab_orders lo
    ON lot.order_id = lo.order_id
JOIN lab_specimens ls
    ON ls.order_id = lo.order_id
WHERE
    lo.priority = 'STAT'
    AND lr.result_status = 'Final'
    AND lr.verified_datetime BETWEEN :start_datetime AND :end_datetime
GROUP BY
    lot.lab_section;

Target / Benchmark

• Target: ≤ 60 minutes from specimen receipt to verification for STAT tests.
• Many UAE hospitals use 60 minutes as internal SLA for STAT chemistry/hematology.

Data Source

Same as KPI-LIS-001, with lab_orders.priority = 'STAT'.

Dimensions / Filters

• Time: hourly, daily, weekly
• Facility, department, lab section
• Test type (e.g., troponin, ABG)
• Location (ED, ICU, OR vs others)

Visualization

• Gauge: current STAT TAT compliance %.
• Line chart: average STAT TAT per hour in last 24 hours.
• Bar chart: STAT TAT compliance by department.

Alert Thresholds

• Warning:
• STAT TAT compliance < 90% over last 4 hours.
• Critical:
• STAT TAT compliance < 80% over last 2 hours.
• Notification recipients:
• Lab Supervisor (real-time alert)
• ED/ICU charge nurse (summary alert)

KPI-LIS-003: Critical Value Notification Compliance

Definition

Percentage of critical results where notification to the responsible provider was acknowledged within 30 minutes of result verification. Supports regulatory and accreditation requirements and patient safety.

Calculation Formula

SELECT
    DATE(lcn.sent_datetime) AS notif_date,
    COUNT(*) AS total_critical_notifications,
    COUNT(CASE
        WHEN lcn.acknowledged_datetime IS NOT NULL
             AND TIMESTAMPDIFF(
                 MINUTE,
                 lcn.sent_datetime,
                 lcn.acknowledged_datetime
             ) <= 30
        THEN 1 END
    ) AS acknowledged_within_30min,
    COUNT(CASE
        WHEN lcn.acknowledged_datetime IS NOT NULL
             AND TIMESTAMPDIFF(
                 MINUTE,
                 lcn.sent_datetime,
                 lcn.acknowledged_datetime
             ) <= 30
        THEN 1 END
    ) * 100.0 / COUNT(*) AS compliance_pct
FROM lab_critical_notifications lcn
JOIN lab_results lr
    ON lcn.result_id = lr.result_id
WHERE
    lr.is_critical = TRUE
    AND lcn.sent_datetime BETWEEN :start_datetime AND :end_datetime
GROUP BY
    DATE(lcn.sent_datetime);

Optional: compliance by department/provider using joins to encounters/providers.

Target / Benchmark

• Target: ≥ 95% acknowledged within 30 minutes.
• Rationale: Common threshold used by accredited labs in UAE and internationally.

Data Source

• lab_results(result_id, is_critical, verified_datetime, patient_id)
• lab_critical_notifications(notification_id, result_id, critical_value, notifying_tech_id, target_provider_id, notification_method, sent_datetime, acknowledged_datetime, acknowledged_by, escalation_level, read_back_confirmed)
• lab_critical_values(test_code_loinc, critical_low, critical_high, age_group, notification_required, escalation_minutes) for audit consistency.

Dimensions / Filters

• Time: day, week, month
• Facility, department
• Notification method (in-app, phone, SMS)
• Provider, provider specialty
• Lab section / test type

Visualization

• Gauge: overall compliance % vs 95% target.
• Line chart: daily compliance trend.
• Bar chart: compliance by department or notification method.
• Table: list of delayed notifications (>30 min) for audit.

Alert Thresholds

• Warning:
• Daily compliance < 95%.
• Critical:
• Any critical result with no acknowledgement after 60 minutes.
• Notification recipients:
• Lab Supervisor, Lab Director
• Quality/Patient Safety Officer (daily summary)
• Department head for repeated delays in their area.

KPI-LIS-004: Specimen Rejection Rate

Definition

Percentage of collected specimens that are rejected due to issues such as hemolysis, clotting, insufficient volume, wrong container, or labelling errors. High rates indicate problems in phlebotomy or pre-analytical processes.

Calculation Formula

SELECT
    DATE(ls.collection_datetime) AS collection_date,
    COUNT(*) AS total_specimens,
    COUNT(CASE
        WHEN ls.condition = 'rejected'
        THEN 1 END
    ) AS rejected_specimens,
    COUNT(CASE
        WHEN ls.condition = 'rejected'
        THEN 1 END
    ) * 100.0 / COUNT(*) AS rejection_rate_pct
FROM lab_specimens ls
WHERE
    ls.collection_datetime BETWEEN :start_datetime AND :end_datetime
GROUP BY
    DATE(ls.collection_datetime);

By rejection reason (assuming rejection_reason_code field):

SELECT
    ls.rejection_reason_code,
    COUNT(*) AS rejected_count,
    COUNT(*) * 100.0 / SUM(COUNT(*)) OVER() AS rejection_reason_pct
FROM lab_specimens ls
WHERE
    ls.condition = 'rejected'
    AND ls.collection_datetime BETWEEN :start_datetime AND :end_datetime
GROUP BY
    ls.rejection_reason_code;

Target / Benchmark

• Target: ≤ 2% overall rejection rate.
• Many UAE facilities adopt 1–2% as internal quality target.

Data Source

• lab_specimens(specimen_id, order_id, patient_id, specimen_type, tube_type, collection_datetime, collector_id, received_datetime, condition, rejection_reason_code, storage_location)

Dimensions / Filters

• Time: day, week, month
• Facility, department / collection location
• Collector (collector_id)
• Specimen type, tube type
• Lab section (via lab_order_tests)

Visualization

• Line chart: rejection rate trend.
• Bar chart: rejection rate by collector, department, or specimen type.
• Pareto chart: top rejection reasons.

Alert Thresholds

• Warning:
• Rejection rate > 2% for 3 consecutive days.
• Critical:
• Rejection rate > 5% on any single day.
• Notification recipients:
• Phlebotomy Supervisor, Lab Supervisor
• Nursing leadership for affected wards.

KPI-LIS-005: QC Pass Rate

Definition

Percentage of QC runs where QC status is “in control” according to configured Westgard rules. Indicates analytical reliability of lab instruments.

Calculation Formula

SELECT
    la.lab_section,
    DATE(lqr.run_datetime) AS qc_date,
    COUNT(*) AS total_qc_runs,
    COUNT(CASE
        WHEN lqr.qc_status = 'in_control'
        THEN 1 END
    ) AS in_control_runs,
    COUNT(CASE
        WHEN lqr.qc_status = 'in_control'
        THEN 1 END
    ) * 100.0 / COUNT(*) AS qc_pass_rate_pct
FROM lab_qc_records lqr
JOIN lab_analyzers la
    ON lqr.analyzer_id = la.analyzer_id
WHERE
    lqr.run_datetime BETWEEN :start_datetime AND :end_datetime
GROUP BY
    la.lab_section,
    DATE(lqr.run_datetime);

Target / Benchmark

• Target: ≥ 95% QC pass rate.
• Rationale: Internal quality target; persistent failures trigger investigation.

Data Source

• lab_qc_records(qc_id, analyzer_id, test_code, control_level, control_lot, observed_value, expected_mean, expected_sd, westgard_rule_violated, qc_status, run_datetime, technologist_id, corrective_action)
• lab_analyzers(analyzer_id, facility_id, lab_section, analyzer_name, manufacturer, model)

Dimensions / Filters

• Time: day, week, month
• Facility, lab section
• Analyzer, test code
• Control level (e.g., Level 1, Level 2)

Visualization

• Line chart: QC pass rate over time by analyzer or section.
• Heatmap: analyzer vs test showing pass rate.
• Table: QC failures with Westgard rule violated and corrective action.

Alert Thresholds

• Warning:
• QC pass rate < 95% for a given analyzer over last 7 days.
• Critical:
• Two consecutive QC failures for same test/analyzer.
• Notification recipients:
• QC Officer, Lab Supervisor, Lab Engineer.

KPI-LIS-006: Auto-Verification Rate

Definition

Percentage of results that are auto-verified by LIS rules (within reference range, delta check passed, QC in control) without manual intervention. Measures efficiency while ensuring safety.

Calculation Formula

Assuming lab_results.auto_verified (BOOLEAN) and lab_results.verification_mode (e.g., 'auto', 'manual'):

SELECT
    lot.lab_section,
    DATE(lr.verified_datetime) AS verified_date,
    COUNT(*) AS total_results,
    COUNT(CASE
        WHEN lr.auto_verified = TRUE
        THEN 1 END
    ) AS auto_verified_results,
    COUNT(CASE
        WHEN lr.auto_verified = TRUE
        THEN 1 END
    ) * 100.0 / COUNT(*) AS auto_verification_rate_pct
FROM lab_results lr
JOIN lab_order_tests lot
    ON lr.order_test_id = lot.order_test_id
WHERE
    lr.result_status = 'Final'
    AND lr.verified_datetime BETWEEN :start_datetime AND :end_datetime
GROUP BY
    lot.lab_section,
    DATE(lr.verified_datetime);

Target / Benchmark

• Target: ≥ 60% auto-verification rate overall.
• Rationale: Balance between automation and safety; actual achievable rate depends on test mix.

Data Source

• lab_results(result_id, order_test_id, result_status, verified_datetime, auto_verified, delta_check_flag, is_critical)
• lab_order_tests(order_test_id, lab_section, test_code_loinc)

Dimensions / Filters

• Time: day, week, month
• Lab section, test
• Facility
• Patient type (inpatient/outpatient)

Visualization

• Bar chart: auto-verification rate by lab section.
• Line chart: trend over time.
• Table: tests with lowest auto-verification rates (candidates for rule tuning).

Alert Thresholds

• Warning:
• Auto-verification rate drops by > 10 percentage points compared to previous month.
• Critical:
• Sudden drop to < 30% for any major section (e.g., Chemistry).
• Notification recipients:
• Lab Supervisor, Lab Informatics / LIS Administrator.

KPI-LIS-007: Test Volume (Daily/Monthly)

Definition

Number of completed tests per period, by lab section and test. Used for capacity planning, staffing, and cost analysis.

Calculation Formula

Daily volume by section:

SELECT
    DATE(lr.verified_datetime) AS result_date,
    lot.lab_section,
    COUNT(*) AS completed_tests
FROM lab_results lr
JOIN lab_order_tests lot
    ON lr.order_test_id = lot.order_test_id
WHERE
    lr.result_status = 'Final'
    AND lr.verified_datetime BETWEEN :start_datetime AND :end_datetime
GROUP BY
    DATE(lr.verified_datetime),
    lot.lab_section;

Monthly volume by test:

SELECT
    DATE_FORMAT(lr.verified_datetime, '%Y-%m') AS result_month,
    lot.lab_section,
    lot.test_code_loinc,
    lot.test_name,
    COUNT(*) AS completed_tests
FROM lab_results lr
JOIN lab_order_tests lot
    ON lr.order_test_id = lot.order_test_id
WHERE
    lr.result_status = 'Final'
    AND lr.verified_datetime BETWEEN :start_datetime AND :end_datetime
GROUP BY
    DATE_FORMAT(lr.verified_datetime, '%Y-%m'),
    lot.lab_section,
    lot.test_code_loinc,
    lot.test_name;

Target / Benchmark

• No fixed numeric target; used to:
• Identify growth trends.
• Support staffing and analyzer capacity decisions.
• Feed into cost-per-test calculations.

Data Source

• lab_results(result_id, order_test_id, result_status, verified_datetime)
• lab_order_tests(order_test_id, order_id, lab_section, test_code_loinc, test_name)
• lab_orders(order_id, facility_id, priority)

Dimensions / Filters

• Time: day, week, month, year
• Facility, department
• Lab section, test
• Priority (STAT vs routine)
• Payer (via encounter/insurance linkage for RCM)

Visualization

• Line chart: total test volume over time.
• Stacked bar chart: volume by section per month.
• Heatmap: hour-of-day vs day-of-week volume.

Alert Thresholds

• Warning:
• Volume > 120% of same day last month (potential overload).
• Critical:
• Volume > 150% of baseline for 3 consecutive days.
• Notification recipients:
• Lab Supervisor, Operations Manager.

KPI-LIS-008: Send-Out TAT Compliance

Definition

Percentage of send-out tests where results are received within the expected turnaround time defined for each reference lab/test. Ensures performance of contracted reference labs.

Calculation Formula

Assuming lab_sendout_orders.expected_tat_days and result_received_datetime:

SELECT
    DATE(lso.shipped_datetime) AS shipped_date,
    lso.reference_lab_id,
    lso.reference_lab_name,
    COUNT(*) AS total_sendouts,
    COUNT(CASE
        WHEN lso.result_received_datetime IS NOT NULL
             AND DATEDIFF(
                 lso.result_received_datetime,
                 lso.shipped_datetime
             ) <= lso.expected_tat_days
        THEN 1 END
    ) AS within_expected_tat,
    COUNT(CASE
        WHEN lso.result_received_datetime IS NOT NULL
             AND DATEDIFF(
                 lso.result_received_datetime,
                 lso.shipped_datetime
             ) <= lso.expected_tat_days
        THEN 1 END
    ) * 100.0 / COUNT(*) AS tat_compliance_pct
FROM lab_sendout_orders lso
WHERE
    lso.shipped_datetime BETWEEN :start_datetime AND :end_datetime
GROUP BY
    DATE(lso.shipped_datetime),
    lso.reference_lab_id,
    lso.reference_lab_name;

Target / Benchmark

• Target: ≥ 90% of send-out tests reported within expected TAT.
• Rationale: Common contractual expectation with reference labs.

Data Source

• lab_sendout_orders(sendout_id, order_test_id, reference_lab_id, reference_lab_name, shipped_datetime, tracking_number, expected_tat_days, result_received_datetime, status)
• lab_order_tests(order_test_id, test_code_loinc, test_name)

Dimensions / Filters

• Time: week, month, quarter
• Reference lab
• Test / test category
• Facility

Visualization

• Bar chart: TAT compliance by reference lab.
• Line chart: overall send-out TAT compliance over time.
• Table: send-outs exceeding expected TAT.

Alert Thresholds

• Warning:
• TAT compliance for any reference lab < 90% in a month.
• Critical:
• TAT compliance < 80% or repeated monthly underperformance.
• Notification recipients:
• Lab Supervisor, Lab Director, Contracts/Procurement.

KPI-LIS-009: Amended/Corrected Result Rate

Definition

Percentage of final results that are later amended/corrected. High rates may indicate analytical or process issues.

Calculation Formula

Assuming lab_results.result_status can be 'preliminary', 'final', 'corrected' and original_result_id for corrections (or status history in audit table):

SELECT
    DATE(lr.verified_datetime) AS result_date,
    COUNT(*) AS total_final_results,
    COUNT(CASE
        WHEN lr.result_status = 'corrected'
        THEN 1 END
    ) AS corrected_results,
    COUNT(CASE
        WHEN lr.result_status = 'corrected'
        THEN 1 END
    ) * 100.0 / COUNT(*) AS corrected_rate_pct
FROM lab_results lr
WHERE
    lr.result_status IN ('Final', 'corrected')
    AND lr.verified_datetime BETWEEN :start_datetime AND :end_datetime
GROUP BY
    DATE(lr.verified_datetime);

If corrections are tracked separately (e.g., is_correction = TRUE), adjust accordingly.

Target / Benchmark

• Target: ≤ 2% corrected result rate.
• Rationale: Internal quality target; persistent high rates require root cause analysis.

Data Source

• lab_results(result_id, order_test_id, result_status, verified_datetime, is_critical, delta_check_flag, corrected_from_result_id)
• lab_result_components (for detailed analysis of which components are corrected)

Dimensions / Filters

• Time: month, quarter
• Lab section, test
• Facility
• Reason for correction (if captured in audit log)

Visualization

• Line chart: corrected result rate over time.
• Bar chart: corrected rate by lab section.
• Table: list of corrected results with reason and responsible staff (for QA review).

Alert Thresholds

• Warning:
• Corrected result rate > 2% for 2 consecutive months.
• Critical:
• Corrected result rate > 5% in any month.
• Notification recipients:
• Lab Director, Quality Officer.

KPI-LIS-010: NABIDH/Malaffi Result Submission Rate

Definition

Percentage of final lab results successfully submitted and accepted by the relevant HIE (NABIDH for Dubai, Malaffi for Abu Dhabi). Supports DHA/DOH interoperability mandates.

Calculation Formula

Assuming integration_message_log tracks submissions with source_module = 'LIS' and message_type = 'ORU^R01' or FHIR DiagnosticReport:

SELECT
    DATE(lr.verified_datetime) AS result_date,
    COUNT(*) AS total_final_results,
    COUNT(CASE
        WHEN iml.hie_submission_status = 'accepted'
        THEN 1 END
    ) AS accepted_results,
    COUNT(CASE
        WHEN iml.hie_submission_status = 'accepted'
        THEN 1 END
    ) * 100.0 / COUNT(*) AS submission_rate_pct
FROM lab_results lr
LEFT JOIN integration_message_log iml
    ON iml.source_module = 'LIS'
   AND iml.source_record_type = 'lab_result'
   AND iml.source_record_id = lr.result_id
   AND iml.message_type IN ('ORU^R01', 'FHIR DiagnosticReport')
WHERE
    lr.result_status = 'Final'
    AND lr.verified_datetime BETWEEN :start_datetime AND :end_datetime
GROUP BY
    DATE(lr.verified_datetime);

Target / Benchmark

• Target: ≥ 99.5% submission acceptance rate.
• Rationale: High reliability expected by DHA (NABIDH) and DOH (Malaffi) programmes.

Data Source

• lab_results(result_id, result_status, verified_datetime, patient_id)
• integration_message_log(message_id, source_module, source_record_type, source_record_id, message_type, destination_system, hie_submission_status, error_code, error_message, sent_datetime, ack_datetime)

Dimensions / Filters

• Time: day, week, month
• HIE (NABIDH vs Malaffi vs others)
• Facility
• Lab section, test

Visualization

• Gauge: overall submission rate vs 99.5% target.
• Line chart: daily submission rate.
• Table: failed submissions with error codes for remediation.

Alert Thresholds

• Warning:
• Submission rate < 99.5% for any day.
• Critical:
• Submission rate < 98% or continuous failures > 30 minutes.
• Notification recipients:
• HIS Integration Team, Lab Informatics, Compliance Officer.

KPI-LIS-011: Phlebotomy Collection Efficiency

Definition

Percentage of specimen collections successfully completed on the first attempt. Reflects phlebotomy skill and patient experience.

Calculation Formula

Assuming lab_specimens.first_attempt_successful (BOOLEAN) or attempt_number:

SELECT
    DATE(ls.collection_datetime) AS collection_date,
    COUNT(*) AS total_collections,
    COUNT(CASE
        WHEN ls.first_attempt_successful = TRUE
        THEN 1 END
    ) AS first_attempt_successes,
    COUNT(CASE
        WHEN ls.first_attempt_successful = TRUE
        THEN 1 END
    ) * 100.0 / COUNT(*) AS collection_efficiency_pct
FROM lab_specimens ls
WHERE
    ls.collection_datetime BETWEEN :start_datetime AND :end_datetime
GROUP BY
    DATE(ls.collection_datetime);

If using attempt_number:

COUNT(CASE WHEN ls.attempt_number = 1 THEN 1 END) * 100.0 / COUNT(*)

Target / Benchmark

• Target: ≥ 95% first-attempt success.
• Rationale: Common internal quality target; improves patient satisfaction.

Data Source

• lab_specimens(specimen_id, order_id, patient_id, collection_datetime, collector_id, condition, first_attempt_successful, attempt_number, rejection_reason_code)
• lab_orders(order_id, facility_id, encounter_id)

Dimensions / Filters

• Time: day, week, month
• Collector, shift
• Facility, department
• Patient age group (paediatric vs adult)

Visualization

• Bar chart: efficiency by collector.
• Line chart: overall efficiency trend.
• Heatmap: efficiency by time of day and day of week.

Alert Thresholds

• Warning:
• Collector efficiency < 90% over last month.
• Critical:
• Overall efficiency < 90% for the lab.
• Notification recipients:
• Phlebotomy Supervisor, Nursing Education (for targeted training).

KPI-LIS-012: Antibiogram Currency

Definition

Number of days since the last facility antibiogram was generated from microbiology susceptibility data. Ensures up-to-date support for antimicrobial stewardship, as expected by DOH/DHA and internal stewardship programmes.

Calculation Formula

Assuming an antibiogram_metadata table or a configuration entry with last_generated_datetime:

SELECT
    DATEDIFF(
        CURRENT_DATE,
        am.last_generated_datetime
    ) AS days_since_last_antibiogram
FROM antibiogram_metadata am
WHERE
    am.facility_id = :facility_id
ORDER BY
    am.last_generated_datetime DESC
LIMIT 1;

If no metadata table exists, the LIS should create one as part of the stewardship feature.

Target / Benchmark

• Target: Updated at least annually (≤ 365 days).
• Many stewardship programmes in UAE aim for annual antibiogram updates; some high-volume centres may update more frequently.

Data Source

• antibiogram_metadata(antibiogram_id, facility_id, generated_datetime, period_start, period_end, generated_by)
• Underlying data: lab_micro_sensitivities, lab_micro_cultures (used to generate antibiogram but not directly for the KPI).

Dimensions / Filters

• Facility
• Service line (e.g., adult vs paediatric antibiogram, ICU vs general wards) if tracked.

Visualization

• Single KPI card: “Days since last antibiogram”.
• Gauge: 0–365 days with green/yellow/red zones.

Alert Thresholds

• Warning:
• Days since last antibiogram > 330 days.
• Critical:
• Days since last antibiogram > 365 days.
• Notification recipients:
• Microbiologist, Pharmacy (Antimicrobial Stewardship), Lab Director.

Standard Reports

Dashboard Wireframe

The Lab Analytics Dashboard (SCR-LIS-010) should present key KPIs with filters and drill-down.

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Lab Analytics Dashboard</title>
</head>
<body style="font-family: Arial, sans-serif; margin: 0; padding: 0; background:#f5f5f5;">
  <!-- Top Filter Bar -->
  <div style="background:#004b8d; color:#fff; padding:10px 20px;">
    <div style="display:flex; justify-content:space-between; align-items:center;">
      <div style="font-size:18px; font-weight:bold;">Lab Analytics Dashboard</div>
      <div>
        <label style="margin-right:10px; font-size:12px;">
          Facility:
          <select style="margin-left:5px; padding:2px 4px;">
            <option>All Facilities</option>
            <option>Dubai General Hospital</option>
            <option>Abu Dhabi Medical Center</option>
          </select>
        </label>
        <label style="margin-right:10px; font-size:12px;">
          Lab Section:
          <select style="margin-left:5px; padding:2px 4px;">
            <option>All Sections</option>
            <option>Chemistry</option>
            <option>Hematology</option>
            <option>Microbiology</option>
            <option>Pathology</option>
          </select>
        </label>
        <label style="margin-right:10px; font-size:12px;">
          Period:
          <select style="margin-left:5px; padding:2px 4px;">
            <option>Last 24 hours</option>
            <option>Last 7 days</option>
            <option>Last 30 days</option>
            <option>Custom...</option>
          </select>
        </label>
        <button style="padding:4px 10px; font-size:12px;">Apply</button>
      </div>
    </div>
  </div>

  <!-- KPI Cards Row -->
  <div style="display:flex; flex-wrap:wrap; padding:15px; gap:10px;">
    <div style="flex:1; min-width:180px; background:#fff; border-radius:4px; padding:10px; box-shadow:0 1px 3px rgba(0,0,0,0.1);">
      <div style="font-size:12px; color:#666;">Routine TAT (Chemistry)</div>
      <div style="font-size:22px; font-weight:bold; color:#333;">3.2 h</div>
      <div style="font-size:11px; color:#0a0;">Within 4 h target</div>
    </div>
    <div style="flex:1; min-width:180px; background:#fff; border-radius:4px; padding:10px; box-shadow:0 1px 3px rgba(0,0,0,0.1);">
      <div style="font-size:12px; color:#666;">STAT TAT Compliance</div>
      <div style="font-size:22px; font-weight:bold; color:#333;">92%</div>
      <div style="font-size:11px; color:#e67e22;">Target ≥ 95%</div>
    </div>
    <div style="flex:1; min-width:180px; background:#fff; border-radius:4px; padding:10px; box-shadow:0 1px 3px rgba(0,0,0,0.1);">
      <div style="font-size:12px; color:#666;">Critical Value Compliance</div>
      <div style="font-size:22px; font-weight:bold; color:#333;">97%</div>
      <div style="font-size:11px; color:#0a0;">Target ≥ 95%</div>
    </div>
    <div style="flex:1; min-width:180px; background:#fff; border-radius:4px; padding:10px; box-shadow:0 1px 3px rgba(0,0,0,0.1);">
      <div style="font-size:12px; color:#666;">Specimen Rejection Rate</div>
      <div style="font-size:22px; font-weight:bold; color:#333;">1.4%</div>
      <div style="font-size:11px; color:#0a0;">Target ≤ 2%</div>
    </div>
    <div style="flex:1; min-width:180px; background:#fff; border-radius:4px; padding:10px; box-shadow:0 1px 3px rgba(0,0,0,0.1);">
      <div style="font-size:12px; color:#666;">HIE Submission Rate</div>
      <div style="font-size:22px; font-weight:bold; color:#333;">99.7%</div>
      <div style="font-size:11px; color:#0a0;">Target ≥ 99.5%</div>
    </div>
  </div>

  <!-- Charts Row -->
  <div style="display:flex; flex-wrap:wrap; padding:0 15px 15px 15px; gap:10px;">
    <!-- Left: TAT Trend -->
    <div style="flex:2; min-width:320px; background:#fff; border-radius:4px; padding:10px; box-shadow:0 1px 3px rgba(0,0,0,0.1);">
      <div style="font-size:13px; font-weight:bold; margin-bottom:5px;">Turnaround Time Trend</div>
      <div style="font-size:11px; color:#666; margin-bottom:5px;">Average TAT (minutes) by day, Routine vs STAT</div>
      <div style="border:1px dashed #ccc; height:200px; text-align:center; line-height:200px; color:#999; font-size:12px;">
        [Line Chart Placeholder]
      </div>
    </div>
    <!-- Right: Volume by Section -->
    <div style="flex:1; min-width:260px; background:#fff; border-radius:4px; padding:10px; box-shadow:0 1px 3px rgba(0,0,0,0.1);">
      <div style="font-size:13px; font-weight:bold; margin-bottom:5px;">Test Volume by Section</div>
      <div style="border:1px dashed #ccc; height:200px; text-align:center; line-height:200px; color:#999; font-size:12px;">
        [Stacked Bar Chart Placeholder]
      </div>
    </div>
  </div>

  <!-- Bottom Row: QC & Rejections -->
  <div style="display:flex; flex-wrap:wrap; padding:0 15px 20px 15px; gap:10px;">
    <!-- QC Performance -->
    <div style="flex:1; min-width:320px; background:#fff; border-radius:4px; padding:10px; box-shadow:0 1px 3px rgba(0,0,0,0.1);">
      <div style="font-size:13px; font-weight:bold; margin-bottom:5px;">QC Pass Rate by Analyzer</div>
      <div style="border:1px dashed #ccc; height:160px; text-align:center; line-height:160px; color:#999; font-size:12px;">
        [Heatmap / Bar Chart Placeholder]
      </div>
    </div>
    <!-- Specimen Rejection Table -->
    <div style="flex:1; min-width:320px; background:#fff; border-radius:4px; padding:10px; box-shadow:0 1px 3px rgba(0,0,0,0.1);">
      <div style="display:flex; justify-content:space-between; align-items:center; margin-bottom:5px;">
        <div style="font-size:13px; font-weight:bold;">Specimen Rejections (Last 7 Days)</div>
        <button style="padding:3px 8px; font-size:11px;">Export CSV</button>
      </div>
      <table style="width:100%; border-collapse:collapse; font-size:11px;">
        <thead>
          <tr>
            <th style="border-bottom:1px solid #ddd; text-align:left; padding:3px;">Date</th>
            <th style="border-bottom:1px solid #ddd; text-align:left; padding:3px;">Reason</th>
            <th style="border-bottom:1px solid #ddd; text-align:left; padding:3px;">Section</th>
            <th style="border-bottom:1px solid #ddd; text-align:right; padding:3px;">Count</th>
          </tr>
        </thead>
        <tbody>
          <tr>
            <td style="border-bottom:1px solid #f0f0f0; padding:3px;">2026-02-05</td>
            <td style="border-bottom:1px solid #f0f0f0; padding:3px;">Hemolysed</td>
            <td style="border-bottom:1px solid #f0f0f0; padding:3px;">Chemistry</td>
            <td style="border-bottom:1px solid #f0f0f0; padding:3px; text-align:right;">8</td>
          </tr>
          <tr>
            <td style="border-bottom:1px solid #f0f0f0; padding:3px;">2026-02-05</td>
            <td style="border-bottom:1px solid #f0f0f0; padding:3px;">Clotted</td>
            <td style="border-bottom:1px solid #f0f0f0; padding:3px;">Hematology</td>
            <td style="border-bottom:1px solid #f0f0f0; padding:3px; text-align:right;">5</td>
          </tr>
          <tr>
            <td style="border-bottom:1px solid #f0f0f0; padding:3px;">2026-02-04</td>
            <td style="border-bottom:1px solid #f0f0f0; padding:3px;">Wrong Tube</td>
            <td style="border-bottom:1px solid #f0f0f0; padding:3px;">Chemistry</td>
            <td style="border-bottom:1px solid #f0f0f0; padding:3px; text-align:right;">3</td>
          </tr>
        </tbody>
      </table>
    </div>
  </div>
</body>
</html>

Regulatory Reports

All regulatory references are UAE-specific and must align with MOH, DOH, DHA, and UAE PDPL requirements. The LIS reporting layer should support generation of the following:

1. MOH (Federal) Laboratory Activity & Quality Reports

• MOH-LAB-001: National Laboratory Activity Summary
• Content:
  • Total tests by category (chemistry, hematology, microbiology, pathology).
  • Notifiable disease-related tests and positive results (e.g., TB, HIV, hepatitis).
  • Aggregate QC performance.
• Data sources:
  • lab_order_tests, lab_results, lab_micro_cultures, lab_micro_sensitivities, lab_qc_records.
• Format:
  • Aggregated Excel/PDF; optional XML/CSV for MOH portals.

• Frequency:

  • Monthly/Quarterly (configurable).

• MOH-LAB-002: Critical Value Policy Compliance

• Content:
  • Critical value notification compliance (KPI-LIS-003).
  • Escalation statistics and unacknowledged criticals.
• Data sources:
  • lab_critical_notifications, lab_results, lab_critical_values.
• Format:
  • PDF with sign-off by Lab Director.

2. DOH (Abu Dhabi) / Malaffi Reports

• DOH-LAB-001: DOH Statistical Submission
• Content:
  • Test volumes by specialty, inpatient/outpatient, and payer (THIQA, Daman, etc.).
  • Turnaround times for key tests (e.g., troponin, INR).
• Data sources:
  • lab_orders, lab_order_tests, lab_results, payer linkage from RCM.

• Format:

  • Excel/CSV aligned with DOH templates.

• DOH-LAB-002: Malaffi Integration Quality

• Content:
  • HIE submission rate (KPI-LIS-010) for Abu Dhabi facilities.
  • Error breakdown by error code, test type.
• Data sources:
  • integration_message_log, lab_results.
• Format:
  • Dashboard + PDF.

3. DHA (Dubai) / NABIDH Reports

• DHA-LAB-001: DHA Lab Statistics
• Content:
  • Test volumes, TAT metrics, and rejection rates for Dubai facilities.
• Data sources:
  • lab_orders, lab_order_tests, lab_results, lab_specimens.

• Format:

  • Excel/PDF.

• DHA-LAB-002: NABIDH Result Submission Compliance

• Content:
  • NABIDH submission rate (subset of KPI-LIS-010 for Dubai).
  • List of failed ORU^R01 messages with reasons.
• Data sources:
  • integration_message_log, lab_results.
• Format:
  • Dashboard + exportable CSV.

4. UAE PDPL (Federal Decree-Law No. 45/2021) Reports

• PDPL-LAB-001: Lab Data Access Audit
• Purpose:
  • Demonstrate who accessed lab results, when, and for what purpose.
• Content:
  • Access logs for lab-related resources (patient, provider, timestamp, action).
• Data sources:
  • audit_log (cross-module), filtered where module = 'LIS' or resource type is lab-related.

• Format:

  • CSV/PDF; supports data subject access requests.

• PDPL-LAB-002: Data Retention & De-identification Report

• Purpose:
  • Show retention periods and anonymisation/pseudonymisation status for lab data used in analytics.
• Content:
  • Counts of records by age, retention policy applied, anonymisation flags.
• Data sources:
  • lab_results, lab_result_components, retention policy tables.
• Format:
  • PDF/Excel.

5. Infection Control & Notifiable Disease Reports

• IC-LAB-001: Notifiable Pathogen Summary
• Content:
  • Positive cultures for notifiable organisms (e.g., TB, MRSA, CRE), with counts by month.
• Data sources:
  • lab_micro_cultures, lab_micro_sensitivities (organism_code_snomed, organism_name).
• Format:
  • PDF/Excel; supports MOH and emirate-level disease surveillance.

Ad-Hoc Reporting

The LIS should provide a self-service reporting layer (for authorised roles) with the following capabilities:

Available Data Fields (Examples)

Orders & Tests

• lab_orders.order_id
• lab_orders.patient_id (via FK to patients)
• lab_orders.encounter_id
• lab_orders.ordering_provider_id
• lab_orders.order_datetime
• lab_orders.order_status
• lab_orders.priority
• lab_orders.clinical_indication
• lab_orders.fasting_status
• lab_orders.accession_number

• lab_orders.facility_id

• lab_order_tests.order_test_id

• lab_order_tests.order_id
• lab_order_tests.test_code_loinc
• lab_order_tests.test_name
• lab_order_tests.lab_section
• lab_order_tests.specimen_type_required
• lab_order_tests.status

Specimens

• lab_specimens.specimen_id
• lab_specimens.order_id
• lab_specimens.patient_id
• lab_specimens.specimen_type
• lab_specimens.tube_type
• lab_specimens.collection_datetime
• lab_specimens.collector_id
• lab_specimens.received_datetime
• lab_specimens.condition
• lab_specimens.rejection_reason_code
• lab_specimens.storage_location

Results

• lab_results.result_id
• lab_results.order_test_id
• lab_results.patient_id
• lab_results.test_code_loinc
• lab_results.result_status
• lab_results.reported_datetime
• lab_results.verified_by
• lab_results.verified_datetime
• lab_results.is_critical
• lab_results.delta_check_flag

• lab_results.auto_verified

• lab_result_components.component_id

• lab_result_components.result_id
• lab_result_components.component_code_loinc
• lab_result_components.component_name
• lab_result_components.value_numeric
• lab_result_components.value_text
• lab_result_components.value_coded
• lab_result_components.unit
• lab_result_components.abnormal_flag

QC & Analyzers

• lab_qc_records.qc_id
• lab_qc_records.analyzer_id
• lab_qc_records.test_code
• lab_qc_records.control_level
• lab_qc_records.observed_value
• lab_qc_records.expected_mean
• lab_qc_records.westgard_rule_violated
• lab_qc_records.qc_status

• lab_qc_records.run_datetime

• lab_analyzers.analyzer_id

• lab_analyzers.facility_id
• lab_analyzers.lab_section
• lab_analyzers.analyzer_name
• lab_analyzers.manufacturer
• lab_analyzers.model

Critical Values & Notifications

• lab_critical_values.test_code_loinc
• lab_critical_values.critical_low
• lab_critical_values.critical_high

• lab_critical_values.age_group

• lab_critical_notifications.notification_id

• lab_critical_notifications.result_id
• lab_critical_notifications.critical_value
• lab_critical_notifications.notifying_tech_id
• lab_critical_notifications.target_provider_id
• lab_critical_notifications.notification_method
• lab_critical_notifications.sent_datetime
• lab_critical_notifications.acknowledged_datetime
• lab_critical_notifications.escalation_level
• lab_critical_notifications.read_back_confirmed

Microbiology & Pathology

• lab_micro_cultures.culture_id
• lab_micro_cultures.specimen_id
• lab_micro_cultures.organism_code_snomed
• lab_micro_cultures.organism_name
• lab_micro_cultures.colony_count

• lab_micro_cultures.identified_datetime

• lab_micro_sensitivities.sensitivity_id

• lab_micro_sensitivities.culture_id
• lab_micro_sensitivities.antibiotic_code
• lab_micro_sensitivities.antibiotic_name
• lab_micro_sensitivities.mic_value

• lab_micro_sensitivities.interpretation

• lab_pathology_cases.case_id

• lab_pathology_cases.case_number
• lab_pathology_cases.case_type
• lab_pathology_cases.diagnosis_code_snomed
• lab_pathology_cases.icd10_code
• lab_pathology_cases.pathologist_id
• lab_pathology_cases.signed_datetime

Export Formats

• CSV:
• For data analysis in external tools (e.g., Excel, BI platforms).
• Must support UTF-8 for Arabic names.
• Excel (XLSX):
• For management reports and pivot tables.
• PDF:
• For official reports, sign-off, and regulatory submissions.
• Secure APIs:
• Optional FHIR-based export of aggregated metrics for enterprise BI.

All exports must respect UAE PDPL:

• Role-based access control.
• De-identification options (e.g., remove MRN/Emirates ID for research extracts).
• Audit logging of report generation and downloads.

Scheduled Report Delivery

The reporting subsystem should support:

• Scheduling Options
• Daily, weekly, monthly, quarterly.
• Specific time of day (e.g., 06:00 UAE time).
• Delivery Channels
• Secure email with link to report (no PHI in email body).
• Internal portal notifications.
• Secure SFTP drop for machine-to-machine transfers (e.g., to corporate BI).
• Recipients & Access Control
• Recipients configured per report.
• Access validated at download time against current roles/permissions.
• Audit & PDPL Compliance
• Log: report ID, parameters, generated_by, generated_at, delivered_to, delivery_method.
• Retention of report outputs according to organisational policy.

This specification provides developer-ready definitions for LIS KPIs, standard and regulatory reports, dashboard layout, and ad-hoc reporting capabilities, aligned with UAE regulatory and interoperability requirements.