Quick BI:Placeholder

Procedure

Create entry

Entry one: Custom SQL editing page

1. On the SQL code editing page, click Placeholder Management.

   

2. In the Placeholder Management panel, the system will automatically identify the placeholders used in your SQL and list them. You can then edit the variable type and default value. Once configured, click Confirm.

   

Entry two: Dataset editing page

1. Click the icon in the top menu bar to manage placeholders on the dataset editing page.

2. Click Create placeholder to add a new placeholder manually.

   

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 the creation of a new placeholder.

      

   2. In the Placeholder Management interface, proceed to create a new placeholder.

2. In the Create Placeholder interface, input the variable name, variable type, and default query value.

Configuration items and description

Value placeholder

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

Format: '$val{Placeholder name}'

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 the start date of the date range control
AND report_date < '$val{report_date_ph.get(1)}' -- Date type, get the end date of the date range control

Expression placeholder

Pass a condition through the query control on the dashboard.

An expression placeholder is necessary when users need to modify the filtering method in the dashboard's filtering conditions, allowing the entire filtering condition to be passed into the SQL.

Format: $expr{Physical field name:Placeholder name}

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

Use the tag placeholder to retrieve the current user's tag value during access.

Format: $tag{User tag name}

For instance, to ensure an employee can only view data from the North area and for Products A and B, set 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 are used consistently with other placeholders in SQL and calculated fields. They support the following four types:

• 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{Primary field, Secondary field: Placeholder name}'

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 query control configuration is as follows:

Scenarios

Scenario 1 - Dynamically adjust calculated field result values

Overview

Set a calculated field and incorporate a placeholder in the expression to dynamically adjust the calculated field's value by passing the placeholder's value through the query control, which will refresh the chart accordingly.

For instance, orders exceeding the placeholder value are deemed large orders, while others are small. You can dynamically alter the criteria for large and small orders by changing the placeholder value, and the chart will update to reflect this.

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 Price, select Value placeholder as the type, choose Numeric as the variable type, set the default query value to Global effect, and optionally set 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 illustration.

      

      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. Click Start analysis -> Create dashboard.

   2. On the dashboard editing page, add a Bar chart as shown in the illustration.

      

   3. Drag the Order scale field to the Category axis/Dimension, place the price field in the Value axis/Measure, and click Update. The bar chart will appear as follows:

      

      Currently, the price field differentiates large and small orders based on the placeholder Price's default value of 100.

   4. Create a query control as illustrated.

      

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

4. View the effect.

   Adjust the criteria for large and small orders by inputting values into the query control.

   For example, with a query control value of 100, the large order amount is 607,200. When the value is 150, the large order amount changes to 525,000.

   

Scenario 2 - Flexible switching of indicators or analysis dimensions

Overview

Use placeholders to switch between multiple indicators and dimensions on a chart to avoid clutter or redundancy from creating multiple charts.

Example: 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, select Value placeholder as the type, choose Text as the variable type, set the default query value to Global effect, and set the default value to Unit price.

2. Create calculated field.

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

      

      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, for example '$val{Indicator}'.

   2. Click Confirm and Save the dataset.

3. Create visualization chart and query control.

   1. Click Start analysis -> Create dashboard.

   2. On the dashboard editing page, add a Bar chart as shown in the illustration.

      

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

   4. Create a query control as illustrated.

   5. In the query condition settings, set the placeholder Indicator as the query condition, select Manual input as the option value source, and configure the Manually entered values.

4. View the effect.

   Switch between different indicators by filtering the values of the query control.

   

Scenario 3 - Dynamically adjust auxiliary lines

Overview

Link the auxiliary line's value to a placeholder to dynamically update its position when the placeholder's value is changed.

For instance, if the auxiliary line represents a monthly sales KPI, you can adjust it monthly through the query control without modifying the report again.

Procedure

1. Create placeholder

   Note

   Numeric types are the only supported placeholders in auxiliary lines.

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

   2. Enter the variable name Target, set the variable type to Numeric, set the default query value to Global effect, and set 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. On 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. Create a query control as illustrated.

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

4. View the effect.

   Change the auxiliary line's position by altering the values in the query control.

   

Scenario 4 - Use placeholders in SQL code to achieve dynamic adjustment and filtering of field result values

Overview

Incorporate placeholders into SQL code when creating a dataset to dynamically adjust and filter field result values by changing the placeholders' values.

Procedure

1. On the dataset editing page, click Create The First Table Using SQL Code or Create table with SQL code in the left panel.

   

2. Enter 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. Click Run to view the results.

   

6. Click Confirm Edit and Save the dataset.

7. Create visualization chart and query control.

   1. Click Start analysis -> Create dashboard.

   2. On the dashboard editing page, add a Pie chart as depicted.

      

   3. Create a query control as illustrated.

      

   4. View the effect.

      Adjust the profit range criteria by entering values in the query control.