How to sort reports by group in a rundry set computing report

During report development, you will often encounter reports that require unconventional statistics, including preset grouping, repeatable grouping, and intra-group sorting, as well as reports computed across row groups, even some reports do not have data sources and need to be computed on the data sources. These reports have some special characteristics and are often difficult to implement using conventional methods. This article uses an example to understand

During report development, you will often encounter reports that require unconventional statistics, including preset grouping, repeatable grouping, and intra-group sorting, as well as reports computed across row groups, even some reports do not have data sources and need to be computed on the data sources. These reports have some special characteristics and are often difficult to implement using conventional methods. This article uses an example to understand

During report development, you will often encounter reports that require unconventional statistics, including preset grouping, repeatable grouping, and intra-group sorting, as well as reports computed across row groups, even some reports do not have data sources and need to be computed on the data sources. These reports have some special characteristics and are often difficult to implement using conventional methods.

This article uses an example to understand the convenient process of creating such a special statistical report.

Report description

Based on the sales management system data, the total order amount of the customer's region in a year is calculated, and the region is sorted in descending order by the total order amount, each region is required to display the sales personnel with the top five sales personnel and their order quantity and order amount. Other personnel are classified into "other" items. The report style is as follows:

Note that "other" items (such as China East) are not displayed if there are fewer than five sales personnel in the region group ). The trouble with this report is that it requires complicated data computing work, but the computing capability of the report tool is generally weak. If you use the report tool itself to complete this computation, it will be quite complicated, for example, to use a dry report, it will be like this:

In a dry report, multiple parameters of ds. group () are used to determine hidden rows based on conditions. Multiple usage methods are combined to complete report development. This shows the strength of the dry report.

However, to sort the order amount After grouping, use the ds1.sum (order amount) expression in the B3 grouping expression as ds. the group () parameter is inefficient when the raw data size is large. Moreover, because only the first five sales are displayed) when a hidden row expression is set in, performance problems still exist in the case of many groups. In addition, you need to use the grid set filter expression in E3 and F3 to calculate the total number of other sales personnel, computing with cell attributes is still inefficient.

Rundry reports are already a report tool with strong computing capabilities. using other report tools will be more troublesome or even impossible.

Another method is to prepare data computing in advance, and the report tool only needs to be responsible for presentation and simple computing. However, no matter how complex SQL statements, stored procedures, and custom JAVA dataset programming are used, such complex operations (the actual situation is often more complicated than this example) it is a heavy workload and difficult to maintain.

The computing report itself has built-in scripts suitable for structured computing, allowing you to easily write data preparation calculations (equivalent to a simpler user-defined dataset ). The preceding report requirements can be completed using the set computing report as follows:

Compile the computing script

UseComputing Script EditorSet script parameters.

Write a script to complete grouping, aggregation, sorting, and other operations.

A1: Establish a database connection;

A2: Execute an SQL statement to query order information based on the year parameter to obtain the initial summary of order data;

A3: After grouping by region, group members are sorted by order amount.

A4-C8: loop A3, take the first five records, and more than the fifth records are classified into "other" items, the results are written back to A3.

A9: the results are sorted in descending order of the total region and returned to the report.

Edit a Report Template

UseComputing report editor, Set the data source for report preview.

Create a report parameter and set the default value.

Create a report and set the dataset, and call the edited script file.

The dfx file path can be either an absolute or relative path. The relative path is the dfx home directory configured in the relative options. The year parameter is a report template parameter, d_year is the script parameter. You can set the duplicate names of the two.

Edit the report expression and directly use the result set returned by the Set Computing script to complete report creation.

For ranking, you need to set the left main grid of B2 to C2, and set the B2 display value expression if (B2> 5 ,"--").

The Set Computing script can be used to quickly complete group sorting reports. The external set computing script has a visual editing and debugging environment, and the edited script can be reused (called by other reports or programs ). However, if the script has been debugged and does not need to be reused, It is troublesome to maintain consistency between the two files (the Set Computing script and Report Template, in this case, it is easier to directly use the script dataset of the Set Computing report.

In the script dataset, you can write scripts to complete computing tasks step by step. The syntax is the same as that of the Set calculator. You can also directly use the data sources and parameters defined in the report. In this example, the script dataset can be used as follows:

1. Click "add" in the dataset settings window. The dataset Type dialog box is displayed. Select "script dataset ";

2. Compile the script in the pop-up script dataset editing window;

: Http://www.raqsoft.com.cn /? P = 208.