DBX and Parameters Overview

Unless specific to a one-time project, most queries can be reused by changing a WHERE clause (or filter block in NoSQL) to suit the present need. Yet it can be a hassle to Edit Source every time you make a minor change. Query Parameters let you insert values at run-time without editing your base query. The syntax is straightforward: DBX recognises any string between double curly braces {{ }} as a Query Parameter.

For example:

SELECT count(0)
FROM events
WHERE action = '{{ keyword }}'

In the above case, {{ keyword }} is the Query Parameter. To change the value of the parameter, DBX places an input box above the results pane. The contents of this input box are passed to the database instead of the double curly braces whenever you execute the query.

Add A Parameter From The UI

You can insert a parameter into your query and immediately activate its settings pane by using the Add Parameter button or associated keyboard shortcut. The parameter will be inserted wherever the text caret appears in your query. If you find that you've inserted the parameter in the wrong part of the query, you can select the entire parameter (including the curly braces!) and cut/paste it wherever necessary.

You can discover the key shortcut on your operating system by hovering your cursor above the Add Parameter button.

You can open a parameter's settings pane by clicking the cog icon on the left:

• Title: by default, the parameter title will be the same as the keyword in the query text. If you want to give it a friendlier name, you can change it here.
• Type: each parameter starts as a Text type. Supported types are Text, Number, Date, Date and Time, Date and Time (with Seconds), and Dropdown List.
  
  

With many databases, you can use a string value to represent a date/time which is equivalent to a Text parameter in DBX. DBX's Date parameters are preferable because they have a special calendar interface to make choosing a date easy. If you know in advance, however, that your date parameters will always come from a short list, you can instead use the Dropdown List or Query Based Dropdown List type.

If you are using Query Parameters of type Date, you can set the parameter's default value to the current date and time when the query is run. While most databases include functions for querying with the current date/time (e.g. GETDATE() in TSQL or CURRENT_DATE in MySQL), this feature in DBX lets you run the same query either up-to-the-minute or further in the past.

Date Range Parameters

For queries that must select data between two dates, DBX provides three levels of Date / Time Range parameters. When chosen from the parameters settings screen, DBX places two complete parameters into your query: one for the start date and one for the end date. You will typically need to separate them in your query (e.g. into different WHERE clauses). However, the parameter selection interface below the query window displays a unified widget in order to easily chose a date range without unnecessary clicking.

Date Range parameters behave exactly like Date parameters but are meant to save you time.

Dropdown Lists

If you want to restrict the scope of possible parameter values when running a query, you can use DBX's Dropdown List parameter type. When selected from the parameter settings panel, a text box appears where you can enter your allowed values, each one separated by a new line. Dropdown lists are Text parameters under the hood, so if you want to use dates/datetimes in your dropdown, you should enter them in the format your data source requires.

Query Based Dropdown List

Dropdown lists can also be tied to the results of an existing query. Just click Query Based Dropdown List under Type in the settings panel. Search for your target query in the Query to load dropdown values from bar. Performance will degrade if your target query returns a large number of records.

If your target query returns more than one column, DBX uses the first one. If your target query returns name and value columns, DBX populates the parameter selection widget with the namecolumn but executes the query with the associated value.

For example, suppose this query:

SELECT user_uuid as 'value', username as 'name'
FROM users

returned this data:

DBX's dropdown list widget would look like this:

But when DBX executes the query, the value passed to the database would be 1001, 1002 or 1003.

FAQ

Can I reuse the same parameter multiple times in a single query?

Sure! Just use the same identifier in the curly brackets. In this example:

SELECT {{org_id}},  count(0)
FROM queries
WHERE org_id = {{org_id}}

We use the {{org_id}} parameter twice.

Can I use multiple parameters in a single query?

Of course, just use a unique name for each one. In this example:

SELECT count(0)
FROM queries
WHERE org_id = {{org_id}} AND created_at > '{{start_date}}'

We use two parameters: {{org_id}} and {{start_date}}.

Value Source Options

New dashboard parameter

Dashboard parameters allow you to set a parameter value in one place on your dashboard and map it to multiple visualisations. Use this option to create a new dashboard-level parameter.

Existing dashboard parameter

If you have already set up a dashboard-level parameter, use this option to map it to a specific query parameter. You will need to specify which pre-existing dashboard parameter will be mapped.

Widget parameter

This option will display a value selector inside your dashboard widget. This is useful for one-off parameters that are not shared between widgets.

Static value

Selecting this option will let you choose a static value for this widget, regardless of the values used on other widgets. Statically mapped parameter values do not display a value selector anywhere on the dashboard which is more compact. This lets you take advantage of the flexibility of Query Parameters without cluttering the user interface on a dashboard when certain parameters are not expected to change frequently.