If a complex logic or model is used during data analysis, you can use an SQL statement to create a dataset. For complex scenarios or specific data analysis scenarios, you can also pass parameters to SQL statements.
Prerequisites
The required data is obtained.
Enter and execute an SQL statement
You can use one of the following methods to enter an SQL statement.
Log on to the Quick BI console to go to the page on which you can enter an SQL statement.
Method 1: On the Data Sources page, click Create Dataset with SQL in the upper-right corner.
Method 2: In the left-side navigation pane of the dataset creation page, select a data source. If no table is displayed on the canvas, click Execute an SQL statement to create a table.
In the SQL editor, enter an SQL statement and click Run.
Sample statement:
SELECT report_date, order_level, shipping_type, area, price, order_number from company_sales_record where ${report_date :report_date} and ${order_level :order_level} and ${order_number :order_number}
After the statement is successfully executed, you can view the execution result on the Results tab.
Click Confirm Edit.
Save the dataset that you created by using a custom SQL statement.
Modify SQL statements
Move the pointer over the table on the canvas and click Edit Code.
Parameters and placeholders
When you view a dashboard during data analysis, you need to pass parameters to the SQL statement that you want to execute. Quick BI allows you to pass parameters to the SQL statement by adding placeholders or parameters.
Type | Description | Scenario | Format |
Placeholder | You can use the filter control on the dashboard to pass a value or a set of values. Note If the placeholder indicates a date value, you must specify the date format to determine the format of the input value. | In most scenarios where parameters need to be passed, you can add a placeholder to the SQL statement that you want to execute to pass parameters. |
|
Parameter | You can use the filter control on the dashboard to pass a condition to an SQL statement. | You can modify the parameters in a filter condition on the dashboard to pass the filter condition to an SQL statement. For example, you can configure |
|
Configuration method
Write an SQL statement based on your business requirements and add a placeholder or parameter to the SQL statement.
On the code edit page, click Parameter.
In the Parameter panel, configure the parameters and click OK. The following table describes the parameters.
Parameter
Description
Type
The parameter passing type. Valid values: Parameter and Placeholder.
Variable Name
The name of the variable that corresponds to the parameter or placeholder.
Variable Type
The type of the variable that corresponds to the parameter or placeholder.
Valid values: Text, Value, and Date.
Query Default Value
Optional. If the dataset contains a large amount of data, the dataset may fail to be saved or the query to the dashboard is time-consuming. To prevent this issue, we recommend that you configure the Query Default Value parameter.
If you specify the effective range and configure the Query Default Value parameter for a field and the default value of the field is configured in a filter, Quick BI passes the parameter based on the default value that is configured in the filter. If the default value of the field is not configured in the filter, Quick BI passes the parameter based on the value of the Query Default Value parameter.
If you specify the effective range but do not configure the Query Default Value parameter for the field, Quick BI passes the parameter based only on the default value that is configured in the filter. If the default value of the field is not configured in the filter, Quick BI replaces SQL conditions with identities. Proceed to the next step.
Click Save in the upper-right corner to save the dataset.
In the top navigation bar, choose .
On the dashboard edit page, add a filter condition and associate a placeholder or a parameter with a field.
For example, when you configure a filter condition, you can select the area field from the filter field drop-down list and associate an SQL placeholder with the field. This way, you can pass one value.
Set Display type to Numerical input box to filter the profit_amt field and associate the field with an SQL parameter to pass a condition.
Query data on the dashboard. When you query the data, the data is passed to the placeholder and parameter based on the filter condition.
The following figure shows a filter condition on a dashboard.
SQL statement that corresponds to the filter condition:
SELECT * FROM company_sales_record WHERE area ='North' AND profit_amt > 0
The SQL statement in the following example includes placeholders:
SELECT * FROM tablename WHERE area in ('${area_ph}') -- The TEXT type. You can enter multiple values. AND name = '${name_ph}' -- The TEXT type. You can enter only one value. AND number = ${number_ph} -- The NUMERIC type. AND report_date > '${report_date_ph.get(0)}' -- The DATE type. This placeholder is used to obtain the start date of the date range control. AND report_date < '${report_date_ph.get(1)}' -- The DATE type. This placeholder is used to obtain the end date of the date range control.
Tag placeholders
You can use a tag placeholder to obtain the tag value of the current user.
Format: $tag {User tag name}
The following example shows the configuration of tags for a user who is granted the permission to view only the data of Product A and Product B in the North area.
Area:
North
Product:
Product A, Product B
Sample SQL code segment:
select * from table_name
where
area = '$tag {Region}'
and
product in ('$tag {Product}')
When a user accesses data, Quick BI automatically reads the tags of the user. Sample SQL statement:
select * from table_name
where
area = 'North'
and
product in ('Product A', 'Product B')
What to do next
If the fields that you want to analyze are in different tables, Quick BI allows you to associate the tables. For more information, see Build a model.