During data analysis, if you have a large volume of data and want to improve query speed, you can create parameters during SQL table creation and bind them to report filters. When you view the report, Business Intelligence (BI) will pass the filter content to the SQL parameters, enabling pre-filtering during SQL queries to improve query speed.
For example, your data table contains data from 2020 to 2023, but you only need to analyze the 2023 data in your daily analysis. If parameters are not used, when you use the time filter to filter out the 2023 data when viewing the report, the system will first query all 3 years of data (2020–2023) before retrieving the 2023 data for display. If parameters are used and bound to the time filter, when you use the time filter to filter out the 2023 data when viewing the report, the system will replace the parameters in the SQL statements with 2023 as the query condition to query only the 2023 data. Therefore, the data query volume after parameters are used is one-third of the original data query volume, and the speed is three times faster.
Note:
Edition limitation of feature usage: This feature is only supported for Basic Edition and Professional Edition users.
Creating SQL Parameters
In the SQL table creation interface, enter ${parameter name} in the SQL statement input box to define parameters. For example, entering ${A} defines a parameter named A.
When producing a report, you can configure filters for region (single-select filter), time (time interval filter), and sales revenue (numeric range filter). The filtered values can be directly applied to the SQL query to enhance the query speed.
At this point, write the statements in the input box as shown below to define 5 parameters: A, B, C, D, and E. Note that the statement syntax should match the filter usage. For example, for the region field "area", if a single-select filter that passes only 1 value is to be used, the syntax should be "area = '${A}'"; if a multi-select filter that passes multiple values is to be used, the syntax should be "area in ('${A}')".
The syntax below is provided for copying and reference purposes.
select*FROM dataSales_1000000
where area ='${A}'
and subscriptionDate >='${B}'and subscriptionDate <='${C}'and salesAmount >='${D}'and salesAmount <='${E}'
After writing the SQL statements, click Parameter Configuration to confirm the defined parameters. Set the parameter type and default value for queries.
Parameter types include "character", "numeric", and "time". When "time" is selected, you can perform a second-level selection for the time format, and the default time format is "YYYY-MM-DD". The definition of parameter type affects the filter type that can be bound subsequently. For example, numeric-type parameters can only be bound to the numeric filter, character-type parameters can only be bound to the text filter, and time-type parameters can only be bound to the time filter. In this example, since A will be bound to the text filter, its parameter type is set to "character"; since B and C will be bound to the time filter, their parameter types are set to "time"; since D and E will be bound to the numeric filter, their parameter types are set to "numeric".
The default query value refers to the parameter's default value when no value is passed through the filter during data querying. This applies to scenarios such as unconfigured filters in the data preview section during SQL table creation, data table viewing pages, or report pages. This field is optional. If left blank, it defaults to the identity "1=1". Configuring this is recommended for large data volumes to reduce query time. For example, in this case where the data table has millions of rows, queries will be slower without a default value. It is recommended to set default values for B and C, which will make queries much faster.
After setting the parameter type and default value in the configuration pop-up window, click "Confirm" to submit for the settings to take effect. Otherwise, the settings will not take effect.
If you want to view the final executed SQL statements, click View SQL. At this point, you will see the final result where parameters are replaced by their default values (or by identity if no default value is set), as shown in Figure 2.
To add or delete parameters, first add or delete them in the SQL statement box, then go to "Parameter Configuration" and click Confirm to submit the modified result.
Binding SQL Parameters
Assume a chart has been configured on the canvas page. Next, we will explain how to bind parameters to filters. You need to configure the single-select filter for region, the time interval filter, and the numeric range filter for sales revenue.
Time filter Select parameter Bind parameters
Text filter
Value filter
Time Functions and Offsets
1. The SQL parameters in BI support time functions, including ${yyyyMMdd} and ${yyyy-MM-dd}. Herein, yyyy represents the 4-digit year, yy represents the 2-digit year, MM represents the month, dd represents the day, HH represents the hour, mm represents the minute, and ss represents the second. Flexible combinations of these parts are supported, as shown in the table below:
Time Parameter
Replacement Value Example (Assuming the Current Time Is 2021-07-10 08:00:00)
${yyyy}
2021
${yyyyMMdd}
20210710
${yyyy-MM-dd}
2021-07-10
${yyyy/MM/dd}
2021/07/10
${yyyyMMddHHmmss}
20210710080000
${yyyyMMddHHmm}
202107100800
${yyyyMMddHH}
2021071008
${yyyy-MM-dd HHmmss}
2021-07-10 080000
${yyyy-MM-dd HHmm}
2021-07-10 0800
${yyyy-MM-dd HH}
2021-07-10 08
2. Supported offset writing formats are shown in the table below.
Time
Time Parameter Format
Replacement Value Example (Assuming the Current Time Is 2021-07-10 08:00:00)
Next N years
${yyyyMMdd+Ny}
If dt=${yyyyMMdd-1M} is referenced, it will be replaced with dt=20210610.
If dt=${yyyyMMdd-1d} is referenced, it will be replaced with dt=20210709.
If dt=${yyyy-MM-dd-1d} is referenced, it will be replaced with dt=2021-07-09.
If dt=${yyyyMMddHHmmss-3h} is referenced, it will be replaced with dt=20210710050000.
If dt=${yyyyMMddHHmmss-25m} is referenced, it will be replaced with dt=20210710073500.
Previous N years
${yyyyMMdd-Ny}
Next N months
${yyyyMMdd+NM}
Previous N months
${yyyyMMdd-NM}
Next N weeks
${yyyyMMdd+Nw}
Previous N weeks
${yyyyMMdd-Nw}
Next N days
${yyyyMMdd+Nd}
Previous N days
${yyyyMMdd-Nd}
3. Commonly used date conversion expressions are supported, as shown below.
Time Parameter Format
Description
Example (Assuming the Current Time Is 2021-07-10 08:00:00)
${yyyyMMdd+ME}
The MONTH END data date corresponds to the end of the month.
20210731
${yyyyMMdd+MS}
The MONTH START data date corresponds to the start of the month.
20210701
${yyyyMMdd+QE}
The QUARTER END data date corresponds to the end of the quarter.
20210930
${yyyyMMdd+QS}
The MONTH START data date corresponds to the start of the quarter.
20210701
${yyyyMMdd+YE}
The YEAR END data date corresponds to the end of the year.
20211231
${yyyyMMdd+YS}
The YEAR START data date corresponds to the start of the year.
20210101
${yyyyMMdd+PME}
The PRI MONTH END data date corresponds to the end of the last month.
20210630
${yyyyMMdd+PYE}
The PRI YEAR END data date corresponds to the end of the last year.
20201231
4. Combined use is supported. For example (assuming the current time is 2021-07-10 08:00:00),
Combinations of offsets, such as ${yyyyMMdd-1M-1d}=20210609.
Combinations of time functions and offsets, such as ${yyyyMMdd+ME-1d}=20210730 and ${yyyyMMdd-1d+ME}=20210731.
5. An SQL parameter composed of a time function and offset can still be bound by a time-type filter when binding the filter. After binding, when viewing the report, the filtered value will replace the entire content within the curly braces, not merely the preceding yyyyMMdd part. For example, if the current system time is 2021-07-10 08:00:00 and the parameter ${yyyyMMdd+ME-1d} is bound to the filter, with the filter selecting both the start date and end date as T-1, the data finally screened out will be from 2021-07-09 to 2021-07-09.
