Use Case: Payroll Reporting

Scenario

A payroll administrator needs to generate a departmental pay run summary. The report must show every active employee in a given department, and for each employee it must list every payslip issued in the current pay period, and for each payslip it must list every deduction line — tax, pension, healthcare, and other withholdings. The final document is passed to a PDF generation service and also stored as a structured JSON archive.

Without Multi-Query this requires either a complex SQL JOIN that produces a flat result set needing reassembly, or a cascade of N+1 queries — one employee fetch followed by one payslip fetch per employee followed by one deductions fetch per payslip. For a department of 80 employees with an average of 2 payslips and 6 deductions each, that naive approach produces 80 × 2 × 6 = 960 separate queries. Multi-Query reduces this to 3 database round-trips regardless of record volume.

Template Design

The template is a 3-level JSON document. The root level selects employees; the first child level selects payslips per employee; the second child (grandchild) level selects deductions per payslip.

{
  "name": "Departmental Payroll Report",
  "sql": "SELECT EmployeeID, FirstName, LastName, JobTitle, BaseSalary
          FROM Employees
          WHERE DepartmentID = @DepartmentID
            AND Deleted = 0
            AND TenantID = @TenantID
          ORDER BY LastName, FirstName",
  "recordType": "Employee",
  "isTemplate": true,
  "parameters": [
    { "name": "DepartmentID", "type": "int", "defaultValue": null }
  ],
  "options": {
    "outputFormat": "json",
    "maxDepth": 5,
    "cacheSeconds": 60
  },
  "children": [
    {
      "collectionName": "payslips",
      "sql": "SELECT PayslipID, PayPeriodStart, PayPeriodEnd,
                     GrossPay, NetPay, Status
              FROM Payslips
              WHERE EmployeeID = @parent.EmployeeID
                AND TenantID = @TenantID
              ORDER BY PayPeriodEnd DESC",
      "recordType": "Payslip",
      "isTemplate": false,
      "children": [
        {
          "collectionName": "deductions",
          "sql": "SELECT DeductionID, DeductionType, Description,
                         Amount, IsPreTax
                  FROM PayslipDeductions
                  WHERE PayslipID = @parent.PayslipID
                    AND TenantID = @TenantID
                  ORDER BY DeductionType",
          "recordType": "PayslipDeduction",
          "isTemplate": false,
          "children": []
        }
      ]
    }
  ]
}

Storing the Template

Insert the template into dbo.Shared_Configurations with a unique catalogue code. The engine resolves templates by this code at execution time.

INSERT INTO dbo.Shared_Configurations
    (Code, [Value], CreatedOn, CreatedBy, TenantID, AppDomainID)
VALUES
    (
        'PAYROLL_REPORT_DEPT',
        '{ ... JSON template from above ... }',
        GETUTCDATE(),
        1,          -- system user ID
        @TenantID,
        @AppDomainID
    );

To update an existing template, use an UPDATE on Code = 'PAYROLL_REPORT_DEPT'. No application restart or deployment is required — the engine reads the current value from the database at each execution.

Executing the Template

Call the JSON endpoint with the DepartmentID parameter as a query-string value. Include a valid Bearer JWT carrying the TenantAdmin role.

GET /api/v1/expressions/multiquery/PAYROLL_REPORT_DEPT.json?DepartmentID=42

Authorization: Bearer eyJhbGci...

Alternatively, use the base endpoint and specify the format explicitly:

GET /api/v1/expressions/multiquery/PAYROLL_REPORT_DEPT?DepartmentID=42&outputFormat=json

Authorization: Bearer eyJhbGci...

Sample JSON Output

The response is a compact nested JsonArray. Each employee record contains a payslips array; each payslip record contains a deductions array.

[
  {
    "EmployeeID": 1001,
    "FirstName": "Sarah",
    "LastName": "Chen",
    "JobTitle": "Senior Accountant",
    "BaseSalary": 72000.00,
    "payslips": [
      {
        "PayslipID": 5510,
        "PayPeriodStart": "2026-05-01",
        "PayPeriodEnd": "2026-05-31",
        "GrossPay": 6000.00,
        "NetPay": 4312.50,
        "Status": "Finalised",
        "deductions": [
          {
            "DeductionID": 90021,
            "DeductionType": "IncomeTax",
            "Description": "PAYE Income Tax",
            "Amount": 1200.00,
            "IsPreTax": false
          },
          {
            "DeductionID": 90022,
            "DeductionType": "Pension",
            "Description": "Employee Pension Contribution",
            "Amount": 360.00,
            "IsPreTax": true
          },
          {
            "DeductionID": 90023,
            "DeductionType": "NationalInsurance",
            "Description": "Employee NI Contribution",
            "Amount": 127.50,
            "IsPreTax": false
          }
        ]
      }
    ]
  },
  {
    "EmployeeID": 1002,
    "FirstName": "Marcus",
    "LastName": "Okafor",
    "JobTitle": "Payroll Analyst",
    "BaseSalary": 58000.00,
    "payslips": [ "..." ]
  }
]

Using the Result in a PDF Generator

Because the output is already a fully nested structure, the PDF generation service can traverse it directly without any client-side reassembly. Bind the outer array to employee rows, nest payslip rows inside each employee section, and nest deduction rows inside each payslip section — using the same JSON path in each template binding expression.