6.0
/
6.0.16
Optimized the function of binding parameters to filter components.
1. Parameter display optimization:
Only parameters in the data source of the subject are displayed. Parameters unrelated to the subject are no longer displayed.
The source paths and parameter names are displayed for parameters, facilitating parameter source locating.
Parameters are displayed in a double-level directory tree for a clearer presentation effect.
2. Parameter binding optimization:
A filter component can be bound to multiple SQL parameters. After binding, all bound parameters are subject to the value of the filter component.
When setting the SQL parameters to be bound to the filter component of the same dashboard, you cannot select parameters that have already been bound to the dashboard.
This document provides SQL writing examples for using SQL dataset parameters in real-time data.
1. where 1=1 represents that the condition is always true, avoiding errors caused by the redundancy of where if no parameter conditions exist in the subsequent statement.
2. If the to-be-added parameter (can be of text, value, or date type) wrapped by the tags <parameter> and </parameter> is empty, all values are retrieved.
3. In SQL table parameters of data preparations, the month and quarter parameters in the year-month parameters, year-quarter parameters, and year-month interval parameters are of text type, with values ranging from 1 to 12 and 1 to 4 respectively. The values cannot be in the format of 0 plus a single digit (such as 01), otherwise, data cannot be read correctly in widgets of the dashboard.
Year
SELECT * FROM Internet Access Data WHERE YEAR(Sales Date)='${Year}'
Text
Year-Month
SELECT * FROM Internet Access Data WHERE YEAR(Sales Date)='${Year}' and month(Sales Date)='${Month}'
Date
SELECT * FROM Internet Access Data WHERE CAST(Statistical Date as varchar) in ('${Date}')
Date Interval
SELECT * FROM Internet Access Data WHERE Statistical Date between '${Start Date}' and '${End Date}'
Year-Month Interval
SELECT * FROM Internet Access Data WHERE format(Statistical Date,'yyyyMM') >=concat('${Start Year}',case when '${Start Month}'>9 then '${Start Month}'else concat(0,'${Start Month}') end ) and format(Statistical Date,'yyyyMM') <=concat('${End Year}',case when '${End Month}'>9 then '${End Month}'else concat(0,'${End Month}') end )
Year-Quarter
SELECT * FROM Internet Access Data WHERE CAST(YEAR(Registration Date) as varchar) = '${Year}' and CAST(FLOOR((MONTH(Registration Date)+2)/3) as varchar) = '${Quarter}'
Texts from which only one value can be retrieved by the parameter
SELECT * FROM Internet Access Data WHERE Contract Type='${Text Parameter}'
Texts from which multiple values can be retrieved by the parameter
SELECT * FROM Internet Access Data WHERE Contract Type in ('${Text Parameter}')
Texts from which all values (when the parameter is empty) and parameter values (when the parameter is not empty) can be retrieved.
SELECT * FROM new_dian where 1=1 ${if(len(Region2) == 0,"","and Region in ('" + Region2 + "')")}
Or SELECT * FROM new_dian where 1=1 <parameter> and Region in ('${Region}')</parameter>
SELECT * FROM Internet Access Data WHERE TO_CHAR("Sales Date",'YYYY')='${Year A}'
SELECT * FROM new_salesdetail WHERE TO_CHAR("Sales Date",'YYYY')='${Year}' and EXTRACT(MONTH FROM "Sales Date")='${Month}'
SELECT * FROM new_salesdetail WHERE TO_CHAR("Sales Date",'YYYY-MM-DD') in ('${Date Parameter}')
SELECT * FROM new_salesdetail WHERE TO_CHAR("Sales Date",'YYYY-MM-DD') between '${Start Date}' and '${End Date}'
SELECT * FROM new_salesdetail WHERE TO_CHAR("Sales Date",'YYYYMM') >=concat('${Start Year}',case when '${Start Month}'>9 then '${Start Month}'else concat(0,'${Start Month}') end ) and TO_CHAR("Sales Date",'YYYYMM') <=concat('${End Year}',case when '${End Month}'>9 then '${End Month}' else concat(0,'${End Month}') end )
SELECT * FROM DEMO_CONTRACT WHERE TO_CHAR("Registration Date",'YYYY')='${Year}' and FLOOR((EXTRACT(MONTH FROM "Registration Date")+2)/3)='${Quarter}'
SELECT * FROM demo_contract WHERE "Contract Type"='${Text Parameter}'
SELECT * FROM demo_contract WHERE "Contract Type" in ('${Text Parameter}')
SELECT * FROM demo_contract WHERE 1=1 <parameter> and "Contract Type" in ('${Text Parameter}') </parameter>
SELECT * FROM new_salesdetail WHERE cast(year(Sales Date) as varchar)='${Year}'
SELECT * FROM new_salesdetail WHERE TO_CHAR("Sales Date",'YYYY')='${Year}' and cast(EXTRACT(MONTH FROM "Sales Date") as varchar)='${Month}'
SELECT * FROM DEMO_CONTRACT WHERE TO_CHAR("Registration Date",'YYYY-MM') >=concat('${Start Year}',case when cast('${Start Month}' as tinyint)>9 then '${Start Month}' else concat('0','${Start Month}') end ) and TO_CHAR("Registration Date",'YYYY-MM') <=concat('${End Year}',case when cast('${End Month}' as tinyint)>9 then '${End Month}' else concat('0','${End Month}') end )
SELECT * FROM DEMO_CONTRACT WHERE TO_CHAR("Registration Date",'YYYY')='${Year}' and cast(FLOOR((EXTRACT(MONTH FROM "Registration Date")+2)/3) as varchar)='${Quarter}'
SELECT * FROM new_salesdetail WHERE TO_CHAR("Sales Date",'YYYY')='${Year A}'
SELECT * FROM DEMO_CONTRACT WHERE TO_CHAR("Registration Date",'YYYY')='${Year}' and FLOOR((EXTRACT(MONTH FROM "Registration Date")+2)/3) ='${Quarter}'
SELECT * FROM xxxx WHERE cast(year(Sales Date) as STRING)='${Year}'
SELECT * FROM xxxx WHERE cast(year(Sales Date) as STRING)='${Year}' and cast(month(Sales Date) as STRING)='${Month}'
SELECT * FROM xxxx WHERE Registration Date in ('${Date Parameter}')
SELECT * FROM xxxx WHERE Registration Date between '${Start Date}' and '${End Date}'
SELECT * FROM xxxx WHERE TO_CHAR("Registration Date",'YYYY-MM') >=concat('${Start Year}',case when cast('${Start Month}' as tinyint)>9 then '${Start Month}' else concat('0','${Start Month}') end ) and TO_CHAR("Registration Date",'YYYY-MM') <=concat('${End Year}',case when cast('${End Month}' as tinyint)>9 then '${End Month}' else concat('0','${End Month}') end )
SELECT * FROM xxxx WHERE cast(year(Registration Date) as STRING)='${Year}' and cast(truncate((month(Registration Date)+2)/3) as STRING)='${Quarter}'
SELECT * FROM demo_contract WHERE Contract Type='${Text Parameter}'
SELECT * FROM demo_contract WHERE Contract Type in ('${Text Parameter1}')
SELECT * FROM demo_contract WHERE 1=1 <parameter> and Contract Type in ('${Text Parameter}') </parameter>
SELECT * FROM xxxx WHERE toString(toYear(Registration Date)) ='${Year}'
SELECT * FROM xxxx WHERE toString(toYear(Registration Date)) ='${Year}' and toString(toMonth(Registration Date)) ='${Month}'
SELECT * FROM xxxx WHERE toString(toYear(Registration Date))='${Year}' and toString(floor((toMonth(Registration Date)+2)/3))='${Quarter}'
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy