Repeatable report grouping and Improvement

Repeatable grouping reports are 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 ......".

The following two instances are used to view the implementation process of the statement.

Instance 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:

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

Implementation

In a dry report, this type of report is mainly completed through the DS. enumgroup () function. The Report Template and expression are as follows:

The expression of cell A2 is:

= 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 years old", age (birthday)> = 45, "45 years old and above ")

With Ds. enumgroup (), you can easily complete the preceding reports. This is especially suitable for scenarios with few groups. When there are many groups, this expression is too complex to maintain the report.

Instance 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:

Implementation

The ds. overlap () function is used to implement such reports. The Report Template and expression are as follows:

The expression of cell A2 is:

= 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 ")

Like instance 1, DS. overlap () is especially applicable to scenarios with fewer groups. When there are many groups, too complex expressions will make the report difficult to maintain.

The preceding two types of reusable grouping reports can be implemented in rundry reports, which is especially simple when there are few groups. This is also an issue that other reporting tools cannot compare to rundry reports. However, when there are many groups and the computing is complicated, it is difficult to implement the statement. The reason is that the preparation and presentation of the report data source are mixed, and a large number of computing tasks need to be implemented in the report template. If the two parts are separated, that is: the Independent Computing layer is responsible for preparing data sources (completing complex computing) and the Independent presentation layer for report presentation, which makes report development clearer.

Based on the improved product set computing report version 5.0 launched by rundry report kernel, rundry has an Independent Computing engine built in to solve data source preparation problems. Complex data computing (Data Source preparation) work can be handed over to the set compute in the Set Computing report, which can be considered as the computing layer of the report; the report itself only receives computation results for report Presentation (sometimes including some simple calculations), thus separating data source preparation from report presentation, making the report production clearer.

For the preceding reports, you can implement and solve the following difficulties through the computing report:

Instance 1

Compile the computing script

First, use the set calculator to write the set calculator script, complete the grouping summary, and prepare the data source for the report:

A1: Query employee table data. Because this table only has a date of birth, the age field is added and calculated in A2. A3 and A4 specify the age range and Its range name, perform repeated enumeration groups in A5, calculate the Group Summary value in A6, and return the result set to the report.

 

Report call

Use the report designer to create a report, use the dataset type of the Set calculator, and select the Set Computing script (age. DFX) Edited above ):

Set a report template and Expression

In a report, you only need to set simple list values without complex computing.

 

Here we can see the benefits of separating data preparation (Set Computing script) from report Presentation (Report Template) in a set computing report, at the computing layer, data source preparation is completed through a complete Computing System of the Set calculator.

Instance 2

A1 reads data from the order table;

A2-B4 lists the maps of major cities and provinces;

A6 Summary by province group;

A7 and A8 are grouped by the specified city;

Finally, the city data is inserted into the A6 provincial summary result through A9.

 

Report call

Use the report designer to create a report, use the dataset type of the Set calculator, and select the Set Computing script (age. DFX) Edited above)

Set a report template and Expression

Based on the two instances above, we can see the differences between the statement-based and the report-based implementation methods:

1. Since the computing layer and the presentation layer are separated, the report development is clearer, and complex computing tasks are completed in the Set calculator, the report itself is only responsible for data presentation (only simple expressions can be written in the report template );

2. The set calculator has a complete Computing System and rich function libraries, such as P. enum () for enumeration grouping and P. align () completes alignment grouping, while the former has three function options, and the latter has six function options. Such a rich library of functions, so that almost all complex operations (Data Source preparation) can be completed in the Set calculator;

3. In addition to a wide range of functions, the assembler adopts a step-by-step encoding method. No matter how complicated the computing logic is, it can be gradually implemented in the assembler, this solves the difficulty of writing and maintaining a statement that can only be written in one expression in the statement.