What are dashboard parameters?

AI/BI dashboard parameters let you substitute different values into dataset queries at runtime. This allows you to filter data by criteria such as dates and product categories before data is aggregated in a SQL query, leading to more efficient querying and precise analysis. Parameters can be used with filter widgets to make dashboards interactive or with visualization widgets to make datasets easier to reuse.

Dashboard authors or editors add parameters to datasets and connect them to one or more widgets on the dashboard canvas. For parameters linked to visualization widgets, the values are static, set by the authors or editors. For parameters associated with filter widgets, dashboard viewers can interact with the data by selecting values in filter widgets at runtime. This interaction reruns the associated queries and displays visualizations based 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.

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 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

For your query to run, choose 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, unless a new default value is specified in the filter widget. See Filters.

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.

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.

Static widget parameters

Static widget parameters are configured directly in a visualization widget, allowing authors to individually parameterize visualization widgets that share the same dataset. This allows for the same dataset to present different views on the canvas.

This example in this section is based on a dataset that queries the samples.nyctaxi.trips table. The provided query returns the distance of each trip and categorizes the pickup day as either Weekday or Weekend. The query parameter filters for results based on whether the pickup occurred on a weekday or weekend.

The query text is provided in the following code block, but the instructions in this section are limited to setting up the associated visualizations configured with static widget parameters. For instructions on how to setup a dataset with parameters, see Add a parameter to a query.

  WITH DayType AS (
    SELECT
      CASE
        WHEN DAYOFWEEK(tpep_pickup_datetime) IN (1, 7) THEN 'Weekend'
        ELSE 'Weekday'
      END AS day_type,
      trip_distance
    FROM samples.nyctaxi.trips
  )
  SELECT day_type, trip_distance
  FROM DayType
  WHERE day_type = :day_type_param

To add a static widget parameter to a visualization:

1. Add a visualization widget to the draft dashboard canvas.

2. With the new widget selected, choose the parameterized dataset from the Dataset drop-down in the configuration panel. For queries that include parameters, a new Parameters section appears in the configuration panel.

   

3. Click the plus sign to the right of the Parameters heading and choose a parameter from the drop-down.

4. By default, the parameter value mirrors what is set in the query on the Data tab. You can keep it or choose a new value to substitute into the dataset. Navigate away from the text field to show the visualization with the new parameter applied.

The following image shows two visualization widgets, each is configured as a histogram with a static widget parameter. The chart on the left shows the distribution of trip distances for trips starting on weekdays while the chart on the right shows the same data for weekends. Both visualizations are based on the same dataset.

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 Add a filter (field/parameter).
2. Click 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.

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)

You can use parameters to extract an attribute from a JSON string. The following example uses the from_json function to convert the JSON string to a struct value. Substituting the string a as the value for the parameter (param) returns the attribute 1.

SELECT
  from_json('{"a": 1}', 'map<string, int>') [:param]

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.

Syntax changes

The following table shows common use cases for parameters, the original Databricks SQL syntax, and the equivalent syntax using named parameter marker syntax.