Quick BI:Placeholder

Procedure

Entry point

Entry 1: Custom SQL editor

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

   

2. In the Placeholder Management panel, the system automatically identifies the placeholders that you use in SQL statements and displays them in the Placeholder Management panel. You can modify the variable type and default value of the placeholder. After the configuration is complete, click OK.

   

Entry 2: Dataset edit page

1. Click the icon in the top navigation bar to manage placeholders.

2. Click Create Placeholder to create a placeholder.

   

Entry point 3: Edit a dashboard

1. On the dashboard editing page, perform the following steps to create a placeholder:

   1. Click the icon to create a placeholder.

      

   2. On the Placeholder Management page, create a placeholder.

2. On the Create Placeholder page, set Variable Name, Variable Type, and Query Default Value.

configuration items and description

Value Placeholder

You can enter a value or a group 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 choices
AND name = '$val{name_ph}' -- text type
AND number = $val{number_ph} -- numeric
AND report_date > '$val{report_date_ph.get(0)}' -- date type gets date range control start date
AND report_date < '$val{report_date_ph.get(1)}' -- date type to get the date range control end date 

Expression Placeholder

You can specify a condition by using the query control on the dashboard.

When the filter conditions on the dashboard, users can freely modify the filtering method, you need to use expression placeholders to pass the entire filter conditions into 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 placeholders

Obtains the tag value of the current access user by using the tag placeholder.

Format: $tag {User tag name}

The following example shows the configuration of tags for a user who is granted the permissions to view only the data of Product A and Product B in the North area.

• Area: North

• Product: Product A, Product B

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

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

System Placeholder

The usage method is the same as that of other placeholders. It can be used in SQL and calculated fields. The following four system placeholders are supported:

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

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

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

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

SQL sample code:

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

Conditional Placeholder

You can specify a set of values by using the drop-down tree query control on the dashboard.

Format: '$condition {level -1 field, level -2 field: placeholder name}'

The maximum number of fields is 10, which must be consistent with the tree structure of the query control.

SQL sample code:

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

Configure the query control as follows:

Scenario

Scenario 1-Dynamically adjust the result value of a calculated field

Scenario description

Specifies a calculated field and references a placeholder in an expression. The value of the calculated field is adjusted by passing the value of the placeholder.

For example, if the value exceeds the placeholder value, it is a large order. Otherwise, it is a small order. You can pass the placeholder value through the query control, dynamically adjust the measurement standards of large and small orders, and refresh the chart accordingly.

Procedure

1. Create Placeholder

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

   2. Enter a variable name Price in the Value Placeholder field. Set the Variable Type parameter to Numeric. Default Value: Global. Default Value: 100. This parameter is optional.

      Note

      A placeholder that uses a calculated field must have a default value that takes effect globally.

      

2. Create a calculated field.

   1. On the dataset edit page, click Create Field and create the Order Size field as shown in the following figure.

      

      Example of a field expression:

      case when [price]>$val{Price}
      then 'big order'
      else 'small order'
      end

   2. Click OK and Save.

3. Create visual charts and query controls.

   1. Click Start Analysis-> Create Dashboard.

   2. On the dashboard edit page, add a column chart as shown in the following figure.

      

   3. Drag the Order Size field to Category Axis /Dimension, drag the price field to Value Axis /Measure, and then click Update. The following column chart is displayed:

      

      In this case, we can see that the measure field price is 100 based on the default value of the placeholder Price to distinguish between large orders and small orders.

   4. Create a query control as shown in the figure.

      

   5. On the Query Condition Setting page, set the placeholder Price as a query condition.

4. View the effect.

   In this case, you can adjust the measure of the size order by entering a numeric value for the query control.

   For example, when the value of the query control is 100, the large order amount is 607200; when the value of the query control is 150, the large order amount is 525000.

   

Scenario 2: Change metrics or analysis dimensions in a flexible manner

Scenarios

If you have multiple metrics and dimensions to display, but the content is not clear enough when you put them in the same chart, and the content is redundant when you build multiple charts, you can use this capability to flexibly switch chart fields.

In this example, the metric dimension is switched.

Procedure

1. Create a placeholder.

   1. In the left-side navigation pane, choose Placeholder Management > Create Placeholder.

   2. In the Indicator field, set the Type parameter to Value Placeholder and the Type parameter to Text. The Default Value parameter is set to Global and the Default Value parameter is set to Unit Price.

2. Create a calculated field.

   1. Click Create Field and create a data field as shown in the following figure.

      Example of a field expression:

      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}'='transportation cost' then [transportation cost]
      else 0
      end

      Note

      Enclose text type placeholders in single quotation marks ("). Example: '$val {metric}'.

   2. Click OK and Save.

3. Create visual charts and query controls.

   1. Click Start Analysis-> Create Dashboard.

   2. On the dashboard edit page, add a column chart as shown in the following figure.

      

   3. Drag the area field to Category Axis /Dimension, drag the data segment to Value Axis /Measure, and then click Update. The column chart is displayed as follows:

   4. Create a query control as shown in the figure.

   5. On the Query Criteria Settings page, set the placeholder indicator as a query condition, select Manually Entered as the option value source, and configure the Manually Entered value.

4. View the effect.

   In this case, you can filter the value of the query control to switch between different metrics.

   

Scenario 3-Dynamically adjust auxiliary lines

Scenario description

Bind the value of a guide line to a placeholder. When you assign a value to a placeholder, the guide line changes.

For example, the auxiliary line is the monthly sales KPI, and the position of the auxiliary line can be dynamically updated only by adjusting the query control every month, and the report does not need to be modified again.

Procedure

1. Create Placeholder

   Note

   Placeholders in auxiliary lines only support numeric.

   1. On the dashboard editing page, create a placeholder as shown in the figure.

   2. Enter a variable name Target. Set the variable type to Value. Default value: Global. Default value: 4000000.

      Note

      Placeholders used in guides must have globally effective default values.

      

2. Set the auxiliary line.

   1. On the Analysis tab, find the guide line and click the pen icon on the right.

   2. Set Add Guides as shown and set the value to the placeholder Target.

      You can see that the guide line 4 million appears on the chart.

3. Create a query control.

   1. Create a query control as shown in the figure.

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

4. View the effect.

   You can adjust the position of the guide line by toggling the value of the query control.

   

Scenario 4-Use placeholders in the SQL code to dynamically adjust and filter field result values

Scenario description

When you use SQL code to create a dataset, placeholders are referenced in the code. You can adjust the values of placeholders to dynamically adjust and filter the result values of fields.

Procedure

1. On the dataset editing page, click Use SQL Code to Create First Table on the canvas or Use SQL Code to Create Table in the left-side navigation pane.

   

2. After entering SQL code

   

   SQL sample 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 'flat'
    end 'order gradation'
   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. Enter the placeholder management interface and enter the default value.

   

   Note

   If a placeholder is applied after a SELECT statement, you must set a globally effective default value.

   In this example, the placeholder "profit_range" must be set to a default value that takes effect globally.

5. Click Run to view the running result.

   

6. Click Confirm and Save.

7. Create visual charts and query controls.

   1. Click Start Analysis-> Create Dashboard.

   2. On the dashboard edit page, add a pie chart as shown in the following figure.

      

   3. Create a query control as shown in the figure.

      

   4. View the effect

      In this case, you can adjust the measure of the profit range by entering a numeric value for the query control.