Report Query Writing

Report queries are standard SQL Server SELECT statements stored in the QueryText column of Reports.Report. Before execution, they are processed by a template engine that injects runtime filter values using a {{ }} placeholder syntax.

Template Syntax

Required Scalar Parameter — `{{@ParamName}}`

A required single-value parameter. The placeholder is replaced with a named SQL parameter (@ParamName). The query will error if the value is not supplied.

SELECT *
FROM [Data].[SewingOutput]
WHERE
  LineID = {{@LineCode}}
  AND SewDate BETWEEN {{@StartDate}} AND {{@EndDate}};

At runtime this becomes:

SELECT *
FROM [Data].[SewingOutput]
WHERE
  LineID = @LineCode
  AND SewDate BETWEEN @StartDate AND @EndDate;

Required Array Parameter — `{{@ParamName[]}}`

A required multi-value parameter. The placeholder expands to a comma-separated list of numbered SQL parameters (@ParamName_1, @ParamName_2, ...). Always use inside an IN (...) clause. The query will error if the value is not supplied.

SELECT *
FROM [Data].[SewingOutput]
WHERE
  LineCode IN ({{@LineCode[]}})
  AND SewDate BETWEEN {{@StartDate}} AND {{@EndDate}};

At runtime with two lines this becomes:

SELECT *
FROM [Data].[SewingOutput]
WHERE
  LineCode IN (@LineCode_1, @LineCode_2)
  AND SewDate BETWEEN @StartDate AND @EndDate;

Optional Block — `{{?@ParamName, SQL fragment}}`

An optional clause. If the parameter is provided and non-empty, the SQL fragment is included with {0} replaced by the parameter reference. If the parameter is missing or an empty array, the entire block is removed from the query — no SQL fragment, no extra whitespace tokens left behind.

Use this to build dynamic WHERE clauses where some filters are truly optional.

Scalar example:

SELECT *
FROM [Data].[WorkerEfficiency]
WHERE
  1 = 1
  {{?@WorkOrderCode, AND WorkOrderCode = {0}}};

SELECT *
FROM [Data].[WorkerEfficiency]
WHERE
  1 = 1
  AND WorkOrderCode = @WorkOrderCode;

SELECT *
FROM [Data].[WorkerEfficiency]
WHERE
  1 = 1
  ;

Array example:

SELECT *
FROM [Data].[WorkerEfficiency]
WHERE
  SewDate BETWEEN {{@StartDate}} AND {{@EndDate}}
  {{?@LineCode[], AND LineCode IN ({0})}};

SELECT *
FROM [Data].[WorkerEfficiency]
WHERE
  SewDate BETWEEN @StartDate AND @EndDate
  AND LineCode IN (@LineCode_1, @LineCode_2);

SELECT *
FROM [Data].[WorkerEfficiency]
WHERE
  SewDate BETWEEN @StartDate AND @EndDate
  ;

Optional Multi-Parameter Block — `{{?@p1, @p2[], SQL fragment}}`

A single optional block that references multiple parameters. If any of the listed parameters is missing or an empty array, the entire block is removed. Use {0}, {1}, etc. to refer to each parameter in order.

SELECT *
FROM [Data].[WorkerEfficiency]
WHERE
  SewDate BETWEEN {{@StartDate}} AND {{@EndDate}}
  {{?@SectionCode[], @LineCode[], AND SectionCode IN ({0}) AND LineCode IN ({1})}};

SELECT *
FROM [Data].[WorkerEfficiency]
WHERE
  SewDate BETWEEN @StartDate AND @EndDate
  AND SectionCode IN (@SectionCode_1) AND LineCode IN (@LineCode_1, @LineCode_2);

SELECT *
FROM [Data].[WorkerEfficiency]
WHERE
  SewDate BETWEEN @StartDate AND @EndDate
  ;

Rules and Gotchas

Templates inside string literals are ignored

