IMPORTANT: This document is specific to Sigma worksheets. To learn about parameters for Sigma’s new Workbooks feature, please visit Workbook Parameters.

A parameter is a customizable field that can be added to a worksheet and referenced in formulas. Creating parameters in your worksheets, and referencing them in formula columns, allows you to dynamically replace values used in calculations across a worksheet’s lineage. You can go on to use these variable calculations in worksheet filters, pass them on to downstream dashboards, or use them to manipulate data source queries. 

Parameters can be huge assets for What-If Analysis and User Input Analysis. One common use case is implementing a parameter in place of a constant value. Using a parameter provides you the flexibility to change that value very quickly. For example, you may want to compare product sales growth by a variable 2%, 5%, and 10% percent. A parameter can be used to quickly track this variable value and inject it into formulas. 

Summary of Content

Requirements
Anatomy of a Parameter
      The Control Panel
      The Parameter List
      The Parameter
      The Parameter Modal
Creating a Parameter
      Create a Basic Parameter
      Create a Value List Parameter
Referencing a Parameter in a Formula
Filtering with Parameters
Using Parameters in Dashboards
Editing a Parameter
Deleting a Parameter
Advanced Parameters
      Data Source Parameters
      SQL Parameters
Parameter Permissions
Related Resources

Requirements

  • You must be a Sigma Creator or Admin to create, edit, and apply worksheet parameters.

Anatomy of a Parameter

The Control Panel

Parameters live in the worksheet control panel, located on the left side of every Sigma worksheet. This panel also includes any worksheet Totals and Filters. Learn more.
Screen_Shot_2020-10-29_at_8.57.03_AM.png

The Parameter List

All existing parameters are listed in the control panel under the header PARAMETERS. If the worksheet has any source parameters, they will be listed directly below the worksheet parameters in the control panel.
Screen_Shot_2020-07-27_at_12.05.41_PM_-_2.png

The Parameter

The following example outlines the basic structure of a parameter when displayed in the control panel.

(1)  Data type icon
This represents the value type of the parameter: text, number, or date.

(2)  Parameter name
The name you give to the parameter.

(3)  Parameter menu
Open this menu to edit, duplicate, or delete the parameter.

(4)  Parameter value input
This is where you can 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. 

Screen_Shot_2020-07-27_at_1.23.14_PM_copy_-_2.png

The Parameter Modal

The parameter modal allows you to create and edit worksheet parameters. Modal field options vary depending on the Data Type and Suggested Value fields.

Screen_Shot_2020-07-27_at_11.41.25_AM_-2.png

Suggested Values: ‘All’ vs ‘List’

The parameter modal’s Suggested Values field determines the parameter’s input type.

All

Depending on the Data Type, a text, number, or date input will be available on the parameter. In the control panel, any value matching the selected value type can be set as the parameter value. Learn how to create this type of parameter.

List

The ‘List’ option allows you to define a custom set of value options for the parameter. In the control panel, any value in the list can be selected as the parameter value. No other values are accepted when using a parameter list in a worksheet*. Learn how to create this type of parameter.

*Please Note: This is not the case when worksheet parameters are used on dashboards OR when worksheets pass values to Data Source Parameters. In both cases, it is possible to pass any value to the parameter. As such, value lists SHOULD NOT be considered a security measure. 

Creating a Parameter

Create a Basic Parameter

  1. If the control panel is not already open, open it by clicking either of the two "Show Controls" buttons.
    Screen_Shot_2020-10-28_at_9.43.55_AM.png

  2. Click on the blue + icon on the right side of the panel’s PARAMETERS section.
    Screen_Shot_2020-10-28_at_9.44.34_AM.png

  3. The Add Parameter modal will now appear on your screen.

  4. Name the parameter.
    Screen_Shot_2020-07-27_at_11.41.25_AM_-2.png

  5. [optional] Add a description.

  6. Select the Data Type that you would like to use for the parameter. This can be text, number, or date.
    In this example, we will select ‘Number’.
    Screen_Shot_2020-08-14_at_10.26.09_AM.png

  7. Under Suggested Values, select ‘All’ or ‘List’.
    ‘All’ allows the parameter to accept any input value of your previously-selected data type. Alternatively, ‘List’ provides you with the option to create a custom list of optional values.  Learn more.
    In this example, we will use the default ‘All’ option. To instead learn how ‘List’ works, skip down to the Create a Value List Parameter instructions.

  8. [optional] Specify a parameter Default Value.

  9. [optional] Select the Format of the parameter’s value.

  10. Click Save to save your new parameter.

  11. Your new parameter will now appear in the worksheet control panel. From here you can quickly input any value.
    Now that you have a parameter, it is time to reference it in a formula!

