docs/api/reports/CUSTOM_REPORTS_FEATURE.md

Custom Reports Feature - Implementation Summary

Overview

Added fully configurable custom reports to the Reports API, allowing users to generate any report with flexible column selection, calculated fields, and aggregations.

Date: December 14, 2025
Status: ✅ Implemented

---

What Was Added

1. New Report Type: custom

Users can now create ad-hoc reports for any entity with complete control over:

• Which columns to include
• Calculated fields (SLA violations, days to complete, etc.)
• Aggregations (count, sum, avg, min, max)
• Grouping dimensions
• Entity-specific filters

2. Supported Entities

• tickets - Work orders/tickets
• timesheets - Daily attendance and work logs
• payroll - Worker compensation records
• expenses - Ticket expenses
• users - User records

3. Key Features

Flexible Column Selection:

{
  "columns": ["id", "ticket_name", "status", "priority", "created_at"]
}

Calculated Fields:

{
  "calculated_fields": ["sla_violated", "days_to_complete", "is_overdue"]
}

Aggregations:

{
  "aggregations": [
    { "field": "id", "type": "count", "alias": "ticket_count" },
    { "field": "total_amount", "type": "sum", "alias": "total_revenue" }
  ]
}

Grouping:

{
  "group_by": ["status", "priority"]
}

---

API Changes

Schema Updates (src/app/schemas/report.py)

New Enums:

• ReportType.CUSTOM - New report type
• AggregationType - count, sum, avg, min, max

New Models:

• AggregationConfig - Configuration for aggregations
• CustomReportConfig - Configuration for custom reports
• CustomReportRow - Flexible row structure

Updated Models:

• ReportFilter.custom_config - Optional custom report configuration
• ReportResponse.data - Now accepts Dict[str, Any] for custom reports

Service Updates (src/app/services/report_service.py)

New Methods:

• _generate_custom_report() - Main custom report generator
• _build_custom_query() - Build query without aggregations
• _build_aggregation_query() - Build aggregation query
• _format_custom_result() - Format regular results
• _format_aggregation_result() - Format aggregation results

Updated Methods:

• generate_report_data() - Routes to custom report generator
• generate_csv_export() - Now handles Dict results

---

Use Cases

1. Dashboard Widgets

Create custom widgets showing exactly the metrics you need:

{
  "report_type": "custom",
  "custom_config": {
    "entity": "tickets",
    "columns": ["status"],
    "aggregations": [{"field": "id", "type": "count"}],
    "group_by": ["status"]
  }
}

2. Performance Leaderboards

Rank workers by any metric:

{
  "report_type": "custom",
  "custom_config": {
    "entity": "timesheets",
    "columns": ["user_id"],
    "aggregations": [
      {"field": "tickets_completed", "type": "sum", "alias": "total_completed"}
    ],
    "group_by": ["user_id"]
  }
}

3. Budget Tracking

Analyze expenses by category:

{
  "report_type": "custom",
  "custom_config": {
    "entity": "expenses",
    "columns": ["expense_type"],
    "aggregations": [
      {"field": "total_cost", "type": "sum", "alias": "total_amount"}
    ],
    "group_by": ["expense_type"]
  }
}

4. Detailed Analysis

Get raw data with calculated fields:

{
  "report_type": "custom",
  "custom_config": {
    "entity": "tickets",
    "columns": ["id", "ticket_name", "status", "created_at", "completed_at"],
    "calculated_fields": ["sla_violated", "days_to_complete"]
  }
}

---

Comparison: Custom vs Pre-defined Reports

Feature	Pre-defined Reports	Custom Reports
Flexibility	Fixed columns	Choose any columns
Entities	Specific use cases	Any entity
Aggregations	Pre-calculated	User-defined
Calculated Fields	Always included	On-demand
Performance	Highly optimized	Good (indexed queries)
Use Case	Standard metrics	Ad-hoc analysis

When to Use Each:

Pre-defined Reports:

• Standard business metrics (SLA, performance)
• Consistent reporting across organization
• Complex multi-table joins
• Pre-calculated KPIs

Custom Reports:

• Ad-hoc analysis
• Custom dashboard widgets
• Flexible data exploration
• User-defined metrics

---

Frontend Implementation

Basic Custom Report

const generateCustomReport = async (config: CustomReportConfig) => {
  const response = await fetch('/api/v1/reports/generate', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${accessToken}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      report_type: 'custom',
      date_range: {
        start_date: startDate,
        end_date: endDate
      },
      custom_config: config,
      project_id: projectId
    })
  });

  return await response.json();
};

Report Builder UI

interface ReportBuilderState {
  entity: 'tickets' | 'timesheets' | 'payroll' | 'expenses' | 'users';
  selectedColumns: string[];
  calculatedFields: string[];
  aggregations: Array<{
    field: string;
    type: 'count' | 'sum' | 'avg' | 'min' | 'max';
    alias?: string;
  }>;
  groupBy: string[];
}

// Allow users to build reports visually
// Save report configurations for reuse
// Export to CSV or display in table/chart

---

Performance Considerations

Optimizations:

• Uses indexed fields for filtering (created_at, project_id, user_id)
• Pagination support via limit and skip
• Aggregations performed at database level (efficient)
• Date range filtering on appropriate fields per entity

Limitations:

• No cross-entity joins (use pre-defined reports for that)
• Calculated fields limited to simple computations
• Large result sets should use pagination

---

Authorization

Same as other reports:

• ✅ PLATFORM_ADMIN - Full access
• ✅ PROJECT_MANAGER - Project-scoped access
• ✅ DISPATCHER - Project-scoped access
• ✅ SALES_MANAGER - Project-scoped access
• ❌ Other roles - No access

---

Testing

Test Aggregation Report

curl -X POST "http://localhost:7860/api/v1/reports/generate" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "report_type": "custom",
    "date_range": {
      "start_date": "2025-12-01",
      "end_date": "2025-12-31"
    },
    "custom_config": {
      "entity": "tickets",
      "columns": ["status", "priority"],
      "aggregations": [
        {"field": "id", "type": "count", "alias": "count"}
      ],
      "group_by": ["status", "priority"]
    }
  }'

Test Detailed Report

curl -X POST "http://localhost:7860/api/v1/reports/generate" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "report_type": "custom",
    "date_range": {
      "start_date": "2025-12-01",
      "end_date": "2025-12-31"
    },
    "custom_config": {
      "entity": "tickets",
      "columns": ["id", "ticket_name", "status", "priority"],
      "calculated_fields": ["sla_violated", "days_to_complete"],
      "additional_filters": {
        "ticket_type": "installation"
      }
    },
    "limit": 50
  }'

---

Future Enhancements

• Cross-entity joins (tickets + timesheets)
• More calculated fields (cost per ticket, utilization rate)
• Saved report templates
• Scheduled report generation
• Email delivery of reports
• Advanced filtering (date ranges per field, OR conditions)
• Custom formulas (calculated columns with expressions)
• Report sharing and permissions

---

Documentation

• Main Docs: docs/api/reports/reports.md
• API Reference: /docs (Swagger)
• Source Code:
  • Schemas: src/app/schemas/report.py
  • Service: src/app/services/report_service.py
  • API: src/app/api/v1/reports.py

---

Migration Notes

No Breaking Changes:

• Existing pre-defined reports work exactly as before
• New custom report type is additive
• All existing API contracts maintained

Backward Compatible:

• Old report requests continue to work
• No database migrations required
• No changes to existing report types