Quick BI:placeholders

Procedure

Create entry

Entry one: custom SQL editing page

1. Navigate to the SQL code editing page and click Placeholder Management.

   

2. In the Placeholder Management panel, the system lists the placeholders used in your SQL. Here, you can adjust the variable type and default value for each placeholder. Once configured, click Confirm.

   

Entry two: data processing interface

• In the Toolbar of the data processing interface, click Create Placeholder, enter the Variable Name, Variable Type, and Query Default Value, then click Confirm.

  

• When a dataset contains placeholders, you can click Field Outline -> Placeholder icon to access the Placeholder Management interface.In the placeholder management interface, click Create Placeholder to manually create a placeholder.

Entry three: dashboard editing page

1. To create a New Placeholder on the dashboard editing page, follow these steps.

   1. Click the icon to initiate a new placeholder creation.

      

   2. In the Placeholder Management interface, select Create New Placeholder.

2. Within the Create Placeholder interface, specify the Variable Name, Variable Type, and Query Default Value.

Configuration Items and Descriptions

Value placeholder

Pass a value or set of values through the query control on the dashboard.

Format: '$val{PlaceholderName}'

Sample SQL Code:

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

Expression placeholder

Pass a condition through the query control on the dashboard.

When users need to modify the filter method in the dashboard's filter conditions, an expression placeholder is used to incorporate the entire filter condition into the SQL.

Format: $expr{PhysicalFieldName:PlaceholderName}

Sample 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

Retrieve the current user's tag value using the tag placeholder.

Format: $tag{UserTagName}

For instance, to ensure an employee only sees data from the North area and specific products, configure the user's tag as follows:

• Area: North

• 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'
and
product in ('Product A', 'Product B')

System placeholder

System placeholders function like other placeholders and can be used in SQL and calculated fields. They support the following four system placeholders:

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

• User Nickname (within organization): $system{userNick}

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

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

Sample SQL Statement:

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

Condition placeholder

Pass a set of values through the tree dropdown query control on the dashboard.

Format: '$condition{PrimaryField, SecondaryField: PlaceholderName}'

The number of fields is limited to a maximum of 10 and must align with the tree structure of the query control.

Sample SQL Statement:

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

The configuration of the query control is as follows:

Acceleration placeholder

Acceleration placeholders accelerate data extraction in custom SQL that includes time parameters. You can place an acceleration placeholder in the WHERE condition. The Quick BI engine then dynamically replaces the time window during extraction and performs incremental offline extraction based on your configuration.

Format: $accel{PhysicalFieldName:PlaceholderName}

Sample SQL code:

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

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

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

Acceleration configuration: For a dataset that has an acceleration placeholder, select the placeholder field as the date field in the acceleration settings. Configure the extraction time range and incremental granularity to complete the acceleration task.

Scenarios

Scenario 1 - Dynamically Adjust Calculated Field Result Values

Scenario description

Use calculated fields and placeholders in expressions to dynamically alter the values of calculated fields by passing different placeholder values.

For instance, orders exceeding the placeholder value are considered large orders; otherwise, they are small orders. Adjust the criteria for order size dynamically using query controls, and the chart will refresh to reflect the changes.

Procedure

1. Create a placeholder.

   1. On the custom SQL editing page or dataset editing page, click Placeholder Management -> Create Placeholder.

   2. Input the variable name Price, select Value Placeholder as the type, choose Numeric for the variable type, set the query default value to Global Effectiveness, and optionally, the default value to 100.

      Note

      Placeholders used in calculated fields must have a globally effective default value.

      

2. Create Calculated Field.

   1. On the dataset editing page, click Create Field and establish the Order Scale field as depicted in the image.

      

      Field expression example:

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

   2. Click Confirm and Save the dataset.

3. Create Visualization Chart and Query Control.

   1. Begin analysis by creating a dashboard.

   2. Add a Bar Chart on the dashboard editing page as shown in the image.

   3. Drag the order size field to the category axis/dimension, place the price field on the value axis/measure, and click Update. The bar graph will display as follows:

      

      Currently, the measure field price distinguishes between large and small orders based on the default placeholder value of 100 for Price.

   4. Create a query control as illustrated.

      

   5. In the query condition settings interface, designate the placeholder Price as the query condition.

4. View the effect.

   You can now adjust the criteria for large and small orders by entering values in the query control.

   For example, when the query control value is set to 100, the large order amount is 607,200. If the query control value is changed to 150, the large order amount becomes 525,000.

   

Scenario 2 - Flexibly Switch Indicators or Analysis Dimensions

Scenario description

Utilize this feature to switch between multiple indicators and dimensions on a chart, avoiding clutter or redundancy from creating multiple charts.

