Custom SQL

Limitations

API data sources do not support ad hoc queries.

Enter SQL code

Follow these steps to add an ad hoc query.

1. Go to the Ad Hoc Query page.

   • Method 1: On the Data Sources page, click Ad Hoc Query in the upper-right corner.

   • Method 2: On the relational model canvas of the dataset editing page, select a data source. If the canvas is empty, click Click to Create a Dataset Using SQL Code on the canvas or Create Table with SQL in the left-side panel.

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

     

2. Enter your SQL code and 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 the query runs successfully, you can preview the results on the Result tab.

3. Click Create Dataset to save the dataset.

   

Edit SQL code

You can edit the SQL code in one of the following ways:

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

• Click the target table on the canvas, and then click edit SQL in the Table Details section at the bottom.

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

  

SQL syntax

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

• The ad hoc query syntax must conform to that of the underlying data source. For example, if your data source is MySQL, the ad hoc query must be written in MySQL query syntax. Some database-specific syntax, such as find() and findOne() in MongoDB, is not supported in Quick BI. In such cases, you must use a SELECT statement to query the data.

• To add comments in your ad hoc query, use the -- prefix followed by a space and the comment text.

  

  Sample SQL statement:

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

• When you join multiple tables in an ad hoc query, use table and field aliases correctly. Note that the syntax for aliases can vary across different database engines.

• An ad hoc query in a dataset has no row limit, but the query string is limited to 65,536 characters. If a query is too complex, you can split its logic or create and join multiple datasets for analysis.

Placeholders

Quick BI uses placeholders to pass parameters. When you view reports and analyze data, you can use query controls to pass placeholder values to the SQL for flexible data analysis. Quick BI supports value placeholders and expression placeholders.

Configuration

Write your SQL query and add placeholders.

1. On the SQL editing page, click placeholder management.

   

2. In the placeholder management 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, if you add a placeholder named profit_range to the SELECT clause 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 '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}

   Without a default value, the query fails to run.

   

   You must set a default value for profit_range in the placeholder management panel.

   

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

   

3. Click Create Dataset 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 condition and bind it to a placeholder.

   For example, when setting a query condition, 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 for order_number and bind it to an expression placeholder to pass a condition.

6. When users query the dashboard, the corresponding values and conditions are passed to the value and expression placeholders.

   For example, assume the dashboard query conditions 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

   The following code provides examples of placeholder usage:

   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.