Variables

What are Variables?
Variables are ways for you to create dynamic, parameterized versions of your dashboard based on who is viewing the dashboard.
Types of Variables
There are a few main ways that variables are introduced into the application: 
1.User Group ID
2.Dashboard Elements
3.Custom Variables
User Group ID
In order to filter down data to a specific user group, the user_group_id variable is provided. This variable uses the database ID of the user group viewing the dashboard. You tell us this ID when you create a user group. 
Dashboard Elements
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. 
When you create interactive dashboard elements, such as dropdowns, date-pickers, and toggle buttons, you are creating new variables which you can use to connect your users interactions to changes in data.
The default ID for a dropdown
To pass in the variables, see the embed code below:
Each element created has a unique ID which you can change and use wherever you need.
1
<explo-dashboard
2
  dash-user-group-token="<dashboard_id>:<group_token>"
3
  isProduction={true}
4
  environment="prod"
5
  variables={JSON.stringify({
6
    office_id: 'value',
7
  })}>
8
</explo-dashboard>
Copied!
These variables get set when your users interact with the element and select a value. When configuring the component, you will also have the option to set default values so that the dashboard loads preselected with certain options.
Custom Variables
Custom variables allow users to pass in parameters through the embed code and not on-dashboard componenents. Click on Manage Custom Variables on the bottom right of the dataset editor to create and delete custom variables.
These variables can be used just like dashboard elements except you can only pass them in and your users can't configure these. Reference the section below on Embedded Component to learn how to pass values into custom variables.
Passing in Variables
Embedded Component
When you embed an Explo Dashboard using an embedded component, you can pass in variables directly which are then available anywhere in the dashboard that accepts variables. 
1
<explo-dashboard
2
  variables={JSON.stringify({
3
    element1: 'value',
4
    element2: 'value2',
5
  })}>
6
</explo-dashboard>
7
​
Copied!
As demonstrated above, you can pass in a set of key value pairs that define new variables that you can use in your dashboard. Full documentation for the embedded component can be found here.
URL Parameters
On any page that loads an Explo dashboard, such as with the embedded component, an iframe URL, or the share URL, Explo looks at the URL to see if any variables are defined there. 
For example, the following URL: https://example.co/reports?dropdown-1=%22BAR_CHART%22​
The Explo dashboard will take dropdown-1 as a variable and use the associated value.
Using Variables
In Datasets
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:
1
[[WHERE field1 = {{element1}}]]
Copied!
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.
1
WHERE 1=1 [[and field1 = {{element1}}]]
2
					[[and field2 = {{element2}}]]
Copied!
In Data Panels
Instead of writing SQL to filter your datasets, you can filter specific data panels by variables directly. To do this, drag the field you want to filter into the Filter section. Then select the Variable option for the filter value input:
Filtering a chart by date
You will then have the option to select the variable defined on the page that you want to filter by. You will only be shown options for variables that are the correct type. For example, if you are filtering a date field, we will only show you date-picker variables.
Now when the date picker is used, the data panel will filter down its data to the selected range!
Data Panel Built-In Variables
KPI Trend
The following variables can be used in the KPI panel's Subtitle field.
Using both variables in the subtitle field
The final result where the variables are replaced with the actual dates
Variable
Description
{{current_period}}
This is the current period of data that will be used as the basis for comparison e.g. Jun 11 - Aug 12
{{comparison_period}}
This is the period that the current_period is compared to for calculating the trend e.g. July 9 - Jun 10
Tips
Segmenting user data
{{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:
1
SELECT *
2
FROM table_1
3
[[WHERE user_id = {{user_group_id}}]]
Copied!
Additional operators
Variables can be used with other operators as well to achieve different filtering needs
1
--use the in operator when configuring a multi-select componenet
2
[[WHERE field1 in {{element1}}]]
3
​
4
--filter for similar values using like
5
[[WHERE field_3 LIKE '%' || {{element3}} || '%']]
6
​
7
--relative date and number operators
8
[[WHERE field_3 >= {{element3}}]]
Copied!
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:
1
--Orginal Query
2
SELECT *
3
FROM models_operation
4
		INNER JOIN models_flow
5
		    ON models_flow.resource_ptr_id = models_operation.flow_id
6
		INNER JOIN models_datasource  
7
				ON models_datasource.id = models_operation.data_source_id
8
[[WHERE models_datasource.team_id = {{user_group_id }}]];
9
​
10
--Improved Query
11
SELECT *
12
FROM models_operation
13
		INNER JOIN models_flow
14
		    ON models_flow.resource_ptr_id = models_operation.flow_id
15
		INNER JOIN (SELECT * 
16
		            FROM models_datasource
17
		            [[WHERE models_datasource.team_id = {{user_group_id }}]]
18
		            ) AS ds
19
        ON ds.id = models_operation.data_source_id;
Copied!
​