Workbook dropdown parameters

By using dropdown parameters, you can collect one or more input values from a known set. For example, you can use a dropdown parameter to select one of your app's requests. Dropdown parameters also provide a user-friendly way to collect arbitrary inputs from users. Dropdown parameters are especially useful in enabling filtering in your interactive reports.

The easiest way to specify a dropdown parameter is by providing a static list in the parameter setting. A more interesting way is to get the list dynamically via a KQL query. You can also specify whether it's single or multi-select by using parameter settings. If it's multi-select, you can specify how the result set should be formatted, for example, as delimiter or quotation.

Create a static dropdown parameter

1. Start with an empty workbook in edit mode.

2. Select Add parameters > Add Parameter.

3. In the new parameter pane that opens, enter:

   1. Parameter name: Environment
   2. Parameter type: Drop down
   3. Required: checked
   4. Allow multiple selections: unchecked
   5. Get data from: JSON

4. In the JSON Input text block, insert this JSON snippet:

   [
       { "value":"dev", "label":"Development" },
       { "value":"ppe", "label":"Pre-production" },
       { "value":"prod", "label":"Production", "selected":true }
   ]
   

5. Select Update.

6. Select Save to create the parameter.

7. The Environment parameter is a dropdown list with the three values.

   

Create a static dropdown list with groups of items

If your query result/JSON contains a group field, the dropdown list displays groups of values. Follow the preceding sample, but use the following JSON instead:

[
    { "value":"dev", "label":"Development", "group":"Development" },
    { "value":"dev-cloud", "label":"Development (Cloud)", "group":"Development" },
    { "value":"ppe", "label":"Pre-production", "group":"Test" },
    { "value":"ppe-test", "label":"Pre-production (Test)", "group":"Test" },
    { "value":"prod1", "label":"Prod 1", "selected":true, "group":"Production" },
    { "value":"prod2", "label":"Prod 2", "group":"Production" }
]

Create a dynamic dropdown parameter

1. Start with an empty workbook in edit mode.

2. Select Add parameters > Add Parameter.

3. In the new parameter pane that opens, enter:

   1. Parameter name: RequestName
   2. Parameter type: Drop down
   3. Required: checked
   4. Allow multiple selections: unchecked
   5. Get data from: Query

4. In the JSON Input text block, insert this JSON snippet:

       requests
       | summarize by name
       | order by name asc
   

5. Select Run Query.

6. Select Save to create the parameter.

7. The RequestName parameter is a dropdown list with the names of all requests in the app.

   

Reference a dropdown parameter

You can reference dropdown parameters.

In KQL

1. Select Add query to add a query control, and then select an Application Insights resource.

2. In the KQL editor, enter this snippet:

       requests
       | where name == '{RequestName}'
       | summarize Requests = count() by bin(timestamp, 1h)
   
   

3. The snippet expands on query evaluation time to:

       requests
       | where name == 'GET Home/Index'
       | summarize Requests = count() by bin(timestamp, 1h)
   

4. Run the query to see the results. Optionally, render it as a chart.

   

Parameter value, label, selection, and group

The query used in the preceding dynamic dropdown parameter returns a list of values that are rendered faithfully in the dropdown list. But what if you wanted a different display name or one of the names to be selected? Dropdown parameters use value, label, selection, and group columns for this functionality.

The following sample shows how to get a list of Application Insights dependencies whose display names are styled with an emoji, has the first one selected, and is grouped by operation names:

dependencies
| summarize by operation_Name, name
| where name !contains ('.')
| order by name asc
| serialize Rank = row_number()
| project value = name, label = strcat('🌐 ', name), selected = iff(Rank == 1, true, false), group = operation_Name

Dropdown parameter options

Parameter	Description	Example
{DependencyName}	The selected value	GET fabrikamaccount
{DependencyName:label}	The selected label	🌐 GET fabrikamaccount
{DependencyName:value}	The selected value	GET fabrikamaccount

Multiple selection

The examples so far explicitly set the parameter to select only one value in the dropdown list. Dropdown parameters also support multiple selection. To enable this option, select the Allow multiple selections checkbox.

You can specify the format of the result set via the Delimiter and Quote with settings. The default returns the values as a collection in the form of a, b, c. You can also limit the number of selections.

The KQL referencing the parameter needs to change to work with the format of the result. The most common way to enable it is via the in operator.

dependencies
| where name in ({DependencyName})
| summarize Requests = count() by bin(timestamp, 1h), name

This example shows the multi-select dropdown parameter at work:

Dropdown special selections

Dropdown parameters also allow you to specify special values that will also appear in the dropdown:

• Any one
• Any three
• ...
• Any 100
• Any custom limit
• All

When these special items are selected, the parameter value is automatically set to the specific number of items, or all values.

Special casing All

When you select the All option, an extra field appears, which allows you to specify that a special value will be used for the parameter if the All option is selected. This special value is useful for cases where "All" could be a large number of items and could generate a very large query.

In this specific case, the string [] is used instead of a value. This string can be used to generate an empty array in the logs query, like:

let selection = dynamic([{Selection}]);
SomeQuery 
| where array_length(selection) == 0 or SomeField in (selection)

If all items are selected, the value of Selection is [], producing an empty array for the selection variable in the query. If no values are selected, the value of Selection will be an empty string, also resulting in an empty array. If any values are selected, they are formatted inside the dynamic part of the query, causing the array to have those values. You can then test for array_length of 0 to have the filter not apply or use the in operator to filter on the values in the array.

Other common examples use '*' as the special marker value when a parameter is required, and then test with:

| where "*" in ({Selection}) or SomeField in ({Selection})

Next steps

Learn about the types of visualizations you can use to create rich visual reports with Azure Workbooks.