Report Run

Endpoint

GET/report/run

Runs a report identified by its slug and returns the rows produced by its SQL template. Filtering and date placeholders are applied automatically when present in the SQL.

Authentication

Provide your API token in the request headers:

X-Api-Token: your_token_prefix.your_token_secret

Parameters

Identification

Parameter	Type	Required	Description
report	string	Required	Report slug (filename without `.sql`), e.g. `flight_bookings_summary`

Common Filters

Filters are applied only if the SQL contains the relevant placeholders (e.g. {{{FILTER}}}). Unsupported filters are safely ignored.

Parameter	Type	Applied Condition	Description
branchID	integer	AND b.ID = :branchID	Branch filter
consultantID	integer	AND co.ID = :consultantID	Consultant filter
clientID	integer	AND cli.ID = :clientID	Client filter
tripID	integer	AND t.ID = :tripID	Trip filter
supplierID	integer	AND s.ID = :supplierID	Supplier filter
productID	integer	AND p.ID = :productID	Product filter
tripStatus	string	AND t.tripStatus = :tripStatus	Trip status
source	string	AND t.source = :source	Booking source
isActive	boolean/int	AND ts.isActive = :isActive	Active sectors only
minTotal	integer	AND ts.total >= :minTotal	Minimum sector total (in cents)
maxTotal	integer	AND ts.total <= :maxTotal	Maximum sector total (in cents)

Date Filters

When present in the SQL, date placeholders are replaced automatically using the following query parameters.

Parameter	Type	Applies To	Placeholder
startDate	date	General date range	Multiple (e.g. `{{{DATE_SECTOR_TRAVEL_DATE}}}`, `{{{DATE_TRIP_OPENED}}}`)
endDate	date	General date range	Multiple
travelStartDate	date	Travel date range	`{{{TRAVEL_DATE}}}`
travelEndDate	date	Travel date range	`{{{TRAVEL_DATE}}}`

Tip: Use ISO dates (YYYY-MM-DD). If only one bound is provided, the other defaults to a very early/late sentinel.

Usage Examples

Run by slug with date range and branch

GET /report/run?report=flight_bookings_summary&startDate=2024-01-01&endDate=2024-12-31&branchID=3

Run with consultant filter

GET /report/run?report=flight_bookings_summary&consultantID=27

Run with travel-date window

GET /report/run?report=flight_bookings_summary&travelStartDate=2024-02-01&travelEndDate=2024-02-28

Response Format

{
  "success": true,
  "data": {
    "report_name": "flight_bookings_summary",
    "display_name": "Flight Bookings Summary",
    "filters_applied": {
      "startDate": "2024-01-01",
      "endDate": "2024-12-31",
      "branchID": 3
    },
    "row_count": 150,
    "execution_time": 245.67,
    "headers": ["Trip","Sector_ID","Branch","Depart","Carrier","Reloc","Travel","Return","Dest","Gross","Net","Markup","Discount","Comm","Client","Phone","Email","DoB"],
    "rows": [
      { "Trip": 12345, "Sector_ID": 67890, "Branch": "MEL", "Depart": "MEL", "Carrier": "QF", "Reloc": "MEL", "Travel": "2024-02-01", "Return": "2024-02-05", "Dest": "SYD", "Gross": "450.00", "Net": "400.00", "Markup": "25.00", "Discount": "0.00", "Comm": "35.00", "Client": "John Smith", "Phone": "+61400123456", "Email": "john@example.com", "DoB": "1985-06-15" }
    ]
  },
  "timestamp": "2025-08-22T00:00:00+00:00"
}

Error Responses

Missing report slug

{
  "success": false,
  "error": "Missing required parameter: report",
  "code": 400
}

Report not found on server

{
  "success": false,
  "error": "Report execution failed",
  "code": 500,
  "details": {
    "report": "unknown_slug",
    "error": "Report SQL file not found: unknown_slug"
  }
}

Code Examples

JavaScript (Fetch)

const response = await fetch('/report/run?report=flight_bookings_summary&perPage=10', {
  headers: { 'X-Api-Token': 'your_token_prefix.your_token_secret' }
});
const data = await response.json();
if (data.success) {
  console.log(data.data.headers);
  data.data.rows.forEach(r => console.log(r));
}

PHP (cURL)

$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, '/report/run?report=flight_bookings_summary&startDate=2024-01-01&endDate=2024-12-31');
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_HTTPHEADER, ['X-Api-Token: your_token_prefix.your_token_secret']);
$response = curl_exec($ch);
$data = json_decode($response, true);
curl_close($ch);
if ($data['success']) {
    foreach ($data['data']['rows'] as $row) {
        print_r($row);
    }
}

Python (requests)

import requests
headers = {'X-Api-Token': 'your_token_prefix.your_token_secret'}
resp = requests.get('/report/run', params={
    'report': 'flight_bookings_summary',
    'startDate': '2024-01-01',
    'endDate': '2024-12-31'
}, headers=headers)
data = resp.json()
if data['success']:
    for row in data['data']['rows']:
        print(row)

Notes

Use the /report/list endpoint to discover available report slugs.
Amounts returned by SQL may already be formatted (e.g., FORMAT(x/100, 2)) depending on your SQL template.
Unknown filters or missing placeholders are ignored without error.
Date parameters must use the exact names shown (e.g., startDate, travelStartDate).

Report Run API