Computation reports for cross-row groups and Improvement

Cross-row group computing reports are very common in report development, such as common same-period comparison, period-over-period comparison calculation, and moving average calculation. This type of reports often reference data in other columns for relative/absolute location operations, making the report implementation not easy. The implementation of the rundry report requires the help of hierarchical and displacement coordinates, supplemented by conditional expressions and the "$" operator. The following example describes how to implement the statement.

Report requirements

The order amount is calculated by month based on the order table and specified year (parameter), and compared with the monthly amount of the previous order, and the same month amount as last year. The report style is as follows:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/45/37/wKiom1PkahHRyav1AAEL5_5BS64867.jpg "style =" float: none; "Title =" 1.jpg" alt = "wkiom1pkahhryav1aael5_5bs64867.jpg"/>

It should be noted that, compared with the same period of last year, it refers to the ratio of last year to the same month of last year. If there is no corresponding month, it is null; only the current year data is required.

Method 1: multiple data sources + hidden Columns

First, you can use two datasets to read data from this year and last year, and then associate the data in the report template to display the data in the same month of the previous two years for comparison.

Dataset settings

Use ds1 and DS2 to retrieve data from this year and last year respectively. The dataset settings are as follows:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/45/38/wKioL1Pkay6wq6YVAAFCeIiCqZ4146.jpg "style =" float: none; "Title =" 2.jpg" alt = "wkiol1pkay6wq6yvaafceiicqz4146.jpg"/>

Report Template and expression settings

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/45/37/wKiom1PkahfCyw31AACOnOYXqDY413.jpg "style =" float: none; "Title =" 3.jpg" alt = "wkiom1pkahfcyw31aaconoyxqdy413.jpg"/>

Column D needs to be hidden so that the last year's data is not displayed in the report.

 

This implementation method is relatively simple and can be implemented without complex expressions. However, the disadvantage is that multiple data sources are used to filter two data sets from the same data table and output them separately, which is less efficient to obtain data. In addition, hiding column D also affects report performance.

Method 2: displacement coordinates + hidden Columns

By grouping by subscription year and month, you can use the displacement coordinates provided by the rundry report to help you hide rows and columns.

Dataset settings

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/45/38/wKioL1PkazTi9iyGAAEn8wmFjrw506.jpg "style =" float: none; "Title =" 4.jpg" alt = "wkiol1pkazti9iygaaen8wmfjrw506.jpg"/>

The dataset parameters are as follows:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/45/38/wKioL1PkazjyoHjeAAG0r63-VOI227.jpg "style =" float: none; "Title =" 5.jpg" alt = "wKioL1PkazjyoHjeAAG0r63-VOI227.jpg"/>

Report Template and expression settings

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/45/37/wKiom1PkaiKA3ISEAADGL0hYAIc834.jpg "style =" float: none; "Title =" 6.jpg" alt = "wkiom1pkaika3iseaadgl0hyaic834.jpg"/>

This method avoids multiple sources by using the advanced Syntax of hierarchical coordinates in the rundry report, reflecting the strength of the rundry report, but still relies on hidden rows and columns, in addition, it is too difficult to write and understand the computation expression of the copeat ratio in E3 (to understand the concept of the sub-grid of the main grid, the use of the displacement coordinate, and how to reference the main grid of the current grid in the lattice set expression), this also makes it difficult to develop and maintain reports.

 

Whether it is a multi-data source or hierarchical coordinate, relying on hidden rows and columns is because the year-on-year comparison and period-over-Period Calculation needs to be completed in the report, and such calculation is often complicated. This is because traditional report tools mix data computing (Data Source preparation) and report Presentation (even powerful reports are no exception). If these two parts are separated, this will make the report development clearer.

Based on the statement, runqian added an engine set calculator for complex data computing to form a new generation of powerful computing report tool set computing report version 5.0. The report template can only be used for report presentation, separate computing and presentation, and sort out the report structure to make the report production clearer and simpler.

The report requirements in this article can be achieved through the following methods:

Set Computing report implementation

Compile the computing script

Use the computing script editing tool to complete the year-on-year comparison computing script and output the computing result set for the report:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/45/37/wKiom1PkaiXw8anXAAHlVgrc3Bo907.jpg "style =" float: none; "Title =" 7.jpg" alt = "wkiom1pkaixw8anxaahlvgrc3bo907.jpg"/>

A1: Execute an SQL statement to read the Order data of the current year and last year from the order and order list based on the specified year

A2: sort by order date in ascending order

A3: group by the year and month of the order date, and summarize the order amount

A4: add the calculated field "compare with the previous period", that is, the amount of this month/The amount of the previous order month

A5: sort by month in ascending order of Order Date

A6: add the calculated field "compared with the same period of last year", that is, the amount of the current month/The amount of the previous month

A7: select the data of the specified year.

A8: return result set for 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 (orders. DFX) Edited above)

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/45/38/wKioL1Pka0GQmEkWAAEZdq9DkrA743.jpg "style =" float: none; "Title =" 8.jpg" alt = "wkiol1pka0gqmekwaaezdq9dkra743.jpg"/>

Report Templates and expressions

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/45/37/wKiom1PkaimQSZ88AACMaSnh74w415.jpg "style =" float: none; "Title =" 9.jpg" alt = "wkiom1pkaimqsz88aacmasnh74w415.jpg"/>

You can use a simple list to directly obtain the computing result of the computing script.

 

You can see the following changes when using a set computing report:

1. Simpler. The computing script can be compiled step by step to split the complex computation that needs to be written in an expression in the original dry report into multiple steps, which is gradually implemented, reducing the complexity of the problem;

2. more flexible. A rich array of function libraries are available for computing devices. You can select multiple methods to implement the same computing task. For example, you can directly use. groups (), and. group () and then summarize;

3. higher performance. When computing a set computing script, it does not have any display attributes (which is very different from the general report tool). Instead, it only returns the computed result set to the report for better performance. In addition, as the computing reports do not rely heavily on hidden columns that affect the report efficiency, the report performance is further improved.