Parameter

Create dynamic notebooks and reports.

By using the Parameter defined in Notebooks, you can make Notebooks and Reports dynamic and interactive. This allows you to control embedded SQL through parameters without directly accessing SQL, and obtain tailored outputs as needed.

Key Features

• Make notebooks and reports dynamic:

  • Make Notebooks and Reports dynamic and interactive.

• Various types of parameters:

  • Choose from various data types and associated input widgets.

• Two types of scopes:

  • Choose parameters used across the Notebook or on a page-by-page basis.

• Parameter overwrite:

  • Fine-grained control by overwriting parameters when chaining SQL.

How to Use

Once you create a parameter, you can reference it within an SQL block in the Notebook using the syntax /param/{param_name}.

For example, if you create a parameter named param1 as a string parameter and input the value val1, the SQL:

SELECT /param/param1

will be converted to:

SELECT 'val1'

Types of Parameters

1. Text input (STRING):

Simple string input field.

• Format:

  • Enclosed in single quotes(' ) in SQL.

  • Ex: Converted to 'val1'

• If empty:

  • ''

2. Text multi input (STRING[])

A field for entering multiple strings.

• Format:

  • Enclosed in single quotes(' ) in SQL.

  • Ex: Converted to 'val1', 'val2'

• If empty:

  • ''

3. Text select (STRING):

A dropdown to select a single string value.

• Source options:

  • Fixed list or table values or query results.

• Format:

  • Enclosed in single quotes(' ) in SQL.

• If empty:

  • ''

4. Text multi select (STRING[])

A dropdown for selecting multiple string values.

• Source options:

  • Fixed list or table values or query results.

• Format:

  • Enclosed in single quotes(' ) in SQL.

  • Ex: Converted to 'val1', 'val2'

• If empty:

  • ''

5. Number input (NUMBER):

A simple numeric input field.

• Options:

  • min, max, steps (intervals)

• If empty:

  • 0

6. Number select (NUMBER):

A dropdown for selecting a single number.

• Source options:

  • Fixed list or table values or query results.

• If empty:

  • 0

7. Date input (DATE):

A simple date input field. Allows selection from a calendar or by entering a date.

• Options:

  • Unit:

    • Date, Week, Month, Year

  • Start day of week (First day displayed in calendar):

    • Sun, Mon, Tue, Wed, Thu, Fri, Sat

  • Relative valid range:

    • Days before today: Dates earlier than this limit become unselectable. For example, entering '90' will make dates earlier than 90 days unselectable.

    • Days after today: Dates beyond this limit become unselectable. For example, entering 0 will make dates from the next day onwards unselectable, and entering -1 will make dates from today onwards unselectable.

  • Absolute valid range:

    • Lower date: Dates before this specified date become unselectable.

    • Upper date: Dates after this specified date become unselectable.

• Format:

  • Expanded in yyyy-MM-dd format with quotes(' ) in SQL.

  • Ex: Converted to '2024-01-01'

8. Date range ([DATE, DATE]) :

Specifies a start and end date as a pair.

• Options:

  • Identical to those for Date Input (DATE)

• Syntax for SQL reference:

  • Start Date: /param/param_name.StartDate

  • End Date: /param/param_name.EndDate

9. Checkbox (BOOLEAN):

Simple Boolean checkbox.

• Format:

  • Expanded as TRUE/FALSE in SQL.

Parameter Escaping: Security and Dynamic Identifier Support

The parameter system handles SQL injection protection and value escaping based on the parameter's context. This mechanism not only provides security but also enables dynamic SQL identifier generation through quoted parameters.

1. String Parameters in Regular Context

-- If parameter value is: user's "data"
WHERE name = /param/str_param         -> WHERE name = 'user\'s \"data\"'

-- If parameter value is: O'Reilly's "Book"
SELECT name = /param/company          -> SELECT name = 'O\'Reilly\'s \"Book\"'

• Parameters are automatically wrapped in single quotes.

• Both single quotes and double quotes within values are escaped with backslash.

2. String Parameters in Quoted Context

-- Inside double quotes (escapes both " and ')
-- If parameter value is: user"s 'data'
SELECT "/param/str_param" as alias    -> SELECT "user\"s \'data\'" as alias

-- Inside single quotes (escapes both " and ')
-- If parameter value is: user"s 'data'
SELECT '/param/str_param' as alias    -> SELECT 'user\"s \'data\'' as alias

-- Inside backticks (escapes only `)
-- If parameter value is: user"s 'data`
SELECT `/param/str_param` as alias    -> SELECT `user"s 'data\`` as alias

• Inside double quotes or single quotes:

  • Both double quotes and single quotes are escaped with backslash

• Inside backticks:

  • Only backticks are escaped with backslash

  • Other quotes remain unchanged

3. Date Parameters Special Processing

-- Regular date parameter
SELECT /param/date_param as normal         -> SELECT '2024-10-02' as normal

-- Date in quotes follows the same escaping rules
SELECT '/param/date_param' as single_quote -> SELECT '2024-10-02' as single_quote
SELECT "/param/date_param" as double_quote -> SELECT "2024-10-02" as double_quote

-- Special case: Table name generation in BigQuery
-- When using BigQuery connection
SELECT * FROM `table_/param/date_param`    -> SELECT * FROM `table_20241002`

• Regular context: Dates are formatted as 'YYYY-MM-DD'

• When used in table names with BigQuery: Dates are formatted as 'YYYYMMDD' (without hyphens)

• Quote escaping rules apply as with string parameters

Dynamic Identifier Generation Support

This context-aware processing enables both secure value handling and dynamic identifier generation (table names, column names):

-- Secure value handling
WHERE status = /param/status             -> WHERE status = 'active'

-- Dynamic table name
SELECT * FROM `table_/param/table_suffix` -> SELECT * FROM `table_sales_2024`

-- Dynamic field selection
SELECT "column_/param/field_type"         -> SELECT "column_revenue"

By placing parameters within quotes, you can safely generate SQL identifiers like table or column names.

Source Options for Select Parameters

Select parameters support three types of source options to generate selectable values:

1. Fixed List

Used when you want to select from fixed values.

• Settings:

  • CSV Text:

    • Define options in CSV text format, with each item on a new line. To specify a display name, include it on the same line with a comma (,).

For example:

value1,Display Name 1
value2,Display Name 2

2. Table Value

Used when you want to generate selectable values from a specific field in a table.

• Settings:

  • Source Table: Select the table from which values are drawn.

  • Value Field: Specify the field to be used as the option value.

  • Label Field: Specify the field to be used as the display label.

3. Query Result

Used when you want to generate options based on the results of a specified SQL query.

• Settings:

  • Connection:

    • Choose the database connection to use for the query.

  • SQL:

    • Input the SQL query that returns the option values. If the query returns multiple columns, the first column will be used as the option value, and the second column (if available) as the display label.

SQL queries for options are executed frequently, so avoid using slow or resource-intensive queries for optimal performance.

Execution Timing of Parameter SQL Queries

When using SQL queries for parameter options, it's important to understand when these queries are executed:

1. When a notebook page is loaded:

   • SQL queries for parameters used on the current page are executed during page initialization

   • Parameters not used on the current page are not initialized and their SQL queries are not executed

2. When a parameter dropdown is opened:

   • SQL query is executed when the dropdown menu is first opened

3. When typing in the search field:

   • After the initial query execution, subsequent filtering is performed on the client-side using the already fetched data

   • SQL is not re-executed for every keystroke

Caching Behavior

SQL queries for parameters use the same caching mechanism as regular notebook SQL blocks:

• Results are cached on the backend for up to 24 hours

• Identical queries (including parameter values) will use cached results

• The frontend also maintains a memory cache during the current session

This means even though parameter dropdowns might be opened frequently, the actual database query is only executed once within the cache period, significantly improving performance.

For more details on SQL query caching, see Query Execution and Caching.

Scopes of Parameters

Parameters can be created from two types of scopes:

• Notebook common

  • Parameters shared across all pages in the notebook

  • Values are retained on each page

• Page specific

  • Parameters specific to each page

Notebook common parameters can be used, for example, when you want to control components on a Grid Page, such as charts created from SQL on a Doc Page, with the same parameter.

Page specific parameters can be used, for example, when you do not want the parameter to affect anything outside of the specific page.

Parameter Overwrite

When chaining SQL, you may want to cut off the parameter dependencies. For example:

• Setting unique parameters for each page when chaining SQL across pages.

• Using different parameters for different SQLs derived from the same base SQL.

Parameters can be overwritten on a popup displayed by hovering over the referenced SQL in SQL Blocks.

Syncing Parameter Values and Execution Results Among Users

Parameter values and execution results in Notebooks are synchronized among users. However, using Notebook Version and Reports ensure that changes to parameter values and execution results are not synchronized between users, reflecting changes only to the user who changes the values.

For more details, please refer to the Notebook Version.