> For the complete documentation index, see [llms.txt](https://docs.getinfinity.app/user-guide/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.getinfinity.app/user-guide/comprehensive-user-guide/12.-advanced-topics/chart-variables.md).

# Chart Variables

Of course! Here's the documentation about **Variables** for your **Infinity** platform, formatted for GitBook:

***

## Variables

Variables in Infinity act as dynamic parameters that can be passed to SQL queries at runtime. They allow for top-level filtering on **Vectors** and **Storyboards**, ensuring that users can filter data even before it is pulled for chart creation.

A **Variable** is a `{name, value}` tuple:

* **Name**: Display name shown to the user.
* **Value**: Underlying value used in SQL queries.

After creating a variable, you can reference it in a database query using a `$` symbol prefix, for example, `$country`.

> **Example Usage in SQL**
>
> ```sql
> SELECT * FROM global_superstore_Orders WHERE Country = $country
> ```

***

### Purpose of Variables

* Enable dynamic filtering of data in **Vectors** and **Storyboards**.
* Help in building queries where the value can change based on user selection.
* Improve the flexibility and reusability of dashboards and reports.

***

## How to Create a Variable

1. Navigate to **Settings** → **Site Settings** → **Variables**.
2.

```
<figure><img src="/files/EyncNdLmV9EjZpHisNZK" alt=""><figcaption></figcaption></figure>
```

3. Click on **Add Variable**.
4.

```
<figure><img src="/files/RcoCX6O6ZFgHQJsnrRYj" alt=""><figcaption></figcaption></figure>
```

5. Fill out the form:

* **Name**:
  * Unique identifier for the variable.
  * Must start with a lowercase letter.
  * Only underscores (`_`) are allowed as special characters.
* **Label**:
  * The display name visible to the users.
* **Type**:
  * The data type of the variable (String, Integer, or Date).
* **Database**:
  * The source database from which the `{name, value}` tuples are fetched.
* **Order By**:
  * **Query**: Display order as returned by the query.
  * **Most Viewed**: Display based on usage/viewership.
* **Query**:
  * SQL query that fetches `{name, value}` pairs.
  * Must be a `SELECT` query returning only `name` and `value` columns.

> **Example Variable Query**
>
> ```sql
> SELECT DISTINCT country_name as Name, country_code as value FROM Countries
> ```

## Single Select vs. Multi Select&#x20;

### 🟢 Single Select Variable

To use a variable in **Single Select** mode, use the following syntax:

```
makefileCopyEdit$<variable_name>
```

For example:

```
bashCopyEdit$customer_segment
```

When used in SQL queries, the placeholder (e.g., `$customer_segment`) is dynamically replaced with the selected value (e.g., `'Consumer'`).

#### Example

**SQL Query Before Execution**

```sql
sqlCopyEditSELECT * FROM global_super_store WHERE Segment = '$customer_segment'
```

**SQL Query After Execution**

```sql
sqlCopyEditSELECT * FROM global_super_store WHERE Segment = 'Consumer'
```

<figure><img src="https://lh7-rt.googleusercontent.com/docsz/AD_4nXdVGbtFasOT2pASxIyy12SlzC4dWPg6RdJONJ2Wp9ggqKBZLEZlzgfKiKbuWiapNhElR7hoB5-MYTFJBqTDVhCCD9C_immK7a6_lM_Uid00QUl5q3iwRQ8m79TeusjKmARLzrbD?key=fVKZzan0hgeBjQi5ZaGDAQ" alt=""><figcaption></figcaption></figure>

### 🔵 Multi Select Variable

To use a variable in **Multi Select** mode, use the following syntax:

```
makefileCopyEdit$<variable_name>[]
```

For example:

```
bashCopyEdit$customer_segment[]
```

In Multi Select mode, the placeholder is replaced with a **comma-separated** list of selected values.

> 📝 Note: The query must be adjusted based on the type of datasource you're using. Most commonly, you'll need to convert the variable to an array structure.

***

#### Example: BigQuery (BQ) Datasource

**SQL Query Before Execution**

```sql
sqlCopyEditSELECT * FROM global_superstore_orders 
WHERE segment IN (
  SELECT segment FROM UNNEST(ARRAY['$customer_segment[]']) AS segment
)
```

**SQL Query After Execution**

```sql
sqlCopyEditSELECT * FROM global_superstore_orders 
WHERE segment IN (
  SELECT segment FROM UNNEST(ARRAY['Consumer', 'Corporate']) AS segment
)
```

***

### 📌 Summary

* **Single Select** → `$variable_name` → Replaced by a single value (e.g., `'Consumer'`)
* **Multi Select** → `$variable_name[]` → Replaced by a list of values (e.g., `'Consumer', 'Corporate'`)
* Always adapt your SQL structure (like using `ARRAY` or `UNNEST`) depending on your data source capabilities.

<figure><img src="https://lh7-rt.googleusercontent.com/docsz/AD_4nXcm0PzNqibmJq60brdimWVL08d1EPD6Yry4gSIeLqx79UVzJ4U9xo_wyHmFPjYMGVjLfePWGkQPGTNYqT4FgyCUsQAhLWGhFba1BYRq60ufcFHKq4jnCAIEH__isuGOFjhdDe8tHg?key=fVKZzan0hgeBjQi5ZaGDAQ" alt=""><figcaption></figcaption></figure>

## Linked Variables

Linked Variables allow you to create dependent relationships between multiple variables. For example: **Country → State → County**.

In a Linked Variable:

* Each variable remains a `{name, value}` pair.
* Variables are comma-separated when created, e.g., `country,state,county`.
* At query time, each variable can be referenced as `$country`, `$state`, and `$county`.

> **Example Linked Variable Query**
>
> ```sql
> SELECT 
>   country_name AS name1, country_code AS value1,
>   state AS name2, state_code AS value2,
>   city_name AS name3, city_code AS value3
> FROM World
> ```

***

## User Access Restrictions on Variable Values

Infinity allows restricting variable values based on **User**, **Role**, or **Distribution List (DL)** access.

* **User/Role/DL columns** must be included in the variable query.
* If the User/Role/DL column is `NULL`, the variable value is accessible to everyone.
* Otherwise, it must contain a comma-separated list of IDs permitted to access that value.

If the list is large, you are encouraged to create **Distribution Lists (DLs)** for easier management.

> **Example Access Control Query**
>
> ```sql
> WITH user_region_mappings AS (
>   SELECT '2' AS user, 'West' AS region UNION ALL
>   SELECT '116', 'Southeast' UNION ALL
>   SELECT '20', 'Central' UNION ALL
>   SELECT '71', 'Central' UNION ALL
>   SELECT '71', 'Northeast'
> )
> SELECT
>   value,
>   name,
>   COALESCE(STRING_AGG(user, ','), '') AS user
> FROM (
>   SELECT DISTINCT 
>     a.region AS value,
>     a.region AS name,
>     b.user
>   FROM file_Veeva_Komodo_ATC_Mapping a
>   LEFT JOIN user_region_mappings b
>   ON a.region = b.region
>   WHERE a.region IS NOT NULL
> ) grouped
> GROUP BY 1, 2
> ```

**Behavior:**

* "West" is accessible only to User ID 2.
* "Central" is accessible to User IDs 20 and 71.
* "Great Lakes" is accessible to everyone (because User is `NULL`).

***

## Key Points to Remember

* Variable names must be unique and lowercase.
* Variables provide dynamic SQL filtering.
* Linked Variables allow cascading, hierarchical relationships.
* User access can be controlled at the variable value level.

***