Quick BI:Use Custom SQL Statement to Create a Dataset

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 Data Source in the upper-right corner.

   • 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 Create Table Using SQL Code on the left-side navigation pane.

2. Enter the SQL code and click Run.

   

   Sample statement:

   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}

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

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.

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

Placeholder

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.

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.

   Note

   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.

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.

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

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.

   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.