Query Variables

Query variables allow values to be attached to variables so that a single value can easily be expressed multiple times within a query, or quickly changes to affect the results of a query. Query variables are created by wrapping the desired variable name in double curly braces anywhere inside a query. An input box in the right sidebar will appear for each query variable created. As an example, this query will return a list of all countries where {{language}} is an officially recognized language:
1
select
2
   country.Name,
3
   countrylanguage.Language,
4
   countrylanguage.Percentage
5
from
6
   countrylanguage
7
   join country on country.Code = countrylanguage.CountryCode
8
where
9
   countrylanguage.Language = {{language}}
10
   and countrylanguage.IsOfficial = 1
11
order by countrylanguage.Percentage desc
Copied!
Once a query variable is added to a query, it will appear in the right sidebar as an empty variable. Enter a value into the variable input to attach a value to it. For this example, we’ll use ‘English’:
Insert a query variable by typing {{variable}}
Supported Data Types 
Query variables support String, Number, Date, and Boolean data types.  For example, the above query can be modified to return a list of all countries where {{language}} is officially recognized and the population is greater than {{min_population}}:
1
select
2
   country.Name,
3
   countrylanguage.Language,
4
   countrylanguage.Percentage
5
from
6
   countrylanguage
7
   join country on country.Code = countrylanguage.CountryCode
8
where
9
   countrylanguage.Language = {{language}}
10
   and countrylanguage.IsOfficial = 1
11
   and country.Population > {{min_population}}
12
order by countrylanguage.Percentage des
Copied!
For this example, we'll look for Spanish-speaking countries with populations of more than five million people:
Query variables with different data types
Note: No further configuration is required, no matter which type of variable is used.  Arctype will automatically detect the type of variable inputted.
Query Variables with LIKE and ILIKE
You may want to use a query variable inside a string in Arctype. For example take this querySELECT * FROM imp_cities
1
SELECT * FROM imp_cities
2
WHERE name LIKE '%city_name%'
Copied!
It would be great to use a variable instead of city_name. This can be achieved using CONCAT
1
SELECT * FROM imp_cities
2
WHERE name LIKE CONCAT('%', {{var}}, '%')
Copied!
Here {{var}} is our query variable and this query works using a string comparison.
Query Variables as Inputs for Dashboards
You can add queries that include query variables to dashboards to create dynamic dashboards with charts and tables that change based on a text or date input.
First create a query that uses a query variable as above. Add this to a Dashboard by clicking on the 3 dots in the top right of the Results Pane and choosing Add to Dashboard.
Second, once the query is added to a dashboard as a chart or table, right click on its title and choose Configure Chart.
Configure a chart or table to link it to a Dashboard input using a query variable.
Third, in the right hand context menu, you will see inputs to the query that are titled after the query variables you used. In the image above, {{end_date}} and {{start_date}} are both query variables that are shown in this menu. Next to these inputs, click the gray link button. If an input such as a text input or a date picker has already been linked to this query variable, the link will be pink instead of gray.
Click the link button in a chart or table that uses a query that contains query variables. Then, select which input in the dashboard you want to link the query to.
Finally, you will see a menu to choose which dashboard input, such as a text input or date picker. In the example above we have a date picker input named endDate that has already been added to the dashboard. We select its value property to link the user selected date to the end_date query variable. Select your input and the value you would like to use from it.
This feature also applies for tables and rows which means you can build charts which change based on clicking a row in a table. For more examples of the surprisingly complex charts that can be built, see this blog post on visualizing sales data in a dashboard.

Queries - Previous

Organizing Queries With Folders

Next - Queries

Query History

Last modified 3mo ago

Copy link

Contents

Supported Data Types

Query Variables with LIKE and ILIKE

Query Variables as Inputs for Dashboards