Parameters in workbooks

Sigma has different ways of using parameters. In a workbook, the value of a parameter can change based on user input. For example, you may want to compare product sales growth by a variables of 2%, 5%, and 10% percent. A parameter can track the variable value and inject it into formulas. For more information see Create Flexible Reports with Parameters.

In a workbook, a control element (control) can act as a parameter in a formula or custom SQL. For example, you can enter the ID of a control as a parameter in a formula, and whenever the user changes the value of the control, the formula recalculates. See Use Controls as Parameters below.

💡

Controls can act as both filters and parameters.

A dataset parameter is a customizable field that you can add to a dataset worksheet and reference in formulas and in custom SQL. This method is useful when you want multiple workbooks to use the same parameter. For more information see Dataset Parameters. To update a dataset parameter using a control, see Pass a Value to a Dataset Parameter from a Control. To use a dataset parameter in SQL, see Reference a Dataset Parameter in SQL.

Requirements

To create and use parameters in workbooks:

• You must have Can Edit access to the individual workbook.
• The workbook must be in Edit mode.
• If using a dataset parameter, create or locate the parameter you want to use. See Dataset Parameters.

Use controls as parameters

To use the value of a control as a parameter in a formula or custom SQL, use the control's identifier. Locate the control ID by selecting the control and scrolling to the bottom of its SETTINGS options.

The syntax for referencing a control in a formula is to enclose the control ID in square brackets:

[New-Control-1]

The function bar shows control IDs in its dropdown:

🚩

Changing a control ID can break any parameter references that use the ID. You can change the control's label without changing its ID.

When you use a control's ID in a formula in a table, the control shows the relationship in its TARGETS tab as Currently referenced by.

Use a List Control as a Parameter

To use a list control as a parameter:

1. Add a control and set its type to List.
2. (Optional) Change the control ID to what you prefer. The default ID uses the format New-Control-1, New-Control-2, etc. 
   Important: If you want to change the ID, do that before you use it as a parameter. If you change it afterwards, the formula will not work as expected.
3. In the SETTINGS tab, configure the source for the list values. In this example the Value source is the Product Type column in a table element that uses the PLUGS sample data. 
   
4. Add the table from which you want to reference the parameter.
5. Add a column to that table.
6. In the column's formula, reference the list control using its Control ID. 
   
7. As you change the selections in the list, the values in the column update.
   

Use a Text Control as a Parameter

This example uses a text control with operators to search on a column of product names.

To use a text control as a parameter:

1. Add a control and set its type to Text. (Be sure this is a Text Control and not the UI Text widget.)
2. (Optional) Change the control ID to what you prefer. The default ID uses the format New-Control-1, New-Control-2, etc. 
   Important: If you want to change the ID, do that before you use it as a parameter. If you change it afterwards, the formula will not work as expected.
3. Configure the text to use operators. In the SETTINGS tab click Show operators. The text control adds the operators. 
   
4. Add the table from which you want to reference the parameter.
5. Target the control to a text column that you want to filter. The value of the text control sets the filter criteria. In this example, the target is a [Product Name] column. Sigma automatically renames the text control to match the filter target, but it does not change the Control ID.
   
6. Add a column.
7. Rename it [Product Name Search].
8. In the column's formula, reference the list control using its Control ID, which you can find in the control's properties in the left panel. Initially, the value of the column is null.
9. As you change the operators and enter text, the values in the column update.
   

Use a Number Control as a Parameter

Configure a number control to be a parameter the same as you would a text control.

Use a Number Control Range Min or Max as a Parameter

If you configure a number control to use a range (using the Select range option in the SETTINGS tab), you can access the minimum or maximum values in the range using the following syntax in a formula:

[Number-Range-Control-ID].min

or

[Number-Range-Control-ID].max

Use a Date Control as a Parameter

Configure a date picker control to be a parameter the same as you would a text control. However, a date control cannot be a filter.

Use a Date Range Control Start or End as a Parameter

This example uses a date range control. You can use the beginning date and end date of a range as parameters.

To use a date range control as a parameter:

1. Choose a table with a date column.
2. Add a control and set its type to Date Range.
3. Configure the TARGET to be a Date column.
   
4. Add a column.
5. Rename it [Start Date].
6. In the column's formula, reference the list control using its Control ID, which you can find in the control's properties in the left panel. For Date Range controls, you can append .start or .end to get the beginning or end date in a range of dates.
   
   Initially, the value of the column is null.
7. As you change the operators and enter date ranges, the values in the columns update.
   

Parameters in Workbook SQL

In a workbook, you can create a new table by directly querying a data source. To reference a control as a parameter, use its Control ID within curly brackets:

{{<control-ID>}}

For more information, see Multi-select Parameters in Workbooks (hand-written SQL source).

Pass a Value to a Dataset Parameter from a Control

Dataset parameters are accessible from the TARGETS tab on a control's workbook editor panel.

In this example, a number control uses the [Min Sales Amount] parameter that was created at Dataset Parameters.

1. Create a new dataset parameter.

2. In a workbook, if you are not already in Edit mode, click Edit.

3. Add a new Number control. 

   🚩

   The control type must match the type of the Dataset parameter. In some instances, the parameter also needs to be referenced inside a column in the Dataset before it becomes available to use in the Workbook. When using a List control, disable the "Allow multiple selection" option in its settings.

4. In the SETTINGS tab, deselect range.
   

5. In the left panel, select the TARGETS tab.

6. Select Add dataset parameter.

7. Select the dataset that has the parameter you want.
   
   Sigma shows the dataset parameters that are defined in the dataset you chose. You can select from only the parameters that match the control's value type. Other parameters that don't match are greyed out.
   

8. Select the parameter. In this case, [Min Sales Amount].

9. Type a number in the control. The data updates. 
   

   💡

   The number entered into the control does not have to be the same as the value range numbers defined in the dataset parameter. Any number within those ranges will work.