Use dynamic parameters
Dynamic parameters let you adjust parts of a statement. During calculation, the values of the dynamic parameters are substituted into the query statement to replace the variables used, ${参数类型:参数名}. When you save the report, the current values of the dynamic parameters are saved as their default values.
You can manage a complex statement fragment that's used multiple times with one dynamic parameter. You can also let report viewers modify the query statement within a certain range through dynamic parameters.
Variable content
A Variable content dynamic parameter lets you select a specific time or enter a custom value, which is substituted directly during calculation.
For example, when you rank users by gold coin amount to get the top N, you can use variable content to represent N. Operations staff can then pull the details of the top 10, top 100, or top 1000 users as needed, without saving multiple reports.
SELECT * FROM
ta.v_user_2
ORDER BY "coin_sum" DESC
LIMIT ${Variable1}
For data security, variable content can't contain tables that weren't used in the original query statement.
Event time
With an Event time dynamic parameter, you can quickly filter event data within the analysis period.
For example, if an SQL report needs to rank users by their cumulative payment amount over the past 7 days, you can add a dynamic parameter and select a rolling date range. When the report runs, it counts payment events in the past 7 days relative to the current day.
SELECT
"#user_id"
, sum("count") count_sum
FROM
v_event_2
WHERE (("$part_event" = 'recharge') AND (${PartDate:date1}))
GROUP BY "#user_id"
ORDER BY count_sum desc
LIMIT 10
If your project has multiple time zones enabled, you can also select Affected by time zone offset for the event time parameter. A dynamic parameter affected by the time zone filters by the event date offset to the display time zone (that is, "$part_date_with_timezone") during calculation, instead of by "$part_date", which is consistent with the logic of other models.
Note that this dynamic parameter only applies to the event table of the current project. It doesn't affect the event tables of other projects in the SQL report.
Expression (number, string, time)
When you filter fields of the number, string, or time type, you can use an expression dynamic parameter for flexible configuration.
For example, cumulative payment amount is a numeric user property. You can insert a number expression into the query statement so that report viewers can filter users whose cumulative payment amount falls within a range or exceeds a certain amount.
SELECT * FROM
ta.v_user_2
WHERE "coin_num" ${Number:number1}
LIMIT 10
Selector
With a Selector dynamic parameter, you can set different option names for statement fragments prepared in advance. Viewers only need to select by name, and the corresponding statement fragment automatically replaces the dynamic parameter.
For example, users can be divided into different types by level range, and report viewers want to quickly view user details by type. You can name the filter statement fragments for different level ranges so that they're easy to select.
SELECT * FROM
ta.v_user_2
WHERE "user_level" ${Selector:selector2}
LIMIT 10