> ## Documentation Index
> Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Reporting Guide

> Learn how to create, run, and export reports in Encore Health OS.

Learn how to create, run, and export reports in Encore Health OS.

***

## Overview

The Encore Health OS reporting engine provides:

* **Query Builder** - Visual interface for creating reports (no SQL required)
* **SQL Editor** - Advanced users can write custom SQL
* **Parameters** - Dynamic reports with user inputs
* **Multiple Formats** - Export to CSV, Excel, PDF
* **Caching** - Fast performance for frequently-run reports
* **Scheduling** - Automated report delivery (coming soon)

***

## Viewing Reports

### Report Library

Access all available reports:

1. Click **Reports** in main menu
2. View reports organized by:
   * **My Reports** - Created by you
   * **Shared with Me** - Shared by others
   * **Organization Reports** - Available to all
   * **Categories** - Financial, HR, Clinical, etc.

### Running a Report

1. Click report name to open
2. If parameters required, fill them in:
   * Date ranges
   * Site selection
   * Department filter
   * etc.
3. Click **Run Report**
4. View results in table format

### Report Results

**View Options:**

* **Table** - Standard row/column view
* **Chart** - Visual representation (if configured)
* **Summary** - Key metrics at top

**Actions:**

* **Export** - Download in various formats
* **Print** - Print-friendly view
* **Share** - Send to colleagues
* **Save** - Bookmark for quick access

***

## Creating Reports (Basic)

### Using the Query Builder

**No SQL knowledge required!**

1. Click **New Report** → **Query Builder**
2. **Step 1: Choose Data Source**
   * Select table (e.g., "Employees", "Transactions")
3. **Step 2: Select Columns**
   * Check columns to include
   * Drag to reorder
4. **Step 3: Add Filters**
   * Click **+ Add Filter**
   * Choose column, operator, value
   * Example: `Status = Active`
5. **Step 4: Sort Results**
   * Choose sort column and direction
6. **Step 5: Preview**
   * Click **Preview** to see results
7. **Step 6: Save**
   * Enter report name and description
   * Choose category
   * Set permissions
   * **Save Report**

### Example: Active Employees Report

**Goal:** List all active employees with their department and hire date.

**Steps:**

1. Data Source: `Employees`
2. Columns:
   * Full Name
   * Department
   * Hire Date
   * Email
3. Filters:
   * Status = Active
4. Sort:
   * Department (A-Z)
   * Then by Last Name (A-Z)
5. Save as "Active Employees by Department"

***

## Creating Reports (Advanced)

### Using SQL Editor

**For advanced users familiar with SQL.**

1. Click **New Report** → **SQL Editor**
2. Write SQL query:

```sql theme={null}
SELECT 
  e.full_name,
  d.name AS department,
  e.hire_date,
  e.email
FROM hr_employees e
JOIN fa_departments d ON e.department_id = d.id
WHERE e.status = 'active'
  AND e.organization_id = auth.uid_org() -- Automatic tenant isolation
ORDER BY d.name, e.last_name;
```

3. Click **Run** to test
4. Save when satisfied

**Security Note:** Row-Level Security (RLS) automatically enforced. You can only query data your role allows.

***

## Report Parameters

### What Are Parameters?

Parameters allow users to customize reports at runtime:

* **Date Range** - "Show transactions between \[Start Date] and \[End Date]"
* **Site Selection** - "Show data for \[Site]"
* **Status Filter** - "Show residents with status \[Active/Discharged/All]"

### Adding Parameters

1. In Query Builder or SQL Editor, click **Add Parameter**
2. Configure:
   * **Name** - Internal reference (e.g., `start_date`)
   * **Label** - User-facing label ("Start Date")
   * **Type** - Date, text, number, dropdown, etc.
   * **Required** - Must user fill this in?
   * **Default Value** - Pre-filled value

### Using Parameters in SQL

```sql theme={null}
SELECT *
FROM fa_transactions
WHERE transaction_date >= {{start_date}}
  AND transaction_date <= {{end_date}}
  AND site_id = {{site_id}};
```

