Params & Liquid Templating

This guide will walk you through creating and using custom parameters within your reports. The "Params" module allows you to define input fields using YAML. These parameters can then be dynamically inserted into your queries using the Liquid templating language, making your reports flexible and interactive.

Overview

The "Params" interface is designed to be intuitive. You'll typically see two main sections:

Parameter Form (Top Panel): This area displays the interactive form generated from your YAML definitions. You or other end-users will use this form to input values for the parameters.
YAML Editor (Bottom Pane): This is where you, as the report designer, will define the structure and type of each parameter using YAML. Changes here are reflected in the Parameter Form.

Overall UI Overview

Defining Parameters with YAML

Parameters are defined in the YAML editor. Each parameter has a name (which will be its key) and a set of properties that define its behavior and appearance in the form.

Basic Structure

Here's a general structure for defining a parameter:

parameter_name:
    type: 'inputType'
    description: 'A helpful description for the user.'
    # Other properties specific to the type...

parameter_name: This is how you will refer to the parameter in your Liquid templates (e.g., {{ parameter_name }}).
type: Specifies the kind of input field. Common types include text, number, date, select, and multiselect.
description: (Optional but highly recommended) Provides a tooltip or help text next to the parameter in the form. Users can often see this by clicking an "i" (info) icon.

Common Parameter Types

1. Text Input

For general text, names, titles, or any string data.

report_title:
    type: 'text'
    description: 'Enter the main title for your report.'

2. Number Input

For numeric values, like quantities, amounts, or IDs.

minimum_amount:
    type: 'number'
    description: 'Filter results greater than this amount.'
item_count:
    type: 'number'
    description: 'Specify the number of items.'

3. Date Input

For selecting dates. Dates are typically expected in YYYY-MM-DD format.

start_date:
    type: 'date'
    description: 'Select the start date for the data range.'
end_date:
    type: 'date'
    description: 'Select the end date for the data range.'

Select and Multiselect Parameters

These types allow users to choose from a predefined list of options.

1. Static Options

You can define options directly in the YAML.

Simple List: Each item in the list becomes both the display label and the submitted value.

status_filter_simple:
    type: 'select'
    description: 'Filter by a simple status.'
    options:
        - "Pending"
        - "Completed"
        - "Failed"

Label-Value Pairs: Provide an array where the first element is the display label and the second is the submitted value. This is useful when the display text needs to be more user-friendly than the actual value used in a query.

country_code_selector:
    type: 'select'
    description: 'Select a country.'
    options:
        - [ "United States", "US" ]
        - [ "Canada", "CA" ]
        - [ "United Kingdom", "GB" ]
        - [ "Australia", "AU" ]

user_roles_multiselect:
    type: 'multiselect'
    description: 'Select one or more user roles.'
    options:
        - [ "Administrator", "admin" ]
        - [ "Editor", "editor" ]
        - [ "Viewer", "viewer" ]

For multiselect, the parameter value will be an array of the selected values (e.g., ["admin", "viewer"]).

2. Dynamic Options (from Queries)

A powerful feature is populating select or multiselect options from the results of another query already defined in your "Queries" module. This is extremely useful for ensuring your dropdowns always have the latest data (e.g., a list of customers, products, or categories).

To do this, use the query, labels, and values keys under options:

customer_id:
    type: 'select'
    description: 'Select a customer from the list.'
    options:
        query: 'all_customers_query'  # Name of an existing query that returns customer data
        labels: 'customer_name'       # Column from 'all_customers_query' to display as labels
        values: 'id'                  # Column from 'all_customers_query' to use as the option values

product_skus:
    type: 'multiselect'
    description: 'Select one or more products.'
    options:
        query: 'active_products_query' # Name of an existing query that returns product data
        labels: 'product_title'        # Column from 'active_products_query' for display
        values: 'sku'                  # Column from 'active_products_query' for the option values

Important:

The queries specified (e.g., all_customers_query, active_products_query) must be defined report queries and return data.
The labels and values fields must correspond to actual column names in the output of those queries.

