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.

Requirements

  • Parameter permissions:
    • If you are a Sigma Creator or Admin, you can view, create, edit, and delete dataset parameters on worksheets and datasets you have access to.
      • You must have edit permissions on the document to publish your work. If you have view-only access, you can save your changes as a new copy.  For more information see Data Permissions.
    • If you are a Sigma Viewer, you can view but not edit dataset worksheet parameters.
    • All Sigma users can input values into parameters made accessible from a workbook.

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-overview.png

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

  1. Go to a dataset view's Worksheet tab.

  2. Click Edit.

  3. If the left control panel is not already open, open it by clicking either of the two Show Controls buttons.
    dataset-showcontrolsoptions.png

  4. 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.

     dataset-add-param-button.png

  5. The Add Parameter modal opens.
    dataset-addparameter-modal.png

  6. Name the parameter.

  7. (Optional) Add a description.

  8. Select the Data Type (Text, Number, or Date) for the parameter.

  9. 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.
  10. (Optional) Specify the Default Value.

  11. (Optional) Select the Format of the parameter’s value.

  12. Click Save.

  13. Use the new parameter:

Use a Value List in a Dataset Parameter

  1. Follow steps (1) through (8) above.

  2. For the Suggested Values, select List.

  3. (Optional) Select the Format of the parameter’s value.For example, you can format a Number value as Currency, Percent, etc.
    dataset-addparameter-listvalue1.png

  4. In the Values List, add each value. If you defined a display format, the Display Value column shows what that will look like. 
    params-edit-parameter-modal.png

  5. (Optional) Select one of the values to use as a default from the Default Value list.

  6. Click Save.

  7. Use the new dataset parameter:

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.

  1. Use any column's menu to create a new column for the parameter value.

  2. In the new column's formula bar, enter the name of the parameter surrounded by square brackets [ ]. 
    params-new-dataset-param.png

  3. Use the new column's menu to rename it [Min Sales Param].

  4. In the parameter list, change the parameter's value and see how Sigma updates the column. 2023-05-15_15-47-42.png

  5. To see how to use the parameter in a formula, create another new column.

  6. 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\]

  7. Sigma automatically calculates a True/False (logical) value for the column.

  8. Rename the new column [Is > Min Sales Amount].

  9. 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.
    params-calculated-column.png

Filter a Dataset Worksheet with a Dataset Parameter

To filter a dataset worksheet based on a dataset parameter:

  1. Create a parameter. See Create a Dataset Parameter.
  2. Reference it in a column’s formula. See Reference a Parameter in a Formula in a Worksheet.
  3. Create a filter for that column. See the next section.

Create a Filter for the Column using the Parameter

  1. Start with an existing parameter and the column referencing it.
  2. From the column’s menu select Add Filter.
  3. The Add Filter modal prompts you select which values to use in the filter. By default all are selected.
  4. Click Save.
    params-add-filter-modal.png
  5. 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.
    2023-05-15_16-20-04.png

Reference a Dataset Parameter in SQL

In Sigma, you can create a workbooks, dataset, or data control by writing a SQL query against a data store. The query 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.

  1. Create a parameter.

  2. 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}}
      Screen_Shot_2021-07-27_at_2.37.40_PM.png
    • 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>}}
  3. 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:

  1. Go to a dataset's worksheet tab.
  2. Click Edit.
  3. If the left control panel is not already open, open it by clicking either of the two Show Controls buttons.
  4. In the PARAMETERS list, hover over the parameter you would like to delete, and click the caret icon to open the dropdown menu.
  5. Click Delete.