Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to use the dynamic parameters in the big data report tool and what the scene is. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

In the process of report development, sometimes we find it difficult for ordinary parameters to meet some business requirements, such as the second dataset to refer to the results of the first dataset for calculation, dynamic control of SQL filtering conditions, dynamic columns, etc. If you encounter this situation, you can try to use dynamic parameters that is ${parameter name} to achieve the desired effect.

So, what are dynamic parameters?

Dynamic parameters are generally computational parameters that depend on ordinary parameters. In the report, dynamic parameters can be regarded as a placeholder. Before the report operation, the system will comprehensively search the whole report definition and replace all the parameter names in ${parameter name} with parameter values, that is, the result of dynamic parameter expression.

Let's take a look at a few examples.

Show that the parameter value is empty for the first time, and query all the data.

Implementation method:

a. Replace the original part of the where clause in the dataset with dynamic parameters as follows

SQL before change:

SELECT order. Order ID, order. Customer ID, order. Order date, order. Shipper area, order. Shipper, order. Freight charge

FROM order

Where order. Order ID > = 10254

Changed SQL:

b. Select [report] à [parameters] from the menu bar to add dynamic parameters.

The expression is: if (bid==null, "", "where order. Order ID > =" + @ bid)

Determine the content of the sql to be replaced according to whether the parameter value of the parameter template bid is empty, as shown in the following figure

Note: bid is the parameter name passed by the parameter template

c. In addition, the settings are developed and designed according to the routine operation of the report.

two。 The second dataset is calculated based on the result of referencing the first dataset

Implementation method:

a. Add a dynamic parameter whose expression is the sql statement of the first dataset, as shown in the following figure

Parameter expression:

If (@ bid==null | | @ bid== "

"where order ID in (" + string (split ("select order ID from order where shipper region =?", "Huazhong"; "demo"), ",") + ")"

"where order ID in (" + string (split (@ bid, ") +") ")

If the parameter bid is empty, the query is based on the execution result of a sql statement, otherwise the data is filtered according to the value of the bid parameter.

b. The dataset section is set up as follows:

c. In addition, the settings are developed and designed according to the routine operation of the report.

3. Filter conditions for dynamically controlling SQL

The dataset SQL contains multiple filtering conditions. It is assumed that when the order ID and shipper region parameter values are empty, these two query conditions are removed from the SQL statement, and if one of them has a value, the corresponding field filtering takes effect, otherwise, both the order ID and shipper region filtering take effect.

Note: parameter arg1 corresponds to order ID and parameter arg2 corresponds to shipper area.

Implementation method:

a. Add new dynamic parameters and use the if () function to achieve the above logical judgment, as shown in the following figure

The parameter expression is:

If (arg1== "" | | arg1==null

If (arg2== "" | | arg2==null, "1x 1", "shipper area ='" + arg2+ "'")

If (arg2== "" | | arg2==null, "order ID=" + arg1, "order ID=" + arg1 + "and shipper region ='" + arg2+ "'")

)

b. The dataset section is set up as follows:

c. In addition, the settings are developed and designed according to the routine operation of the report.

About big data report tool in the use of dynamic parameters and what the scene is shared here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.