Consider the example of switching indicator dimensions.

Procedure

1. Create Placeholder.

   1. On the custom SQL editing page or dataset editing page, click Placeholder Management -> Create Placeholder.

   2. Enter the variable name Indicator, set the type to Value Placeholder, choose Text for the variable type, set the query default value to Global Effectiveness, and the default value to Unit Price.

2. Create Calculated Field.

   1. Click Create Field and establish the Data field as shown in the image.

      Field expression example:

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

      Note

      Text type placeholders should be enclosed in single quotes, such as '$val{Indicator}'.

   2. Click Confirm and Save the dataset.

3. Create Visualization Chart and Query Control.

   1. Begin analysis by creating a dashboard.

   2. On the dashboard editing page, add a Bar Chart as shown in the image.

      

   3. Drag the Area field to the Category Axis/Dimension, place the Data segment on the Value Axis/Measure, and click Update. The bar chart will display as follows:

   4. Create a query control as depicted in the image.

   5. In the query condition settings interface, set the placeholder Indicator as the query condition. Opt for Manual Input as the source of option values and configure the Manually Entered values.

4. View the Effect.

   You can now switch between different indicators by filtering the values in the query control.

   

Scenario 3 - Dynamically Adjust Auxiliary Line

Scenario description

Link the auxiliary line's value to a placeholder so that when the placeholder's value is updated, the auxiliary line adjusts accordingly.

For example, if the auxiliary line represents a monthly sales KPI, you can dynamically update its position each month through the query control without needing to edit the report again.

Procedure

1. Create a placeholder.

   Note

   Auxiliary line placeholders only support numeric types.

   1. On the dashboard editing page, create a placeholder as illustrated.

   2. Input the variable name Target, set the variable type to Numeric, the query default value to Global Effectiveness, and the default value to 4,000,000.

      Note

      Placeholders used in auxiliary lines must have a globally effective default value.

      

2. Set Auxiliary Line.

   1. In the Analysis tab, locate the auxiliary line and click the pencil icon to edit.

   2. Configure and add the auxiliary line as shown, setting its value to the placeholder Target.

      The chart now displays the auxiliary line at the 4 million mark.

3. Create Query Control.

   1. Generate a query control as shown in the image.

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

4. View the Effect.

   Alter the auxiliary line's position by changing the values in the query control.

   

Scenario 4 - Use Placeholders in SQL Code to Achieve Dynamic Adjustment and Filtering of Field Result Values

Scenario description

Incorporate placeholders into SQL code when creating a dataset to dynamically adjust and filter field result values by modifying placeholder values.

Procedure

1. On the dataset editing page, click Click To Create Dataset Using SQL Code on the canvas or Create Table with SQL Code on the left panel.

   

2. After entering the SQL code

   

   Sample SQL Statement:

   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 Level'
   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. Access the placeholder management interface and input the default value.

   

   Note

   Placeholders applied after the select statement must have a globally effective default value.

   In this example, the placeholder "profit_range" requires a globally effective default value.

5. Execute the query by clicking Run to view the results.

   

6. Finalize your edits by clicking Confirm Edit and then Save the dataset.

7. Create Visualization Chart and Query Control.

   1. Begin analysis by creating a dashboard.

   2. On the dashboard editing page, add a Pie Chart as depicted in the image.

      

   3. Create a query control as illustrated.

      

   4. View the Effect

      You can now modify the profit range criteria by entering values in the query control.

      

Scenario 5: Enable extraction acceleration for a dataset that contains an acceleration placeholder

Scenario description

You can add an acceleration placeholder to the WHERE condition of a custom SQL statement and set the partition size in the acceleration settings. This enables dynamic offline data extraction and incremental writing. This method is useful if the source table contains a large volume of data, which causes the original custom SQL query to time out and prevents you from configuring extraction acceleration.

Procedure

1. Create a placeholder.

   Add an acceleration placeholder to the WHERE condition of your custom SQL statement.

   Focus on the core query logic and let the placeholder handle time filtering.

   

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

2. Set a query default value.

   To ensure that the SQL runs quickly during editing and preview, you can set a default value for the placeholder.

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

   

3. Configure extraction acceleration.

   After you save the dataset, you can configure extraction acceleration.

   1. Enable the Quick BI engine.

      

   2. For Extraction Range, select Specify Date Range. For Date Field, select the field that corresponds to the acceleration placeholder, such as report_date.

      

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

4. Verify the query and acceleration effect

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

   2. On the Performance Diagnostics page of the chart, check the Widget Loading Duration and see whether the Acceleration Engine is enabled in the Widget Loading Details to confirm that acceleration is working.