Custom Reports - MLM (Manteniendo la Misión)

Overview

While the interactive dashboards provide comprehensive pre-built reports, the MLM CMMS architecture supports custom report generation through direct service access and database views. This guide covers advanced reporting patterns for developers and power users.

Report Service API

The reportService.ts module exposes five primary report generators that can be called programmatically:

Available Report Functions

import { getExecutiveSummaryReport } from '../services/reportService';

const report = await getExecutiveSummaryReport({
  locationId: 5,
  from: '2024-01-01T00:00:00',
  to: '2024-03-31T23:59:59'
});

// Returns:
// {
//   meta: { generatedAt: string, rowCount: number },
//   kpis: { openWorkOrders, overdueWorkOrders, urgentOpen, ... },
//   byLocation: ReportBucket[],
//   bySpecialIncident: ReportBucket[],
//   topConsumedParts: ReportBucket[]
// }

Filter Parameters

All report functions accept DashboardReportFilters:

src/types/Report.ts

export type DashboardReportFilters = {
  locationId?: number;        // Filter by specific facility
  from?: string;              // ISO timestamp (inclusive start)
  to?: string;                // ISO timestamp (inclusive end)
};

Omit locationId to aggregate across all locations. Date filters apply to different fields per report (e.g., created_at for tickets, performed_at for maintenance logs).

Data Structures

Common Types

ReportBucket
WorkTechnicianRow
AssetTopRow
PartRankingRow

type ReportBucket = {
  label: string;    // Category name (e.g., "Pendiente", "Alta")
  value: number;    // Aggregated count or metric
};

Used for horizontal bar charts and distribution breakdowns.

type WorkTechnicianRow = {
  technician: string;           // Technician name
  openCount: number;            // Active work orders
  closedCount: number;          // Completed work orders
  avgResolutionHours: number;   // Mean time to resolution
};

Sorted by open count descending, limited to top 10.

type AssetTopRow = {
  assetId: number;      // Asset ID
  code: string;         // Asset code (e.g., "EQ-2024-001")
  name: string;         // Asset name
  value: number;        // Metric (tickets, cost, or downtime)
};

Used for asset ranking tables.

type PartRankingRow = {
  code: string;          // Part code
  name: string;          // Part name
  value: number;         // Quantity or suggested amount
  warehouse?: string;    // Warehouse code + name
  ticketId?: string;     // Associated work order
};

Multi-purpose for consumption and reorder reports.

Legacy Report Functions

The service maintains backward-compatible functions for older components:

These functions are deprecated and will be removed in a future release. Migrate to the new dashboard report APIs.

getCountByStatus

src/services/reportService.ts:584-611

export async function getCountByStatus(
  filters?: ReportFilters
): Promise<CountByStatusDTO[]> {
  // Returns ticket counts for Pendiente, En Ejecución, Finalizadas
  // Only includes accepted (is_accepted=true) tickets
}

// Usage:
const statusData = await getCountByStatus({
  location_id: '5',
  from: '2024-01-01',
  to: '2024-01-31'
});

getCountByField

src/services/reportService.ts:613-634

export async function getCountByField(
  field: 'location_id' | 'assignee' | 'requester',
  filters?: ReportFilters
): Promise<CountByFieldDTO[]> {
  // Aggregates ticket counts by specified field
}

// Usage:
const byLocation = await getCountByField('location_id', { from: '2024-01-01' });
const byAssignee = await getCountByField('assignee');

Chart Data Converters

import { toBarChartFromStatus, toBarChartFromField } from '../services/reportService';

const statusCounts = await getCountByStatus();
const chartData = toBarChartFromStatus(statusCounts, 'My Dataset Label');

// Returns Chart.js compatible BarChartData structure

Database Views

Custom reports can query optimized database views directly:

v_tickets_compat

Comprehensive ticket view with joined data:

CREATE VIEW v_tickets_compat AS
SELECT 
  t.id,
  t.status,
  t.is_accepted,
  t.is_urgent,
  t.priority,
  t.location_id,
  t.assignee,
  t.requester,
  t.created_at,
  t.finalized_at,
  t.deadline_date,
  t.is_archived,
  t.special_incident_id,
  si.name AS special_incident_name,
  u.name AS created_by_name,
  -- Additional computed fields
