SQL Controls

In raw SQL cards, reference controls using the [[param.control_id]] placeholder syntax. When the query runs, Semaphor replaces each placeholder with the SQL-safe literal value selected by the viewer.

Enum Control (Single Select)

Create a dropdown that lets viewers pick one value.

Control definition:

• ID: region
• Type: enum
• Options: East, West, Central

SELECT product, SUM(revenue) AS total_revenue
FROM orders
WHERE region = [[param.region]]
GROUP BY product
ORDER BY total_revenue DESC

When the viewer selects "West", Semaphor resolves the query:

SELECT product, SUM(revenue) AS total_revenue
FROM orders
WHERE region = 'West'
GROUP BY product
ORDER BY total_revenue DESC

Multi-Enum Control (IN Clause)

Create a multi-select dropdown for filtering by multiple values.

Control definition:

• ID: categories
• Type: multi_enum
• Options: Electronics, Furniture, Office Supplies

SELECT category, COUNT(*) AS order_count
FROM orders
WHERE category IN ([[param.categories]])
GROUP BY category

When the viewer selects "Electronics" and "Furniture":

SELECT category, COUNT(*) AS order_count
FROM orders
WHERE category IN ('Electronics', 'Furniture')
GROUP BY category

Boolean Control (Toggle)

Create a toggle switch for conditional logic in SQL.

Control definition:

• ID: use_estimated
• Type: boolean

SELECT product_id,
  CASE
    WHEN [[param.use_estimated]] THEN estimated_cost
    ELSE actual_cost
  END AS cost
FROM inventory

When the viewer toggles the switch on:

SELECT product_id,
  CASE
    WHEN TRUE THEN estimated_cost
    ELSE actual_cost
  END AS cost
FROM inventory

Multiple Controls in One Query

You can use several controls in the same query. Each placeholder resolves independently.

Controls:

• region (enum): East, West, Central
• min_amount (number): minimum order amount
• start_date (date): beginning of date range

SELECT product, SUM(revenue) AS total_revenue
FROM orders
WHERE region = [[param.region]]
  AND amount >= [[param.min_amount]]
  AND order_date >= [[param.start_date]]
GROUP BY product
ORDER BY total_revenue DESC

Built-in Context Values

Semaphor provides built-in context references that do not require control definitions. Use these to access viewer or dashboard settings.

SELECT DATE_TRUNC('week', created_at AT TIME ZONE [[ctx.calendar.tz]]) AS week,
       COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1

Required vs Optional Controls

A control marked as required prevents the query from running until the viewer selects a value. The card displays an error state prompting the viewer to make a selection.

An optional control that has no value resolves to NULL. Write your SQL to handle this case:

SELECT product, SUM(revenue) AS total_revenue
FROM orders
WHERE ([[param.region]] IS NULL OR region = [[param.region]])
GROUP BY product

This pattern returns all regions when no selection is made, and filters to the selected region otherwise.

Placeholder Rules

• Placeholders inside SQL comments (-- or /* */) are ignored
• Placeholders inside dollar-quoted strings ($tag$...$tag$) are ignored
• Placeholder IDs are case-sensitive -- [[param.Region]] and [[param.region]] are different
• Each placeholder ID must map to a defined control on the card or dashboard
• Use [[param.x]] for controls (double brackets). Filters use {{ filters | where }} (curly braces) -- they are separate systems