Create a Value List Parameter

  1. Follow steps (1) through (6) under Create a Basic Parameter.

  2. Under Suggested Values, select ‘List’.
    Screen_Shot_2020-08-13_at_4.40.40_PM_-_2.png

  3. [optional] Select the Format of the parameter’s value.
    In this example, let’s select ‘Currency’.

  4. Under Values List, add each value you would like to use as the parameter value. Display values will be automatically pre-filled based on your previously-specified Format field.
    Screen_Shot_2020-07-27_at_4.00.00_PM_-_2.png

  5. [optional] After creating your value list, go back up to the Default Value field, and select a default value.

  6. Click Save to save your new parameter.

  7. Your new parameter will now appear in the worksheet control panel. From here you can quickly input any value.
    Now that you have a parameter, it is time to reference it in a formula!

Referencing a Parameter in a Formula

Parameters can be referenced by their name in any column formula. The formula will automatically rerun, updating your data, whenever you enter a new parameter value.
To reference a parameter in a formula, type the name of the parameter into the formula bar, using square brackets [ ]. 

Screen_Shot_2020-10-29_at_8.59.10_AM.png

To demonstrate this, let’s reuse the parameter from the above Create a Basic Parameter instructions to calculate a new column’s value.

  1. Create a new column in the worksheet.

  2. Type a formula into the formula bar.
    In this example, our formula will check if each table row’s [Unit Price] is greater than our [Min Unit Price] parameter. (ie `[Unit Price] > [Min Unit Price]`)
    Screen_Shot_2020-07-27_at_4.29.29_PM_-_2.png

  3. Hit ‘Enter’ on your keyboard to calculate the value in the formula editor.

  4. This will calculate a True/False (logical) column.
    The formulated column will automatically update when you enter a new parameter value.
    Screen_Shot_2020-10-29_at_9.08.48_AM.png

Filtering with Parameters

To filter your data based on a parameter, you will need to (1) create a parameter, (2) reference it in a column’s formula, and (3) create a filter for that column.

To demonstrate this, let’s reuse the parameter from the above Create a Basic Parameter instructions and the column from the Referencing a Parameter in a Formula instructions, to create a new filter.

  1. To start, you will need our previously created parameter and the column referencing it.
    For this example, the column has been renamed to be more readable.
    Screen_Shot_2020-10-29_at_9.10.10_AM.png

  2. Open the column’s menu, and select Add Filter....

  3. The filter modal will appear prompting you to create a filter.
    Customize your filter and/or hit Save to continue.
    Screen_Shot_2020-07-27_at_5.09.00_PM_2.png

  4. The filter will appear in the control panel under FILTERS.
    You can now update both the parameter and the new filter directly from the control panel to focus your data!
    Screen_Shot_2020-10-29_at_9.11.15_AM.png

Using Parameters in Dashboards

When you add a visualization to a dashboard from a worksheet that uses parameters, you have the option to also use those parameters on the dashboard. This can be done by applying the Set Parameter action to a dashboard control target visualization. To learn how, visit Dashboard Controls.

When a dashboard uses worksheet parameters, the input value passed to the parameter for query evaluation is dependent on control input. Depending on control settings, this may mean that suggested values beyond what are listed in the worksheet parameter can be injected as a value.