**Parameter Substitution:**

* `{{start_date}}` replaced with user's input
* Type-safe (dates remain dates, no SQL injection)

### Parameter Types

| Type             | Example Use Case                |
| ---------------- | ------------------------------- |
| **Date**         | "Report start date"             |
| **Date Range**   | "Reporting period"              |
| **Text**         | "Employee name search"          |
| **Number**       | "Minimum amount threshold"      |
| **Dropdown**     | "Select site" (from list)       |
| **Multi-Select** | "Choose departments" (multiple) |
| **Boolean**      | "Include inactive?" (Yes/No)    |

***

## Exporting Reports

### Export Formats

Click **Export** and choose format:

| Format            | Best For                    | Features                      |
| ----------------- | --------------------------- | ----------------------------- |
| **CSV**           | Excel import, data analysis | Plain text, all data          |
| **Excel (.xlsx)** | Formatted spreadsheets      | Formatting, formulas, charts  |
| **PDF**           | Printing, emailing          | Fixed layout, headers/footers |
| **JSON**          | API integration             | Machine-readable              |

### Export Options

Configure export:

* **Include Headers** - Column names in first row
* **Date Format** - MM/DD/YYYY or YYYY-MM-DD
* **Decimal Places** - 2 (default) or custom
* **Page Orientation** - Portrait/Landscape (PDF only)
* **Logo** - Include org logo (PDF only)

### Scheduled Exports (Coming Soon)

Automate report delivery:

1. Create/open report
2. Click **Schedule**
3. Configure:
   * **Frequency** - Daily, weekly, monthly
   * **Recipients** - Email addresses
   * **Format** - CSV, Excel, PDF
   * **Parameters** - Use defaults or prompt user
4. **Save Schedule**

***

## Report Caching

### How Caching Works

Frequently-run reports cache results for faster loading:

* **Cache Duration** - Results stored for X minutes
* **Automatic Refresh** - Cache clears when data changes
* **Per-User** - Your results don't affect others

### Cache Indicators

* 🟢 **Cached** - Results loaded from cache (fast)
* 🟡 **Refreshing** - Cache expired, re-running query
* 🔴 **Expired** - Cache cleared, need to re-run

### Manual Cache Control

* **Refresh Now** - Force cache clear and re-run
* **Disable Caching** - Always run live query (admin only)

***

## Report Permissions

### Permission Levels

| Level      | Can View | Can Run | Can Edit | Can Delete | Can Share |
| ---------- | -------- | ------- | -------- | ---------- | --------- |
| **View**   | ✅        | ✅       | ❌        | ❌          | ❌         |
| **Run**    | ✅        | ✅       | ❌        | ❌          | ❌         |
| **Edit**   | ✅        | ✅       | ✅        | ❌          | ✅         |
| **Delete** | ✅        | ✅       | ✅        | ✅          | ✅         |
| **Owner**  | ✅        | ✅       | ✅        | ✅          | ✅         |

### Sharing Reports

1. Open report
2. Click **Share**
3. Add users/roles:
   * Enter email or role name
   * Choose permission level
4. **Send Invitation**

**Public Reports:**

* Check **Make Public** to share with all org members
* Public reports appear in "Organization Reports"

***

## Report Categories

Organize reports by category:

### Standard Categories

* **Financial** - Budget, transactions, accounts
* **HR** - Employees, payroll, attendance
* **Clinical** - Residents, assessments, outcomes
* **Operational** - Census, occupancy, incidents
* **Compliance** - Audits, certifications, grants

### Custom Categories

Admins can create custom categories:

1. Go to **Settings** → **Reports** → **Categories**
2. Click **New Category**
3. Enter name and description
4. **Save**

***

## Troubleshooting

### Report Timeout

**Cause:** Query takes too long (>30 seconds).

**Solutions:**

* Add filters to reduce data (e.g., date range)
* Contact admin to optimize query
* Run during off-peak hours

### No Data Returned

**Check:**

