SQLly directives

Small SQL comments that remember how a result looks: its columns, widths, formats, colors, pivot, and chart.

A directive is an ordinary SQL comment that SQLly understands. To the database it is just a comment, while SQLly uses it to shape or format the result. Because directives live in the query text, they travel with the file and apply again every time you run it.

Every directive shares the same namespace - the word sqlly after the comment marker:

-- sqlly view: pivot
-- sqlly grid: order=Region, Year, Sales; freeze=1; sort=Sales desc
-- sqlly pivot: row:Region; column:Year; value:Sales:sum
-- sqlly chart: kind:bar; label:Region; values:Sales
-- sqlly format: column=Sales; number=2; width=40dp
-- sqlly conditional: column=Sales; bar=#5B9BD5
-- sqlly column: Running Total = !Sales + N(!"Running Total"[1])

You rarely have to type them. Arrange the result the way you like it, then click the lock in front of the result's name: SQLly writes the whole arrangement into the query as one block of directives. See Lock the grid.

The directive families

param: and set are the two that change what the database receives. SQLly substitutes their values into the SQL before sending it, outside strings and comments. A parameter's value goes in as an escaped literal of its declared type (text, int, decimal, bool, date, or datetime). A set value goes in verbatim. Computed-column formulas read both as @name.

Where a directive must go

• On a line of its own. A directive after SQL code on the same line is ignored. name is the one exception and works as a trailing comment.
• view: and the canvas size go on the first line. They are only read on the first non-empty line of the query. In a lock block at the top, the block's first directive line counts as that first line.
• Spacing is forgiving for most families: --sqlly view: pivot and -- SQLLY view: pivot both work.
• format: is strict. It must be spelled exactly -- sqlly format:, with a space after --.
• Inside a block comment, a directive is switched off, which is a handy way to disable one for a while. The one exception is the /* sqlly lock block: its lines work exactly as if each one were written on its own line.

-- sqlly view: pivot
-- sqlly pivot: row:Region; column:Year; value:Sales:sum
-- sqlly format: column=Sales; number=2
SELECT Region, Year, Sales FROM sales;

Help in the app

• Hover any -- sqlly comment for a short description, with a link to the full list of options.
• Help › Formatting Directives… (also in the command palette) opens a reference window with every family, its parameters, and copy-ready examples.
• Let SQLly write it. Click the lock in front of a result's name to write the whole result as directives, or right-click a result column header and choose Format › Set Formatting Override… to have one format: directive written for you.

Related: Pivot, Chart, Formatting, and Conditional formatting cover the same features set up interactively.

In this section

Grid

Write the column order, hidden and frozen columns, sort, grouping, and filters into the query.

Formatting in progress

Give each column the presentation and width it deserves, automatically whenever the query runs.

Conditional

Color cells by their values for one query: highlight rules, color scales, and data bars.

Computed columns

Add columns written as Excel formulas: running totals, margins, changes from the row before, and dates.

Pivot

Turn a familiar result set into a cross-tab with a short, portable comment.

Chart

Tell the Chart tab what story to draw before the result even arrives.

Lock the grid

One click writes the result exactly as it looks now into the query, so it comes back the same on every run.