Create and manage dataset parameters
A dataset parameter is a customizable field that you can add to a dataset worksheet and reference in formulas and in custom SQL. Creating dataset parameters and referencing them allows you to dynamically replace values used in calculations.
Use a parameter instead of a constant value for what-if and user input analysis. Using a parameter lets users change its value very quickly. For example, you may want to compare product sales growth by a variable 2%, 5%, and 10% percent. A parameter can quickly inject this variable value into a formula.
Dataset parameters can also improve performance for expensive workbook queries. Use dataset parameters to filter the data down to only what is needed.
User requirements
The ability to create, edit, delete, and use parameters in datasets requires the following:
- You must be assigned an account type with the Create, edit, and publish datasets permission enabled.
- You must be the the dataset owner or be granted Can edit dataset permissions.
The ability to view parameters in a dataset, requires the following:
- You must be assigned an account type with the View datasets permission enabled.
- You must be the the dataset owner or be granted Can view dataset permissions.
For permissions required to use dataset parameters in workbooks, see Parameters in workbooks.
Dataset Parameters in the Dataset Worksheet UI
From the Sigma Home page, select an existing dataset or create a new one.
Tip: Click + Create New to define a new dataset. For more information see Create Datasets.
In the dataset's view, access dataset parameters from the Worksheet tab's left panel.
Dataset Parameters List
Sigma lists all existing dataset parameters in the left panel under the header PARAMETERS. Each parameter shows its:
- Data type icon: Represents the value type of the parameter, such as text (abc), number (123), or date (calendar). For more information see Data Value Types.
- Parameter name: The name you give to the parameter.
- Parameter + menu: Menu to edit, duplicate, or delete the parameter.
- Parameter value input: Where you set the parameter’s value. The input type is dependent on the parameter’s settings. You may see a list of values, a text input, a numeric input, or a date input.
Create a Dataset Parameter
-
Go to a dataset view's Worksheet tab.
-
Click Edit.
-
If the left control panel is not already open, open it by clicking either of the two Show Controls buttons.
-
Click + Add Parameter at the top of the PARAMETERS list.
Tip: If you don't see the + icon, you may not be in Edit mode.
-
The Add Parameter modal opens.
-
Name the parameter.
-
(Optional) Add a description.
-
Select the Data Type (Text, Number, or Date) for the parameter.
-
The Suggested Values option determines the parameter’s input type. Select either:
- All: Depending on the Data Type, a text, number, or date value.
- List: A custom set of value options. Users can select any value in the list. See the instructions below at Use a Value List in a Dataset Parameter.
-
(Optional) Specify the Default Value.
-
(Optional) Select the Format of the parameter’s value.
-
Click Save.
-
Use the new parameter:
- From the dataset worksheet's left panel, you can change the value.
- Reference a Dataset Parameter in a Formula in a Dataset Worksheet.
- Use it in a control. For more information see Parameters in Workbooks.
Use a Value List in a Dataset Parameter
-
Follow steps (1) through (8) above.
-
For the Suggested Values, select List.
-
(Optional) Select the Format of the parameter’s value.For example, you can format a Number value as Currency, Percent, etc.
-
In the Values List, add each value. If you defined a display format, the Display Value column shows what that will look like.
-
(Optional) Select one of the values to use as a default from the Default Value list.
-
Click Save.
-
Use the new dataset parameter:
- From the dataset worksheet's left panel, you can change the value.
- Reference a Dataset Parameter in a Formula in a Dataset Worksheet.
- Use it in a control. For more information see Parameters in Workbooks.
Reference a Dataset Parameter in a Formula in a Dataset Worksheet
Reference a dataset parameter by its name in a worksheet column formula. The formula will automatically rerun and update the data whenever you enter a new parameter value.
Note: This example assumes that the dataset has a [Revenue] column or the equivalent.
-
Use any column's menu to create a new column for the parameter value.
-
In the new column's formula bar, enter the name of the parameter surrounded by square brackets [ ].
-
Use the new column's menu to rename it [Min Sales Param].
-
In the parameter list, change the parameter's value and see how Sigma updates the column.
-
To see how to use the parameter in a formula, create another new column.
-
The new column's formula checks whether each table row’s [Revenue] is greater than the [Min Sales Amount] dataset parameter. Enter:
[Revenue] > [Min Sales Amount]
-
Sigma automatically calculates a True/False (logical) value for the column.
-
Rename the new column [Is > Min Sales Amount].
-
In the side panel, change the value of the parameter to see how it affects the true/false values.
The formulated column automatically updates when you enter a new parameter value.
Filter a Dataset Worksheet with a Dataset Parameter
To filter a dataset worksheet based on a dataset parameter:
- Create a parameter. See Create a Dataset Parameter.
- Reference it in a column’s formula. See Reference a Parameter in a Formula in a Worksheet.
- Create a filter for that column. See the next section.
Create a Filter for the Column using the Parameter
- Start with an existing parameter and the column referencing it.
- From the column’s menu select Add Filter.
- The Add Filter modal prompts you select which values to use in the filter. By default, all are selected.
- Click Save.
- The filter appears in the side panel under FILTERS.
You can now update both the parameter and the new filter directly from the side panel.
Reference a Dataset Parameter in SQL
In Sigma, you can create a dataset or populate a workbook element or data control by writing a SQL query against a data store. The query you write can contain a dataset parameter. After a parameter is created, you can reference the value of that parameter in your SQL source by wrapping the parameter’s name within curly brackets:
{{<parameter name>}}
After you add the parameter to the SQL, a change to the parameter value will propagate to your SQL source and subsequently reload the data to reflect your parameter use.
-
Create a new data element from SQL. For more information see Create a Data Element.
- When writing the SQL, inject the parameter by referencing its control ID in double curly-brackets:
{{<parameter name>}}
For example:
{{min-sales-amount}}
- If the parameter's value is being injected with single quotation marks, you can remove these quotation marks by prepending the special keyword "#raw" before its name:
{{#raw <parameter name>}}
- When writing the SQL, inject the parameter by referencing its control ID in double curly-brackets:
-
After saving the SQL, test your new SQL parameter by inputting a value and watching the data element update accordingly.
Delete a Dataset Parameter
To permanently delete a dataset parameter:
- Go to a dataset's worksheet tab.
- Click Edit.
- If the left control panel is not already open, open it by clicking either of the two Show Controls buttons.
- In the PARAMETERS list, hover over the parameter you want to delete, and then click the caret icon to open the dropdown menu.
- Click Delete.
Updated 4 months ago