Create join and cross-row computing reports

Create join and cross-row 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. For example, a statement (including an inventory coverage period, which facilitates production scheduling or procurement tasks) can be written in SQL statements. Generally, report tools do not have powerful computing capabilities and cannot be directly completed. 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 the computing inventory coverage cycle (associated cross-row calculation) report based on the business in the link.

Report background

Source dataAs follows:

In the above two tables, the inventory table records the current inventory, and the inventory demand table shows the monthly product requirements, so that the current month's inventory can cover several months.Example:

For example, the 201401-month C111 inventory is 100,

201401-20 = 80,

201402 month inventory demand 20, that is, 80-20 = 60,

The inventory demand for the Month of 201403 is 50, that is, 60-50 = 10,

201404 month inventory demand 20, that is, 10-20 =-10, cannot meet the demand.

That is, the 201401-month C111 inventory can only cover 3 months, with a cycle of 3.

 

The report style is as follows:

The difficulty of this report is that, after matching the relevant information in the inventory demand table according to the product code, inventory requirements and current inventory quantity should be compared cyclically to calculate the inventory coverage period. It is difficult to complete similar cyclic computing in SQL. Generally, report tools do not have the data source computing capability and must complete computing in reports. For example, the following figure shows how to use the rundry report:

When using the rundry report to complete this report, you need to use temporary variables (for report tools that cannot use temporary variables, it is more difficult to implement) and hide cells (e g h three columns as auxiliary columns, need to hide), and multi-data source solutions. While increasing the difficulty of report implementation, because the data source needs to be traversed multiple times and many extended hidden cells are used, the report efficiency is low and difficult to understand and maintain.

A set computing report has built-in scripts suitable for structured computing. You can prepare the data in advance (equivalent to a simpler user-defined dataset). The report tool simply needs to display the data in numbers. 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 parameter to year.

Write scripts to complete data completion and output the computed result set for the report.

A1: connect to the data source;

A2: Execute the fetch SQL statement to obtain the inventory demand data based on the year parameter and sort the data by time;

A3: Execute the fetch SQL statement to obtain inventory data, and add the field inventory overwrite period field (c_count). The value is initialized to 0;

A4-B6: cyclic inventory table, traverse the inventory demand table according to the specified product code, calculate the inventory coverage period, and change the c_count value in A3, the logical process is clear, easy to understand and maintain;

A7: return result set of the report.

Edit a Report Template

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

Create a parameter and set the default value.

Create a report and 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 report template parameters and Script Parameters are both ym, in fact, they can be different.

Edit the report expression. In this case, you only need to use the normal grid report production method to display the number:

The report results are as follows:

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;

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