What are Variables?

Variables are generated when you add certain components to the dashboards (dropdown selector and date selectors). In addition, the {{user_group_id}} variable is automatically created for every dashboard and is used to segment your data between customers.

• Variables names are displayed on the top left corner of the component editor and are labeled depending on the component type. This is what you will enter into the curly brackets in your SQL query.

Custom Variables

Custom variables allow users to pass in parameters through the embed code.

• Select Manage Customer Variables in the bottom right corner to create or edit custom variables. Once those variables are defined, you can use them in your SQL queries like other variables by inserting them within the curly brackets.

To pass in the variables, see the embed code below:

<explo-dashboard
  dash-user-group-token="<dashboard_id>:<group_token>"
  isProduction={true}
  environment="prod"
  variables={JSON.stringify({
    office_id: 'value',
  })}>
</explo-dashboard>

Syntax

All variables are contained in double curly brackets {{}}, Explo will recognize any text in-between curly brackets in your SQL query as a variable.

Double square brackets [[]] are used for where clauses with variables such that if there is no variable selected, Explo will ignore the code between the square brackets.

All variable clauses must be placed inside square brackets.

Example of where clause using a variable in square brackets:

[[WHERE field1 = {{element1}}]]

When filtering by 2 different fields, you can use the syntax below to contain the "and" in the square brackets. The 1=1 is required here otherwise there will be an incomplete clause statement if neither filter is configured.

WHERE 1=1 [[and field1 = {{element1}}]]
					[[and field2 = {{element2}}]]

Default variable for segmenting data by customers

{{user_group_id}}

• The user group variable is a default variable that's already created in every dashboard.

• It is important to use this variable in every dataset that a panel is built off of if you want it to be customer specific. To configure this, select the field that has your customer's ID and set it equal to {{user_group_id}}

Example:

SELECT *
FROM table_1
[[WHERE user_id = {{user_group_id}}]]

Additional operators

Variables can be used with other operators as well to achieve different filtering needs

--use the in operator when configuring a multi-select componenet
[[WHERE field1 in {{element1}}]]
​
--filter for similar values using like
[[WHERE field_3 LIKE '%' || {{element3}} || '%']]
​
--relative date and number operators
[[WHERE field_3 >= {{element3}}]]

Tips

Improving query speed by filtering before joining

In general, it is best to filter tables before joining to other tables, by doing so, you can significantly cut down the calculation and query time needed to compute the entire query.

Example:

--Orginal Query
SELECT *
FROM models_operation
		INNER JOIN models_flow
		    ON models_flow.resource_ptr_id = models_operation.flow_id
		INNER JOIN models_datasource  
				ON models_datasource.id = models_operation.data_source_id
[[WHERE models_datasource.team_id = {{user_group_id }}]];
​
--Improved Query
SELECT *
FROM models_operation
		INNER JOIN models_flow
		    ON models_flow.resource_ptr_id = models_operation.flow_id
		INNER JOIN (SELECT * 
		            FROM models_datasource
		            [[WHERE models_datasource.team_id = {{user_group_id }}]]
		            ) AS ds
        ON ds.id = models_operation.data_source_id;

​