Text parameter criteria

When a query depends on many parameters, the query will be stalled until each of its parameters has been resolved. Sometimes a parameter could have a simple query that concatenates a string or performs a conditional evaluation. These queries still make network calls to services that perform these basic operations, and that process increases the time it takes for a parameter to resolve a value. The result is long load times for complex workbooks.

When you use criteria parameters, you can define a set of criteria based on previously specified parameters that will be evaluated to provide a dynamic value. The main benefit of using criteria parameters is that criteria parameters can resolve values of previously specified parameters and perform simple conditional operations without making any network calls. The following example is a criteria-parameters use case.

Example

Consider the following conditional query:

let metric = dynamic({Counter});
print tostring((metric.object == 'Network Adapter' and (metric.counter == 'Bytes Received/sec' or metric.counter == 'Bytes Sent/sec')) or (metric.object == 'Network' and (metric.counter == 'Total Bytes Received' or metric.counter == 'Total Bytes Transmitted')))

If you're focused on the metric.counter object, the value of the parameter isNetworkCounter should be true if the parameter Counter has Bytes Received/sec, Bytes Sent/sec, Total Bytes Received, or Total Bytes Transmitted.

This can be translated to a criteria text parameter:

In the preceding screenshot, the conditions will be evaluated from top to bottom and the value of the parameter isNetworkCounter will take the value of whichever condition evaluates to true first. All conditions except for the default condition (the "else" condition) can be reordered to get the desired outcome.

Set up criteria

1. Start with a workbook with at least one existing parameter in edit mode.

   1. Select Add parameters > Add Parameter.
   2. In the new parameter pane that opens, enter:
      • Parameter name: rand
      • Parameter type: Text
      • Required: checked
      • Get data from: Query
      • Enter print rand(0-1) in the query editor. This parameter will output a value between 0-1.
   3. Select Save to create the parameter.

   Note

   The first parameter in the workbook won't show the Criteria tab.

   

2. In the table with the rand parameter, select Add Parameter.

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

   • Parameter name: randCriteria
   • Parameter type: Text
   • Required: checked
   • Get data from: Criteria

4. A grid appears. Select Edit next to the blank text box to open the Criteria Settings form. For a description of each field, see Criteria Settings form.

   

5. Enter the following data to populate the first criteria, and then select OK:

   • First operand: rand
   • Operator: >
   • Value from: Static Value
   • Second operand: 0.25
   • Value from: Static Value
   • Result is: is over 0.25

   

6. Select Edit next to the condition Click edit to specify a result for the default condition to edit the default condition.

   Note

   For the default condition, everything should be disabled except for the last Value from and Result is fields.

7. Enter the following data to populate the default condition, and then select OK:

   • Value from: Static Value
   • Result is: is 0.25 or under

   

8. Save the parameter.

9. Refresh the workbook to see the randCriteria parameter in action. Its value will be based on the value of rand.

Criteria Settings form