Workbook Parameters

A parameter is a customizable field that can be added to a workbook and referenced in formulas. They allow you to dynamically replace constant values used in calculations across your workbook. 

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
Create a Parameter
Reference a Parameter in a Formula
Advanced Parameters
      Data Source Parameters
            Pass a Value to a Data Source Parameter
            EXAMPLE
      SQL Parameters
            Use a Parameter in SQL
Related Resources

Requirements

  • To create a control element, you must have Can Edit access to the individual workbook and be in Edit mode.
  • Existing controls can also be used in View and Explore mode. Learn more

Create a Parameter

To create a parameter, enter Edit mode, then:

  1. In the editor panel's sidebar, click the + icon.
    This opens the     ADD NEW panel.
  2. Under CONTROL ELEMENTS select your desired control element type.
    The new element appears on the page.
  3. Under Control type select Parameter. 
    Screen_Shot_2022-02-09_at_11.29.48_AM.png
  4. Use the editor panel to configure the parameter's Settings. 

Reference a Parameter in a Formula

Prerequisite: a parameter

To reference a parameter in a formula, enter Edit mode, then:

  1. Add a new column to the element in which you want to reference the parameter.
  2. With the column selected, type your formula into the formula bar.
    The parameter id should be placed inside square brackets (e.g. [My-First-Parameter]). 
    Screen_Shot_2022-02-09_at_11.37.54_AM.png
  3. Hit Enter on your keyboard, to save your formula.
  4. Once the formula is saved, changing the parameter value will automatically trigger the calculated column to re-calculate.
    Screen_Shot_2022-02-09_at_11.39.58_AM.png

Advanced Parameters

Data Source Parameters

Data source parameters are parameters that live in the workbook's data source (Dataset) but receive input from a parameter inside the workbook. 

Pass a Value to a Data Source Parameter

Data source parameters are accessible from the Source tab on a control's workbook editor panel view.

Before you start: This action is only available in Edit mode. To begin editing, click Edit in the top right corner of the page. Learn more

  1. Create a new workbook parameter or select an existing one.
  2. In the editor panel under Settings, select a Value type to pass to your data source's parameter. The value type must match the data source parameters value type.
    Screen_Shot_2021-07-26_at_3.48.11_PM.png
  3. Open the workbook parameter's Source tab.
    Screen_Shot_2021-07-26_at_3.52.35_PM.png
  4. Click + Add Source. Then select your data source.
    Note: Only data sources containing one or more parameter(s) that accepts your selected value type will be available. The parameter(s) need to be referenced inside a column in the source, otherwise this option will appear greyed out.
    Screen_Shot_2021-07-26_at_3.58.33_PM.png
  5. If the data source has more than one parameter, use the dropdown menu to select your target parameter. 
    Screen_Shot_2021-07-26_at_4.01.31_PM.png

EXAMPLE 

In the following example, the [My Parameter] parameter accepts a number value and passes it to a data source's parameter.

The table to the right of [My Parameter] is powered by the same data source. Its column, [+ 5 column], is calculated from the data source. The formula for [+ 5 column] adds 5 to the data source parameter: 

[data-source-parameter] + 5

When a value is input into [My Parameter], it is passed to the data source column and [+ 5 column] is recalculated with that value. 

0 + 5 = 5
3 + 5 = 8
10 + 5 = 15

2021-07-26_16-28-34__1_.gif

SQL Parameters

Workbook parameters can be included in written SQL. 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>}}

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.

Use a Parameter in SQL

Before you start: This action is only available in Edit mode. To begin editing, click Edit in the top right corner of the page. Learn more

  1. Create a parameter.

  2. Identify your parameter's Control ID. This identifier will be referenced in your written SQL.

    Example: In the image below, the Control ID is "min-sales-amount".
    NOTE: We highly recommend that you give your parameters distinctive Control IDs that do not duplicate the name of any other Control IDs, fields or columns. Duplicate naming can cause unexpected behavior and obscure errors. As a best practice, we typically prepend the names of our parameters with "prm_", e.g. "prm_min-sales-amount".

  3.  

    Screen_Shot_2021-07-27_at_2.32.03_PM.png

  4. Create a new data element from SQL. 

    • When writing your SQL, inject the parameter by referencing its control ID in double curly-brackets: {{<parameter name>}}

      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>}}

  5. After saving your SQL, you can test your new SQL parameter by inputting a value and watching the data element update accordingly.

 

Related Resources

Documentation

Intro to Control Elements

From the Sigma Community

Multi-select Parameters in Workbooks (hand-written SQL source)
Multi-select Parameters in Workbooks (Calculated Fields)

Was this page helpful?
Yes No