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:
Place your cursor where you want to place the parameter in your query.
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:
- 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.
- 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:
- Create a dataset whose result set is limited to a list of possible parameter values.
- Create a dataset query that uses a parameter.
- 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:
Add a visualization widget to the draft dashboard canvas.
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.
Click the plus sign to the right of the Parameters heading and choose a parameter from the drop-down.
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:
- Click
Add a filter (field/parameter).
- Click
next to Parameters in the configuration panel.
- 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, Azure 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)
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.
Parameter use case | Databricks SQL syntax | AI/BI dashboard syntax |
---|---|---|
Load only data before a specified date | WHERE date_field < '{{date_param}}' You must include quotes around the date parameter and curly brackets. |
WHERE date_field < :date_param |
Load only data less than a specified a numeric value | WHERE price < {{max_price}} |
WHERE price < :max_price |
Compare two strings | WHERE region = {{region_param}} |
WHERE region = :region_param |
Specify the table used in a query | SELECT * FROM {{table_name}} |
SELECT * FROM IDENTIFIER(:table) When a user enters this parameter, they should use the full three-level namespace to identify the table. |
Independently specify the catalog, schema, and table used in a query | SELECT * FROM {{catalog}}.{{schema}}.{{table}} |
SELECT * FROM IDENTIFIER(:catalog \|\| '.' \|\| :schema \|\| '.' \|\| :table) |
Use parameters as a template in a longer, formatted string | “({{area_code}}) {{phone_number}}” Parameter values are automatically concatenated as a string. |
format_string(“(%d)%d, :area_code, :phone_number) See Concatenate multiple parameters for a complete example. |
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for