Report-SQL extends ETL-SQL with dedicated statement types for building interactive dashboards: SET REPORT TITLE, CREATE DATASET, CREATE VISUAL, CREATE PAGE, CREATE CONTAINER, CREATE NAVIGATION, CREATE BUTTON, and CREATE STYLE — plus a CLI build tool and live browser hosts for serving reports.

┌─────────────────────┐    build / serve      ┌─────────────────────┐
│  (report script)    │                       │  (etl-sql-report)   │
│ your_report.rptsql  │ ──────────────────▶   etl-sql-report CLI   │
└─────────────────────┘                       └──────────┬──────────┘
                                                         │ evaluates script
                                                         ▼
                                              ┌─────────────────────┐
                                              │  ETL-SQL Engine     │
                                              │  (Evaluator)        │
                                              └──────────┬──────────┘
                                                         │ builds ReportManifest
                                                         ▼
                                      ┌──────────────────────────────┐
                                      │       ManifestBuilder        │
                                      │  ┌──────────────────────── ┐ │
                                      │  │ VisualManifest (x N)    │ │
                                      │  │ PageManifest   (x N)    │ │
                                      │  │ DatasetManifest (x N)   │ │
                                      │  └─────────────────────────┘ │
                                      └──────────┬───────────────────┘
                                                 │
                              ┌──────────────────┴──────────────────┐
                              │                                     │
                              ▼                                     ▼
                  ┌───────────────────────┐           ┌──────────────────────┐
                  │  MarkdownRenderer     │           │  ReportPlayer        │
                  │  → .report.md         │           │  (ASP.NET Kestrel)   │
                  │  → .snapshot.json     │           │  http://localhost:5200│
                  └───────────────────────┘           └──────────────────────┘

A .rptsql file is a normal ETL-SQL script that may also contain Report-SQL statements. The engine evaluates it exactly like any .etlsql file; the new statements register definitions in the execution context. After evaluation the ManifestBuilder snapshots the data and produces a ReportManifest — a serializable JSON structure consumed by both the static Markdown renderer and the live web dashboard.

To build interactive reports that respond to user input (slicers, date pickers) efficiently, you must understand the Three-Tier Logic model. This distinguishes between logic that runs once during the build and logic that runs every time a user changes a filter.

-- ❌ INCORRECT (The Tier 2 Trap): @region will only filter on the initial load.
SELECT * INTO #summary FROM sales WHERE region = @region;
SELECT 'Product A' AS Label, 100 AS Value INTO #kpi
UNION ALL
SELECT 'Product B', 250;