Any {{ }} content inside a SQL string literal (single quotes) is left as-is. This means you can safely use literal braces in LIKE patterns or string comparisons without escaping.

-- This is safe — {{@name}} inside quotes is NOT processed
SELECT * FROM t WHERE note LIKE '%{{special}}%';

Scalar params must not use `[]`; array params must use `[]`

Using {{@StartDate[]}} when StartDate is a scalar will cause an error at runtime. Similarly, using {{@LineCode}} instead of {{@LineCode[]}} for an array filter will also error.

Empty arrays count as missing for optional blocks

An empty LineCode[] makes {{?@LineCode[], AND LineCode IN ({0})}} disappear from the query. This is intentional — it allows the frontend to pass all filters unconditionally and have the query ignore the ones left blank.

Duplicate parameter names are flagged

Using the same parameter name twice (e.g., two {{@StartDate}} blocks) is flagged as a "Duplicate" error during validation.

Leading tabs are stripped automatically

The stored query text has a consistent tab prefix stripped before execution. Write queries indented normally — the engine normalizes whitespace.

Full Examples

Efficiency report with all optional filters

SELECT
  w.WorkerCode,
  w.WorkerName,
  l.LineCode,
  SUM(e.TargetQty) AS TargetQty,
  SUM(e.ActualQty) AS ActualQty,
  CAST(SUM(e.ActualQty) * 100.0 / NULLIF(SUM(e.TargetQty), 0) AS DECIMAL(5, 2)) AS EfficiencyPct
FROM [Data].[WorkerEfficiency] AS e
JOIN [Essentials].[Worker] AS w ON e.WorkerID = w.WorkerID
JOIN [Essentials].[Line] AS l ON e.LineID = l.LineID
WHERE
  e.SewDate BETWEEN {{@StartDate}} AND {{@EndDate}}
  {{?@LineCode[], AND l.LineCode IN ({0})}}
  {{?@WorkerCode[], AND w.WorkerCode IN ({0})}}
  {{?@WorkOrderCode[], AND e.WorkOrderCode IN ({0})}}
GROUP BY
  w.WorkerCode,
  w.WorkerName,
  l.LineCode
ORDER BY
  EfficiencyPct DESC;

Quality fault summary with paired optional filters

SELECT
  f.FaultCode,
  f.FaultName,
  COUNT(*) AS FaultCount,
  l.LineCode
FROM [Data].[QualityLog] AS q
JOIN [Essentials].[Fault] AS f ON q.FaultID = f.FaultID
JOIN [Essentials].[Line] AS l ON q.LineID = l.LineID
WHERE
  q.LogDate BETWEEN {{@StartDate}} AND {{@EndDate}}
  {{?@LineCode[], AND l.LineCode IN ({0})}}
  {{?@FaultCode[], AND f.FaultCode IN ({0})}}
  {{?@WorkOrderCode[], @PurchaseOrderCode[], AND q.WorkOrderCode IN ({0}) AND q.PurchaseOrderCode IN ({1})}}
GROUP BY
  f.FaultCode,
  f.FaultName,
  l.LineCode
ORDER BY
  FaultCount DESC;

Packing report with scalar and array filters

SELECT
  p.BoxID,
  p.PackStationName,
  p.PurchaseOrderCode,
  p.StyleName,
  p.ColorCode,
  p.SizeCode,
  p.Qty,
  p.PackedAt
FROM [dbo].[POPackingListDataSet] AS p
WHERE
  p.PackedAt BETWEEN {{@StartDate}} AND {{@EndDate}}
  {{?@PackStationName[], AND p.PackStationName IN ({0})}}
  {{?@PurchaseOrderCode[], AND p.PurchaseOrderCode IN ({0})}}
  {{?@ColorCode[], AND p.ColorCode IN ({0})}}
  {{?@SizeCode[], AND p.SizeCode IN ({0})}}
ORDER BY
  p.PackedAt DESC;