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
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
- In the editor panel's sidebar, click the + icon.
This will open the ADD NEW panel. - Under CONTROL ELEMENTS select your desired control element type.
Your new element will appear on the page. - Under Control type select Parameter.
- Use the editor panel to configure the parameter's Settings.
Reference a Parameter in a Formula
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
- Create a parameter.
- Add a new column.
- 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]). - Hit Enter on your keyboard, to save your formula.
- Once the formula is saved, changing the parameter value will automatically trigger the calculated column to re-calculate.
Advanced Parameters
Data Source Parameters
Data source parameters are parameters that live on the workbook's data source but receive input from a parameter on the child 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
- Create a new workbook parameter or select an existing one.
- 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.
- Open the workbook parameter's Source tab.
- 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. - If the data source has more than one parameter, use the dropdown menu to select your target parameter.
EXAMPLE
In the following example, [My 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
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
-
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".
-
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}}
-
-
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
From the Sigma Community
Multi-select Parameters in Workbooks (hand-written SQL source)
Multi-select Parameters in Workbooks (Calculated Fields)