Using Parameters in Queries with Liquid

Once parameters are defined and users provide values via the form, you can use these values in your SQL queries (or other script types supported by the "Queries" module) using Liquid templating.

Liquid is a templating language that allows you to embed dynamic content. Parameter values are inserted using double curly braces: {{ parameter_name }}.

Basic Variable Interpolation

Let's say you have defined the following parameters in YAML:

start_date:
    type: 'date'
end_date:
    type: 'date'
status:
    type: 'select'
    options:
        - "Active"
        - "Inactive"
minimum_value:
    type: 'number'

And a user provides the following values:

start_date: 2024-01-01
end_date: 2024-03-31
status: Active
minimum_value: 100

You can use these in a SQL query like this:

SELECT *
FROM transactions
WHERE transaction_date BETWEEN '{{start_date}}' AND '{{end_date}}'
  AND account_status = '{{status}}'
  AND amount >= {{minimum_value}};

When the query runs, Liquid will process it, and the actual SQL sent to the database will be:

SELECT *
FROM transactions
WHERE transaction_date BETWEEN '2024-01-01' AND '2024-03-31'
  AND account_status = 'Active'
  AND amount >= 100;

Note on SQL Quoting:

Liquid inserts the parameter value directly.
For string or date values in SQL, ensure you wrap the Liquid tag in single quotes: '{{parameter_name}}'.
For numeric values, quotes are usually not needed: {{numeric_parameter}}.

Advanced Liquid Templating

Liquid offers more than just variable substitution. You can use tags for logic (like if conditions and for loops) and filters to modify variables.

Conditional Logic: `if`, `elsif`, `else`

You can conditionally include parts of your query using {% if %} tags. This is very useful for optional filters.

Syntax: {% if condition %} ... SQL part to include if condition is true ... {% elsif another_condition %} ... SQL part if the first condition is false and this one is true ... {% else %} ... SQL part if all preceding conditions are false ... {% endif %}

Example: Optional customer filter.

Let's assume you have a customer_id parameter that might be empty if the user wants to see data for all customers.

# Parameter definition
optional_customer_id:
    type: 'number'
    description: 'Enter Customer ID (optional). Leave blank for all customers.'
# Assume also a 'department' parameter (text)
department:
    type: 'text'
    description: 'Filter by department (e.g., Sales, HR).'

Query:

SELECT *
FROM employee_data
WHERE 1 = 1 -- A common trick to ensure AND clauses can always be appended
    {% if department and department != "" %}
  AND department_name = '{{department}}'
{% endif %}
{% if optional_customer_id %}
  AND customer_id = {{optional_customer_id}}
{% endif %}

If department is "Sales" and optional_customer_id is 123, it renders:

SELECT *
FROM employee_data
WHERE 1=1
  AND department_name = 'Sales'
  AND customer_id = 123

If department is empty and optional_customer_id is also empty, it renders:
```
SELECT *
FROM employee_data
WHERE 1=1
```
(The department != "" check handles cases where an empty string might be passed for a text field.)

Looping: `for`

Loops are essential when dealing with parameters that can have multiple values, such as those from a multiselect input. A common use case is building SQL IN clauses.

Syntax: {% for item in collection %} ... content to repeat for each item ... {{ item }} ... {{ forloop.index }} ... {{ forloop.last }} {% endfor %}

Example: Filtering by multiple product SKUs from a multiselect parameter.

# Parameter definition
selected_skus:
    type: 'multiselect'
    description: 'Select one or more product SKUs.'
    options:
        query: 'all_products'
        labels: 'product_name'
        values: 'sku'

If a user selects SKUs: ["ABC-1001", "XYZ-2002", "DEF-3003"], the selected_skus parameter will hold this array.

Query:

SELECT *
FROM products
WHERE 1 = 1 {% if selected_skus and selected_skus.size > 0 %}
  AND sku IN (
    {% for current_sku in selected_skus %}
      '{{ current_sku }}'{% unless forloop.last %},{% endunless %}
    {% endfor %}
  )
{% endif %}

