Descriptions of Dynamic Parameters

Dynamic parameters support adjusting part of the content in a statement. In real computations, the values of dynamic parameters will be automatically substituted into query statements to replace variables used${Parameter type: Parameter name}. When saving reports, the values of the current dynamic parameters will be saved as default values.

You can manage a segment of repeatedly used, complicated statements by a dynamic parameter, or you can also allow report viewers to modify query statements within a certain scope through dynamic parameters.

Variable content

Alterable content as a dynamic parameter supports the selection of a specific time or customized input value, which can directly substitute relevant parameters in computation.

For example, to obtain the rank of TOP N by the number of coins, you can use alterable content to substitute N and the operator pulls details of the top-10, top-100 or top-1000 users based on needs to save the need to save multiple reports.

SELECT * FROM
ta.v_user_2
ORDER BY "coin_sum" DESC
LIMIT ${Variable1}

Notes

For the purpose of data security, reports that have not been used in the original query statements cannot appear in the alterable content.

Event time

Using the dynamic parameter of event time, you can rapidly filter event data within the time frame of analysis.

For example, if the SQL report needs to rank users by accumulative payments over the past 7 days, you can add the dynamic parameter and select the range of dynamic time so that statistics of payment events over the past 7 days in relation to the current day will be conducted during the report query.

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 multiple timezones are enabled in your project, you can additionally select the event time parameter "whether affected by time zone offset". Dynamic parameters affected by timezone filter event dates that are offset to the displayed timezone (i.e.,"$part_date_with_timezone")during computation instead of filtering the "$part_date"in order to ensure consistency with other model logics.

It should be noted that the dynamic parameter takes effect only on the events of the current project without affecting the event list of other projects in the SQL report.

Expression (Numeric,Text and Time)

When filtering numerical, text and time fields, you can use the dynamic parameter of expression to conduct flexible configuration.

For example, the cumulative payment amount is a Number user property, and you can insert numerical expressions in query statements to allow report viewers voluntarily filter users whose cumulative payment amounts fall within a certain range or larger than a certain amount.

SELECT * FROM
ta.v_user_2
WHERE "coin_num" ${Number:number1}
LIMIT 10

Selector

You can use the dynamic parameter of option to set different option names for pre-prepared statement segments. In this way, viewers only need to make selections based on the names and the corresponding statement segments can automatically substitute dynamic parameters.

For example, users can be classified into different types based on grade intervals, and if report viewers want to quickly view user details, you can set names for filter statement segments for different grade interviews to make it easier to make selections.

SELECT * FROM
ta.v_user_2
WHERE "user_level" ${Selector:selector2}
LIMIT 10