All Products
Document Center

Quick BI:Use an SQL Statement to Create a Dataset

Last Updated:Apr 26, 2024

In data analysis, some complex logic or models can be used to create data tables by using custom SQL. For complex scenarios or specific data analysis scenarios, you can also pass parameters to SQL statements.


You have obtained data.

Enter and execute an SQL statement

You can use one of the following methods to enter an SQL statement.

  1. Log on to the Quick BI console to go to the page on which you can enter an SQL statement.

    • Entry 1: On the Data Sources page, click Create Dataset in the upper-right corner. image

    • Entry 2: In the left-side navigation pane, select a data source. If no data table is available on the canvas, click Use SQL Code to Create First Table on the canvas or Use SQL Code to Create Table on the left-side navigation pane. image.png

  2. Enter the SQL code and click Run.


    Sample statement:

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

    After the task is run, you can view the result on the Result tab. image.png

  3. Click Confirm Edit.

    Save the dataset that you created by using a custom SQL statement.

Modify SQL statements

You can use one of the following methods to modify the SQL code:

  • Move the pointer over the table on the canvas and click the Edit Code icon. image.png

  • Click the destination table on the canvas. In the right pane, click Edit Code. image.png


Quick BI provides placeholders to pass parameters. When you view reports and analyze data, you can use the query control to pass the placeholder values to SQL statements. This allows you to analyze data in a flexible manner. The Quick BI supports value placeholders and expression placeholders. You can configure them as required.



Use scenarios


Value placeholder

You can enter a value or a group of values through the query control on the dashboard.


If the placeholder indicates a date value, you must specify the date format to determine the format of the input value.

In most scenarios where parameters need to be passed, you can add a placeholder to the SQL statement that you want to execute to pass parameters.

'$val {placeholder name}'

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.

For example, in the following figure, you can set Profit> 50 or Profit <50 for the dashboard. In this case, you must pass in the SQL statement along with the operator. 参数传参

$expr {Physical field name: placeholder name}

Configuration methods

Write SQL statements based on your business needs and add placeholders.

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


  2. In the Placeholder Management panel, configure the following placeholders and click OK.


    See Placeholder Management for configuration items.


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

    For example, if you add the placeholder "profit_range" after the SELECT statement when you create a dataset, the code is as follows:

    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}

    In this case, if you do not set the default value, the system cannot run as expected.


    You must set the query default value of profit_range in placeholder management.


    After the default value is set, it can run normally. The running result is as follows:


  3. Click Save to save the dataset. image.png

  4. In the top navigation bar, choose Start Analysis > Create Dashboard.

  5. On the dashboard editing page, add query conditions and bind placeholders.

    For example, when you set a query condition, you can use a single-selection filter area from the drop-down list and bind an SQL placeholder. You can specify one value. image.png

    Use text filtering to filter order_number, bind SQL parameters, and pass in a condition. image.png

  6. When a dashboard is queried, the corresponding content is passed into the value placeholder and expression placeholder based on the query conditions.

    For example, the following figure shows the query conditions for a dashboard. image.png

    SQL statement that corresponds to the filter condition:

    SELECT * FROM company_sales_record
    WHERE area ='North' 
    AND order_number > 0

    The SQL statement in the following example includes placeholders:

    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 

For more information about how to use placeholders, see Placeholders.


Compatible with historical writing:

Value placeholder (formerly placeholder) history: ${placeholder name}

The history of the placeholder (original parameter) in an expression: ${Physical field name: Parameter name}

What to do next

If the fields that you want to analyze are in different data tables, you can Quick BI associate the data tables. For more information, see Build a model.