Custom Reports

The CEF Core report builder provides two paths to generating reports: pre-built templates for common use cases and a visual query builder for fully custom analysis. All reports support interactive Highcharts visualizations, tabular data views, and multi-format export.

Reports are served by the /api/v1/reports backend service. Every executed report returns a structured response containing the result rows, total row count, execution time in milliseconds, and a suggested chart type for visualization.

• ✓Pre-Built Templates: One-click reports for loan portfolios, notes summaries, financial statements, and analytics
• ✓Visual Query Builder: Select tables, pick fields, add filters, define joins, and preview the generated SQL before execution
• ✓Highcharts Visualization: Bar, line, pie, and area charts with time series support
• ✓Export: Download results as CSV, Excel (with formatted headers), PDF, or JSON
• ✓Save and Share: Save custom reports, organize with tags and favorites, and share with other users or roles

CEF Core ships with categorized report templates that cover the most common reporting needs. Navigate to Reports in the sidebar to see the full template library.

Running a Template

1. Browse templates: Open Reports in the sidebar. Templates are grouped by category. Use the search bar or category tabs to find the one you need.
2. Set parameters: Click a template to open it. Fill in the parameter form (date range, filters, grouping options). Required parameters are marked with an asterisk.
3. Execute: Click Run Report. The system calls POST /api/v1/reports/templates/:id/execute with your parameters. Results appear in a data table below the chart.
4. Review results: The response includes rowCount, executionTimeMs, and a chartType suggestion. The chart renders automatically above the table.
5. Export or save: Click the export button to download in your preferred format, or click Save as Custom Report to add it to your saved reports with your parameter selections preserved.

For reports that go beyond the pre-built templates, the visual query builder lets you construct custom queries without writing SQL. The builder walks you through table selection, field picking, filter configuration, and join definitions.

Step 1: Select Tables

The builder fetches available tables from GET /api/v1/reports/metadata/tables. Tables are organized by schema (loans, investor_notes, gl, cash, escrow, fees, crm). Select your primary table, then optionally add related tables to join.

Step 2: Choose Fields

After selecting a table, the builder loads its fields via GET /api/v1/reports/metadata/tables/:id/fields. Each field shows its name, data type, and whether it is nullable. Check the fields you want in your report output. You can reorder columns by dragging.

Step 3: Add Filters

Click Add Filter to constrain results. Each filter consists of a field, an operator (equals, not equals, greater than, less than, between, contains, is null, is not null), and a value. Multiple filters combine with AND logic. Date fields show a date picker, numeric fields show a number input, and text fields show a text input.

Step 4: Define Joins

If you selected multiple tables, the builder loads relationships from GET /api/v1/reports/metadata/tables/:id/relationships and suggests foreign key joins automatically. You can accept the suggested joins or configure them manually by selecting the join type (INNER, LEFT, RIGHT) and the matching columns.

Step 5: Validate and Execute

Before running, click Preview SQL to see the generated query. The builder calls POST /api/v1/reports/query/validate to check for syntax errors, missing joins, or unauthorized table access. Once validated, click Execute which calls POST /api/v1/reports/query/execute and renders the results.

CEF Core uses Highcharts for interactive report visualizations. Every executed report can be rendered as a chart. The backend suggests a chartType based on the data shape, but you can override it from the chart toolbar.

Time Series Configuration

When your report includes a date column, the chart automatically enables time series mode. You can configure the date column used for the x-axis, the aggregation interval (daily, weekly, monthly, quarterly, yearly), and whether to show cumulative or per-period values. Select the date column from the Time Axis dropdown in the chart toolbar.

Every report, whether from a template or the query builder, can be exported to four formats. Click the Export button in the report toolbar and select your format. The system calls POST /api/v1/reports/templates/:id/export (for templates) or POST /api/v1/reports/:id/export (for saved reports) with the requested format.

Save any report configuration for quick re-execution. Saved reports preserve your selected fields, filters, parameters, chart settings, and export preferences so you can re-run them with a single click.

1. Save a report: After executing a report, click Save Report. Provide a report_name (required) and the query_config is captured automatically. The system calls POST /api/v1/reports/reports.
2. Organize with tags: Add tags like "monthly", "board-meeting", or "audit" to group related reports. Tags are searchable from the saved reports list.
3. Set as favorite: Click the star icon on any saved report to add it to your favorites. Favorites appear at the top of the report list for quick access.
4. Browse saved reports: Open Reports → My Reports. Filter by category, tags, or search by name. The list calls GET /api/v1/reports/reports with optional query parameters for category, tags, search, and favorites.
5. Re-execute: Click any saved report, then click Run. The system calls POST /api/v1/reports/reports/:id/execute with the saved parameters. You can modify parameters before running without changing the saved version.
6. Update or delete: Click Edit to update the report name, tags, or query config (PUT /api/v1/reports/reports/:id). Click Delete to remove it permanently (DELETE /api/v1/reports/reports/:id). You can only delete reports you own.

Share saved reports with specific users or entire roles. Sharing lets team members run the same report without re-creating it, and gives administrators visibility into reports used across the organization.

1. Open sharing: From a saved report, click Share. This opens the sharing dialog.
2. Add recipients: Search for users by name or email, or select a role (ADMIN, TREASURY, STAFF). Each recipient gets an independent permission level.
3. Set permissions: Choose a permission level for each recipient:
   • View: The recipient can execute the report and export results but cannot modify the configuration.
   • Edit: The recipient can modify parameters, tags, and chart settings. Changes are saved to their own copy.
   • Delete: The recipient can remove their access to the shared report.
4. Confirm: Click Share to save. The system calls POST /api/v1/reports/reports/:id/share with the list of users, roles, and their permissions.

All report endpoints require JWT authentication. Use the authFetch helper from @/lib/auth-fetch for frontend calls.

Templates

Visual Query Builder

Saved Reports

• ✓Always Add Date Filters: Reports without date constraints scan the entire history. Add a date range filter to improve execution time and limit the result set to the period you need.
• ✓Favorite Frequent Reports: Star the reports you run regularly. Favorites appear at the top of the saved reports list and are accessible in one click from the Reports sidebar.
• ✓Use Tags for Organization: Tag reports consistently (e.g., "monthly-close", "board-packet", "audit-request") so your team can quickly find the right report using the tag filter.
• ✓Validate Before Executing: Use the Preview SQL button in the query builder to review the generated query. The validation endpoint catches join errors and unauthorized table access before you run the query.
• ✓Choose the Right Chart Type: Bar charts for comparisons, line charts for trends, pie charts for proportions, area charts for cumulative values. Switch chart types from the toolbar to find the best fit for your data.
• ✓Export Excel for Board Meetings: The Excel export includes formatted headers, auto-sized columns, and currency formatting. PDF exports include the chart image. Both formats are suitable for board presentations.
• ✓Share with Roles, Not Individuals: When sharing a report with a team, share with the role (e.g., TREASURY) rather than individual users. New team members automatically receive access.
• ✓Use Time Series for Trend Analysis: Select a date column as the time axis and set the aggregation interval to monthly or quarterly. This transforms any row-level data into a trend chart automatically.