Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

The making of Multi-valued Fuzzy query report

2025-01-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Internet Technology >

Share

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

Fuzzy query and multi-valued query are often involved in data query system. We may all know something about fuzzy queries. Fuzzy matching is based on the keywords entered by the user in the parameter box, which is achieved through the like condition in SQL. On the other hand, most of the multi-valued queries input more than 2 keywords in the parameter box at a time and pass the parameters in the form of string group, integer group and so on, which is completed by in condition in SQL.

But the needs of users are often endless! The most typical is the combination of these two good methods, that is, the combination of the two queries-enter multiple keywords in the parameter box and make a fuzzy query based on multiple keywords. For example, users need to make a multi-valued fuzzy query based on the product name, and when entering "cheese, sugar", they want to find a list of products with cheese or sugar in the product name, as shown in the following figure:

In order that the programmer brother can work overtime with say goodbye as soon as possible, no longer be single Wang (huh? Why am I so enthusiastic? Let's take a look at how the drying report achieves the perfect combination of the two queries....

It takes a total of... Steps 1, 2, 3, 4:

1. Define the master table template:

Among them

Line 1-3 sets the row property to the header, and the rest of the behavioral data area is fine.

The B3 cell expression is: = macro1 to display the stitching results of the query conditions in order to verify that the final data is correct.

The A5 cell expression is: = ds1.select (product ID) to fetch all the products in the ds1 result set.

The B5-F5 cell expression is: = ds1. Field name, the purpose is to extract the corresponding product information according to the previous product number

two。 Define parameters:

Among them

Cpmc is a normal parameter, which is the query value entered by the user.

Macro1 is a dynamic parameter. According to the cpmc stitching query condition, the value expression is: if (cpmc== null or cpmc== "," 1q1 "," product name like'% "+ replace (@ cpmc,"%'or product name like'% ") +"% "). The dynamic parameter here is not only used to display the stitching results of query conditions in the report results page, but also to filter sql data.

Explain in detail again: when cpmc is empty, return 1: 1, which means that all data are queried, otherwise, the string replacement function replace () will first be used to replace the "," in the parameters with "%'or product name like'%") + "%" (note the spaces in it), and then the replacement results will be concatenated with the strings before and after the final query conditions. For example, when the user enters "cheese, sugar", the replacement result of replace is "cheese%'or product name like'% sugar%', and then concatenated with the string before and after the final result is product name like'% cheese%'or product name like'% sugar%'.

3. Define the dataset:

Here, the special use of the parameter ${parameter name} is used in the query condition, which is equivalent to a placeholder effect, and the query will be populated to this position with the dynamic parameter content defined earlier.

Note: the settings in steps 2-3 are added in the main table template.

4. Define the parameter template:

In the property bar to the right of the report tool design area, set the cell web variable name of B2 to cpmc.

Bingo, multi-valued fuzzy query is thus perfectly realized!

Review

Let's review the problems encountered and the skills to solve them:

1. Data sets are fetched dynamically by SQL:

Use the ${parameter name} usage of the parameter

2. The query condition is empty, and all the queries are:

Use the usage of parameter 1 to 1

3. Multi-value matching:

Split multiple query keywords with "," and splice them into multiple or-connected like conditions.

Finally, we found that, in fact, we only use the special usage of the parameters of the moistening report and the stitching of SQL query conditions, is it not careless, but also double get new skills? If it goes on like this, it won't be long before you can refuse to work overtime and refuse to be single.

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Internet Technology

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report