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:**
```json
{
  "columns": ["id", "ticket_name", "status", "priority", "created_at"]
}
```

**Calculated Fields:**
```json
{
  "calculated_fields": ["sla_violated", "days_to_complete", "is_overdue"]
}
```

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

**Grouping:**
```json
{
  "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:
```json
{
  "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:
```json
{
  "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:
```json
{
  "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:
```json
{
  "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
```typescript
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
```typescript
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
```bash
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
```bash
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