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

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. 
3. Click on Add Variable.

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

SELECT DISTINCT country_name as Name, country_code as value FROM Countries

Single Select vs. Multi Select

🟢 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

sqlCopyEditSELECT * FROM global_super_store WHERE Segment = '$customer_segment'

SQL Query After Execution

sqlCopyEditSELECT * FROM global_super_store WHERE Segment = 'Consumer'

🔵 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

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

SQL Query After Execution

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.

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

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

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.

---