What are dashboard parameters?

Preview

This feature is in Public Preview.

Dashboard parameters are one way to make dashboards interactive, enabling viewers to use single-value selectors and date pickers to input specific values into dataset queries at runtime. For example, parameters can filter data based on criteria like dates and product categories before it’s aggregated in a SQL query, allowing for more efficient querying and precise analysis.

Parameters are added to datasets and connected to one or more widgets on the canvas of a dashboard by a dashboard author or editor. Dashboard viewers interact with the dashboard data by selecting values in filter widgets at runtime. This reruns the associated queries and presents visualizations built on the filtered data.

Parameters directly modify the query, which can be powerful. Dataset filters can also offer dashboard interactivity, more features, and better performance with large datasets than parameters. See Filters.

Add a parameter to a query

You must have at least Can Edit permissions on the draft dashboard to add a parameter to a dashboard dataset. You can add parameters directly to the dataset queries in the Data tab.

Gif shows an example of the following steps.

To add a parameter to a query:

  1. Place your cursor where you want to place the parameter in your query.

  2. Click Add parameter to insert a new parameter.

    This creates a new parameter with the default name parameter. To change the default name, replace it in the query editor. You can also add parameters by typing this syntax in the query editor.

Edit a query parameter

To edit a parameter:

  1. Click Gear icon next to the parameter name. A Parameter details dialog appears and includes the following configuration options:

    • Keyword: The keyword that represents the parameter in the query. This can only be changed by directly updating the text in the query.

    • Display name: The name in the filter editor. By default, the title is the same as the keyword.

    • Type: Supported types include String, Date, Date and Time, Numeric.

      • The default type is String.

      • The Numeric datatype allows you to specify between Decimal and Integer. The default numeric type is Decimal.

  2. Click another part of the UI to close the dialog.

Set a default parameter value

You can set a default value for your parameter by typing it into the text field under the parameter name. Run the query to preview the query results with the parameter value applied. Running the query also saves the default value. When you set this parameter using a filter widget on the canvas, the default value is used.

Query-based parameters

Query-based parameters allow authors to define a dynamic or static list of values that viewers can choose from when setting parameters as they explore data in a dashboard. They are defined by combining a field filter and a parameter filter in a single filter widget.

To create a query-based parameter, the dashboard author performs the following steps:

  1. Create a dataset whose result set is limited to a list of possible parameter values.

  2. Create a dataset query that uses a parameter.

  3. Configure a filter widget on the canvas that filters on a field and uses a parameter.

    • The Fields configurations should be set to use the field with the desired list of eligible parameter values.

    • The Parameters configuration should be set to select a parameter value.

Note

If a dataset used in query-based parameters is also used in other visualizations on a dashboard, a viewer’s filter selection modifies all connected queries. To avoid this, authors should create a dedicated dataset for query-based parameters that is not used in any other visualizations on the dashboard.

See Use query-based parameters for a step-by-step tutorial that demonstrates how to add a query-based parameter and visualization.

Create a dynamic parameter list

To create a dynamic dataset that populates the drop-down that viewers use to select parameter values, write a SQL query that returns a single field and includes all the values in that field. Any new value in that field is automatically added as a parameter selection when the dataset is updated. An example SQL query is as follows:

 SELECT
    DISTINCT c_mktsegment
  FROM
    samples.tpch.customer

Create a static parameter list

You can create a static dataset that includes only values that you hardcode into your dataset. An example query is as follows:

SELECT
  *
FROM
  (
    VALUES
      ('MACHINERY'),
      ('BUILDING'),
      ('FURNITURE'),
      ('HOUSEHOLD'),
      ('AUTOMOBILE')
  ) AS data(available_choices)

Filter types

Single Value and Date Picker filters support setting parameters. When setting query-based parameters with a Date Picker filter, dates that appear in the underlying query’s results are shown in black. Dates that do not appear in the query results are gray. Users can choose gray dates even though they are not included in the underlying query.

Remove a query parameter

To remove a parameter, delete it from your query.

Show parameters on the dashboard

Adding a filter to your dashboard canvas allows viewers to select and modify parameter values, so they can interactively explore and analyze the data. If you do not expose the parameter on the dashboard, viewers see only query results that use the default parameter value that you set in the query.

To add a parameter to your dashboard:

  1. Click Filter Icon Add a filter (field/parameter).

  2. Click add field icon next to Parameters in the configuration panel.

  3. Click the parameter name you want the viewer to use with this widget.

Include parameters in the URL

Parameter settings are stored in the URL, allowing users to bookmark it to maintain their dashboard’s state, including pre-set filters and parameters, or to share it with others for consistent application of the same filters and parameters.

Parameter syntax examples

The following examples demonstrate some common use cases for parameters.

Insert a date

The following example includes a Date parameter that limits query results to records after a specific date.

SELECT
  o_orderdate AS Date,
  o_orderpriority AS Priority,
  sum(o_totalprice) AS `Total Price`
FROM
  samples.tpch.orders
WHERE
  o_orderdate > :date_param
GROUP BY
  1,
  2

Insert a number

The following example includes a Numeric parameter that limits results to records where the o_total_price field is greater than the provided parameter value.

SELECT
  o_orderdate AS Date,
  o_orderpriority AS Priority,
  o_totalprice AS Price
FROM
  samples.tpch.orders
WHERE
  o_totalprice > :num_param

Insert a field name

In the following example, the field_param is used with the IDENTIFIER function to provide a threshold value for the query at runtime. The parameter value should be a column name from the table used in the query.

SELECT
  *
FROM
  samples.tpch.orders
WHERE
  IDENTIFIER(:field_param) < 10000

Insert database objects

The following example creates three parameters: catalog, schema, and table. Dashboard viewers can use filter widgets on the canvas to select parameter values.

SELECT
  *
FROM
  IDENTIFIER(:catalog || '.' || :schema || '.' || :table)

See IDENTIFIER clause.

Important

Enabling dashboard viewers to access data through parameter selections, like table or catalog names, could lead to accidental exposure of sensitive information. If you’re publishing a dashboard with these options, Databricks recommends not embedding credentials in the published dashboard.

Concatenate multiple parameters

You can include parameters in other SQL functions. This example allows the viewer to select an employee title and a number ID. The query uses the format_string function to concatenate the two strings and filter on the rows that match. See format_string function.

SELECT
  o_orderkey,
  o_clerk
FROM
  samples.tpch.orders
WHERE
  o_clerk LIKE format_string('%s%s', :title, :emp_number)

Dashboard parameters vs. Databricks SQL query parameters

Dashboard parameters use the same syntax as named parameter markers. See Named parameter markers. Dashboards do not support Databricks SQL style parameters.