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.

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

   • Method 2: In the relational model canvas on the dataset editing page, after you select a data source, if the canvas contains no data tables, click Create Dataset from SQL Code on the canvas or Create Table from SQL Code in the left panel.

   • Entry 3: On the physical modeling canvas of the dataset editing page, click Create Table with SQL in the left-side panel.

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

   

   For example:

   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 see the result preview on the Run Results tab.

3. Click Confirm Edit to save the dataset created by the ad hoc query.

Edit SQL code

You can edit the SQL code in the following three ways:

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

• Click the target table on the canvas, and in the Table Details section at the bottom, click Edit Code.

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

  

SQL syntax

• Ad hoc queries for datasets support SELECT statements but not INSERT, UPDATE, DELETE statements or stored procedures.

• The query syntax must follow the SQL dialect of the data source. For example, if you use a MySQL data source, you must write MySQL-compliant SELECT statements. Some database-specific syntaxes, such as the find() and findOne() methods in MongoDB, are not supported in Quick BI. You must use SELECT statements to query data.

• To add a comment in an ad hoc query, use the -- comment format. Note the space after the double hyphen.

  

  For example:

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

• When joining multiple tables in an ad hoc query, note that the syntax for table and field aliases can differ between databases.

• An ad hoc query can return an unlimited number of rows, but the query string cannot exceed 65,536 characters. If necessary, you can simplify the logic, split the query into multiple datasets, and then join them for analysis.

Placeholders

Quick BI uses placeholders to pass parameters. During report viewing and data analysis, you can use a query control to pass values to placeholders in your SQL, enabling flexible data analysis. Quick BI supports value placeholders and expression placeholders, which you can configure as needed.

Configuration

Write your SQL query and add placeholders as required.

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

   

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

   

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

   Note

   If you use a placeholder in the SELECT clause, you must set a global default value.

   For example, if you add a placeholder named profit_range in the SELECT clause when creating 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 'Break-even'
    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}

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

   

   In the Parameter Settings panel, set a default query value for profit_range.

   

   With the default value set, 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 editing page, add a query control and bind it to a placeholder.

   For example, when you set query conditions, you can use a single-select drop-down list to filter area. Binding the list to an SQL placeholder allows you to pass one value.

   Apply a text filter to order_number, bind an SQL parameter, and pass a condition.

6. When a query runs from the dashboard, the query control passes the corresponding content to the value and expression placeholders.

   For example, assume the query conditions on a dashboard are set as shown in the following figure.

   The resulting SQL is as follows:

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

   Placeholder usage examples:

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

For more placeholder use cases, see Placeholders.