Placeholders

Procedure

Creation entry points

Custom SQL editor

1. On the SQL editor page, click Placeholder Management.

   

2. In the Placeholder Management panel, the system automatically identifies and displays the placeholders that you use in SQL. You can modify the variable type and default value for each placeholder, and then click OK.

   

Data preparation page

• On the Toolbar of the data preparation page, click New Placeholder. Enter a Variable Name, Variable Type, and Default Query Value, then click OK.

  

• If a dataset already contains placeholders, go to Field Outline > Placeholders and click the icon to open the Placeholder Management page. On the Placeholder Management page, click New Placeholder to create one manually.

Dashboard editor

1. On the dashboard editor page, create a new placeholder using one of the following methods:

   1. Click the icon to create a new placeholder.

      

   2. On the Placeholder Management page, click New Placeholder.

2. On the New Placeholder page, enter a Variable Name, Variable Type, and Default Query Value.

Parameters

Value placeholder

You can use a value placeholder to pass a single value or a set of values from a query control on a dashboard.

Format: '$val{placeholder_name}'

Example SQL code:

SELECT * FROM tablename
WHERE area in ('$val{area_ph}') -- Text type, multi-select
AND name = '$val{name_ph}' -- Text type, single-select
AND number = $val{number_ph} -- Numeric type
AND report_date > '$val{report_date_ph.get(0)}' -- Date type, get the start date of a date range control
AND report_date < '$val{report_date_ph.get(1)}' -- Date type, get the end date of a date range control

Expression placeholder

You can use an expression placeholder to pass a condition from a query control on a dashboard.

An expression placeholder lets users dynamically change the filtering logic on a dashboard by passing the entire filter condition to the SQL query.

Format: $expr{physical field name:placeholder name}

Example SQL code:

SELECT report_date,
 order_level,
 shipping_type,
 area,
 price,
 order_number
from company_sales_record
where $expr{report_date :report_date}
and $expr{order_level :order_level}
and $expr{order_number :order_number}

Tag placeholder

A tag placeholder retrieves the tag value of the current user.

Format: $tag{user tag name}

For example, to restrict an employee's data access to only the North China region and for products A and B, you can configure the user's tags as follows:

• Region: North China

• Product: Product A, Product B

select * from table_name
where 
area = '$tag{Area}'
and
product in ('$tag{Product}')

select * from table_name
where 
area = 'North China'
and
product in ('Product A', 'Product B')

System placeholder

You can use system placeholders in the same way as other placeholders in SQL queries and calculated fields. The following four system placeholders are supported:

• Username (login account): $system{accountName}

• User nickname (in the organization): $system{userNick}

• User ID (in the Quick BI system): $system{userId}

• Third-party system user ID (Alibaba Cloud ID): $system{accountId}

Example SQL code:

select
  *
from
  company_sales_record
where
  customer_name = '$system{accountName}'
  and customer_name = '$system{userNick}'

Conditional placeholder

You can use a conditional placeholder to pass a set of values from a tree-structured dropdown query control on a dashboard.

Format: '$condition{primaryField, secondaryField: placeholderName}'

You can specify up to 10 fields. The field hierarchy must match the structure of the tree-structured query control.

Example SQL code:

select
 *
from
 company_sales_record
where 
 $condition{product_type,product_sub_type,product_name :product_para}

The query control is configured as follows:

Acceleration placeholder

An acceleration placeholder enables extraction acceleration for custom SQL queries with time-based parameters. When placed in the WHERE clause, the Quick Engine uses the placeholder to dynamically replace the time window during data extraction and perform incremental offline extraction based on your settings.

Format: $accel{physical field name:placeholder name}

Example SQL code:

select
  *
from
  company_sales_record
where
  $accel{report_date:report_date}

Default Query Value: To prevent custom SQL queries from timing out, you can set a default value for the acceleration placeholder.

Example: The default value must be a complete filter expression, such as report_date<'2025-04-01', not just a date value.

Acceleration Settings: For a dataset that contains an acceleration placeholder, you can select the placeholder field as the Date Field in the acceleration settings. Then, you can configure the extraction time range and incremental granularity to configure the acceleration task.

Use cases

Scenario 1: Adjust calculated field results

Overview

Passing different values to the placeholder dynamically adjusts the calculated field's output.

For example, you can define an order as a "large order" if its value exceeds a placeholder's value, and a "small order" otherwise. By using a query control to pass a new value to the placeholder, you can dynamically change this threshold, and the chart will update accordingly.

Procedure

1. Create a placeholder.

   1. On the custom SQL or dataset editor page, click Placeholder Management > New Placeholder.

   2. Enter Price as the variable name, select value placeholder as the type and Numeric as the variable type. Set the default query value scope to Global and the default value to 100 (optional).

      Note

      A placeholder used in a calculated field must have a default value with a Global scope.

      

2. Create a calculated field.

   1. On the dataset editor page, click New Field and create a field named Order Size as shown in the image.

      

      Example field expression:

      case when [price]>$val{Price}
      then 'Large Order'
      else 'Small Order'
      end

   2. Click OK, then Save the dataset.

3. Create a chart and a query control.

   1. Click Start Analysis > Create Dashboard.

   2. On the dashboard editor page, add a bar chart as shown in the image.

   3. Drag the Order Size field to Category Axis/Dimension and the price field to Value Axis/Measure. Click Update. The bar chart appears as follows:

      

      Based on the default value of 100 for the 'Price' placeholder, the 'price' measure is categorized into "Large Order" and "Small Order".

   4. Create a new query control as shown in the image.

      

   5. In the query condition settings, set the placeholder Price as the query condition.

