Use Cases Overview
Five practical scenarios that demonstrate where Multi-Query adds the most value. Each use case shows a different depth of hierarchy, output format, or integration pattern.
Payroll Reporting
Generate employee → payslip → deduction trees for payroll reports. A 3-level hierarchy returned as structured JSON, ready for PDF generation or spreadsheet export.
Read more →Org Chart
Render a department → team → employee hierarchy as an interactive HTML table. Uses HTML output mode with standalone formatting for direct iframe embedding.
Read more →Audit Trails
Fetch an entity record and all its change events in a single call. The root query returns the entity; child collections return each audit event with its field-level diff rows.
Read more →Config Snapshots
Snapshot tenant configuration with all child override rows included. Useful for debugging effective settings, exporting configuration for support, or comparing environments.
Read more →Expression Embedding
Embed live hierarchical data inside any BizFirst expression using the {{multi-query:sqlserver.CODE}} directive. No REST call required from the expression author's perspective.
When to Use Multi-Query
Multi-Query is the right tool when you need a complete hierarchical record tree for read purposes. The table below compares it against the two most common alternatives.
| Criterion | Multi-Query | Plain SQL (single query) | Multiple API Calls |
|---|---|---|---|
| Hierarchical output structure | Native — result is already nested JSON or HTML | Requires manual de-duplication of parent columns in application code | Requires client-side assembly of results from multiple responses |
| Database round-trips | One per level of hierarchy (controlled, predictable) | One, but JOIN complexity grows with depth | One per parent row per level (N+1) |
| Tenant isolation | Automatic — injected from JWT, no author action needed | Must be hand-coded in every query | Depends on each individual endpoint's implementation |
| Template reusability | Stored in dbo.Shared_Configurations; called by code, expressions, or REST |
Ad-hoc or embedded in application layer | Must build bespoke orchestration per use case |
| Output format flexibility | JSON or expandable HTML — switchable per-request | Raw tabular rows; consumer must format | Depends on each endpoint |
| Caching | Built-in via cacheSeconds option per template |
Must implement at application layer | Must implement at client or gateway layer |
| Best for | Reports, displays, exports, embedded widgets requiring nested data | Simple flat lookups, aggregations, complex business-logic reads | Cases where each level is independently paginated or lazily loaded |
Use Case Quick Reference
| Use Case | Hierarchy Depth | Output Format | Typical Caller |
|---|---|---|---|
| Payroll Reporting | 3 levels (Employee → Payslip → Deduction) | JSON | Reporting service, PDF generator |
| Org Chart | 3 levels (Department → Team → Employee) | HTML (standalone) | Atlas form Display control, iframe |
| Audit Trails | 2–3 levels (Entity → Event → FieldDiff) | JSON or HTML | Admin panel, compliance export |
| Config Snapshots | 2 levels (Config → Override) | JSON | Support tooling, environment comparison |
| Expression Embedding | Any (defined by referenced template) | HTML (fragment or standalone) | BizFirst expression engine, workflow nodes |