Report with repeated groups

Repeated grouping reports are also common and troublesome tasks in report development. Repeatable grouping reports mean that the same record appears in different groups for repeated statistics. Common repeatable grouping reports have two types: Group Interval intersection, for example: count the number of users between the ages of 20-30, 25-35, and 30-40. The other is the inclusion of grouping intervals, for example, the summary data of each region and major cities in the region, "North China, including Beijing; east China, including Shanghai ......".

Such reports are often difficult to implement through report tools. Even if they are implemented, they are often accompanied by difficulties in developing and maintaining reports and poor performance.

This problem can be well solved through the set calculator. Here we use two examples to illustrate the implementation process.

Example 1

Based on the basic information table of employees, the total number of employees and bonuses of each age group are collected by age. The report style is as follows:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/3C/BA/wKiom1PDMvajKx18AADBCKBTIhI811.jpg "style =" float: none; "Title =" esproc_repeat_group_1.jpg "alt =" wkiom1pdmvajkx18aadbckbtihi811.jpg "/>

Here, "30-40" and "35-45" are duplicated.

Set calculator code

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/3C/BA/wKioL1PDMsvx3jMqAAEmZbXCWLE836.jpg "Title =" esproc_repeat_group_2.jpg "style =" float: none; "alt =" wkiol1pdmsvx3jmqaaemzbxcwle836.jpg "/>

A1 establishes a data source connection. A2 queries the employee table data. Because this table only has a date of birth, the age field is added and calculated in A3. A4 and A5 specify the age range and Its range name, repeatable grouping in A6, calculate the Group Summary value in A7, and return the result set to the report in A8.

Report call

Since the main task of the Set calculator is to prepare data sources for the report, it only performs data computing, and the report Presentation work is still completed by the report tool. Here we use the rundry report for presentation.

Dataset settings

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/3C/BA/wKioL1PDMs_C2nDLAACfzdo3-qs084.jpg "Title =" esproc_repeat_group_3.jpg "style =" float: none; "alt =" wKioL1PDMs_C2nDLAACfzdo3-qs084.jpg "/>

Select the Stored Procedure dataset and use the class Stored Procedure Call method. The "age group" is the name of the Set calculator script (DFX.

Report Template

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/3C/BA/wKiom1PDMwDxBijaAAB0EHPENkA968.jpg "Title =" esproc_repeat_group_4.jpg "style =" float: none; "alt =" wkiom1pdmwdxbijaaab0ehpenka968.jpg "/>

 

When it comes to rundry reports, as an excellent report tool, this example of rundry reports can be implemented through the DS. enumgroup () function:

Ds1.enumgroup (true, age (birthday)> 20 & age (birthday) <= 30, "20 to 30 years old", age (birthday)> 30 & age (birthday) <= 40, "30 to 40", age (birthday)> = 35 & age (birthday) <45, "35 to 45 ", age (birthday)> = 45, "45 years old and above ")

This is the case of four groups. It can be imagined that if there are dozens of groups, this expression will be very lengthy and difficult to maintain, but it is very clear in the Set calculator.

Example 2

According to the order table, the order status of each province is counted. The order status of some major cities (such as Qingdao, Shenzhen, and Shijiazhuang) must be listed. The report format is as follows:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/3C/BA/wKioL1PDMtXAzL0ZAAFE5dJhJfI817.jpg "Title =" esproc_repeat_group_5.jpg "style =" float: none; "alt =" wkiol1pdmtxazl0zaafe5djhjfi817.jpg "/>

Set calculator code

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/3C/BA/wKiom1PDMwaw8MSKAAE5EdcN3KM849.jpg "Title =" esproc_repeat_group_6.jpg "style =" float: none; "alt =" wkiom1pdmwaw8mskaae5edcn3km849.jpg "/>

A1 and A2: Establish a database connection to read data from the order table;

A3: lists the corresponding cities and provinces;

A7: group by province;

A8 and A9: grouped by the specified city ing;

A10: insert the city data into the A7 summary result by province.

Report call

The report calling method is the same as that in the previous example. The Report Template and its expression are as follows:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/3C/BA/wKiom1PDMwaCu1ERAABcoMp-wuE196.jpg "Title =" esproc_repeat_group_7.jpg "style =" float: none; "alt =" wKiom1PDMwaCu1ERAABcoMp-wuE196.jpg "/>

 

You can use either of the following methods to separate the statement:

1. Relying on a large number of hidden rows

That is, group by province, group by city in the next line, and set the extension following the previous line. Set the row expression of the city to hide all cities that do not need to be displayed.

Although this method can be implemented, a large number of hidden rows greatly affect the report efficiency, especially when there are many urban groups.

2. Use the overlap () function

In a dry report, DS. overlap () can be used to complete repeatable grouping reports. In this example, the expression can be written as follows:

Ds1.overlap (true, Province = "Shanghai", "Shanghai", Province = "Beijing", "Beijing", Province = "Sichuan", "Sichuan ", province = "Tianjin", "Tianjin", Province = "Shandong", "Shandong", city = "Qingdao", "Qingdao ", province = "Guangdong", "Guangdong", city = "Shenzhen", "Shenzhen", Province = "Jiangsu", "Jiangsu ", province = "Jiangxi", "Jiangxi", Province = "Hebei", "Hebei", city = "Shijiazhuang", "Shijiazhuang ", province = "Hainan", "Hainan", Province = "Fujian", "Fujian", Province = "Liaoning", "Liaoning", Province = "Chongqing ", "Chongqing", Province = "Shaanxi", "Shaanxi ")

The overlap implementation has two obvious disadvantages: the first is the expression length, which is unbearable, the readability is difficult to change, and similar expressions are written many times, the advantages of automatic reports are gone; the second reason is that the statistical provinces are all written to the expressions, so that the report is too fixed to change with the original data, and the report template needs to be modified when the data changes, making maintenance difficult.

When the set calculator completes such reports, it does not hide the column and column concerns that affect efficiency, and does not restrict the use of reports due to the length of expressions and fixed reports, as a powerful tool for set computing, the set computing tool has a wide range of class libraries with clear code structures. It can help you quickly create such reports in special formats and is an indispensable helper in report production.