Create and Run Reports

Embarc lets you define your own SQL-based or formatted report templates and execute them on demand. Use these APIs to register a report once, then call it from your portals, dashboards, or scheduled jobs.

1. Create or Update a Report Definition

POST /reports (or PUT /reports/{id} to edit) registers the report metadata, SQL, and parameters.

POST /reports
{
  "reportName": "Loans Due Next 30 Days",
  "reportType": "Table",
  "reportSubType": "Read",
  "reportCategory": "Portfolio",
  "description": "Upcoming instalments by customer and loan officer",
  "reportSql": "
      SELECT l.id AS loanId,
             c.display_name AS customer,
             l.loan_officer_id AS loanOfficerId,
             rs.duedate,
             rs.principal_due_derived AS principalDue,
             rs.interest_due_derived AS interestDue
      FROM m_loan l
      JOIN m_client c ON c.id = l.client_id
      JOIN m_loan_repayment_schedule rs ON rs.loan_id = l.id
      WHERE rs.completed_derived = 0
        AND rs.duedate BETWEEN ${fromDate} AND ${toDate}
        ${officeFilter}
  ",
  "coreReport": false,
  "useReport": true,
  "reportParameters": [
    { "parameterName": "fromDate", "parameterDisplayName": "Start Date",
"parameterType": "DATE" },
    { "parameterName": "toDate", "parameterDisplayName": "End Date",
"parameterType": "DATE" },
    { "parameterName": "officeFilter", "parameterDisplayName": "Office
Clause", "parameterType": "OFFICE" }
  ]
}

Key points:

reportType can be Table, Chart, or reportTemplates.
Parameters referenced as ${param} in SQL must appear in reportParameters. Embarc injects them safely.
Set coreReport=false for custom reports so you can edit/delete them later.

2. Run the Report

Use GET /runreports/{reportName} with query parameters prefixed by R_.

Example: JSON output

GET /runreports/Loans%20Due%20Next%2030%20Days
    ?R_fromDate=2025-06-01
    &R_toDate=2025-06-30
    &R_officeFilter=AND l.office_id = 3

Default response is JSON (generic result set with column metadata + rows).
Pagination/query filters are baked into your SQL; use LIMIT if necessary.

Export options

Add exportCSV=true for CSV, or set output-type=PDF|XLS|HTML for formatted report templates - backed reports.

GET /runreports/Loans%20Due%20Next%2030%20Days
    ?R_fromDate=2025-06-01
    &R_toDate=2025-06-30
    &R_officeFilter=
    &exportCSV=true

To see supported export targets for a specific report:

GET /runreports/availableExports/Loans%20Due%20Next%2030%20Days

3. Manage Reports

List all definitions: GET /reports
Fetch definition: GET /reports/{id}
Update description or SQL: PUT /reports/{id}
Delete (non-core only): DELETE /reports/{id}

All create/update/delete operations are logged in the audit trail and respect maker-checker if enabled.

Tips for Reliable Reporting

Parameterize everything: Use ${param} placeholders in SQL and define them in reportParameters. This prevents SQL injection and makes the UI friendly.
Respect permissions: Only users with report permissions (READ_REPORT, RUN_REPORT) can execute reports.
Optimise SQL: Join against indexed columns and limit result sets to keep response times low.
Reuse for dashboards: The JSON format is perfect for BI pipelines or embedded widgets; CSV/PDF exports suit end-user downloads.
Automate via mailing jobs: Combine custom reports with Report Mailing Jobs to schedule regular deliveries (daily collections, weekly risk summaries, etc.).

With these APIs you can stand up any custom dataset—from simple listings to complex aggregations—and serve it through Embarc’s existing authentication, authorisation, and export pipeline.