FROM tickets t
LEFT JOIN special_incidents si ON t.special_incident_id = si.id
LEFT JOIN users u ON t.created_by = u.id;

v_assets

Asset details with location names:

CREATE VIEW v_assets AS
SELECT 
  a.id,
  a.code,
  a.name,
  a.status,
  a.criticality,
  a.is_active,
  a.warranty_end_date,
  a.location_id,
  l.name AS location_name
FROM assets a
LEFT JOIN locations l ON a.location_id = l.id;

v_inventory_kardex

Stock movement ledger:

CREATE VIEW v_inventory_kardex AS
SELECT 
  k.id,
  k.part_id,
  p.code AS part_code,
  p.name AS part_name,
  k.qty_delta,
  k.unit_cost,
  k.movement_side,  -- 'IN' or 'OUT'
  k.doc_type,
  k.occurred_at,
  k.ticket_id
FROM kardex k
JOIN parts p ON k.part_id = p.id;

v_available_stock

Current inventory availability:

CREATE VIEW v_available_stock AS
SELECT 
  s.part_id,
  p.code AS part_code,
  p.name AS part_name,
  s.warehouse_id,
  w.code AS warehouse_code,
  w.name AS warehouse_name,
  s.on_hand_qty,
  s.reserved_qty,
  (s.on_hand_qty - s.reserved_qty) AS available_qty
FROM stock_on_hand s
JOIN parts p ON s.part_id = p.id
JOIN warehouses w ON s.warehouse_id = w.id;

Custom Report Examples

Quarterly Maintenance Cost Analysis

const q1Report = await getAssetsReport({
  from: '2024-01-01T00:00:00',
  to: '2024-03-31T23:59:59'
});

const q1Cost = q1Report.kpis.maintenanceCostTotal;
const topAssets = q1Report.topByCost.slice(0, 5);

console.log(`Q1 Total: $${q1Cost}`);
topAssets.forEach(asset => {
  console.log(`${asset.code}: $${asset.value}`);
});

Technician Performance Benchmarking

const workReport = await getWorkManagementReport({
  from: '2024-01-01T00:00:00',
  to: '2024-01-31T23:59:59'
});

const technicians = workReport.byTechnician;
const avgResolution = workReport.kpis.avgResolutionHours;

const aboveAverage = technicians.filter(
  tech => tech.avgResolutionHours < avgResolution
);

const belowAverage = technicians.filter(
  tech => tech.avgResolutionHours >= avgResolution
);

console.log(`Top Performers (< ${avgResolution}h avg):`);
aboveAverage.forEach(tech => {
  console.log(`  ${tech.technician}: ${tech.avgResolutionHours}h (${tech.closedCount} closed)`);
});

Inventory Reorder Automation

const partsReport = await getInventoryPartsReport();

const criticalReorders = partsReport.topReorderParts.filter(
  part => part.value > 10  // Suggested quantity > 10
);

if (criticalReorders.length > 0) {
  console.log('⚠️ CRITICAL REORDERS NEEDED:');
  criticalReorders.forEach(part => {
    console.log(`  ${part.code} - ${part.name}`);
    console.log(`    Warehouse: ${part.warehouse}`);
    console.log(`    Suggested Qty: ${part.value}`);
  });
  
  // TODO: Generate purchase orders automatically
  // await createPurchaseOrders(criticalReorders);
}

Location Demand Heatmap

const executiveReport = await getExecutiveSummaryReport({
  from: '2024-01-01T00:00:00',
  to: '2024-12-31T23:59:59'
});

const locationDemand = executiveReport.byLocation;
const maxDemand = Math.max(...locationDemand.map(loc => loc.value));

const heatmap = locationDemand.map(location => ({
  location: location.label,
  demand: location.value,
  intensity: (location.value / maxDemand) * 100,
  priority: location.value > maxDemand * 0.7 ? 'HIGH' : 
            location.value > maxDemand * 0.4 ? 'MEDIUM' : 'LOW'
}));

console.table(heatmap);

Data Export Strategies

While the UI doesn’t provide direct export buttons, you can extract report data programmatically:

JSON Export

