Shulou(Shulou.com)06/01 Report--

1. Problem description

FineReport, the organization tree report is implemented by id and parent id in the organization tree report. If there are many levels, it will be cumbersome to set the filter condition and shape for each cell. Therefore, FineReport provides a special data set-tree data set, which can be recursive automatically with simple settings. It is convenient to implement the organization tree report as follows:

Figure 1

Figure 2

2. FineReport build tree

2.1 create a new report and add dataset

Create a workbook, add a dataset ds1 to fetch the original data, and the SQL statement is SELECT * FROM company department.

2.2 define a tree dataset

1) build a tree based on parent fields

Use case: the original table structure conforms to the ID and parentID structure. We can use the parent ID field to generate a tree and add a tree dataset, as shown below:

2) build the tree according to the data length

Use case: all the ID in the original table structure are in one column, and there is no parent ID field, but the ID is regular, the length of each group is the same, and the first N bits of the child are the parent number, add the tree dataset, as shown below:

2.3 Preview data

If you preview the tree dataset, you can see that the recursive tree data has been automatically generated. FR_GEN_0 is the highest level, descending in turn, as follows:

3. Example 1 Longitudinal Organization Tree

Drag the corresponding data column into the cell as shown below, and set the left parent of the A2 cell to the left parent of the A1 Magi A3 cell to A2:

3.1 conditional attributes

With the preview data above, we can see that starting from the second-tier FR_GEN_1, there will be blank data. This is because the data stored in the database has the department name and department ID of the department at the next level, and the department level of the department above is one level lower. For example, the first behavior headquarters of the above data, although there are sub-departments under the headquarters, the department name and department ID of the headquarters department should be stored in the database. If the corresponding level of the headquarters is level 1, then there is only FR_GEN_0 layer in the corresponding data record row, and the following two layers, FR_GEN_1 and FR_GEN_2, will have no data and appear blank.

In the process of making a template, there will be blank data from the second level. You need to hide the blank data. Select A2 and A3 cells, add conditional attributes, and hide the row when the data is empty, as shown below:

Note: if the hierarchical structure of the organizational structure is uncertain, that is, some levels have sub-layers and some levels do not have sub-layers, the implementation of the organizational tree report, please see the non-standard organizational tree report

3.2 other Settings

Since the automatically generated field is encoded, you can use a data dictionary to change it to the corresponding department name, as shown in the following figure:

3.3 Save and Preview

Save the template and click the paging preview, as shown in figure 1

4. FineReport example 2 horizontal organization tree

Drag the corresponding data column into the cell as shown in the following figure, and set the expansion direction of B1 and C1 cells to horizontal in the right cell property sheet-extended properties.

And set the left parent of the B1 cell to B1, and the left parent of the C1 cell to B1:

4.1 condition attribute

With the preview data above, we can see that starting from the second-tier FR_GEN_1, there will be blank data. This is because the data stored in the database has the department name and department ID of the department at the next level, and the department level of the department at the upper level will be one level lower. For example, the first part of the above data is listed as the headquarters. Although there are sub-departments under the headquarters, the department name and department ID of the headquarters department should be stored in the database. If the corresponding level of the headquarters is level 1, then there is only FR_GEN_0 layer in the corresponding data record column, and the following two layers, FR_GEN_1 and FR_GEN_2, will have no data and appear blank.

In the process of making a template, there will be blank data from the second level. You need to hide the blank data. Select cells B1 and C1, add conditional attributes, and hide the column when the data is empty, as shown below:

4.2 other Settings

Since the automatically generated field is encoded, you can use a data dictionary to change it to the corresponding department name, as shown in the following figure:

4.3 Save and Preview

Save the template and click the paging preview, as shown in figure 2.

5.FineReport sample 3 stored procedure creates a tree dataset

5.1 problem description

The directly spanning tree dataset of the stored procedure is invalid, as shown in the figure:

5.2 solution

First establish the database query dataset ds1, then "call stored procedure name stored procedure parameters" in the dataset ds1, and then use ds1 to generate the tree dataset.

If you call the database stored procedure to fetch a number, you can only use the query statement select to fetch the number. For other writing methods (such as the following), the returned result is not guaranteed and is not recommended:

The specific steps are as follows:

Create a new database query, enter: {call username.package.procedure ('${p1}','${p2}','${p3}','${p3}',?)}, and then set the initial values of the following parameters, resulting in a normal data set ds1, as shown in the figure:

Then through the dataset ds1, set the tree dataset, and then you can generate the tree dataset normally, as shown in the figure:

The rest of the operation is the same as described in this article, except that the organization tree report is implemented through the stored procedure, and the step of calling the stored procedure needs to be added.

This article is excerpted from the blog Garden: http://www.cnblogs.com/laoA188/p/6944560.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.