The common method of processing the cross-row group operation report and the solution of the set report

Report Description

The order amount is counted according to the order form and the specified year (parameter), and the monthly amount compared with the previous order, and the same month amount compared with last year. The report style is as follows:

It is important to note that--compared to the same period last year, the ratio of the same month to last year, no corresponding month is empty; requires only the current year data to be displayed.

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:

method One: Multiple data sources + hidden columns

The comparison is done by first reading the data for this year and last year through two datasets, and then displaying the data for two years in the report template, respectively.

DataSet settings

with DS1 and DS2 data for this year and last year, the datasets are set as follows:

Report templates and expression settings

Where you need to set column D to hide so that the report does not show last year's data.

This implementation is relatively simple, without complex expressions can be achieved, but the disadvantage is to use a multi-data source, from the same data table filtered two times to output two data sets, the efficiency is low. In addition, hiding column D also affects report performance.

Method Two: Displacement coordinate + hidden column

The displacement coordinates provided by the dry report are used to assist in the way of hiding the rows and columns by the order date.

DataSet settings

The data set parameters are as follows:

Report templates and expression settings

in this way, through the high-level coordinates of the dry report to avoid the multi-source, embodies the strength of the dry report, but still rely on the hidden ranks to achieve, and E3 in the calculation of the ratio of the calculation expression is too difficult to write and understand (to understand the concept of the main lattice, The use of displacement coordinates and how to refer to the current lattice's main grid in a lattice expression also poses difficulties for report development and maintenance.

Whether it is a multi-data source or a hierarchical coordinate, relying on hidden rows and columns is due to the need to complete the same chain calculation in the report, and often such calculations are more complex. This is because traditional reporting tools mix data calculations (data source preparation) with report rendering (even if the powerful dry report is no exception), and if you separate the two parts, the report development will be clearer.

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 report requirements can be done using a collection report.

writing a collection script

Use the set-up Script Editor to write a set-up script using the system default data source demo.

Write a script to complete the quarter-on-year calculation (the right side is the cell run value):

A1: Connect data source demo;

A2: Take out the order data for this year and last two years according to the year parameter;

A3: Close the data connection;

A4: sorted by order date;

A5: Grouped by year and year, and aggregate order amount;

A6: Calculate the previous period, note that using [-1] to refer to the previous record;

A7: Sort by month;

A8: Calculates the ratio of the same month and the previous year, i.e. the same period ratio;

A9: Selects only this year's data and returns the result set for the report through A10.

edit a report template

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

new report template settings report parameters:

to set up the collector dataset, call 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 arg1 is the script parameter, and year is the report template parameter, where the report input parameters are passed to the script for data calculation. In fact, two parameters can have the same name.

report templates and expressions are as follows:

As you can see, you can use the collector script to quickly complete a report across row group operations. Moreover, the external set-up 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 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. Using the second reporting requirement as an example, you can do this with a script dataset:

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 and parameter year that are defined in the report are 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.

The common method of processing the cross-row group operation report and the solution of the set report