Editing a Parameter

  1. Open the control panel.

  2. In the PARAMETERS list, hover over the control you would like to edit, and click the caret icon to open the dropdown menu.
    Screen_Shot_2020-07-27_at_7.05.19_PM_-_2.png

  3. Click Edit....

  4. This will open the Edit Parameter modal. Make your desired changes and click ‘Save’.

Deleting a Parameter

The following instructions will permanently delete your parameter.

  1. Open the control panel.

  2. In the PARAMETERS list, hover over the parameter you would like to delete, and click the caret icon to open the dropdown menu.

  3. Click Delete.

Advanced Parameters

Data Source Parameters

What are Data Source Parameters?

Parameters are not limited to worksheets and dashboards. They can also be created on Sigma Datasets. Dataset parameters are passed to downstream worksheets, where they can then receive input. In the worksheet interface, these parameters are referred to as Data Source Parameters, because they live on the worksheet’s data source(s).

If a worksheet has data source parameters, they will be listed in the control panel directly under any existing worksheet parameters.

What can I do with them?

The data source parameter list in a worksheet allows you to pass values up to the worksheet’s source-Datasets’ parameters by means of the worksheet’s local parameters. This means you can control parameter values on your data source via parameters on your worksheet, ultimately impacting what data is passed down to your worksheet.

For example, if a column formula is calculated in a Dataset using a parameter on that Dataset, your downstream worksheet’s source parameters will allow you to pass values up to the Dataset parameter. In turn the column formula in the Dataset will be recalculated per that parameter value, and the result will trickle back down to your worksheets.

Note: Only the query results being passed from the Dataset to the worksheet are impacted. The Dataset itself and all other downstream worksheets ARE NOT impacted. 

Passing a Worksheet Parameter to a Data Source

Data source parameters are listed in the worksheet control panel under your worksheet parameters. To pass a worksheet parameter to a source query:

  1. Open the control panel, and find the source parameter under Source Parameters.
  2. Click the caret icon to expand the parameter.
    Screen_Shot_2020-07-28_at_8.47.27_AM_-_2.png
  3. Under Assigned Worksheet Parameter, select an existing worksheet parameter or create a new one.
    Note: A single worksheet parameter can affect several Dataset parameters. 

SQL Parameters

Parameters on your worksheet can pass values to your SQL data sources as well. Once 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>}}.

Note: SQL parameters only support a limited set of special characters including: spaces (“ “), apostrophes (“‘“), underscores(“_”), and dashes (“-”).

After the parameter is added to your SQL, a change to the parameter value will propagate to your SQL source and subsequently reload your worksheet to reflect your parameter use.

Using a Worksheet Parameter in an SQL Data Source

  1. If you do not already have one, create a new worksheet sourced from SQL.
    Screen_Shot_2020-10-29_at_9.12.51_AM.png

  2. Add a parameter to the worksheet via the control panel.

  3. Open the Data Source Panel on the right side of your worksheet, and select ‘Edit Source’ from your SQL source’s ••• menu.
    Screen_Shot_2020-10-29_at_9.13.42_AM.png

  4. Add a reference to your parameter in the SQL editor.
    In this example, we will filter our table to only show rows with ‘Unit Price’ greater than our `Min Unit Price` parameter value.
    Screen_Shot_2020-07-28_at_10.49.48_AM_2.png

  5. Click the blue Done button in the top right of the screen.

  6. You can now modify the parameter from the control panel, and the queried data will update accordingly.
    sql-param.gif

Parameter Permissions

  • If you are a Sigma Creator or Admin, you can view, create, edit, and delete parameters on worksheets and Datasets you have access to. However, as with other document changes, you must have edit permissions on the document to publish your work. If you have view-only access, you may save your document changes as a new copy. Learn more about permissions.
  • If you are a Sigma Viewer, you have access to view, but not edit, worksheet and Dataset parameters.
  • All Sigma users can input values into parameters made accessible from a dashboard.

Related Resources

Worksheet Controls
Worksheet Filters

 

IMPORTANT: This document is specific to Sigma worksheets. To learn about parameters for Sigma’s new Workbooks feature, please visit Workbook Parameters.