Regular row-column cross-table for non-conventional statistics in rundry computing reports

In actual information systems, a lot of data computing is performed for front-end display. Reports are the most common form. These computing implementation processes are often not simple, and it is difficult to use SQL or stored procedures, thus affecting the front-end report design. Images. However, the computing capability of the rundry set computing report is flexible, which can take full advantage of the characteristics of the problem to meet various unconventional computing requirements. This section describes how to implement a fixed-column cross-Report solution based on the business in the link.

Report background

Source dataAs follows:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/4D/77/wKioL1RR1oui0TB8AADHrzS8ON4387.jpg "style =" float: none; "Title =" report5_nonstatic_fixedrow_1.jpg "alt =" wkiol1rr1oui0tb8aadhrzs8on4387.jpg "/>

The following content must be displayed in the report:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/4D/77/wKiom1RR1jOCDApIAACk41vPKLo334.jpg "style =" float: none; "Title =" report5_nonstatic_fixedrow_2.jpg "alt =" wkiom1rr1jocdapiaack41vpklo334.jpg "/>

Here, the total number of records should be calculated by category, and the number of records in each month should be filled to 1-12 months respectively, where the number of records without data is displayed as 0.

 

The data in this difficult report database is incomplete. Some groups may be missing after grouping by category and month. Grouping with SQL statements is troublesome. Generally, report tools do not have the ability to compute data sources. Therefore, you must complete computation in the report. For example, the following figure shows how to use the rundry report:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/4D/77/wKioL1RR1oziEPw6AAEFsBaO1h8322.jpg "style =" float: none; "Title =" report5_nonstatic_fixedrow_3.jpg "alt =" wkiol1rr1oziepw6aaefsbo1h8322.jpg "/>

When you use a dry report to complete this report, you need to use a multi-data source solution. You can write a conditional expression in the cell to complete filtering and statistical operations. Instead, you cannot use the report tool's cross-table model to automatically limit the dataset. Although it is not very difficult, the data source (SET) needs to be traversed multiple times in actual computation, and the report efficiency is low.

A set computing report has built-in scripts suitable for structured computing. You can prepare data in advance (equivalent to a simpler user-defined dataset ), the report tool simply draws data using the cross tabulation model. The preceding report requirements can be completed using the set computing report as follows:

Compile the computing script

UseComputing Script Editor,Create a set computing script and set the Script Parameters to year.

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/4D/77/wKiom1RR1jTAubktAACOzPNEJ9E343.jpg "style =" float: none; "Title =" report5_nonstatic_fixedrow_4.jpg "alt =" wkiom1rr1jtaubktaaco zpnej9e343.jpg "/>

Write a script to complete the data completing Operation, and output the computing result set for the report:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/4D/77/wKioL1RR1o7QGL2yAAE3Z-YiX8s593.jpg "style =" float: none; "Title =" report5_nonstatic_fixedrow_5.jpg "alt =" wKioL1RR1o7QGL2yAAE3Z-YiX8s593.jpg "/>

A1: connect to the data source;

A2: Execute the fetch SQL statement to obtain detailed data based on the year parameter;

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/4D/77/wKiom1RR1jXSB4wrAACvDiBI3z4713.jpg "style =" float: none; "Title =" report5_nonstatic_fixedrow_6.jpg "alt =" wkiom1rr1jxsb4wraacvdibi3z4713.jpg "/>

A3: perform cross-multiplication for the classification and month sequences, and set the data volume to 0 to obtain the result set with the classification, month, and quantity;

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/4D/77/wKioL1RR1o-BOdAwAACBoa7W1cs986.jpg "style =" float: none; "Title =" report5_nonstatic_fixedrow_7.jpg "alt =" wKioL1RR1o-BOdAwAACBoa7W1cs986.jpg "/>

A4-B5: Cycle source data, according to the corresponding fields of the A3 set corresponding to the classification and month, modify A3. Number = A3. Number + 1, the A3 set is as follows:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/4D/77/wKiom1RR1jax44LmAACQPyc2hos491.jpg "style =" float: none; "Title =" report5_nonstatic_fixedrow_8.jpg "alt =" wkiom1rr1jax44lmaacqpyc2hos491.jpg "/>

We use the features of a fixed row and column to directly calculate the location of the target data, thus avoiding the traversal-based judgment and search. However, the report tool cannot use this direct locating method to find the target record, and can only use slow condition traversal.

A7: return result set of the report.

Edit a Report Template

Use the computing report editor to edit a report template for data presentation. Default data source demo of the connection report:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/4D/77/wKioL1RR1o_iYpPYAABSFUlGFhI946.jpg "style =" float: none; "Title =" report5_nonstatic_fixedrow_9.jpg "alt =" wkiol1rr1o_iyppyaabsfulgfhi946.jpg "/>

Create a parameter and set the default value:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/4D/77/wKiom1RR1jezICJQAACEqnS9ZQA346.jpg "style =" float: none; "Title =" report5_nonstatic_fixedrow_10.jpg "alt =" wkiom1rr1jezicjqaaceqns9zqa346.jpg "/>

Create a report and set the dataset. Call the edited script file:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/4D/77/wKioL1RR1pCSiYuGAADui-flQGQ577.jpg "style =" float: none; "Title =" report5_nonstatic_fixedrow_11.jpg "alt =" wKioL1RR1pCSiYuGAADui-flQGQ577.jpg "/>

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 y_date parameter is the Report Template parameter, year is the script parameter. In fact, the two can have the same name.

To edit a report expression, you only need to use the common single-data-source cross-Report method:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/4D/77/wKiom1RR1jiBMGLNAAB2y3PAxPc757.jpg "style =" float: none; "Title =" report5_nonstatic_fixedrow_12.jpg "alt =" wkiom1rr1jibmglnaab2y3paxpc757.jpg "/>

The report results are as follows:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/4D/77/wKioL1RR1pHwL6bLAACcrMP7oWM573.jpg "style =" float: none; "Title =" report5_nonstatic_fixedrow_13.jpg "alt =" wkiol1rr1phwl6blaccrmp7owm573.jpg "/>

If the script has been debugged and does not need to be reused, you can also directly use the script dataset of the computing report, where you can directly use the data source and parameters defined in the report, the process is simpler:

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:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/4D/77/wKiom1RR1jnhuKy7AAE6oZgCZoQ224.jpg "style =" float: none; "Title =" report5_nonstatic_fixedrow_14.jpg "alt =" wkiom1rr1jnhuky7aae6ozgczoq224.jpg "/>

The data source demo and parameter y_date 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.

Regular row-column cross-table for non-conventional statistics in rundry computing reports