Tupaia Report
Tupaia Report Documentation[edit | edit source]
Overview[edit | edit source]
The Tupaia Report provides insights into client support activities (e.g., Oral Care, Mental Health) and financials (costs, referrals) for Tupaia cases. It includes:
- Client Stages (Aggregated): Summary counts of client progress
- Client Stages (Detail): Individual client status tracking
- Report Details: All Tupaia activity details
Data is consolidated from Kotahi CMS into three materialized views for efficient Power BI reporting.
Key Concepts[edit | edit source]
Definitions[edit | edit source]
| Term | Description |
|---|---|
| Assessment Done | Indicates Tupaia assessment completion status (Yes/No)
|
| Intervention Stage | Client progress status (see Stages Definition) |
| Cost | Approved payment amounts from JSON fields |
Stages Definition[edit | edit source]
| Stage | Criteria |
|---|---|
| Need Identified | Requirement detected, no activity exists |
| Intervention Booked | Activity scheduled but not completed |
| Intervention Completed | Service marked as done in JSON |
| Referral Received | External referral logged with date |
| Declined | Client actively declined support |
| Not Applicable | No action needed or data missing |
Configuration[edit | edit source]
- Access: Currently limited to Moli and data team
- Refresh: cron jobs refresh materialized views
Technical Design[edit | edit source]
Data Sources[edit | edit source]
| Table | Purpose |
|---|---|
kotahi.clients
|
Client demographics and case names |
kotahi.activities
|
Activity records and JSON metadata |
kotahi.clientrelationships
|
Family/household calculations |
Materialized Views[edit | edit source]
kotahi.mvw_tupaia_report[edit | edit source]
Purpose: Powers Report Details page with all activity details relating to Tupaia service
Key Columns:
| Column | Description |
|---|---|
| Whānau Ora Case ID | Case identifier from CustomFields |
| Cost($) | Approved payment amount from JSON |
kotahi.mvw_tupaia_stages[edit | edit source]
Purpose: Drives Client Stages (Detail) page with individual client statuses
Key Columns:
| Column | Description |
|---|---|
| DentalStatus | Current dental intervention state |
| EyeHearingStatus | Vision/auditory care progress |
| MHStatus | Mental health referral status |
Business Rules:
- Excludes clients discharged (ActivityTypeId=33)
- Uses lateral joins for latest activity per type
- JSON field driven status calculations
kotahi.mvw_tupaia_stages_counts[edit | edit source]
Purpose: Provides aggregated numbers for Client Stages (Aggregated)
Example Metrics:
| Metric | Description |
|---|---|
| dental_need_identified | Clients needing dental intervention |
| bowel_up_to_date | Client bowel screening is up to date |
Reports[edit | edit source]
Power BI Pages[edit | edit source]
| Page | Source MV | Features |
|---|---|---|
| Report Details | mvw_tupaia_report | Tupaia activities details |
| Client Stages (Detail) | mvw_tupaia_stages | Individual client journeys |
| Client Stages (Aggregated) | mvw_tupaia_stages_counts | Service utilization metrics |