Quick BI:Custom SQL

Enter SQL code

You can add custom SQL using one of the following methods:

1. Navigate to the custom SQL page.

   • Entry 1: On the data source page, click Create Dataset By SQL in the upper-right corner.

   • Entry 2: On the dataset editing page, select the data source in the left panel. If there is no data table on the canvas, click Create The First Table By Using SQL Code on the canvas or Create table by SQL code in the left panel.

2. After entering the SQL code, click Run.

   

   Sample SQL 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 a successful run, you can preview the results on the Run Result tab.

3. Click Confirm Edit.

   Save the dataset you created with the custom SQL statement.

Modify SQL code

To modify the SQL code, you can:

• Hover the mouse over the table on the canvas and click the Edit Code icon.

• Click the target table on the canvas. In the right panel, click Edit Code.

Placeholders

Quick BI allows for parameter passing using placeholders. This feature enables flexible data analysis during report viewing and data analysis by passing placeholder values to SQL through a query control. Quick BI supports both value and expression placeholders, which you can configure as needed.

Configuration method

Compose SQL to meet business requirements and incorporate placeholders.

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

   

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

   

   For configuration items, refer to Placeholder Management.

   Note

   If a placeholder is used after the select statement, you must assign a globally effective default value.

   For instance, when creating a dataset, include the placeholder "profit_range" after the select statement 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'Profitable'
    else 'Flat'
    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}

   Without a default value, the code will not run correctly.

   

   Set the query default value for "profit_range" in Placeholder Management.

   

   After setting the default value, the code will run correctly, as shown:

   

3. Click Save to store the dataset.

4. In the top menu bar, select Start Analysis > Create Dashboard.

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

   For instance, to set query conditions, use a drop-down single selection to filter area, bind the SQL placeholder, and pass in one value.

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

6. When the dashboard is queried, it will transmit the specified content to the value and expression placeholders based on the query conditions.

   For example, the dashboard query conditions are illustrated below.

   The corresponding SQL is as follows:

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

   An example of using placeholders is as follows:

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

For scenarios where placeholders are applied, refer to Placeholders.