Use query-based parameters

The article guides you through the steps to create an interactive dashboard that uses query-based parameters. It assumes a basic familiarity with building dashboards on Azure Databricks. See Get started for foundational instruction on creating dashboards.

Requirements

• You are logged into a Azure Databricks workspace.
• You have the SQL entitlement in that workspace.
• You have at least CAN USE access to one or more SQL warehouses.

Create a dashboard dataset

This tutorial uses generated data from the samples catalog on Azure Databricks.

1. Click New in the sidebar and select Dashboard from the menu.

2. Click the Data tab.

3. Click Create from SQL and paste the following query into the editor. Then click Run to return the results.

   SELECT
     *
   FROM
     samples.tpch.customer
   

4. Your newly defined dataset is autosaved with the name Untitled Dataset. Double-click the title then rename it Marketing segment.

Add a parameter

You can add a parameter to this dataset to filter the returned values. The parameter in this example is :segment. See What are dashboard parameters? to learn more about parameter syntax.

1. Paste the following WHERE clause at the bottom of your query. A text field with the parameter name segment appears below your query.

   WHERE
     c_mktsegment = :segment
   

2. Type BUILDING into the text field below your query to set the default value for the parameter.

3. Rerun the query to inspect the results.

Configure a visualization widget

Add a visualization for your dataset on the canvas by completing the following steps:

1. Click the Canvas tab.
2. Click Add a visualization to add a visualization widget and use your mouse to place it in the canvas.

Setup the X-axis

1. If necessary, select Bar from the Visualization dropdown menu.
2. Click to choose the data presented along the X-axis. You can use the search bar to search for a field by name. Select c_nationkey.
3. Click the field name you selected to view additional configuration options.
   • As the Scale Type, select Categorical.
   • For the Transform selection, choose None.

Setup the Y-axis

1. Click next to the Y-axis, then select c_acctbal.
2. Click the field name you selected to view additional configuration options.
   • As the Scale Type, select Quantitative.
   • For the Transform selection, choose SUM.

The visualization is automatically updated as you configure it. The data shown includes only records where the segment is BUILDING.

Add a filter

Set up a filter so that dashboard viewers can control which marketing segment to focus on.

1. Click Add a filter (field/parameter) to add a filter widget. Place it on the canvas.
2. From the Filter drop-down menu in the configuration panel, select Single value.
3. Select the Title checkbox to show a title field on your filter widget.
4. Click the placeholder title on the widget and type Segment to retitle your filter.
5. Click next to Parameters in the configuration panel.
6. Choose segment from the Marketing segment dataset.

Your configured filter widget shows the default parameter value for the dataset.

Define a selection of values

The filter you created is functional, but it requires the viewer to know the available range of choices before they can type a selection. It also requires that users match the case and spelling when entering the desired parameter value.

To create a drop-down list so that the viewer can select a parameter from a list of available options, create a new dataset to define the list of possible values.

1. Click the Data tab.

2. Click Create from SQL to create a new dataset.

3. Copy and paste the following into the editor:

   SELECT
     DISTINCT c_mktsegment
   FROM
     samples.tpch.customer
   

4. Run your query and inspect the results. The five marketing segments from the table appear in the results.

5. Double-click the automatically generated title, then rename this dataset Segment choice.

Update the filter

Update your existing filter to use the dataset you just created to populate a drop-down list of values users can select from.

1. Click Canvas. Then, click the filter widget you created in a previous step.
2. Click next to Fields.
3. Click Segment choice, then click the field name c_mktsegment.

Your filter widget updates as you change the configuration. Click the field in the filter widget to see the available choices in the drop-down menu.

Note

This tutorial contains a simplified use case meant to demonstrate how to use query-based parameters. An alternate approach to creating this dashboard is to apply a filter to the c_mktsegment field.

Next steps

Keep learning about how to work with dashboards with the following articles:

• Learn more about applying filters. See Filters.
• Learn more about dashboard parameters. See What are dashboard parameters?.
• Publish and share your dashboard. See Publish a dashboard.