# 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.

***


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.getinfinity.app/user-guide/comprehensive-user-guide/12.-advanced-topics/chart-variables.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.