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

In the process of report development, we often filter and query the data with parameters, which is usually realized by defining which field to filter in the where part of the sql statement, and then the parameter template passes different values to this field for data query, and returns different result sets for report display. The use of general parameters has been introduced in the tutorial documentation. I will not repeat it here. Today, the editor will only share two tips that most people do not know, which can improve the efficiency and flexibility of report development:

The value is empty to query all data.

How to use dynamic parameters (macros)

Tip 1: how to query all the data when the parameter value is empty

Here we avoid being discovered by the organization (scared) seize the time to skip drawing patterns, build datasets, and so on, and get to the point. First, take a look at the settings of the usual query dataset sql, as shown in the following figure:

Among them, one of the dataset sql? Represents a parameter that corresponds to a field name, so you must assign default values to the parameters in order to ensure that there is data in the first display result page. What we want is to query all data when the parameters are empty. In this case, we need to apply a trick-"or? is null" based on the original parameter settings. After the application, the data set sql and parameter settings are as follows:

Two after revision? It corresponds to the same field and the two parameter expressions in the dataset parameters must be consistent with those in the sql statement. The order corresponds, which actually means that both question marks apply to the employee name field. In this way, after a simple two-step modification, we can query all the data when the parameter is empty.

Now that we have mastered this little skill, let's take a look at another trick used by the parameter:

Tip 2: the usage of ${parameter name}. An example is as follows:

The data table and corresponding fields to be queried are selected in the parameter template, and the data of the corresponding data table is presented in the main table.

I believe that it is not unfamiliar for you to see this effect image. It is nothing more than selecting parameters to query and filter. Unlike in the past, we are not filtering the data of a fixed data table here. Instead, we choose different data tables to render.

The key settings to achieve this effect are as follows:

Setting of sql statement in dataset

Different from the usual way of writing the dataset sql, we do not always write the data table and field to be queried, but use ${parameter name} twice to replace the field name and data table name that we were supposed to write. The usage of ${parameter name} is a special use of parameters, which is often used to replace expressions dynamically in the report to quickly realize the splicing of dynamic execution statements.

At the same time, this usage is often used in conjunction with parameter templates for ease of operation.

Review and summary

And then... .. And then... .. Let's share some tips on the use of parameters here, and let's review the unknown secrets and operations:

1. Identifier of the parameter in the drying

[knowledge points] the identifier is a question mark (?). When you apply the parameter definition, you should pay attention to the order of the number of parameters. Corresponding.

two。 Parameter is empty to query all data

[knowledge points] apply or? Is null

3.$ {Parameter name}

[skill] is equivalent to a placeholder, and can replace any part of the sql statement, or even the entire sql statement definition.

At this point, the sharing of tips on the use of parameters is over. Get has done this trick, and the work of making tables is not getting easier and easier.

Come and join the Runqian family, get for more quick and easy report making methods.

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.