Run dry set calculation report how to achieve sorting within report grouping

In report development, you often encounter reports that require unconventional statistics, pre-set groupings, repeatable groupings, group sorting, and reports that cross-row group calculations, and even reports that have no data sources and need to recalculate data sources. These reports are inherently specific and often difficult to implement using conventional methods.

This article uses an example to understand the convenient process of making this kind of special statistic report in the collection report.

Report description

According to the sales management system data, the total number of orders in the customer's region for a given year, the region and the total order amount descending, requiring each region to display sales in the top five salesperson and their order quantity and order amount, other people into the "other" item. The report style is as follows:

It should be noted that "other" items (such as eastern China) are not displayed if the number of sales is less than 5 in a regional group. The trouble with this report is that more complex data calculations are needed, but the general reporting tools are less computationally capable, and it can be quite complicated to do this with the report tool itself, such as using a dry report:

In the dry report, the use of Ds.group () of a number of parameters, combined with the criteria to determine the hidden line, a variety of use methods combined with the completion of the report development, visible run-dry report of the strong.

However, because the order amount to be aggregated after grouping is sorted, the Ds1.sum (order amount) expression is applied as the parameter of the Ds.group () in the B3 grouping expression, which is less efficient for large amounts of raw data, and because only the top 5 sales are displayed, Hidden row expressions are set in C3 (rank) and D3 (others), and there is still a performance problem with larger groupings, and you need to use the Set filter expression in E3 and F3 to calculate other salesperson totals, which are calculated with cell attributes, and are still inefficient.

Run dry report is already a very powerful reporting tool, the use of other reporting tools will only be more cumbersome or even can not be achieved.

Another option is to prepare the data in advance, and the reporting tools need only be responsible for rendering and simple calculation. But whether you're programming with complex SQL, stored procedures, or custom Java datasets, it's a lot of work and difficult to maintain for such a complex operation, which is often more cumbersome than this example.

The collection report itself has a script for structured computing that makes it easy to write the calculations for data preparation (which is equivalent to a more user-friendly custom dataset). The above reporting requirements can be accomplished using a set of reports:

Writing a collection script

Use the set Script Editor to set the script parameters first.

Write scripts to complete data grouping and sorting operations.

A1: Establish database connection;

A2: Execute SQL query Order information according to the year parameter, get preliminary summary order data;

A3: The members of the group are sorted by order amount after the region grouping.

A4-c8: Cycle A3, take the top five records, and the record of more than fifth into the "other" item, the results are written back to A3.

A9: Results are returned to the report in descending order by region total.

Edit a report template

Use the Collection report editor to set up a data source for report preview.

Create a new report parameter and set a default value.

Create a new report and set up the collector DataSet to invoke the edited script file above.

Where the DFX file path can be either an absolute path or a relative path, the relative path is the DFX home directory configured in relative options, the parameter year is the report template parameter, and d_year is the script parameter, you can set the name of the two names.

Edit the report expression to complete the report production directly using the result set returned by the collection script.

For rankings, you need to set B2 Zolt to C2 and set the B2 display value expression if (b2>5, "--").

You can use the collector script to quickly complete the sorting class report in the group, with the external set-up script having a visual editing 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 would be cumbersome to maintain the consistency of the two files (both the set and report templates), which makes it easier to use the script dataset directly for the set report.

In a script dataset, you can step through a script to complete a calculation task, with the syntax consistent with the concentrator, and you can use the data sources and parameters that the report defines well. This example uses a script dataset to do this:

1. Click the "Add" button in the DataSet Settings window to pop up the dataset Type dialog and select "Script Data Set";

2. Write the script in the Popup Script dataset editing window;

As you can see here, the data source demo that is defined in the report is used directly in the script dataset, which is simpler and more straightforward than a separate set-up script.

3. Report templates and expressions are consistent with the use of the Collector dataset, and no longer repeat them.

HTTP://WWW.RAQSOFT.COM.CN/?P=208 of the collection report:

Run dry set calculation report how to achieve sorting within report grouping