Work with dashboard parameters

This article explains how to use parameters on AI/BI dashboards. If you want to learn about field filters instead, see Filter on fields.

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 static parameters set in visualization widgets, the values are set by the authors or editors. For parameters used in 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 field 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

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.

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.

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 Parameters section appears in the configuration panel.

    A visualization configuration panel showing the option to choose a paramter.

  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.

  5. Review your dashboard.

    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.

    Two histograms, configured to use static widget-level parameters, as described.

Compare data using a filter condition

You can add a filter condition that allows you to compare aggregations on part of the data to the aggregations applied to the whole data set.

The following example extends the previous query to include a filter condition where the specified parameter value is All, which does not appear in the data. The first part of the filter condition works the same as in the previous example, filtering for results where the day_type is either Weekday or Weekend. The second part of the filter condition checks if the parameter itself is set to a certain value, in this case All, that does not appear in the data. When you set the default value for that parameter in the dataset editor, you’re effectively bypassing the filter when neither Weekday nor Weekend are passed in as values.

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
  OR :day_type_param = 'All'

You can use this dataset to configure three visualization widgets, with the day_type_param set to All, Weekday and Weekend respectively. Then, dashboard viewers can compare each of the datasets filtered by day type to the whole dataset.

The following GIF shows how you can quickly build three charts from the dataset created with this query.

  1. The first chart is configured to show the Trip Distances by Day Type dataset with a parameter value set to Weekday.

  2. Clone the chart to maintain all the applied configurations.

  3. To show data for trips taken on the weekend, adjust the static parameter to Weekend.

  4. Clone the new chart to maintain all of the previously applied configurations.

  5. Set the parameter in the new chart to All to show total trip counts by distance for weekend and weekday trips.

A gif as described in the preceding text.

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.

Working with filter widgets and static parameters

Parameters are fixed values substituted into a query at runtime. If two or more references to the same parameter are set with different values when your dashboard updates, visualization widgets display an error indicating which parameter has conflicting values. This can occur when multiple filter widgets use the same parameter or when a static parameter conflicts with a filter widget that allows viewer selection.

To set fallback values for different parameters in the same query, you could use a pattern like the one in the following example.

CASE WHEN :static_param != 'All' THEN :static_param ELSE :dash_param END

In this example, :static_param refers to a static parameter, which should be set to ‘All’ in both the visualization widget and dataset editor. The parameter :dash_param is a dynamic value that is controlled by a filter widget on the dashboard canvas. This statement checks for a value provided as a static parameter. If none is set, it falls back to a viewer-selected dashboard parameter value.

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.

Dashboard parameters vs. Mustache query parameters

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

Syntax examples

Common uses for parameters include inserting dates, numbers, text, database objects, and values from JSON strings. For examples that demonstrate how to use parameters in these cases, see Named parameter syntax examples

Important

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