CREATE VISUAL KpiCard AS CARD (SOURCE = #kpi, MAPPINGS(VALUE = Value, LABEL = Label));

Temp tables are reusable across multiple visuals, debuggable with a plain SELECT, and consistent with the rest of ETL-SQL.

-- 1. Connect and pull data
CREATE CONNECTION c AS FLATFILE('data/sales.csv');

SELECT region, SUM(revenue) AS revenue
INTO #summary
FROM c
GROUP BY region;

-- 2. Define a visual
CREATE VISUAL SalesByRegion AS BAR (
  SOURCE = #summary,
  MAPPINGS (X = region, Y = revenue),
  OPTIONS (
    DATA_LABELS = ON WITH (
      POSITION    = 'INSIDE_TOP',  -- TOP | BOTTOM | LEFT | RIGHT | CENTER | INSIDE | INSIDE_TOP | ...
      COLOR       = '#FFFFFF',     -- CSS color
      FONT_SIZE   = 12,            -- Numeric size
      FONT_WEIGHT = 'BOLD',        -- NORMAL | BOLD
      FONT_FAMILY = 'Arial',
      FORMAT      = 'N1'           -- .NET format string
    ),
    GRID = ON
  )
);

-- 3. Arrange on a page (STRUCTURE uses CSS grid-template-areas)
CREATE PAGE Main AS DASHBOARD (
  LAYOUT (
    STRUCTURE = 'A',
    MAP ('A' = SalesByRegion)
  )
);

Save as report.rptsql, then:

etl-sql-report build report.rptsql        # → report.report.md + report.snapshot.json
etl-sql-report serve report.rptsql        # → opens http://localhost:5200

Report-SQL uses standard ETL-SQL @variables for all parameters. Declare them with DECLARE at the top of your script — any variable marked INPUT can be overridden by the Report Portal at runtime (when running on-demand or via a subscription).

-- Basic text/number parameters
DECLARE @region   VARCHAR INPUT = 'All';
DECLARE @min_sale DECIMAL INPUT = 0;

-- Relative date range — the portal resolves these fresh on each subscription fire
DECLARE @start    RELDATE INPUT = 'M-1';  -- first day of last month
DECLARE @end      RELDATE INPUT = 'D';    -- today

SELECT region, SUM(revenue) AS revenue
INTO #summary
FROM prod.Sales
WHERE region   = CASE WHEN @region = 'All' THEN region ELSE @region END
  AND sale_date BETWEEN @start AND @end
  AND amount   >= @min_sale
GROUP BY region;

RELDATE is the recommended type for date-range parameters in subscription-friendly reports. Instead of storing a fixed date, the subscriber stores an expression — M-1, W-1, D-7 — that is resolved to a concrete date each time the subscription fires.

DECLARE @start RELDATE INPUT = 'M-1';  -- default: first day of last month
DECLARE @end   RELDATE INPUT = 'D';    -- default: today

-- Override week boundaries if your organisation uses a different week start:
SET WEEK_START_DAY = 'Sunday';

See the ETL-SQL Grammar Reference for the full expression syntax.

Use ACTIONS (ON_CHANGE = SET_PARAMETER(@var, value)) on SLICER, DATEPICKER, or SLIDER visuals to wire user input to a parameter variable. The variable's value is then injected into every visual that references it.

DECLARE @region VARCHAR = 'All';

CREATE VISUAL RegionFilter AS SLICER (
  SOURCE  = #regions,
  MAPPINGS (VALUE = region_name),
  DEFAULT = 'All',
  ACTIONS (ON_CHANGE = SET_PARAMETER(@region, value))
);

CREATE VISUAL SalesChart AS BAR (
  SOURCE = (SELECT * FROM #summary WHERE region = @region OR @region = 'All'),
  MAPPINGS (X = region, Y = revenue)
);

Report Portal executes report scripts securely using the logged-in viewer's identity. Within your report SQL, you can use built-in identity variables and predicate functions to dynamically filter datasets so users only see data they are authorized to view.

• @@CURRENT_USER — The username of the logged-in viewer.
• @@CURRENT_USER_ID — The numeric identifier of the viewer.
• @@IS_ADMIN — Boolean flag indicating if the viewer is an administrator (bypassing RLS by default).

• HAS_GROUP('group_name') — Returns TRUE if the viewer is a member of the specified group.
• USER_GROUPS() — Table-valued function returning all groups the viewer belongs to.

1. Filtering by Username Filter rows where the manager matches the current viewer:

SELECT OrderId, Region, Total
INTO #filtered_orders
FROM prod_db.dbo.Orders
WHERE Manager = @@CURRENT_USER OR @@IS_ADMIN = TRUE;

2. Group-based Regional Filter Filter rows using HAS_GROUP to check for region-specific access:

SELECT OrderId, Region, Total
INTO #filtered_orders
FROM prod_db.dbo.Orders
WHERE (Region = 'US' AND HAS_GROUP('US_Sales') = TRUE)
   OR (Region = 'EU' AND HAS_GROUP('EU_Sales') = TRUE)
   OR @@IS_ADMIN = TRUE;

3. Dynamic Set-Membership Filter Use a subquery against USER_GROUPS() to join against a regional mapping table:

SELECT o.OrderId, o.Region, o.Total
INTO #filtered_orders
FROM prod_db.dbo.Orders o
JOIN #region_mappings m ON o.Region = m.RegionCode
WHERE m.GroupName IN (SELECT GroupName FROM USER_GROUPS())
   OR @@IS_ADMIN = TRUE;

Sets the report title and description displayed in the dashboard header and catalog page.

SET REPORT TITLE = 'Sales Dashboard';
SET REPORT DESCRIPTION = 'Regional and product-level revenue analysis for Q1 2026.';

Both statements are optional. If omitted the script filename is used as the title.

Markdown flags such as TITLE_MD, SUBTITLE_MD, and TOOLTIP_MD belong on CREATE VISUAL, CREATE PAGE, CREATE CONTAINER, or CREATE BUTTON style blocks. SET REPORT TITLE and SET REPORT DESCRIPTION are plain report metadata strings.

Report-SQL follows normal ETL-SQL statement style: name the object first, use AS before the body, and keep object-specific clauses inside the outer parentheses.

Use these forms as the preferred style in docs, samples, and generated scripts:

CREATE DATASET &sales_summary AS (
  SELECT region, SUM(revenue) AS revenue
  FROM #sales
  GROUP BY region
);

CREATE VISUAL RevenueByRegion AS BAR (
  SOURCE   = &sales_summary,
  MAPPINGS (X = region, Y = revenue),
  STYLE    (THEME = light)
);

CREATE PAGE Overview AS DASHBOARD (
  LAYOUT (
    STRUCTURE = 'A',
    MAP ('A' = RevenueByRegion),
    GAP = '16px'
  )
);

Syntax notes:

• CREATE PAGE <name> AS DASHBOARD (...) defines a page that loads result visuals immediately and keeps controls live.
• CREATE PAGE <name> AS PAGINATED (...) defines a page that stages prompt changes until an APPLY_PARAMETERS button is clicked.
• Page layout may be written directly with STRUCTURE/MAP or inside LAYOUT (...). Prefer LAYOUT (...) in new scripts for consistency with containers.
• CREATE DATASET uses &dataset names only. Use #temp for intermediate engine tables created by SELECT ... INTO #temp; use &dataset for reusable report-owned datasets.
• STYLE = StyleName applies a named style. STYLE (key = value, ...) applies inline overrides. A standalone STYLE (...) statement is not valid.
• SOURCE = #temp, SOURCE = &dataset, SOURCE = ViewName, and SOURCE = (SELECT ...) are the canonical source forms. #temp is engine memory; &dataset is a report dataset definition or portal-registered dataset; ViewName is a session-scoped ETL-SQL query view created with CREATE VIEW.

Use each report document for a specific job:

• Docs/Report_SQL_Guide.md explains how to build reports and should favor complete, readable examples.
• Docs/Reference/Grammar.md is the exact syntax contract and should stay close to parser behavior.
• src/ETL-SQL.Core/Resources/Help/Report/*.md feeds editor help and hover text, so examples must stay short and parser-backed.
• samples/**/*.rptsql files are runnable workflows and should use canonical syntax unless they are intentionally testing compatibility.

Sets global metadata and overrides for the entire report. These settings affect the dashboard shell, navigation profiles, and master branding.

• Global vs. Local: SET REPORT settings provide the master default for the dashboard. For example, setting SET REPORT THEME = 'dark' will theme all pages and the dashboard shell unless a specific CREATE PAGE has its own STYLE (THEME = ...) override.
• Shell vs. Content: Unlike CREATE VISUAL or CREATE PAGE which define the content, SET REPORT affects the Host Shell (the browser tab, the header bar, the sidebar container, and injected assets like JS/CSS).

-- Branding and Shell Customization
SET REPORT TITLE = 'Global Sales Dashboard';
SET REPORT THEME = 'glass'; -- Sets the default for all pages
SET REPORT LOGO = 'https://example.com/assets/logo-white.svg';
SET REPORT CSS = '
  :root { --accent-color: #ff9900; }
  .dashboard-header { border-bottom: 2px solid var(--accent-color); }
';

CREATE [OR ALTER] VISUAL <name> AS <TYPE> (
  [SOURCE = <source>,]
  [TITLE = '<string>',]
  [SUBTITLE = '<string>',]
  [TOOLTIP = '<string>',]
  [VISIBLE = ON|OFF,]
  [FETCH = AUTO|ON_LOAD|ON_RUN,]
  [SUMMARY (
    [GRAND_TOTAL = ON|OFF,]
    [aggregate(column) [AS alias], ...]
  ),]
  [MAPPINGS (role = column, ...),]
  [OPTIONS (key = value, ..., X_AXIS (...), Y_AXIS (...), COLORS (...)),]
  [STYLE = <styleName> | STYLE (key = value, ...),]
  [SERIES (type column, ...),]
  [ACTIONS (trigger = action, ...)]
);

All clauses inside the outer ( ) are separated by commas. The closing ) ends the statement. SOURCE is required for all types except TEXT, DATEPICKER, RELDATEPICKER, SLIDER, and SEARCH.

VISIBLE controls whether the visual is shown in the UI. It does not control when the visual's data is fetched.

-- Hidden helper visual; still follows normal fetch rules
CREATE VISUAL ExpensiveBreakdown AS BAR (
  SOURCE  = (SELECT category, SUM(revenue) AS revenue
             FROM #sales WHERE region = @region GROUP BY category),
  VISIBLE = OFF,
  MAPPINGS (X = category, Y = revenue)
);

Accepted values: ON (default), OFF, TRUE, FALSE, 1, 0.

FETCH controls when a visual's SOURCE is evaluated:

Prompt/control visuals are SLICER, MULTISELECT, DATEPICKER, RELDATEPICKER, SLIDER, SEARCH, CHECKBOX, TEXTBOX, NUMBERBOX, TEXT, and IMAGE. Charts, CARD, TABLE, MATRIX, MAP, and other data visuals are result visuals.

The report viewer adds a maximize control to data and chart visuals by default. Input/control visuals (SLICER, MULTISELECT, DATEPICKER, RELDATEPICKER, SLIDER, SEARCH, CHECKBOX, TEXTBOX, and NUMBERBOX) hide maximize by default so their interactive controls are not covered.

Use STYLE (ALLOW_MAXIMIZE = ON) to opt a control visual into maximize, or STYLE (ALLOW_MAXIMIZE = OFF) to hide maximize on a visual that would normally show it. Maximizing a visual preserves the current manifest state and resizes charts to fit the expanded card. Press Esc or click the restore control to return to the page layout.

SOURCE = (
  SELECT category, sub_category,
    value * 100.0 / SUM(value) OVER(PARTITION BY category) AS pct
  FROM #data
)

| SEARCH | Free-text search box with debounce. No SOURCE required. | <input type="text"> | | CHECKBOX | Boolean toggle. No SOURCE required. | <input type="checkbox"> | | TEXTBOX | Text input field. No SOURCE required. | <input type="text"> | | NUMBERBOX | Numeric input with validation. No SOURCE required. | <input type="number"> |

The SOURCE clause provides the data for the visual. It can be:

-- a) A pre-populated temp table (set earlier in the same script)
SOURCE = #my_temp_table

-- b) An inline SELECT (must be wrapped in parentheses)
SOURCE = (SELECT region, SUM(revenue) AS rev FROM #summary GROUP BY region)

Inline SELECTs are evaluated at build time and their results are snapshotted into the manifest. If you need the visual to refresh independently, use CREATE DATASET and reference the dataset by name.

TEXT, DATEPICKER, RELDATEPICKER, SLIDER, SEARCH, CHECKBOX, TEXTBOX, and NUMBERBOX visuals do not require SOURCE. MULTISELECT requires a SOURCE to populate the option list.

CREATE VISUAL RevenueCard AS CARD (
  SOURCE = ...,
  TITLE    = 'Total Revenue',
  SUBTITLE = 'All regions, YTD',
  MAPPINGS (VALUE = revenue)
);

TITLE overrides the visual name as the chart heading. SUBTITLE appears below the title in smaller text.

Adds hover-triggered content shown when the user hovers over the visual's title area. Three forms are supported:

1. Plain text

TOOLTIP = 'Sum of all regional revenue, YTD.'

2. Named container reference

References an existing CREATE CONTAINER by name. The container's visuals are rendered as a popover panel. The hovered value is injected as a parameter (@hover_value) so tooltip visuals can filter to the point being inspected.

CREATE VISUAL RegionSparkline AS LINE (
  SOURCE = (SELECT month, SUM(revenue) AS revenue FROM #summary
            WHERE region = @hover_value GROUP BY month),
  MAPPINGS (X = month, Y = revenue)
);

CREATE CONTAINER RegionTooltip AS BOX (
  LAYOUT (
    STRUCTURE = 'A',
    MAP ('A' = RegionSparkline)
  )
);

CREATE VISUAL RevenueByRegion AS BAR (
  SOURCE   = (SELECT region, SUM(revenue) AS revenue FROM #summary GROUP BY region),
  MAPPINGS (X = region, Y = revenue),
  TOOLTIP  = RegionTooltip       -- container reference
);

3. Inline anonymous container

Defines tooltip content inline — an optional markdown string followed by a VISUALS list. The outer ( ) delimit the inline block; markdown and VISUALS are separated by a comma.

TOOLTIP = ('**Revenue trend** for the hovered region', VISUALS(RegionSparkline))

-- markdown only (no chart)
TOOLTIP = ('Click a bar to drill down by month.')

-- visuals only (no markdown)
TOOLTIP = (VISUALS(RegionSparkline, RegionTable))

Full example:

CREATE VISUAL RevenueCard AS CARD (
  SOURCE   = #kpis,
  TITLE    = 'Total Revenue',
  TOOLTIP  = ('**Total Revenue** — sum of all regional revenue, YTD.', VISUALS(TrendSparkline)),
  MAPPINGS (VALUE = revenue, LABEL = label)
);

@hover_value is set to the X-axis or LABEL value of the hovered data point. Tooltip visuals that reference @hover_value in their inline SELECT are re-queried on each hover. TOOLTIP applies to all visual types, CREATE PAGE, CREATE CONTAINER, and CREATE BUTTON.

Maps the columns returned by SOURCE to semantic roles expected by the renderer. Roles are case-insensitive.

-- Simple: single series
MAPPINGS (X = month, Y = revenue)

-- Grouped: one line/bar per region
MAPPINGS (X = month, Y = revenue, SERIES = region)

A scatter plot for exploring correlations between two numeric dimensions. If the SIZE mapping is provided, it renders as a BUBBLE chart.

-- Scatter
MAPPINGS (X = score, Y = rank)

-- Bubble
MAPPINGS (X = score, Y = rank, SIZE = population, LABEL = city)

MAPPINGS (LABEL = category, VALUE = total)

MAPPINGS

MAPPINGS (VALUE = Revenue, LABEL = MetricName, GOAL = Target, DELTA = PriorRevenue)

OPTIONS

-- Goal-tracking card with progress ring and status badge
CREATE VISUAL RevenueKpi AS CARD (
    SOURCE = (SELECT SUM(Revenue) AS Revenue, 500000 AS Target FROM #sales),
    TITLE  = 'Total Revenue',
    MAPPINGS (VALUE = Revenue, GOAL = Target),
    OPTIONS (
        FORMAT            = 'C0',
        ABBREVIATE        = ON,
        SHOW_GOAL         = ON,
        SHOW_PERCENT_OF_GOAL = ON,
        SHOW_PROGRESS     = ON,
        PROGRESS_STYLE    = RING,
        ICON_SET          = CHECKS,
        LABEL_MET         = 'Target achieved!',
        LABEL_MISSED      = 'Behind target'
    )
);

-- Delta card showing change vs prior period
CREATE VISUAL RevenueTrend AS CARD (
    SOURCE = (SELECT SUM(Revenue) AS Revenue, SUM(PriorRevenue) AS Prior FROM #sales),
    TITLE  = 'Revenue vs Prior Year',
    MAPPINGS (VALUE = Revenue, DELTA = Prior),
    OPTIONS (FORMAT = 'C0', DELTA_LABEL = 'vs prior year', ABBREVIATE = ON)
);

The IMAGE visual renders an image from a URL or a local file path. Variables of type IMAGE can also be passed directly to report visuals.

DECLARE @logo IMAGE = 'C:\Data\Branding\logo.png';

CREATE VISUAL CompanyLogo AS IMAGE (
  OPTIONS (
    SRC = @logo,
    FIT = 'contain' -- contain | cover | fill | none
  )
);

TEXT visuals render free-form string content. No SOURCE is required; the content is provided via the DEFAULT clause.

CREATE VISUAL WelcomeText AS TEXT (
  TITLE   = 'Welcome',
  DEFAULT = '### Hello, World!\nThis is a *markdown-enabled* text block.'
);

SLICER uses SOURCE to provide option rows and ACTIONS to bind the selection to a parameter. The MAPPINGS clause specifies which column from the source holds the display value.

CREATE VISUAL RegionFilter AS SLICER (
  SOURCE  = (SELECT DISTINCT region FROM #summary ORDER BY region),
  MAPPINGS (VALUE = region),
  ACTIONS  (ON_CHANGE = SET_PARAMETER(@region, region)),
  DEFAULT  = 'All'
);

The DEFAULT option on a SLICER pre-selects that value in the dropdown on load. It is cosmetic only — it does not declare the page parameter. The corresponding DECLARE @region VARCHAR = 'All' at the top of the script is what makes @region available to visual queries from the first render.

Same pattern as SLICER: SOURCE provides options, ACTIONS binds the selection.

CREATE VISUAL CategoryFilter AS MULTISELECT (
  SOURCE = (SELECT DISTINCT category FROM #products ORDER BY category),
  MAPPINGS (VALUE = category),
  ACTIONS (ON_CHANGE = SET_PARAMETER(@category, category))
);

SLIDER is a numeric range control. No SOURCE is required. Set bounds and step in OPTIONS, then bind the value to a page parameter via ACTIONS.

CREATE VISUAL YearSlider AS SLIDER (
  TITLE   = 'Year',
  TOOLTIP = 'Drag to filter by year',
  OPTIONS (MIN = 2020, MAX = 2026, STEP = 1, DEFAULT = 2024),
  ACTIONS (ON_CHANGE = SET_PARAMETER(@year, value))
);

DATEPICKER is a date input control. No SOURCE is required.

CREATE VISUAL StartDate AS DATEPICKER (
  TITLE   = 'Start Date',
  TOOLTIP = 'Filter results from this date',
  OPTIONS (MIN = '2020-01-01', MAX = '2026-12-31', DEFAULT = '2024-01-01'),
  ACTIONS (ON_CHANGE = SET_PARAMETER(@startDate, value))
);

RELDATEPICKER is a combined control for relative or absolute date values. No SOURCE is required. It renders a text input (accepts relative expressions such as D-7, M-1, Y-1 or ISO dates like 2026-04-27), a 📅 calendar button that writes the selected ISO date into the text box, and a row of quick-pick buttons (Today, D-1, D-7, D-30, M-1, M-3, Y-1).

Use DATEPICKER when the variable is DATE/DATETIME and only absolute dates are needed. Use RELDATEPICKER when users need to enter relative expressions that your script evaluates with RELDATE().

DECLARE @Start RELDATE = 'M-1';
DECLARE @End   RELDATE = 'D-0';

CREATE VISUAL StartPicker AS RELDATEPICKER (
  TITLE   = 'From',
  OPTIONS (DEFAULT = 'M-1'),
  ACTIONS (ON_CHANGE = SET_PARAMETER(@Start, value))
);

CREATE VISUAL EndPicker AS RELDATEPICKER (
  TITLE   = 'To',
  OPTIONS (DEFAULT = 'D-0'),
  ACTIONS (ON_CHANGE = SET_PARAMETER(@End, value))
);

-- In each data visual's inline SELECT:
-- DECLARE @StartDate DATE = RELDATE(@Start);
-- DECLARE @EndDate   DATE = RELDATE(@End);
-- SELECT * FROM orders WHERE order_date BETWEEN @StartDate AND @EndDate;

SEARCH is a free-text input box with debounce. No SOURCE is required.

CREATE VISUAL ProductSearch AS SEARCH (
  TITLE   = 'Search',
  OPTIONS (PLACEHOLDER = 'Type a product name...', DEFAULT = ''),
  ACTIONS (ON_CHANGE = SET_PARAMETER(@searchTerm, value))
);

These visuals provide standard form-like inputs for parameter control. Unlike charts, they use dedicated top-level properties for validation and layout.

CREATE VISUAL ShowActive AS CHECKBOX (
  TITLE = 'Show Active Only',
  LABEL_POSITION = 'LEFT',
  ACTIONS (ON_CHANGE = SET_PARAMETER(@active_only, value))
);

CREATE VISUAL UserName AS TEXTBOX (
  TITLE = 'User Filter',
  LABEL_POSITION = 'TOP',
  OPTIONS (PLACEHOLDER = 'Enter name...'),
  ACTIONS (ON_CHANGE = SET_PARAMETER(@user, value))
);

CREATE VISUAL PriceThreshold AS NUMBERBOX (
  TITLE = 'Min Price',
  LABEL_POSITION = 'LEFT',
  MIN = 0,
  MAX = 10000,
  DECIMALS = 2,
  ACTIONS (ON_CHANGE = SET_PARAMETER(@min_price, value))
);

Input visuals are typically placed in a SCROLL container or a dedicated sidebar/header row on a PAGE.

Parameter binding for DATEPICKER, RELDATEPICKER, SLIDER, and SEARCH (and for SLICER / MULTISELECT) is always explicit — there is no automatic wiring. The mechanism is:

1. Declare a variable at the top of your script: DECLARE @year INT = 2024
2. Use @year inside the inline SELECT of any visual that should react to it.
3. Add ACTIONS (ON_CHANGE = SET_PARAMETER(@year, value)) to the control visual.

The second argument to SET_PARAMETER is a column reference:

• For SLIDER, DATEPICKER, RELDATEPICKER, and SEARCH: use the literal word value — it refers to the control's current value at the time the event fires.
• For SLICER and MULTISELECT: use the column name from your SOURCE query that holds the selectable value (e.g., region).

When the user interacts with a control, the dashboard posts the new value to the server, which re-evaluates only the visuals whose SELECT queries reference the updated variable. Visuals that do not reference the changed variable are not re-queried. Control binding belongs in ACTIONS (ON_CHANGE = SET_PARAMETER(...)).

When using visual filters to control parameters, ensure the target variable type matches the filter's behavior:

MAPPINGS (X = hour, Y = weekday, VALUE = count)

MAPPINGS (LABEL = product, VALUE = revenue)

CREATE VISUAL RevenueKpi AS GAUGE (
  SOURCE = (SELECT 73 AS pct, 100 AS target, 'Revenue Target' AS lbl),
  MAPPINGS (VALUE = pct, MAX = target, LABEL = lbl),
  OPTIONS (MIN = 0, MAX = 100, TITLE = 'Revenue vs Target')
);

-- Using a MINMAX variable for bounds
DECLARE @bounds MINMAX(INT) = (0, 200);
CREATE VISUAL BalancedGauge AS GAUGE (
  SOURCE = #data,
  MAPPINGS (VALUE = val),
  OPTIONS (MIN = @bounds.MIN, MAX = @bounds.MAX)
);

-- Semi-circle gauge (Power BI style)
CREATE VISUAL EfficiencyGauge AS GAUGE (
  SOURCE = (SELECT 85 AS value),
  MAPPINGS (VALUE = value),
  OPTIONS (GAUGE_STYLE = 'SEMI_CIRCLE', TITLE = 'Operating Efficiency')
);

MIN and MAX options override column-derived bounds. Both default to 0 / 100 when omitted.

CREATE VISUAL SalesFunnel AS FUNNEL (
  SOURCE = (SELECT Stage, Leads FROM #funnel ORDER BY Leads DESC),
  MAPPINGS (LABEL = Stage, VALUE = Leads)
);

CREATE VISUAL CashFlow AS WATERFALL (
  SOURCE = (SELECT Period, Delta FROM #cashflow),
  MAPPINGS (X = Period, Y = Delta)
);

Use the COLORS (positive = '#5cb85c', negative = '#d9534f') option inside OPTIONS (COLORS (...)) to customise bar colors.

CREATE VISUAL PriceDistribution AS BOXPLOT (
  SOURCE = (SELECT category, low, q1, median, q3, high FROM #price_stats),
  MAPPINGS (X = category, LOW = low, Q1 = q1, MEDIAN = median, Q3 = q3, HIGH = high),
  TITLE = 'Price Distribution by Category'
);

TABLE visuals use all columns returned by SOURCE in definition order. No MAPPINGS clause is needed.

See Table Summaries for calculating aggregates and Conditional Formatting for applying cell colors.

The SUMMARY clause enables server-side calculation of aggregates and grand totals for TABLE visuals. The computed results appear in a sticky footer.

CREATE VISUAL SalesTable AS TABLE (
  SOURCE = #sales,
  OPTIONS (
    GRID = (HEADER, FOOTER),  -- ALL | NONE | HEADER | FOOTER | LEFT | RIGHT | TOP | BOTTOM
    SHOW_NO_DATA_PLACEHOLDER = ON
  ),
  SUMMARY (
    GRAND_TOTAL = ON,
    SUM(revenue) AS 'Total Revenue'
  )
);

• GRID: Controls border visibility. ALL (default) shows full grid. NONE removes all lines. Supports multi-select via lists: GRID = (HEADER, FOOTER, LEFT).
• SHOW_NO_DATA_PLACEHOLDER: Displays a "No Data" icon or empty state when the source result is empty.

COMBO visuals use the SERIES block to assign which columns render as bars vs lines. The X mapping provides the category axis.

CREATE VISUAL SalesCombo AS COMBO (
  SOURCE = (SELECT month, revenue, units FROM #summary),
  MAPPINGS (X = month),
  SERIES (BAR revenue, LINE units)
);

General key/value options plus optional axis sub-blocks. All are optional:

OPTIONS (
  -- flat options:
  TITLE           = 'Revenue Over Time',
  STACKED         = ON,
  SMOOTH          = ON,
  FORMAT          = 'N0',
  LEGEND_POSITION = BOTTOM,   -- TOP | BOTTOM | LEFT | RIGHT

  -- axis sub-blocks (BAR, HBAR, LINE, SCATTER only):
  X_AXIS (
    LABEL = 'Month',
    MIN   = 0,
    MAX   = 100
  ),
  Y_AXIS (
    LABEL = 'Revenue ($)',
    MIN   = 0
  ),

  -- color map (any chart type):
  COLORS (
    'North' = '#4e79a7',
    'South' = '#f28e2b'
  )
)

Maps category values to specific hex colors. Key is the category value (quoted if it contains spaces); value is a CSS color string.

COLORS (
  'East'  = '#4e79a7',
  'West'  = '#f28e2b',
  'North' = '#76b7b2'
)

Controls legend placement. Use as a flat key in the OPTIONS block:

OPTIONS (LEGEND_POSITION = TOP)     -- TOP | BOTTOM | LEFT | RIGHT

Applies CSS colors to TABLE visual cells based on full logical expressions. Each rule acts as a branch in an implied CASE statement.

CREATE VISUAL FinancialSummary AS TABLE (
  SOURCE = (SELECT Category, Revenue, Margin FROM #summary),
  FORMATTING (
    Revenue < 0 OR Margin < 0      THEN 'red',
    Revenue >= 100000 AND Revenue < 500000 THEN 'yellow',
    Revenue >= 500000 AND Margin > 0.1 THEN '#28a745',
    Category IS NULL THEN 'gray'
  )
);

Rule syntax: <Expression> THEN 'color'

Formatting rules support the full ETL-SQL expression engine, including AND, OR, NOT, IS NULL, IS NOT NULL, and standard library functions. Multiple rules are evaluated top-to-bottom; the first matching rule wins.

Adds reference lines and statistical curves on top of BAR, LINE, HBAR, and SCATTER visuals. Each entry specifies an overlay type, a line style, and optional color and label.

OVERLAYS (
  <type>  AS SOLID|DASHED|DOTTED [WITH (COLOR = '<css>', LABEL = '<text>')],
  ...
)

GOAL and AVERAGE render as ECharts markLine overlays on the chart — they are always horizontal and do not require additional data points. MOVING_AVG, LINEAR, and the regression types render as additional line series computed at build time from the Y column values.

Both COLOR and LABEL are optional:

• COLOR — any CSS color string ('#e74c3c', 'rgb(0,0,0)'). Defaults to #888888.
• LABEL — text shown on the overlay in the chart legend. Defaults to the overlay type name.

-- Sales line chart with goal, average, and 3-month moving average
CREATE VISUAL RevenueByMonth AS LINE (
  SOURCE   = (SELECT month, SUM(revenue) AS revenue FROM #summary GROUP BY month),
  MAPPINGS (X = month, Y = revenue),
  OVERLAYS (
    GOAL(100000)  AS DASHED WITH (COLOR = '#e74c3c', LABEL = 'Annual Target'),
    GOAL(80000)   AS DOTTED WITH (COLOR = '#e67e22', LABEL = 'Minimum'),
    AVERAGE       AS DASHED WITH (COLOR = '#3498db', LABEL = 'Mean'),
    MOVING_AVG(3) AS SOLID  WITH (COLOR = '#2ecc71', LABEL = '3-Month Avg')
  )
);

-- Scatter plot with linear regression and polynomial fit
CREATE VISUAL ScoreVsRank AS SCATTER (
  SOURCE   = (SELECT score, rank FROM #results),
  MAPPINGS (X = score, Y = rank),
  OVERLAYS (
    LINEAR      AS DASHED WITH (COLOR = '#9b59b6', LABEL = 'Linear Fit'),
    POLYNOMIAL(2) AS DOTTED WITH (COLOR = '#e67e22', LABEL = 'Poly Fit')
  )
);

Multiple GOAL lines are supported — just add additional GOAL(n) entries. OVERLAYS applies to BAR, HBAR, LINE, and SCATTER visuals; it is ignored on PIE, DONUT, TABLE, CARD, and filter controls.

Applies visual-level styling properties. Visual-level STYLE takes precedence over page-level THEME.

STYLE (
  THEME      = dark,          -- dark | light
  HEIGHT     = 400,           -- pixels
  WIDTH      = 600,           -- pixels
  BACKGROUND = '#1a1a2e',
  BORDER     = '1px solid #333'
)

* Note on HEIGHT/WIDTH: These properties apply to all report objects, including VISUAL, CONTAINER, and BUTTON. When applied to a container, they constrain the outer boundary of the layout.

Actions wire up interactive behavior in the live dashboard:

ACTIONS (
  ON_CLICK = DRILL_DOWN(Target = DetailChart, Key = region)
)

Each report object type has one valid trigger shape:

Invalid trigger/object combinations are syntax errors. For example, a BAR visual cannot declare ON_CHANGE, and a SLICER cannot declare ON_CLICK.

ON_CLICK = DRILL_DOWN(Target = <VisualName>, Key = <column>)

When clicked, passes the selected row's value in Key as a filter into the target visual's inline SELECT. The target visual is re-queried with the key value injected into the parameter context.

Key may be a single column name or a parenthesised list for composite keys:

ON_CLICK = DRILL_DOWN(Target = OrderDetail, Key = (region, product))

ON_CLICK = DRILL_IN(HIERARCHY = (<col1>, <col2>, ...))

Enables in-place hierarchical drill-down on chart visuals (BAR, LINE, etc.). When the user clicks a data point the chart re-aggregates at the next level of the hierarchy and shows a breadcrumb trail. Clicking any breadcrumb segment navigates back up.

• The SOURCE query must return all hierarchy columns plus the Y metric.
• No hidden page or extra visual is needed — the drill happens inside the same visual slot.
• The chart title bar automatically renders breadcrumb navigation (e.g. Year > Q2 > May).

CREATE VISUAL SalesByPeriod AS BAR (
  SOURCE = (SELECT Year, Quarter, Month, SUM(Revenue) AS Revenue
            FROM #sales_raw
            GROUP BY Year, Quarter, Month),
  MAPPINGS (X = Year, Y = Revenue),
  OPTIONS  (TITLE = 'Revenue by Period — click to drill'),
  ACTIONS  (ON_CLICK = DRILL_IN(HIERARCHY = (Year, Quarter, Month)))
);

ON_CLICK = DRILL_REPORT (
  REPORT = 'SalesDetail',    -- Recommended: Use logical name from reports.json
  -- FILE = 'detail.rptsql', -- Alternative: Use relative file path
  PARAMETERS (
    @TargetID = user_id,     -- Map source column to target parameter
    @ReportDate = @start     -- Map local variable to target parameter
  )
)

Navigates to a completely different report. This is the preferred way to implement Master-Detail dashboards where the detail view is complex, hosted separately, or part of a multi-report application.

• REPORT: The logical name (slug) of the target report as defined in a reports.json manifest.
• FILE: The physical path to the target .rptsql file (relative to the current report).
• PARAMETERS: A mapping of target report parameters to source column names or local variables.
• Navigation Behavior:
  • In VS Code:
    • Tab-to-Tab: Clicking a drill link in a preview panel opens the target report in a new VS Code tab, passing the parameters into the new preview session.
    • Launch Options: Use the 🚀 Launch Options dropdown to "Launch all reports in directory" or "Launch using reports.json". This ensures the local server hosts the entire suite for full navigation testing.
  • In Report Player / Portal: The browser navigates to the target URL (e.g. /reports/SalesDetail?@TargetID=123) and the engine injects the query string parameters into the target report's execution context.

• In the Report Portal, the browser navigates to the target report URL.
• In the Standalone Player, the browser navigates to the sibling report on the same server.

ON_CHANGE = SET_PARAMETER(@paramName, <columnRef>)

Sets the named @param to the selected value. Any visual whose inline SELECT references @paramName is automatically re-queried.

ON_CLICK = RUN_SCRIPT('<scriptPath>', @param = <columnRef> [, ...])

Executes a .etlsql script as a side-effect when a chart element is clicked. Column values from the clicked row are forwarded as named parameters. Useful for write-back workflows (approve, archive, flag).

ACTIONS (ON_CLICK = RUN_SCRIPT('C:\scripts\approve_order.etlsql', @order_id = id))

ON_CLICK = CLEAR_FILTERS

Resets all active filters and cross-highlighting states on the current page. Typically used on a dedicated reset button:

CREATE BUTTON ResetAll AS (
  TITLE   = 'Clear All Filters',
  ACTIONS (ON_CLICK = CLEAR_FILTERS)
);

ON_CLICK = APPLY_PARAMETERS

Triggers a full report refresh in Staged Mode. Useful for paginated reports where you want the user to set multiple filters and then click "Run" to fetch data once.

ON_CLICK = NAVIGATE_PAGE(<PageName>)

Shows the named page in the current report. This is the canonical action for report-page navigation from a button. It can navigate to pages that are hidden from the navigation bar with VISIBLE = OFF.

ON_CLICK = SET_UI_STATE(<Target>, <Key>, <Value>)

Changes the ephemeral visual state of report objects without triggering a data re-query.

• Target:
  • 'VisualName': Target a single visual or container.
  • ('V1', 'V2'): Target multiple objects.
  • 'TAG:TagName': Target all objects with a specific TAG option.
• Key: VISIBLE, COLLAPSED, BACKGROUND-COLOR, COLOR, CLASS.
• Value: ON/OFF, colors (hex/name), or class names (prefix with + to add, - to remove).

Example: Collapse a filter panel after running report

CREATE BUTTON RunBtn AS (
  TITLE   = 'Run Report',
  ACTIONS (
    ON_CLICK = (
      APPLY_PARAMETERS,
      SET_UI_STATE('FilterPanel', 'COLLAPSED', ON)
    )
  )
);

### INTERACTIONS {#interactions}

Controls how a visual responds when the user clicks an element on **another** chart on the same page.

```sql
INTERACTIONS (
  ON_SELECT = HIGHLIGHT | FILTER | NONE,
  MATCHING = Region
)

CREATE VISUAL CategoryBreakdown AS BAR (
  SOURCE  = #sales,
  MAPPINGS (X = Category, Y = Revenue),
  INTERACTIONS (ON_SELECT = HIGHLIGHT)
);

CREATE VISUAL SalesTable AS TABLE (
  SOURCE  = #sales,
  INTERACTIONS (ON_SELECT = FILTER)   -- rows disappear when a bar is clicked
);

Pre-computes a named report dataset that can be independently refreshed and optionally encrypted or compressed. Use this when multiple visuals share the same expensive base query, or when you want separate refresh cadences.

CREATE DATASET &<name>
  [REFRESH EVERY '<interval>']
  [TTL = '<duration>']
  [COMPRESS = ON|OFF]
  [ACCESS PUBLIC | PRIVATE]
  [ENCRYPT = MACHINE | PASSWORD | KEYFILE]
  [PASSWORD = '<password>']
  [KEYFILE  = '<path>']
AS ( SELECT ... );

In portal hosting, every managed cache is encrypted with the portal at-rest key. The ENCRYPT clause on CREATE DATASET is transport intent only; it does not make the managed portal file directly portable. Use EXPORT DATASET, then PUBLISH DATASET, to move a snapshot between portals.

In standalone execution without a portal registry, the clause is applied directly:

-- Machine-bound (simplest — no credentials to manage)
CREATE DATASET &sales_snap
  REFRESH EVERY '1h'
  TTL = '24h'
  COMPRESS = ON
  ENCRYPT = MACHINE
  AS (SELECT region, product, SUM(revenue) AS revenue
      FROM sales
      GROUP BY region, product);

-- Password-protected (portable)
CREATE DATASET &sales_secure
  ENCRYPT = PASSWORD
  PASSWORD = 'MyS3cretPhrase'
  AS (SELECT * FROM sensitive_table);

-- Key-file protected
CREATE DATASET &sales_keyfile
  ENCRYPT = KEYFILE
  KEYFILE = 'C:\keys\report.key'
  AS (SELECT * FROM sales);

Use this two-step workflow to move a materialized dataset between portals:

1. EXPORT DATASET decrypts the source portal cache and creates a portable copy encrypted with a one-time transport credential.
2. Transfer the encrypted file and credential separately.
3. PUBLISH DATASET decrypts that copy once and re-encrypts it with the destination portal's at-rest key.

Transport credentials are never persisted. The published copy is portal-managed and is not itself portable; keep the original export for subsequent transfers.

EXPORT DATASET &sales_snap
  TO 'C:\Transfer\sales_snap.parquet'
  ENCRYPT = PASSWORD
  PASSWORD = 'one-time-transport-secret';

PUBLISH DATASET
  FROM 'C:\Transfer\sales_snap.parquet'
  AS &sales_imported
  INTO '/Finance/Imported'
  ACCESS PRIVATE
  ENCRYPT = PASSWORD
  PASSWORD = 'one-time-transport-secret';

EXPORT DATASET &sales_snap
  TO 'C:\Transfer\sales_snap.parquet'
  ENCRYPT = KEYFILE
  KEYFILE = 'C:\Transfer\keys\dataset_transport.pub';

PUBLISH DATASET
  FROM 'C:\Transfer\sales_snap.parquet'
  AS &sales_imported
  INTO '/Finance/Imported'
  ACCESS PUBLIC
  ENCRYPT = KEYFILE
  KEYFILE = 'C:\Transfer\keys\dataset_transport';

The key files must match the engine's supported key-file encryption format. EXPORT DATASET requires read access to the source dataset. PUBLISH DATASET requires an existing destination folder and folder Manage permission. Dataset names are globally unique. ACCESS defaults to PRIVATE.

Both operations are failure-atomic: incomplete output is discarded, failed publish allocation is rolled back, and retrying the same global name is supported. Passwords, key paths containing credentials, and portal at-rest keys must not be printed or logged.

Arranges visuals and containers into a named layout. Multiple pages can be defined in one script; the web dashboard renders each as a distinct section. Page mode is explicit: DASHBOARD pages load data immediately and respond to controls as they change; PAGINATED pages stage prompt changes and load result visuals when the user clicks a button with APPLY_PARAMETERS.

CREATE [OR ALTER] PAGE <name> AS DASHBOARD|PAGINATED (
  [TITLE = '<string>',]
  [TOOLTIP = '<string>',]
  [REFRESH = <seconds>,]
  LAYOUT (
    STRUCTURE = '<grid-template-areas>',
    MAP (
      '<slot>' = VisualOrContainerName,
      ...
    )
    [, GAP = '<css-size>']
  )
  [, STYLE = <styleName> | STYLE (key = value, ...)]
  [, VISIBLE = ON|OFF]
);

STRUCTURE, MAP, and GAP may also be written directly in the page body instead of inside LAYOUT (...), but LAYOUT (...) is the preferred style for consistency with CREATE CONTAINER.

Dashboard pages show all data on first render. Controls post parameter changes immediately and affected visuals refresh.

Paginated pages show prompt/control visuals on first render, but result/data visuals using FETCH = AUTO wait until an APPLY_PARAMETERS button is clicked. Use FETCH = ON_LOAD for any result visual that should render before Run, and FETCH = ON_RUN to force a control or helper visual to wait until Run.

APPLY_PARAMETERS is a run command, not a mode detector. A script can mix dashboard and paginated pages.

VISIBLE = OFF hides the page from the navigation bar while still rendering it in the DOM. Hidden pages are only reachable via DRILL_DOWN or programmatic navigation.

CREATE PAGE DetailView AS PAGINATED (
  LAYOUT (
    STRUCTURE = 'A',
    MAP ('A' = DetailTable)
  ),
  VISIBLE = OFF
);

A button or chart click action can navigate to a hidden page:

CREATE BUTTON ShowDetail AS (
  TITLE   = 'View Details',
  ACTIONS (ON_CLICK = DRILL_DOWN(Target = DetailView, Key = id))
);

Hidden pages are useful for drill-through flows where the detail page should not appear as a permanent nav item.

STRUCTURE is a CSS grid-template-areas string. Slot letters appear as space-separated names within a row; rows are separated by /.

-- Single visual filling the full width
STRUCTURE = 'A'

-- Two visuals side by side
STRUCTURE = 'A B'

-- Two rows: header spanning both columns, then two below
STRUCTURE = 'A A / B C'

-- Three rows: KPI row, chart row, table row
STRUCTURE = 'A B C / D D D / E E E'

Each unique letter becomes a grid area. The renderer calculates column count from the maximum number of distinct letters in any single row.

MAP assigns each visual or container to a slot letter. Slot letters must match those used in STRUCTURE.

MAP (
  'A' = KpiCard,
  'B' = BarChart,
  'C' = LineChart,
  'D' = DataTable
)

Applies page-level styling. The THEME key cascades to all charts on the page unless overridden at the visual level.

STYLE (
  THEME      = dark,
  BACKGROUND = '#0f0f1a'
)

Report-SQL uses standard ETL-SQL @variables for all parameters. Declare them at the top of your script with DECLARE, use them inside visual SELECT queries, and bind them to filter controls via ACTIONS (ON_CHANGE = SET_PARAMETER(@varName, value)). See the How Parameter Binding Works section for the full mechanism.

When a parameter changes, the DashboardService re-evaluates all visuals whose inline SELECTs reference that parameter. Unaffected visuals are not re-queried.

CREATE PAGE Overview AS DASHBOARD (
  LAYOUT (
    STRUCTURE = 'A B / C C / D D',
    MAP (
      'A' = TotalRevenue,
      'B' = RegionFilter,
      'C' = RevenueByRegion,
      'D' = SalesTable
    )
  ),
  STYLE (THEME = dark)
);

Defines a custom ECharts color theme that can be applied to any visual or page with STYLE (THEME = themeName). Themes are saved as JSON files to {TemplatePath}/Themes/ and embedded in the report manifest so the web player can register them at render time.

CREATE THEME corporate AS (
  BACKGROUND   = '#1a1a2e',       -- chart / card background
  TEXT_COLOR   = '#eeeeee',       -- title, legend, axis labels
  ACCENT_COLOR = '#4ecca3',       -- primary series color
  COLORS       = '#4ecca3, #e94560, #f5a623, #0078d4',  -- full palette
  GRID_COLOR   = '#2a2a4e'        -- axis grid lines
);

Apply the theme just like any built-in ECharts theme:

CREATE VISUAL RevenueChart AS BAR (
  SOURCE   = (SELECT Month, Revenue FROM #data),
  MAPPINGS (X = Month, Y = Revenue),
  STYLE    (THEME = corporate)
);

DROP THEME corporate;
DROP THEME corporate IF EXISTS;

Removes the theme from memory and deletes the .json file from disk.

Defines a named, reusable style that can be applied to CREATE VISUAL, CREATE PAGE, CREATE CONTAINER, and CREATE BUTTON statements. Properties defined in the named style act as defaults; any inline STYLE (...) block on the target overrides them.

CREATE STYLE <name> (
  key = value,
  ...
);

Style properties are CSS-like key/value pairs (strings, numbers, or identifiers). Common keys:

-- Define shared styles once
CREATE STYLE DarkCard (
  BACKGROUND-COLOR = '#1e1e2e',
  COLOR = '#cdd6f4',
  BORDER-RADIUS = '8px',
  PADDING = '16px'
);

CREATE STYLE PanelBorder (
  border = '1px solid #444',
  border-radius = '4px'
);

-- Reference by name in CREATE VISUAL
CREATE VISUAL RevenueKpi AS CARD (
  SOURCE = #kpis,
  STYLE = DarkCard,
  MAPPINGS (VALUE = revenue, LABEL = label)
);

-- Inline overrides take precedence over the named style
CREATE VISUAL AlertKpi AS CARD (
  SOURCE = #kpis,
  STYLE = DarkCard,
  STYLE (color = '#f38ba8'),   -- override color only
  MAPPINGS (VALUE = alerts, LABEL = label)
);

-- Apply to pages and containers too
CREATE PAGE Main AS DASHBOARD (
  LAYOUT (
    STRUCTURE = 'A B',
    MAP ('A' = RevenueKpi, 'B' = AlertKpi)
  ),
  STYLE = PanelBorder,
);

Named styles are resolved at manifest build time and merged into the target's final style map. They are not emitted as a separate entity in the manifest.

Report manifests use one cascade everywhere. Later layers override earlier layers:

1. Built-in runtime and chart theme defaults.
2. Report-level defaults from SET REPORT, including SET REPORT THEME.
3. Page-level named and inline styles.
4. Container-level named and inline styles.
5. Visual/button named style.
6. Visual/button inline STYLE (...).
7. Runtime interaction state such as hover, selection, and pending parameters.

The manifest contains resolved styles objects for report, page, container, button, and visual objects. Browser hosts render those resolved objects instead of re-reading CREATE STYLE definitions.

CREATE [OR ALTER] CONTAINER <name> AS BOX|SCROLL|DRAWER|SIDEBAR|TABS|ACCORDION|MODAL|POPOVER (
  [TITLE = '<string>',]
  [SUBTITLE = '<string>',]
  [TOOLTIP = '<string>',]
  [VISIBLE = ON|OFF,]
  [ICON = '<name>',]
  [STYLE = <styleName> | STYLE (key = value, ...),]
  LAYOUT (
    STRUCTURE = '<grid-template-areas>',
    MAP ('<slot>' = VisualOrContainerName, ...),
    [GAP = '<css-size>',]
    [PINNABLE = ON|OFF]
  ),
  [OPTIONS (key = value, ...)]
);

VISIBLE = OFF hides the entire container (and all its children) until toggled via an action. For drawer containers, this controls whether the drawer starts opened or closed. ICON = '<name>' is also top-level and controls the drawer/popover trigger icon.

Containers use the same STRUCTURE and MAP logic as pages, but nested placement is always inside LAYOUT (...). Every container with children must have a STRUCTURE and a MAP.

Layout behavior belongs in LAYOUT (...): grid structure, slot maps, gaps, responsive layout keys, and drawer pinning. OPTIONS (...) is reserved for type-specific container behavior; common state such as VISIBLE, ICON, TITLE, SUBTITLE, TOOLTIP, and STYLE is top-level.

-- Single visual (single-slot STRUCTURE)
CREATE CONTAINER KpiRow AS BOX (
  LAYOUT (
    STRUCTURE = 'A B C',
    MAP (
      'A' = TotalRevenue,
      'B' = TotalUnits,
      'C' = AvgOrderValue
    )
  )
);

-- Multi-row layout
CREATE CONTAINER InfoPanel AS BOX (
  TITLE = 'Product Insights',
  LAYOUT (
    STRUCTURE = 'A B / C C',
    MAP (
      'A' = ProductImage,
      'B' = PriceCard,
      'C' = DescriptionText
    )
  )
);

Reference the container in a page's MAP just like a visual:

CREATE PAGE Main AS DASHBOARD (
  STRUCTURE = 'A A / B C',
  MAP (
    'A' = InfoPanel,
    'B' = RevenueChart,
    'C' = SalesTable
  )
);

Use DRAWER for filter panels that can float over the page and be toggled via a trigger icon pinned to the page edge.

CREATE CONTAINER FilterDrawer AS DRAWER (
  TITLE      = 'Filters',
  ICON       = 'filter',
  LAYOUT (
    STRUCTURE  = 'A / B / C',
    MAP (
      'A' = RegionFilter,
      'B' = YearSlider,
      'C' = CategoryFilter
    ),
    PINNABLE = ON
  )
);

CREATE PAGE Dashboard AS DASHBOARD (
  LAYOUT (
    STRUCTURE = 'A A / B C',
    MAP (
      'A' = FilterDrawer,
      'B' = RevenueChart,
      'C' = SalesTable
    )
  )
);

When PINNABLE = ON the drawer has two states:

• Overlay (default): floats on top of the layout; other visuals are not resized.
• Pinned: inserts as an inline column; the grid reflows around it.

Adds a navigation bar that controls which page is visible. The bar renders above the page content.

CREATE [OR ALTER] NAVIGATION <name> AS TAB|BUTTON|LINK (
  [ORIENTATION = HORIZONTAL|VERTICAL,]
  [DEFAULT = <PageName>],
  PAGES (Page1, Page2, ...)
);

CREATE NAVIGATION MainNav AS TAB (
  ORIENTATION = HORIZONTAL,
  DEFAULT = Overview,
  PAGES (Overview, Details, Trends)
);

If DEFAULT is omitted, the first page in the list is shown on load.

The ORIENTATION option determines the placement and behavior of the navigation bar:

• HORIZONTAL (Default): Renders a horizontal bar at the top of the report, above the page content. Best for reports with 3-5 pages.
• VERTICAL: Renders the navigation as a sidebar on the left. The STRUCTURE of the active page is resolved within the remaining horizontal space. Best for complex reports with many pages or sub-sections.

Adds an interactive button to a page. Buttons are placed in MAP slots just like visuals.

CREATE [OR ALTER] BUTTON <name> AS (
  [TITLE   = '<string>',]
  [TOOLTIP = '<string>',]
  [OPTIONS (key = value, ...),]
  [ACTIONS (trigger = action, ...),]
  [STYLE = <styleName> | STYLE (key = value, ...)]
);

Buttons are command emitters. Use ACTIONS (ON_CLICK = ...) for behavior.

-- Navigation button
CREATE BUTTON GoBack AS (
  TITLE   = '← Back',
  TOOLTIP = 'Return to the previous page',
  ACTIONS (ON_CLICK = BACK)
);

-- Refresh button
CREATE BUTTON RefreshData AS (
  TITLE   = 'Refresh',
  TOOLTIP = 'Reload all visuals from source data',
  ACTIONS (ON_CLICK = REFRESH_REPORT),
  STYLE (BACKGROUND-COLOR = '#2563eb', COLOR = '#ffffff', BORDER-RADIUS = '4px')
);

-- Refresh selected visuals
CREATE BUTTON RefreshMetrics AS (
  TITLE   = 'Refresh Metrics',
  ACTIONS (ON_CLICK = REFRESH_VISUALS(SalesTable, RevenueChart))
);

-- Export a specific visual's data to CSV
CREATE BUTTON DownloadCsv AS (
  TITLE   = 'Download CSV',
  OPTIONS (TARGET = SalesDetail),
  ACTIONS (ON_CLICK = EXPORT_CSV)
);

-- Export a specific visual's data to Excel
CREATE BUTTON DownloadExcel AS (
  TITLE   = 'Download Excel',
  OPTIONS (TARGET = SalesDetail),
  ACTIONS (ON_CLICK = EXPORT_EXCEL),
  STYLE (BACKGROUND-COLOR = '#217346', COLOR = '#ffffff')
);

-- Custom action button
CREATE BUTTON DrillBtn AS (
  TITLE   = 'View Detail',
  ACTIONS (ON_CLICK = DRILL_DOWN(Target = DetailPage, Key = id))
);

-- Navigate to a report page
CREATE BUTTON DetailsButton AS (
  TITLE   = 'Details',
  ACTIONS (ON_CLICK = NAVIGATE_PAGE(Details))
);

EXPORT_CSV and EXPORT_EXCEL require OPTIONS (TARGET = VisualName) where VisualName is the name of any visual currently rendered on the same page. The exported data reflects the rows in the manifest — if the visual is cross-filtered, the full dataset (not the filtered view) is exported.

Place buttons in a page layout exactly like any visual:

CREATE PAGE Dashboard AS DASHBOARD (
  STRUCTURE = 'A B / C C',
  MAP (
    'A' = GoBack,
    'B' = RefreshData,
    'C' = SalesChart
  )
);

ALTER modifies one or more properties of an existing report object without redefining it. Any clause omitted keeps its current value.

-- Change a visual's title and source
ALTER VISUAL RevenueByRegion (
  TITLE  = 'Revenue by Region (Updated)',
  SOURCE = #new_summary
);

-- Add a tooltip to an existing page
ALTER PAGE Overview (
  TOOLTIP = 'Live sales data, refreshed hourly'
);

-- Update container metadata
ALTER CONTAINER KpiRow (
  TITLE = 'KPI Summary'
);

-- Change a button's label
ALTER BUTTON GoBack (
  TITLE = '← Return'
);

-- Rename a style property
ALTER STYLE DarkCard (
  BACKGROUND-COLOR = '#2a2a3e'
);

Supported object types: VISUAL, PAGE, CONTAINER, BUTTON, STYLE, NAVIGATION, DATASET

CREATE OR ALTER is equivalent to ALTER if the object already exists, or CREATE if it does not. This is useful for idempotent scripts that are run repeatedly.

CREATE OR ALTER VISUAL TotalRevenue AS CARD (
  SOURCE   = (SELECT SUM(revenue) AS val FROM #summary),
  TITLE    = 'Total Revenue',
  MAPPINGS (VALUE = val)
);

CREATE OR ALTER STYLE DarkCard (
  BACKGROUND-COLOR = '#1e1e2e',
  COLOR            = '#cdd6f4',
  BORDER-RADIUS    = '8px'
);

Supported for all report object types: VISUAL, PAGE, CONTAINER, BUTTON, STYLE, NAVIGATION, DATASET.

Permanently removes a report object from the execution context.

DROP VISUAL [IF EXISTS] <name>;
DROP PAGE [IF EXISTS] <name>;
DROP CONTAINER [IF EXISTS] <name>;
DROP BUTTON [IF EXISTS] <name>;
DROP STYLE [IF EXISTS] <name>;
DROP NAVIGATION [IF EXISTS] <name>;
DROP DATASET [IF EXISTS] <name>;

IF EXISTS suppresses the error when the named object does not exist. Without it, dropping a non-existent object raises an ExecutionException.

-- Clean up before rebuilding
DROP VISUAL IF EXISTS TotalRevenue;
DROP PAGE   IF EXISTS Overview;

-- Error if MyNav does not exist
DROP NAVIGATION MyNav;

Evaluates the script, builds a ReportManifest, and writes output files:

etl-sql-report build report.rptsql
etl-sql-report build report.rptsql --output out/dashboard.md
etl-sql-report build report.rptsql --format json
etl-sql-report build report.rptsql --format pdf

Output files produced:

Script-level report export supports an optional PDF mode selector:

EXPORT REPORT 'reports/sales.rptsql'
FORMAT PDF
TO 'out/sales.pdf'
WITH (PDF_MODE = STATIC);

EXPORT REPORT 'reports/sales.rptsql'
FORMAT PDF
TO 'out/sales.pdf'
WITH (
  PDF_MODE     = BROWSER,
  BROWSER_PATH = 'C:\Program Files\Google\Chrome\Application\chrome.exe'
);

HOST and BROWSER_PATH are accepted only with FORMAT PDF. HOSTED and BROWSER are opt-in high-fidelity modes; STATIC remains the portable default.

Flags:

Re-evaluates the script and updates the snapshot without writing a new report document:

etl-sql-report refresh report.rptsql

The snapshot is stored alongside the script as <script>.snapshot.json. The ReportPlayer considers the snapshot stale if the script file has been modified since the snapshot was built, or if the TTL (default 24 h) has elapsed.

Starts the web dashboard at http://localhost:5200:

# Single report
etl-sql-report serve report.rptsql

# Multi-report catalog (see reports.json below)
etl-sql-report serve --manifest reports.json

# Override the default port
etl-sql-report serve report.rptsql --port 8080

# Port 0 = OS-assigned (actual URL echoed as REPORT_URL=...)
etl-sql-report serve report.rptsql --port 0

Internally this launches ETL-SQL.ReportPlayer (the Kestrel ASP.NET server) and opens the browser after 2.5 s. Keep the process running; the dashboard is served for as long as the process is alive.

Multiple reports can be hosted together using a reports.json manifest file:

{
  "reports": [
    { "name": "sales",     "path": "reports/sales.rptsql",     "description": "Regional sales dashboard" },
    { "name": "inventory", "path": "reports/inventory.rptsql", "description": "Inventory levels by SKU" }
  ]
}

Start the server:

etl-sql-report serve --manifest reports.json

The catalog page at http://localhost:5200 lists all reports. Each report is accessible at http://localhost:5200/reports/<name>. API routes are prefixed per-report: /reports/<name>/api/manifest, /reports/<name>/api/refresh, etc.

The ReportPlayer is a lightweight ASP.NET Minimal API server that hosts the report as an interactive dashboard.

When a parameter changes, DashboardService checks which visuals depend on that parameter (by scanning the inline SELECT for variable references) and re-queries only those visuals. Unaffected visuals keep their current data without re-evaluation.

If the manifest was built before the script file was last written, or if more than 24 hours have passed, a yellow banner appears:

⚠ Snapshot may be stale — run etl-sql-report refresh to update.

You can also hit /api/refresh to force a live rebuild without restarting the server.

There are two separate frontends depending on context:

• VS Code WebviewPanel: loads the bundled React application (ui/dist/index.html). The extension injects the manifest as window.__INITIAL_STATE__.messages and sets window.VIEW_TYPE = 'report'. The React app reads the initial manifest from that variable and receives subsequent refreshes via webview.postMessage() without re-mounting.
• Web (ReportPlayer): a single vanilla-JS file (wwwroot/report-runtime.js). Uses the pre-embedded manifest in single-report mode or fetches from /api/manifest in multi-report mode.

Both frontends use Apache ECharts v5 for chart rendering.

With a .rptsql file open, run ETL-SQL: Preview Report from the command palette or click the $(graph) icon in the editor title bar. A webview panel opens beside the editor and auto-refreshes every time you save the file.

Configuration:

The language server checks .rptsql files automatically:

The snapshot and API both return this structure:

{
  "source":      "C:/reports/sales.rptsql",
  "builtAt":     "2026-04-12T18:00:00Z",
  "title":       "Sales Dashboard",
  "description": "Regional revenue analysis",

  "visuals": [
    {
      "name":       "RevenueByRegion",
      "visualType": "Bar",
      "chartConfig": "{ /* ECharts option object JSON */ }",
      "columns":  ["region", "revenue"],
      "rows":     [["East", "12000"], ...],
      "options":  {
        "mapping:x":       "region",
        "mapping:y":       "revenue",
        "axis:x:label":    "Region",
        "axis:y:label":    "Revenue ($)"
      },
      "styles":   { "THEME": "dark" },
      "actions":  [
        { "type": "SET_PARAMETER", "trigger": "ON_CHANGE",
          "parameterName": "@region", "valueExpression": "region" }
      ]
    }
  ],

  "pages": [
    {
      "name":      "Overview",
      "structure": "A B / C C",
      "slotMap":   { "A": "TotalRevenue", "B": "RegionFilter", "C": "RevenueByRegion" },
      "styles":    { "THEME": "dark" }
    }
  ],

  "containers": [
    {
      "name":          "KpiRow",
      "containerType": "BOX",
      "structure":     "A B",
      "slotMap":       { "A": "TotalRevenue", "B": "TotalUnits" },
      "styles":        { "HEIGHT": "200" }
    }
  ],

  "navigations": [
    {
      "name":        "MainNav",
      "navType":     "TAB",
      "orientation": "HORIZONTAL",
      "defaultPage": "Overview",
      "pages":       ["Overview", "Details"]
    }
  ],

  "datasets": [
    {
      "tempTableName":   "&sales_snap",
      "refreshInterval": "1h",
      "ttl":             "24h",
      "lastRefresh":     "2026-04-12T18:00:00Z",
      "rowCount":        4800
    }
  ]
}

Source CSV columns: region, product, units, revenue, month

SET REPORT TITLE = 'Sales Dashboard';
SET REPORT DESCRIPTION = 'Regional and product-level revenue by month.';

DROP CONNECTION IF EXISTS c;
CREATE CONNECTION c AS FLATFILE('TestData/test_sales.csv');

-- Shared base dataset (refreshed every hour)
CREATE DATASET &summary
  REFRESH EVERY '1h'
  ENCRYPT = MACHINE
  AS (SELECT month, region, product,
             SUM(units)   AS units,
             SUM(revenue) AS revenue
      FROM c
      GROUP BY month, region, product);

-- Region filter slicer
CREATE VISUAL RegionFilter AS SLICER (
  SOURCE   = (SELECT DISTINCT region FROM &summary ORDER BY region),
  MAPPINGS (VALUE = region),
  ACTIONS  (ON_CHANGE = SET_PARAMETER(@region, region)),
  DEFAULT  = 'All'
);

-- KPI card: total revenue
CREATE VISUAL TotalRevenue AS CARD (
  SOURCE = (SELECT SUM(revenue) AS val, 'Total Revenue' AS lbl
            FROM &summary
            WHERE @region = 'All' OR region = @region),
  TITLE    = 'Total Revenue',
  MAPPINGS (VALUE = val, LABEL = lbl),
  OPTIONS  (FORMAT = 'C0')
);

-- Bar chart: revenue by region
CREATE VISUAL RevenueByRegion AS BAR (
  SOURCE = (SELECT region, SUM(revenue) AS revenue
            FROM &summary
            WHERE @region = 'All' OR region = @region
            GROUP BY region),
  TITLE    = 'Revenue by Region',
  MAPPINGS (X = region, Y = revenue),
  OPTIONS (
    X_AXIS (LABEL = 'Region'),
    Y_AXIS (LABEL = 'Revenue ($)', MIN = 0)
  )
);

-- Multi-series bar: revenue by region and month
CREATE VISUAL RevenueByRegionMonth AS BAR (
  SOURCE = (SELECT month, region, SUM(revenue) AS revenue
            FROM &summary
            GROUP BY month, region),
  TITLE    = 'Revenue by Region (Monthly)',
  MAPPINGS (X = month, Y = revenue, SERIES = region),
  OPTIONS  (STACKED = ON, X_AXIS (LABEL = 'Month'), Y_AXIS (LABEL = 'Revenue ($)', MIN = 0))
);

-- Donut chart: revenue share by product
CREATE VISUAL RevenueByProduct AS DONUT (
  SOURCE = (SELECT product, SUM(revenue) AS revenue
            FROM &summary
            GROUP BY product),
  TITLE    = 'Revenue by Product',
  MAPPINGS (LABEL = product, VALUE = revenue)
);

-- Line chart: units by month
CREATE VISUAL UnitsByMonth AS LINE (
  SOURCE = (SELECT month, SUM(units) AS units
            FROM &summary
            GROUP BY month),
  TITLE    = 'Units Sold by Month',
  MAPPINGS (X = month, Y = units),
  OPTIONS  (SMOOTH = ON, X_AXIS (LABEL = 'Month'), Y_AXIS (LABEL = 'Units Sold', MIN = 0))
);

-- Detail table: all rows with summary
CREATE VISUAL SalesTable AS TABLE (
  SOURCE = &summary,
  SUMMARY (
    GRAND_TOTAL = ON,
    SUM(revenue) AS 'Total Revenue'
  )
);

-- Brand logo
CREATE VISUAL BrandLogo AS IMAGE (
  OPTIONS (
    SRC = 'https://etl-sql.io/logo.png',
    FIT = 'contain'
  )
);

-- KPI container using modern grid layout
CREATE CONTAINER KpiRow AS BOX (
  LAYOUT (
    STRUCTURE = 'A B',
    MAP (
      'A' = BrandLogo,
      'B' = TotalRevenue
    )
  )
);

-- Dashboard pages
CREATE PAGE Overview AS DASHBOARD (
  STRUCTURE = 'A B / C C / D D',
  MAP (
    'A' = KpiRow,
    'B' = RegionFilter,
    'C' = RevenueByRegion,
    'D' = SalesTable
  )
);

CREATE PAGE Trends AS DASHBOARD (
  STRUCTURE = 'A A / B C',
  MAP (
    'A' = RevenueByRegionMonth,
    'B' = UnitsByMonth,
    'C' = RevenueByProduct
  )
);

-- Navigation (defined after pages so the LayerOrder linter is satisfied)
CREATE NAVIGATION MainNav AS TAB (
  ORIENTATION = HORIZONTAL,
  DEFAULT = Overview,
  PAGES (Overview, Trends)
);

For reports with heavy queries or many parameters, you should avoid refreshing the report on every character change. Use the Run-to-Data pattern:

1. Define the page as CREATE PAGE <name> AS PAGINATED (...).
2. Put prompt controls and the Run button before the result visuals in the page layout.
3. Use ACTIONS (ON_CLICK = APPLY_PARAMETERS) on the Run button.
4. Leave result visuals at FETCH = AUTO or set FETCH = ON_RUN explicitly. The engine shows a placeholder until Run is clicked.

Never explicitly CAST a RELDATE parameter to a DATE or DATETIME in your SQL.

• RELDATE variables hold relative expressions like 'D-7' or 'M-1'.
• If you write CAST(@start AS DATE), the engine attempts to treat the string 'D-7' as a literal date, which fails.
• Correct Pattern: Use the variable directly: WHERE event_time >= @start. The engine handles the resolution of the relative expression into an absolute timestamp automatically during comparison.

• Use DATEPICKER for fixed date parameters that do not need relative logic.
• Use RELDATEPICKER for RELDATE INPUT variables. It provides a specialized UI with quick-action buttons (Today, Yesterday, Last 7 Days, etc.) and ensures the string passed back to the engine is a valid relative date expression.

Yes. The STYLE (...) block on a CREATE BUTTON supports standard CSS properties. The report runtime specifically handles:

• BACKGROUND-COLOR (or BACKGROUND)
• COLOR
• PADDING
• BORDER-RADIUS
• FONT-WEIGHT (e.g., bold)
• FONT-SIZE
• BORDER
• BOX-SHADOW (e.g., 0 2px 4px rgba(0,0,0,0.2))

CREATE BUTTON RunBtn AS (
    TITLE = 'Run Report',
    ACTIONS (ON_CLICK = APPLY_PARAMETERS),
    STYLE (
        BACKGROUND = '#2563eb', 
        COLOR = '#ffffff', 
        FONT-WEIGHT = 'bold',
        BOX-SHADOW = '0 2px 4px rgba(0,0,0,0.2)'
    )
);