The Set calculator implements scattered and irregular computing in reports.

Irregular computing in reports often brings great difficulties to report development. It is often difficult or even impossible to directly use report tools. Here, irregular calculation refers to the statistical items (calculation) with special conditions in the report. For example, in the sales order amount statistics, the total and proportion of the top five sales amounts are calculated; in the Student Score statistics, the top 10 students with a language score exceed 90. In the user recharge statistics, the top 10 students have more than half of the total value.

This type of report can be easily developed through the set calculator. We will take two examples to learn about the specific practices.

1. instance 1 1.1. Report description

The sales status of each salesperson is calculated based on the employee and order table, and the total order amount, the total number of the first three sales orders, the proportion, and the number of the first half of sales orders are listed in the report. The requirements are sorted in descending order of sales. The report style is as follows:

650) This. width = 650; "src =" http://blog.raqsoft.cn/wp-content/uploads/2014/07/esProc_report_scatteded_1.jpg "width =" 552 "Height =" 242 "alt =" esproc_report_scatteded_1.jpg "/>

1.2 set calculator code

650) This. width = 650; "src =" http://blog.raqsoft.cn/wp-content/uploads/2014/07/esProc_report_scatteded_2.jpg "width =" 553 "Height =" 205 "alt =" esproc_report_scatteded_2.jpg "/>

A1: Establish a database connection

A2: Execute SQL statements to obtain sales data, which are sorted in descending order of sales and output to the report as a dataset.

A3: Create an empty sequence table to store the summary value and output it to the report using Dataset 2.

A4: calculates the total order amount.

A5: Take the top three sales records and calculate the total order amount

A6: Ratio of the top three amounts to the total amount

A7: Temporary Variable

A8: calculates the number of people with the first half of sales

A9: insert summary data into A3

A10: return the sales data of A2 and the A3 summary result set to the report. Here, the set calculator returns two result sets for the report to be called.

1.3 report call

As the computing middleware, the set calculator encapsulates the standard JDBC interface for program calls. Here, the result is displayed using the rundry report.

650) This. width = 650; "src =" http://blog.raqsoft.cn/wp-content/uploads/2014/07/esProc_report_scatteded_3.jpg "width =" 555 "Height =" 202 "alt =" esproc_report_scatteded_3.jpg "/>

 

The report tool can complete report development with simple values.

1.4 report tool implementation

650) This. width = 650; "src =" http://blog.raqsoft.cn/wp-content/uploads/2014/07/esProc_report_scatteded_4.jpg "width =" 553 "Height =" 161 "alt =" esproc_report_scatteded_4.jpg "/>

 

Here, we have selected a relatively powerful function to run a dry report. During the implementation process, we still need to use the aid to hide cells. When there are many records, this will greatly affect the performance. In addition, the grid set and grid set expressions are used here, and hierarchical coordinate writing is difficult for report developers to understand and use, however, the step-by-step encoding method using the set calculator is clearer and easier to maintain, and the performance is better.

In this example, only the C2 (column C) Hidden lattice is used. Although the expression is slightly difficult to write, the impact on performance and understanding is still acceptable. However, if we simply change the report requirements, the situation will be very different. Let's look at the next instance.

2. instance 2 2.1. Report description

The sales status of each salesperson is calculated based on the employee and order table, and the total order amount, the total number of the first three sales orders, the proportion, and the number of the first half of sales orders are listed in the report. Records must be sorted by employee ID,No longerOrder by order amount. The report format is as follows:

650) This. width = 650; "src =" http://blog.raqsoft.cn/wp-content/uploads/2014/07/esProc_report_scatteded_5.jpg "width =" 552 "Height =" 230 "alt =" esproc_report_scatteded_5.jpg "/>

If the report requirement is changed, will it have a great impact on the implementation of the Set calculator and the dry report? See the implementation.

2.2 set operator code

650) This. width = 650; "src =" http://blog.raqsoft.cn/wp-content/uploads/2014/07/esProc_report_scatteded_6.jpg "width =" 550 "Height =" 223 "alt =" esproc_report_scatteded_6.jpg "/>

The code of the Set calculator is almost unchanged from instance 1. It only adds A5 to sort by order amount, and A6 and A9 are sorted and then recorded. Here we can see the flexibility of using the set operator encoding. What about the implementation of the Statement?

2.3 report tool implementation

650) This. width = 650; "src =" http://blog.raqsoft.cn/wp-content/uploads/2014/07/esProc_report_scatteded_7.jpg "width =" 551 "Height =" 198 "alt =" esproc_report_scatteded_7.jpg "/>

Here we can see that the rundry report obtains the sales records sorted by employee IDs through the second row, and uses the third row (A3) to hide the rows whose results are sorted by sales, so as to obtain the top three records. The third row duplicates the data and the report must be computed twice, which has a huge impact on the report performance.

In addition, columns C and the third row hidden by report maintenance personnel when they open the report template for modification cause great problems, it's hard to figure out why we need to repeat the data twice.

 

Through the above example, we can see that the Set calculator as an Independent Computing Program has great flexibility and reusability, in addition, the set calculator is very good at ordered Operations (such as taking the first three in the example). At the same time, the set calculator can return multiple result sets at a time for the report to use, this avoids repeated data computing in many cases and provides great help for report development.