Organization tree by tree dataset

In the Organization tree sample, the ID and the parent ID are used to implement the organization tree report. If there are many levels, it will be cumbersome to set filter conditions and shapes for each cell. Therefore, FineReport provides a special dataset-tree dataset , Only need simple settings to automatically recurse out the hierarchy, and it is convenient to realize the organization tree report as shown in the figure below:

Figure 1: Vertical organization tree

Figure 2: Horizontal organization tree

The tree dataset is generally constructed in two steps:

1） Add ordinary datasets with hierarchical relationships;

2） Build a tree dataset based on a common dataset.

1. Add a normal dataset

Add dataset ds1 to retrieve the original data, the SQL statement is SELECT * FROM CorporationDepartment.

2. Build the tree dataset

1） Build a tree based on the parent field

Use case: The original table structure conforms to the ID and parentID structure. We can generate a tree through the parent ID field and add a tree data et, as shown in the figure below.

It needs to be emphasized that in this case, a unique root node is required (the node whose parentID is empty in the figure below). If not, then the index cannot be established normally when building the tree. Finally, some functional problems are likely to occur when using the drop-down tree control in the foreground, such as the control cannot give default values, such as fuzzy search.

2) Build a tree according to the length of the data

Use case: All IDs 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:

3. Tree dataset preview

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

1. Template design

Drag the corresponding data column into the cell as shown in the figure below, the default cell is expanded vertically and the left parent is set: the left parent cell of A2 is set to A1, and the left parent cell of A3 is set to A2:

2. Conditional attributes hide blank lines

With the above preview data, you can see that there will be blank data starting from the second level FR_GEN_1. This is because the data stored in the database has the department name and department ID of the upper-level department, and the department level of the upper-level department will be Lower level, for example, the first line of the above data is the headquarters. Although there are sub-departments under the headquarters, the department name and department ID of the headquarters must be stored in the database. The level corresponding to the headquarters is level 1, then the corresponding There is only FR_GEN_0 layer in the data record row of the, and the following two layers, FR_GEN_1 and FR_GEN_2, will have no data and are displayed as blank.

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

Note: If the level structure of the organization structure is uncertain, that is, some levels have sub-levels, and some levels donot have sub-levels, please refer to Irregular organization tree.

3. Other settings

Since the automatically generated field is a code, you can use the data dictionary to convert it to the corresponding department name, as shown in the figure below:

4. Effect preview

1) PC 

As shown in Figure 1 in the Overview.

2) Mobile

App and HTML5 have the same effect, as shown in the following figure:

5. Template download

For the completed template, please refer to %FR_HOME%\webroot\WEB-INF\reportlets\doc-EN\Advanced\ReportApplication\BasicApplication\Organization tree 

by tree dataset.cpt.

Click to download the template: Organization tree by tree dataset.cpt

1. Template design

Drag the corresponding data column into the cell as shown in the figure below, set the cell to expand horizontally and set the parent cell: the left parent cell of cell B1 is set to A1, and the left parent cell of cell C1 is set to B1:

2. Conditional attributes hide blank columns

With the above preview data, you can see that there will be blank data starting from the second level FR_GEN_1. This is because the data stored in the database has the department name and department ID of the upper-level department, and the department level of the upper-level department will be Lower level, for example, the first column of the above data is the headquarters. Although there are sub-departments under the headquarters, the department name and department ID of the headquarters must be stored in the database. The corresponding level of the headquarters is the first level. There is only FR_GEN_0 level in the corresponding data record column, and the following two levels, FR_GEN_1 and FR_GEN_2, will have no data and are displayed as blank.

In the process of template creation, 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 in the following figure:

3. Other settings

Since the automatically generated field is a code, you can use the data dictionary to convert it to the corresponding department name, as shown in the figure below:

4 Effect preview

1) PC

As shown in Figure 2 in the Overview.

2) Mobile

App and HTML5 have the same effect, as shown in the figure below:

1. Create a new dataset

Create a database to query the dataset ds1, and then call stored procedure name stored procedure parameter in the dataset ds1, and then use ds1 to generate the tree dataset.

Note: If you call the database stored procedure to fetch, the official only supports the query statement select for fetching, other writing methods (such as the following writing), the returned result is not guaranteed, and it is not recommended to use.

The specific steps are as follows:

To create a new database query, enter:{call username.package.procedure('${p1}','${p2}','${p3}',?)}, And then set the initial value of the next parameter, so that a common dataset ds1 is generated. Then use the dataset ds1 to set the tree dataset. At this time, the tree dataset can be generated normally, as shown in the figure:

2. Matters needing attention

The stored procedure directly spanning the tree data set is invalid, as shown in the figure:

Many people in the comments asked about the unknown level. We might as well use recursive thinking to sort out what is an organization tree: there is an obvious parent-child relationship between data.

Starting from a certain level, if we recursively search upwards, we can always find a limited parent level. At this time, there is no undecided level. We use the largest parent level as the number of levels.

If we search downwards recursively, the actual sub-levels are also limited. If the sub-levels continue to grow, no matter how many levels it grows to, the deepest sub-level can eventually be found.