Reporting Guide

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.

​

Overview

• 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

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

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

• 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

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

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

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

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

​

Report Parameters

​

What Are Parameters?

• 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

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

• {{start_date}} replaced with user’s input
• Type-safe (dates remain dates, no SQL injection)

​

Parameter Types

​

Exporting Reports

​

Export Formats

​

Export Options

• 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)

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

• 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

​

Sharing Reports

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

• Check Make Public to share with all org members
• Public reports appear in “Organization Reports”

​

Report Categories

​

Standard Categories

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

​

Custom Categories

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

​

Troubleshooting

​

Report Timeout

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

​

No Data Returned

• 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

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

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

​

Permission Denied

​

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

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

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

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

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

​

Performance Optimization

• 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

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

• DROP, TRUNCATE, DELETE, UPDATE, ALTER
• CREATE, GRANT, REVOKE

​

Report Templates

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

​

Support

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