selected_skus.size > 0: Checks if the array is not empty. (.size is a common Liquid way to get array length or string length).
{% for current_sku in selected_skus %}: Iterates through each SKU in the array.
'{{ current_sku }}': Puts each SKU in single quotes (assuming SKUs are strings).
{% unless forloop.last %},{% endunless %}: Adds a comma after each SKU, unless it's the last item in the loop. This creates a correctly formatted SQL list.

Rendered SQL if selected_skus is ["ABC-1001", "XYZ-2002"]:

SELECT *
FROM products
WHERE 1 = 1
  AND sku IN (
              'ABC-1001',
              'XYZ-2002'
    )

Liquid Filters

Filters modify the output of variables or Liquid objects. They are used with a pipe character |. {{ variable | filter_name: argument }}

Here are some common and useful filters:

default: Provides a fallback value if the variable is empty or undefined.

-- Processing for region: {{ region_param | default: 'North America' }}
WHERE start_date >= '{{ start_date_param | default: "2024-01-01" }}'

date: Formats a date. The input can be a timestamp, a date string recognized by Liquid, or the special word "now" (or "today").
```
-- Report generated on: {{ "now" | date: "%Y-%m-%d %H:%M:%S" }}
-- Transaction month: {{ transaction_date_param | date: "%B %Y" }}
```
(Note: Date parameters from the form might already be in SQL-compatible YYYY-MM-DD format, so re-formatting might not always be needed for SQL WHERE clauses but can be useful for comments or display.)
String Manipulation:
- upcase, downcase, capitalize:
```
WHERE product_category = '{{ category_param | upcase }}'
```
- append, prepend: Add text to the beginning or end.
```
{% assign log_prefix = user_id_param | prepend: "User_" %}
-- Log Prefix: {{ log_prefix }}
```
- replace, replace_first: Replace occurrences of a substring.
```
-- {{ 'Sales Report for Q1' | replace: 'Q1', 'Q2' }}
-- Renders: Sales Report for Q2
```
- strip, lstrip, rstrip: Remove whitespace.
- truncate: Shortens a string to a specified length. {{ long_description | truncate: 25, "..." }} (shows first 25 chars, then "...")
- escape: Escapes a string (e.g., for HTML or XML, less common directly in SQL values but good for comments or generated code). -- User comment: {{ user_comment_param | escape }}

Array/Collection Filters:

size: Returns the number of items in an array or characters in a string. (Already used in the for loop example).

join: Joins array elements into a string with a separator.

