Common methods for processing cross-row group computing reports and solutions for computing reports and computing reports

Common methods for processing cross-row group computing reports and solutions for computing reports and computing reports
Report description

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:

Note that the ratio of the same month last year to the same month last year is null if no corresponding month exists.Show only current year data.

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:

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:

Report Template and expression settings

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

The dataset parameters are as follows:

Report Template and expression settings

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.

 

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.

Compile the computing script

UseComputing Script EditorCompile a set computing script and use the system default data source demo.

Compile a script to complete year-on-year comparison calculation (the cell running grid value is displayed on the right ):

A1: connect to the data source demo;

A2: Obtain the order data for this year and last year based on the year parameter;

A3: Close the data connection;

A4: sort by order date;

A5: group by year and month, and summarize the order amount;

A6: computing ratio in the previous period. Here we noticed that [-1] was used to reference the previous record;

A7: sort by month;

A8: calculate the ratio of the same month to the previous year, that is, the same-period ratio;

A9: select only the data of the current year and use A10 as the report return result set.

Edit a Report Template

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

Create a report template and set report parameters:

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 arg1 parameter is the script parameter, year is the Report Template parameter. Here, the report input parameter is passed to the script for data computing. In fact, the two parameters can have the same name.

 

The Report Template and expression are as follows:

As you can see, you can quickly complete the report of cross-row group operations using the set operator script. In addition, the external 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. The second report requirement is described as an example. You can use the script dataset 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;

Here we can see that the data source demo and the parameter year defined in the report are directly used in the script dataset, which is simpler and more direct than the separate set computing script.

3. The report template and expression are the same as those using the dataset of the cube.