import { saveAs } from 'file-saver';

const report = await getWorkManagementReport({
  from: '2024-01-01T00:00:00',
  to: '2024-03-31T23:59:59'
});

const blob = new Blob(
  [JSON.stringify(report, null, 2)],
  { type: 'application/json' }
);

saveAs(blob, 'work-management-q1-2024.json');

CSV Conversion

function convertToCSV(data: Record<string, any>[]): string {
  if (data.length === 0) return '';
  
  const headers = Object.keys(data[0]).join(',');
  const rows = data.map(row => 
    Object.values(row).map(val => 
      typeof val === 'string' ? `"${val}"` : val
    ).join(',')
  );
  
  return [headers, ...rows].join('\n');
}

const assetsReport = await getAssetsReport();
const csvData = convertToCSV(assetsReport.topByCost);
const blob = new Blob([csvData], { type: 'text/csv' });
saveAs(blob, 'top-assets-by-cost.csv');

Excel Export (xlsx)

import * as XLSX from 'xlsx';

const report = await getInventoryPartsReport();

const workbook = XLSX.utils.book_new();

// KPIs sheet
const kpisData = Object.entries(report.kpis).map(([key, value]) => ({
  Metric: key,
  Value: value
}));
const kpisSheet = XLSX.utils.json_to_sheet(kpisData);
XLSX.utils.book_append_sheet(workbook, kpisSheet, 'KPIs');

// Top consumed parts sheet
const partsSheet = XLSX.utils.json_to_sheet(report.topConsumedParts);
XLSX.utils.book_append_sheet(workbook, partsSheet, 'Top Consumed');

// Reorder suggestions sheet
const reorderSheet = XLSX.utils.json_to_sheet(report.topReorderParts);
XLSX.utils.book_append_sheet(workbook, reorderSheet, 'Reorder');

XLSX.writeFile(workbook, 'inventory-report.xlsx');

Advanced Filtering

Multi-Location Aggregation

const locationIds = [1, 3, 5, 7];  // Multiple facilities

const reports = await Promise.all(
  locationIds.map(id => getExecutiveSummaryReport({ locationId: id }))
);

const aggregated = {
  totalOpenWO: reports.reduce((sum, r) => sum + r.kpis.openWorkOrders, 0),
  totalOverdue: reports.reduce((sum, r) => sum + r.kpis.overdueWorkOrders, 0),
  totalUrgent: reports.reduce((sum, r) => sum + r.kpis.urgentOpen, 0)
};

console.log('Multi-Location Summary:', aggregated);

Rolling Time Windows

function getLastNDays(days: number): DashboardReportFilters {
  const to = new Date();
  const from = new Date();
  from.setDate(from.getDate() - days);
  
  return {
    from: from.toISOString(),
    to: to.toISOString()
  };
}

const last7Days = await getWorkManagementReport(getLastNDays(7));
const last30Days = await getWorkManagementReport(getLastNDays(30));
const last90Days = await getWorkManagementReport(getLastNDays(90));

console.log('SLA Trends:');
console.log(`  7d: ${last7Days.kpis.slaOnTimeRate}%`);
console.log(` 30d: ${last30Days.kpis.slaOnTimeRate}%`);
console.log(` 90d: ${last90Days.kpis.slaOnTimeRate}%`);

Performance Considerations

Report queries can be resource-intensive. Follow these guidelines for optimal performance:

Limit Date Ranges

Avoid querying more than 12 months of data in a single report. Use pagination for historical analysis.

Use Location Filters

Filter by specific locations when possible to reduce dataset size.

Cache Results

Store report results in component state or global cache for repeated access.

Parallel Fetching

Use Promise.all() for independent reports (e.g., multiple locations) to reduce total wait time.

Monitor Row Counts

Check meta.rowCount in responses; counts > 5,000 may indicate need for additional filtering.

Query Limits

Database views are limited to prevent timeouts:

src/services/reportService.ts

const { data } = await supabase
  .from('v_tickets_compat')
  .select('...')
  .limit(10000);  // Hard limit on all report queries

If your organization exceeds 10,000 tickets/assets/parts in a reporting period, consider implementing server-side pagination or pre-aggregated summary tables.

Extending the Report Service