4. View the result.

   You can enter a number in the query control to dynamically adjust the threshold for large and small orders.

   For example, if the query control value is 100, the total amount for large orders is 607,200. If the value is 150, the total amount for large orders is 525,000.

   

Scenario 2: Switch metrics or dimensions

Overview

Displaying multiple metrics or dimensions in a single chart can be cluttered, while creating separate charts for each is redundant. You can use placeholders to flexibly switch the fields in a chart.

This example shows how to switch a metric.

Procedure

1. Create a placeholder.

   1. On the custom SQL or dataset editor page, click Placeholder Management > New Placeholder.

   2. Enter Metric as the variable name, select value placeholder as the type and Text as the variable type. Set the default query value scope to Global and the default value to Unit Price.

2. Create a calculated field.

   1. Click New Field and create a field named Data as shown in the image.

      Example field expression:

      case when '$val{Metric}'='Order Amount' then [Order Amount]
      when '$val{Metric}'='Profit Amount' then [Profit Amount]
      when '$val{Metric}'='Unit Price' then [Unit Price]
      when '$val{Metric}'='Shipping Cost' then [Shipping Cost]
      else 0
      end

      Note

      Enclose text-type placeholders in single quotes, for example, '$val{Metric}'.

   2. Click OK, then Save the dataset.

3. Create a chart and a query control.

   1. Click Start Analysis > Create Dashboard.

   2. On the dashboard editor page, add a bar chart as shown in the image.

      

   3. Drag the Region field to Category Axis/Dimension and the Data field to Value Axis/Measure. Click Update. The bar chart appears as follows:

   4. Create a new query control as shown in the image.

   5. In the query condition settings, set the placeholder Metric as the query condition. For Option Value Source, select Manual Input and configure the values for Manual Entry.

4. View the result.

   You can select different values in the query control to switch between metrics.

   

Scenario 3: Dynamically adjust a reference line

Overview

You can bind a reference line to a placeholder, allowing it to update automatically when the placeholder's value changes.

For example, if a reference line represents the monthly sales KPI, you can use a query control to adjust its value each month without editing the report.

Procedure

1. Create a placeholder.

   Note

   Placeholders used in reference lines must be numeric.

   1. On the dashboard editor page, create a new placeholder as shown in the image.

   2. Enter Target as the variable name, select Numeric as the variable type. Set the default query value scope to Global and the default value to 4000000.

      Note

      A placeholder used in a reference line must have a default value with a Global scope.

      

2. Set the reference line.

   1. On the Analysis tab, find Reference Line and click the pencil icon next to it.

   2. Add a reference line and set its value to the placeholder Target as shown in the image.

      A reference line at the 4,000,000 mark appears on the chart.

3. Create a query control.

   1. Create a new query control as shown in the image.

   2. Set the placeholder Target as the query condition and click OK.

4. View the result.

   You can change the value in the query control to adjust the position of the reference line.

   

Scenario 4: Use SQL placeholders to adjust and filter results

Overview

When creating a dataset with custom SQL, you can reference placeholders in your code. By adjusting the placeholder values, you can dynamically adjust field results and filter data.

Procedure

1. On the dataset editing page, click Create Dataset from SQL Code on the canvas or Create Table from SQL Code in the left panel.

   

2. Enter your SQL code.

   

   Example SQL code:

   SELECT report_date, order_level, shipping_type,price,order_number,area,
    case when profit_amt< ${profit_range} then 'Loss' 
    when profit_amt> ${profit_range} then 'Profit'
    else 'Break-even'
    end 'order_rank'
   from company_sales_record
   where $expr{report_date :report_date}
   and $expr{order_level :order_level}
   and $expr{order_number :order_number}

3. Click Placeholder Management.

   

4. On the Placeholder Management page, enter the default values.

   

   Note

   If a placeholder is used in a SELECT statement, you must set a default value with a Global scope.

   In this example, the "profit_range" placeholder must have a default value with a Global scope.

5. Click Run to see the results.

   

6. Click Confirm Edit and save the dataset.

7. Create a chart and a query control.

   1. Click Start Analysis > Create Dashboard.

   2. On the dashboard editor page, add a pie chart as shown in the image.

      

   3. Create a new query control as shown in the image.

      

   4. View the result

      You can enter a number in the query control to dynamically adjust the profit range threshold.

      

Scenario 5: Enable extraction acceleration

Overview

This is useful for large tables where custom SQL queries might time out and prevent extraction acceleration configuration.

Procedure

1. Create a placeholder.

   In the WHERE clause of your custom SQL, add an acceleration placeholder.

   This allows you to focus on the core query logic, while the placeholder handles time-based filtering.

   

   Example: Add a report_date acceleration placeholder to the WHERE clause: where $accel{report_date:report_date}.

2. Set a default query value.

   Set a default value for the placeholder to ensure the SQL query runs quickly during editing and previewing.

   In the dataset's Placeholder Management panel, set the default value for the acceleration placeholder to a complete expression, such as: report_date < '2025-04-01'.

   

3. Configure extraction acceleration.

   After the dataset is saved, you can configure extraction acceleration.

   1. Enable the Quick Engine.

      

   2. For Extraction Scope, select Specify Date Range and select the field associated with the acceleration placeholder (for example, report_date) as the Date Field.

      

   3. Configure the extraction date range and incremental range, then save and start the acceleration task.

4. Verify query and acceleration performance

   1. Use the dataset to create a chart and run a query.

   2. On the chart's performance diagnosis page, check the Component Loading Time. Under Component Loading Details, verify that the Acceleration Engine is enabled.