Basics
SQL snippets are reusable fragments of SQL that can be embedded in queries across many reports.
A SQL snippet does not have to be an entire query - it can be just a text fragment or even just a single value. When the snippet is used in a query, the text of the snippet reference is replaced with the snippet content.
You can create a new SQL snippet by giving it a name and content. Specifying a data source in the SQL snippet editor allows to use code completion for tables and columns used in the snippet.
To use a SQL snippet in a query, embed the snippet name in square brackets, e.g. a snippet named `revenue` defined thus
Name: revenue Text: SUM(price_paid) FROM sales WHERE order_type<>'test_order'
when used in a query like
SELECT [revenue]
GROUP BY {saletime:month}
will expand 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. Many similar queries can be shortened and become easier to maintain.
Usage
Snippets are used in the report editor when writing queries:
1. Code completion offers all the snippets that can be used: Simply type [ and the list of snippets with their descriptions appears:
2. Create a snippet on the spot by selecting a piece of text and selecting the "Create Snippet" action:
3. Show the snippet text by hovering over its use - the tooltip that appears will show the snippet text and description:
4. Inline a snippet by replacing the snippet name with its content to use the snippet as a template for further customization:
Advanced usage
Snippets can be even more powerful with some extra capabilities:
- You can use snippets within other snippets, to get gradually higher-level building blocks. The snippet-within-snippet is limited to 10 levels of nesting, and would not allow to have "cycles" in the usage (where 'snippet 1' would use 'snippet 2' that already uses 'snippet 1' inside)
- Add Parameters to a snippet to get more flexibility when the snippet is used. For more information see Snippets with parameters