Set parameters for multiple calculations

Modeling
,
1

Hey everyone!

Is there a way that I can set parameters, like environment variables, that I can call across multiple calculations?

For example, set a variable named “X” and use it to divide new columns A, B, and C by that value. Whenever I change the X value, that will update the columns A, B and C.

2

After some experimenting and productive conversations with the Omni team, we came up with a good solution for situations when you want to centralize parameters that will be utilized across multiple calculations in your workbook.

The solution was to create a new SQL view that built a virtual view. That view contains the values you provide as parameters in columns. Your query view should look like this structure:

SELECT
   2 AS "PARAMETER1",
   10 AS "PARAMETER2",
   'NAME1' AS "PARAMETER3"

Save the query as a view in your data model.

Now that you have that view, you will define a relationship between it and your base view, as well as any other views that you see necessary. The interesting part here is that you will create the relationships as a cross join.

You can use the UI to construct the relationship between your parameters view and the base view, but the ultimate result will look something like this in your relationship YAML file:

- join_from_view: parameters
 join_to_view: your_base_view
 join_type: cross
 relationship_type: many_to_many

In that way, you’ve created a centralized view where you can always go to change value while also being able to leverage these values across your data model for various calculations, which will eventually impact your charts.

If you want to allow users to edit values directly from the dashboard, you can use filters in the UI to capture values manually entered by the user and update the parameters in the parameter view.

For each parameter, add a filter to your dashboard’s user interface. Once that is complete, return to your parameters view definition and change it with the mustache syntax to capture values from the filters you created in the UI. The updated YAML file should be like this:

SELECT
   {{filters.parameters.parameter1.value}} AS "PARAMETER1",
   {{filters.parameters.parameter2.value}} AS "PARAMETER2",
   {{filters.parameters.parameter3.value}} AS "PARAMETER3"

Your parameters will now follow the selections made by the user in the dashboard UI.

Bonus: In the dashboard UI, you can set a default value for your filter’s parameters instead of the default “any value”.