Quick BI:Custom SQL

Limitations

API data sources do not support ad hoc queries.

Enter SQL code

You can create an ad hoc query in the following ways.

1. Go to the ad hoc query page.

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

   • Entry 2: On the relational model canvas of the dataset editor, select a data source. If no data tables are on the canvas, click Click to use SQL code to create a dataset on the canvas or Create table with SQL code in the left-side panel.

   • Entry 3: On the physical modeling canvas of the dataset editor, click Create table with SQL code in the left-side panel.

     

2. After you enter the SQL code, click Run.

   

   The following code provides a sample SQL statement:

   SELECT  report_date,
           or*******el,
           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 :or********er}

   After the query runs successfully, you can preview the results on the Result tab.

3. Click Create Dataset to save the dataset created from the ad hoc query.

Modify SQL code

You can modify SQL code in three ways:

• Click the icon to the right of the target table.

• Click the target table on the canvas. In the Table Details section at the bottom, click Edit SQL.

• Click the icon to the right of the target table and select Edit SQL.

  

SQL syntax

• Ad hoc queries mainly support SELECT statements. INSERT, UPDATE, DELETE, and stored procedures are not supported.

• The query syntax must conform to the database syntax of your data source. For example, MySQL data sources require MySQL query syntax. Database-specific syntaxes such as find() and findone() in MongoDB are not supported in Quick BI. Use SELECT statements to query data in ad hoc queries.

• To add comments to an ad hoc query, use the -- Comment format. A space is required after the two hyphens (--).

  

  The following code provides a sample SQL statement:

  SELECT  area,  -- Area
          price  -- Price  
  from    company_sales_record

• If you join multiple tables in an ad hoc query, make sure that you use table aliases and column aliases correctly. Alias syntax can vary across databases.

• An ad hoc query has no row limit, but the query cannot exceed 65,536 characters. If necessary, split or optimize the query, or create multiple datasets and join them.

Placeholders

Quick BI supports two types of placeholders for passing parameters in SQL: value placeholders and expression placeholders.

Configuration

Write your SQL statement and add placeholders based on your business requirements.

1. On the SQL editor page, click Parameter Settings.

   

2. In the Parameter Settings panel, configure the placeholders and click OK.

   

   For more information about the configuration items, see Create a dataset.

   Note

   If a placeholder is used in a SELECT clause, you must set a global default value for it.

   For example, you add a placeholder named profit_range in the SELECT clause when creating a dataset, as shown in the following 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 'Break-even'
    end as 'Or*er Level'
   from company_sales_record
   where $expr{report_date :report_date}
   and $expr{order_level :order_level}
   and $expr{or**********************er}

   If you do not set a default value, the query fails to run.

   

   You must set a default query value for profit_range in the parameter settings.

   

   After you set the default value, the query runs successfully. The following figure shows the result:

   

3. Click Save to save the dataset.

4. In the top menu bar, click the icon or click the icon and select Create Dashboard.

   

5. On the dashboard editor page, add a query condition and bind it to a placeholder.

   For example, when setting a query condition, you can use a single-select drop-down list to filter area and bind it to a value placeholder to pass a single value.

   Use a text filter on or********er and bind it to an expression placeholder to pass a condition.

6. When you query data on the dashboard, the values from the query controls are passed to the corresponding placeholders in your SQL.

   For example, the following figure shows the query conditions on a dashboard.

   The corresponding SQL statement is as follows:

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

   Placeholder usage example:

   SELECT * FROM tablename
   WHERE area in ('$val{area_ph}') -- Text type, multi-select
   AND name = '$val{name_ph}' -- Text type, single-select
   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 picker
   AND report_date < '$val{report_date_ph.get(1)}' -- Date type, get the end date of the date range picker

For more information about placeholder use cases, see Placeholders.