Adding Custom Metrics

Define Type

Add your metric to the appropriate report type in src/types/Report.ts:

export type WorkManagementReport = {
  // ... existing fields
  kpis: {
    // ... existing KPIs
    customMetric: number;  // Your new metric
  };
};

Calculate Value

Add calculation logic in the report service function:

export async function getWorkManagementReport(filters) {
  // ... existing logic
  
  let customMetric = 0;
  for (const row of tickets) {
    if (/* your condition */) {
      customMetric += 1;
    }
  }
  
  return {
    // ... existing return
    kpis: {
      // ... existing KPIs
      customMetric
    }
  };
}

Display in Dashboard

Add KPI tile in ReportsDashboard.tsx:

const workKpis = useMemo<KpiConfig[]>(() => {
  if (!workState.data) return [];
  
  return [
    // ... existing KPIs
    { 
      id: 'customMetric', 
      label: 'Custom Metric', 
      value: workState.data.kpis.customMetric,
      tone: 'default'
    }
  ];
}, [workState.data]);

Add to Default Order

Include in default KPI arrangement:

const DEFAULT_KPI_ORDER: Record<TabId, string[]> = {
  work: [
    // ... existing IDs
    'customMetric'
  ],
  // ...
};

Troubleshooting

Empty Results

No Data in Date Range

Verify tickets/assets/parts exist in the specified time window. Try expanding the date range or removing the location filter.

Permission Issues

Ensure the service role key (backend) or user session (frontend) has SELECT permissions on required tables and views.

Archived Data Excluded

Most reports filter out archived tickets (is_archived = false). Check archive status if expecting higher counts.

Performance Issues

Slow Query Execution

Database views may need indexing. Add indexes on frequently filtered columns:

CREATE INDEX idx_tickets_created_at ON tickets(created_at);
CREATE INDEX idx_tickets_location ON tickets(location_id);
CREATE INDEX idx_tickets_status ON tickets(status);

High Memory Usage

Reduce dataset size by filtering to recent dates or specific locations. Consider server-side aggregation for very large installations.

Best Practices

Version Control

Store custom report scripts in version control with clear documentation and date ranges for reproducibility.

Automated Scheduling

Use cron jobs or scheduled functions to generate reports at regular intervals (daily, weekly, monthly).

Data Validation

Always validate report output against known values; spot-check KPIs with manual queries to ensure accuracy.

Audit Logging

Log report generation events (timestamp, user, filters) for compliance and troubleshooting.

Return to Overview

Back to Reporting & Analytics overview and feature summary

​Overview

​Report Service API

​Available Report Functions

​Filter Parameters

​Data Structures

​Common Types

​Legacy Report Functions

​getCountByStatus

​getCountByField

​Chart Data Converters

​Database Views

​v_tickets_compat

​v_assets

​v_inventory_kardex

​v_available_stock

​Custom Report Examples

​Quarterly Maintenance Cost Analysis

​Technician Performance Benchmarking

​Inventory Reorder Automation

​Location Demand Heatmap

​Data Export Strategies

​JSON Export

​CSV Conversion

​Excel Export (xlsx)

​Advanced Filtering

​Multi-Location Aggregation

​Rolling Time Windows

​Performance Considerations

​Query Limits

​Extending the Report Service

​Adding Custom Metrics

​Troubleshooting

​Empty Results

​Performance Issues

​Best Practices

Version Control

Automated Scheduling

Data Validation

Audit Logging

Return to Overview

Overview

Report Service API

Available Report Functions

Filter Parameters

Data Structures

Common Types

Legacy Report Functions

getCountByStatus

getCountByField

Chart Data Converters

Database Views

v_tickets_compat

v_assets

v_inventory_kardex

v_available_stock

Custom Report Examples

Quarterly Maintenance Cost Analysis

Technician Performance Benchmarking

Inventory Reorder Automation

Location Demand Heatmap

Data Export Strategies

JSON Export

CSV Conversion

Excel Export (xlsx)

Advanced Filtering

Multi-Location Aggregation

Rolling Time Windows

Performance Considerations

Query Limits

Extending the Report Service

Adding Custom Metrics

Troubleshooting

Empty Results

Performance Issues

Best Practices