{% comment %} If selected_tags_param is ("urgent", "review"( {% endcomment %}
-- Tags: {{ selected_tags_param | join: ", " }}
-- Renders: Tags: urgent, review

first, last: Get the first or last element of an array.

map: Iterates over an array and extracts a property from each item (if items are objects) or applies a filter to each item.

{% comment %}
  If items_param is ({id:1, name:"A"}, {id:2, name:"B"})
  This would create an array like (1, 2)
{% endcomment %}
{% assign item_ids = items_param | map: "id" %}

{% comment %}
  If string_numbers_param is ("10", "20", "30")
  This can attempt to convert them (behavior depends on Liquid variant)
  A common trick for string to number is 'plus: 0' if 'to_number' isn't available.
{% endcomment %}
{% assign numeric_values = string_numbers_param | map: plus: 0 %}

{% comment %}
  If categories_param is ("electronics", "books")
  This creates ("ELECTRONICS", "BOOKS")
{% endcomment %}
{% assign uppercased_categories = categories_param | map: 'upcase' %}

uniq: Removes duplicate items from an array.
sort: Sorts an array. Can take a property name for objects. {% endraw %}

Mathematical Filters:

plus, minus, times, divided_by, modulo: Perform arithmetic.

{% assign page_size = 20 %}
{% assign current_page = page_number_param | default: 1 %}
{% assign offset_value = current_page | minus: 1 | times: page_size %}
LIMIT {{ page_size }} OFFSET {{ offset_value }}

Assigning Variables: `assign`

You can create new Liquid variables within your template.

{% assign default_records_limit = 50 %}
{% assign records_to_fetch = user_limit_param | default: default_records_limit %}
SELECT * FROM data_table LIMIT {{ records_to_fetch }};

Capturing Output: `capture`

You can capture a block of rendered Liquid output into a variable.

{% capture filename %}report_{{ report_type_param | default: 'generic' }}_{{ "now" | date: "%Y%m%d" }}.csv{% endcapture %}
-- Suggested filename: {{ filename }}

Case/When Logic (Switch Statement)

For more complex conditional logic based on the value of a single variable.

{% assign status_id_value = 0 %}
{% case status_param %}
  {% when "Pending" %}
    {% assign status_id_value = 1 %}
  {% when "Active" %}
    {% assign status_id_value = 2 %}
  {% when "Closed" %}
    {% assign status_id_value = 3 %}
  {% else %}
    {% assign status_id_value = -1 %}
{% endcase %}

SELECT * FROM items WHERE status_identifier = {{ status_id_value }};

Saving Your Parameters

After defining or modifying your parameters in the YAML editor, or when default values in the form need to be persisted, you'll need to save them. Look for a "Save" button or icon, often a cloud or disk symbol. This action will store your YAML configuration and any current default values you've set in the form.

Tips and Best Practices

Descriptive Names: Use clear, meaningful names for your parameters. start_date is better than d1.
Always Add Descriptions: The description field is invaluable for end-users who may not know the purpose of each parameter.
Test Thoroughly: After setting up parameters and using them in queries, test with various inputs:
- Valid values.
- Empty values (if optional).
- Edge cases.
SQL Quoting: Remember that Liquid inserts values as-is. String and date values in SQL queries usually need to be enclosed in single quotes ('{{my_string_param}}'). Numeric values typically don't.
multiselect and IN Clauses: When using multiselect for SQL IN clauses, use the for loop with forloop.last to correctly format the comma-separated list, and ensure string values within the list are individually quoted if necessary.
Start Simple: If you're new to Liquid, start with basic variable interpolations and gradually introduce more complex logic like if conditions and for loops as needed.
Consult Liquid Documentation: Liquid has many more features. If you have a specific transformation in mind, the official Liquid documentation (or that specific to your platform's Liquid implementation) can be a great resource.

By mastering parameters and Liquid templating, you can create highly dynamic and user-friendly reports that adapt to various analytical needs.

Overview

Get Started

Reports

Integrations

Params & Liquid Templating

Overview

Defining Parameters with YAML

Basic Structure

Common Parameter Types

1. Text Input

2. Number Input

3. Date Input

Select and Multiselect Parameters

1. Static Options

2. Dynamic Options (from Queries)

Using Parameters in Queries with Liquid

Basic Variable Interpolation

Advanced Liquid Templating

Conditional Logic: `if`, `elsif`, `else`

Looping: `for`

Liquid Filters

Assigning Variables: `assign`

Capturing Output: `capture`

Case/When Logic (Switch Statement)

Saving Your Parameters

Tips and Best Practices

Overview

Get Started

Reports

Integrations

Params & Liquid Templating

Overview

Defining Parameters with YAML

Basic Structure

Common Parameter Types

1. Text Input

2. Number Input

3. Date Input

Select and Multiselect Parameters

1. Static Options

2. Dynamic Options (from Queries)

Using Parameters in Queries with Liquid

Basic Variable Interpolation

Advanced Liquid Templating

Conditional Logic: if, elsif, else

Looping: for

Liquid Filters

Assigning Variables: assign

Capturing Output: capture

Case/When Logic (Switch Statement)

Saving Your Parameters

Tips and Best Practices

Conditional Logic: `if`, `elsif`, `else`

Looping: `for`

Assigning Variables: `assign`

Capturing Output: `capture`