Portal Community
Use Case HTML Output

Use Case: Org Chart

Render a live organisational hierarchy — departments, teams, and individuals — as an expandable HTML table powered by a single Multi-Query template. No front-end assembly code required.

Scenario

An HR administrator needs a live, browseable organisational chart embedded inside an Atlas form dashboard. The chart must show every department, each team within a department, and each employee within a team — with the ability to collapse and expand sections interactively. The data must always reflect the current state of the database; it cannot be a stale snapshot.

Rather than building a bespoke front-end component with its own data-fetching logic, the team uses Multi-Query's HTML output mode. The template is stored once, the engine renders the expandable table, and the result is embedded in the Atlas Display control via an expression directive. When HR updates department or team membership in the underlying tables, the chart automatically reflects the change on the next page load — no code deployment required.

Template Design

The template uses a 3-level hierarchy: Departments (root) → Teams (child) → Employees (grandchild). The outputFormat is set to "html" and outputFormatTemplate to "standalone" so the engine wraps the table in a complete HTML document with inline CSS and expand/collapse JavaScript.

{
  "name": "Full Organisational Chart",
  "sql": "SELECT DepartmentID, DepartmentCode, DepartmentName,
                 CostCentre, HeadCount
          FROM Departments
          WHERE Deleted = 0
            AND TenantID = @TenantID
          ORDER BY DepartmentName",
  "recordType": "Department",
  "isTemplate": true,
  "parameters": [],
  "options": {
    "outputFormat": "html",
    "outputFormatTemplate": "standalone",
    "maxDepth": 5,
    "cacheSeconds": 120
  },
  "children": [
    {
      "collectionName": "teams",
      "sql": "SELECT TeamID, TeamName, TeamLead, HeadCount
              FROM Teams
              WHERE DepartmentID = @parent.DepartmentID
                AND Deleted = 0
                AND TenantID = @TenantID
              ORDER BY TeamName",
      "recordType": "Team",
      "isTemplate": false,
      "children": [
        {
          "collectionName": "employees",
          "sql": "SELECT EmployeeID, FirstName, LastName,
                         JobTitle, EmploymentType, StartDate
                  FROM Employees
                  WHERE TeamID = @parent.TeamID
                    AND Deleted = 0
                    AND TenantID = @TenantID
                  ORDER BY LastName, FirstName",
          "recordType": "Employee",
          "isTemplate": false,
          "children": []
        }
      ]
    }
  ]
}

HTML Output Mode

When outputFormat is set to "html", the engine renders an expandable cascaded table rather than a JSON array. Each level of the hierarchy appears as an indented nested table row group. Clicking a parent row expands or collapses its child rows.

Option Value Effect
outputFormat "html" Returns an HTML table instead of a JSON array.
outputFormatTemplate "standalone" Wraps the table in a complete <!DOCTYPE html> document with inline CSS and JavaScript. Suitable for <iframe> embedding or saving as a self-contained file.
outputFormatTemplate "fragment" Returns only the <table> element. Suitable for injection into an existing page that already provides the surrounding document structure and stylesheet.
HTML output includes inline expand/collapse JavaScript — no external dependencies required. The standalone document is fully self-contained. It does not load jQuery, React, or any external library. The expand/collapse behaviour is implemented in vanilla JavaScript inlined by the engine at render time.

Endpoints

There are two ways to request HTML output for this template.

Using the .html suffix endpoint

GET /api/v1/expressions/multiquery/ORG_CHART_FULL.html

Authorization: Bearer eyJhbGci...

This endpoint always returns HTML. The outputFormatTemplate (fragment vs standalone) is determined by the template's options setting.

Using query-string overrides on the base endpoint

GET /api/v1/expressions/multiquery/ORG_CHART_FULL
    ?outputFormat=html
    &outputFormatTemplate=standalone

Authorization: Bearer eyJhbGci...

Query-string values override the template's options for this specific request only. This allows a single template to serve both JSON consumers and HTML consumers without maintaining two separate template records.

Storing the Template

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

Embedding in an Atlas Form

Add a Display control to the Atlas form and set its expression to the following directive. The expression engine resolves the directive at render time, calls the Multi-Query endpoint internally, and injects the returned HTML into the control's output.

{{multi-query:sqlserver.ORG_CHART_FULL|outputFormat=html|outputFormatTemplate=standalone}}

The pipe-separated options after the template code are directive-level overrides and behave identically to the query-string overrides on the REST endpoint. They are applied only for this expression invocation and do not modify the stored template.

Caching in expression context: When the directive is evaluated inside an expression, the cacheSeconds value from the template options applies. Setting cacheSeconds: 120 means the org chart HTML is regenerated at most once every two minutes, even if many users open the same Atlas form simultaneously. Adjust this value based on how frequently your department and team data changes.

Viewing the Output

The standalone HTML document produced by the engine contains:

The table columns rendered per level are derived directly from the SQL SELECT list. To customise which columns appear, adjust the SELECT clause in the template's SQL — no engine configuration change is needed.

← Previous: Payroll Reporting Next: Audit Trails →