* Parameter values (date range too narrow?)
* Permissions (do you have access to this data?)
* Filters (too restrictive?)
* Data exists (maybe there's truly no data)

### Export Failed

**Causes:**

* Result set too large (>100K rows)
* Network issue
* Browser memory limit

**Solutions:**

* Add filters to reduce rows
* Export as CSV (smaller than Excel)
* Use scheduled export (email delivery)

### Permission Denied

**Cause:** You don't have access to underlying data tables.

**Solution:** Contact report owner or admin to grant access.

***

## Best Practices

1. **Use Parameters** - Make reports reusable with different inputs
2. **Name Clearly** - "Q1 2025 Revenue by Site" not "Report 1"
3. **Add Descriptions** - Explain what report shows and when to use
4. **Test Thoroughly** - Run with various parameters before sharing
5. **Set Reasonable Defaults** - Pre-fill common parameter values
6. **Document SQL** - Add comments to complex queries
7. **Review Regularly** - Archive unused reports

***

## Common Report Examples

### 1. Budget vs. Actual

**Purpose:** Compare budgeted to actual spend by account.

**Query:**

```sql theme={null}
SELECT 
  a.account_number,
  a.name AS account_name,
  b.budget_amount,
  SUM(t.amount) AS actual_amount,
  (SUM(t.amount) - b.budget_amount) AS variance
FROM fa_accounts a
JOIN fa_budget_lines b ON a.id = b.account_id
LEFT JOIN fa_transactions t ON a.id = t.account_id
WHERE t.transaction_date BETWEEN {{start_date}} AND {{end_date}}
GROUP BY a.account_number, a.name, b.budget_amount
ORDER BY ABS(variance) DESC;
```

### 2. Employee Roster

**Purpose:** List all employees with contact info.

**Query:**

```sql theme={null}
SELECT 
  full_name,
  email,
  phone_number,
  department,
  position,
  hire_date,
  status
FROM hr_employees
WHERE organization_id = auth.uid_org()
  AND status = {{status_filter}}
ORDER BY department, last_name;
```

### 3. Census Report

**Purpose:** Daily census by site and program.

**Query:**

```sql theme={null}
SELECT 
  s.name AS site,
  p.name AS program,
  COUNT(r.id) AS resident_count,
  b.total_beds,
  ROUND(COUNT(r.id)::float / b.total_beds * 100, 1) AS occupancy_rate
FROM rh_residents r
JOIN pf_sites s ON r.site_id = s.id
JOIN rh_programs p ON r.program_id = p.id
JOIN rh_bed_inventory b ON s.id = b.site_id
WHERE r.status = 'active'
  AND r.organization_id = auth.uid_org()
GROUP BY s.name, p.name, b.total_beds
ORDER BY occupancy_rate DESC;
```

***

## For Administrators

### Report Administration

Manage all reports:

1. Go to **Settings** → **Reports**
2. View:
   * All reports (across org)
   * Report usage (run count)
   * Slow queries (>5s)
   * Failed executions

### Performance Optimization

**Tips for Faster Reports:**

* Add indexes to frequently-queried columns
* Limit result set (pagination)
* Use materialized views for complex calculations
* Cache results for expensive queries
* Archive old data (move to history tables)

### SQL Validation

Reports undergo automatic SQL validation:

* ✅ SELECT only (no INSERT/UPDATE/DELETE)
* ✅ No destructive operations (DROP, TRUNCATE)
* ✅ Parameters properly typed
* ✅ RLS automatically enforced

**Blocked Keywords:**

* `DROP`, `TRUNCATE`, `DELETE`, `UPDATE`, `ALTER`
* `CREATE`, `GRANT`, `REVOKE`

### Report Templates

Create report templates for common use cases:

1. Create base report with parameters
2. Mark as **Template**
3. Users can clone and customize

***

## Support

**Need Help?**

* In-app: Click **Help** → **Reporting Support**
* Training: **Help** → **Report Training Videos**
* SQL Help: **Help** → **SQL Reference Guide**

***

**Version:** 3.0.0\
**Last Updated:** 2025-11-29