SQL Snippets

Overview

SQL snippets are reusable fragments of SQL that can be embedded in queries across many reports. All SQL snippets defined for your organization can be managed from the organization settings.

To create a SQL snippet, select Add SQL Snippet. The following dialog will appear.

Every SQL snippet must have a name, which is later used to reference the snippet in a SQL query (e.g. of a report). The Datasource selection only influences code completion in the SQL editor, but has no relevance for the actual use of the snippet. The text of a snippet does not have to be a syntactically valid SQL query. Usually, the snippet text is just a SQL fragment, which can be just a single literal value.

tip

A SQL snippet can also be created directly from within the report SQL editor. See ad hoc snippets.

To use a SQL snippet in a query, the snippet name is enclosed in square brackets, as in [my_snippet_name]. For example, if a snippet named revenue with the snippet text

SUM(price_paid) FROM sales WHERE order_type <> 'test_order'

is used in the SQL query

SELECT [revenue]
GROUP BY {saletime:month}

it expands upon query execution into

SELECT SUM(price_paid) FROM sales WHERE order_type<>'test_order'
GROUP BY {saletime:month}

Thus, snippets provide a central definition of frequently used SQL fragments and editing a snippet changes all report queries using that snippet. As a result, many similar queries can be shortened and become easier to maintain.

tip

Snippets can reference other snippets. However, a chain of snippet references can be no more than 10 levels "deep" and must not contain cycles.

Auto-Completion

In the report SQL editor, snippets are included in the suggested auto-completions.

Ad hoc snippets

Snippets can also be created in an ad hoc manner directly from within the report SQL editor. To do so, select the portion of the SQL that you want to extract into a snippet and choose Create Snippet from the editor's expanded Run-menu.

Tooltips

Hovering over a snippet in the report SQL editor opens a tooltip with the snippet text and description.

Inlining

A snippet reference can be inlined in the report SQL editor, e.g. to serve as a template for further customization. To do so, select the snippet name (with or without the surrounding square brackets) and choose Inline Snippet from the editor's expanded Run-menu.

Parameters

A SQL snippet can optionally have parameters. To use snippet parameters, specify the parameter names in the Params input field of the snippet editing dialog (multiple parameter names are separated by a comma). Then use The parameter names in the Text input for the snippet text by enclosing them in square brackets (analogous to referencing another snippet).

To use a parameterised snippet in a report, specify the parameters in brackets, analogous to a function call in a programming language:

SELECT [in_eur(100, 'USD')]

Any parameters that are defined by a snippet but are not passed when the snippet is used will resolve to a literal NULL value in the resolved snippet text.