I. Overview
When there is a branch hierarchical relationship between things, they can generally be displayed in a folded or indented organization tree.
In the data table structure, this branch hierarchical relationship is generally represented by two fields similar to ID and parentID. The following figure shows the table structure relationship and the organization tree effect achieved. Each object of the organization is uniquely represented by the first column. The relationships between objects (upper objects) are all represented by the second column (the value is the value existing in the first column). As shown below:
II. Example
1. Prepare data
Create a new general report, add the dataset ds1, the SQL statement is: SELECT * FROM CorporationDepartment.
2. Design table
By analyzing the data in the dataset, it can be seen that the organizational structure contains three layers, so we need to take out these three layers separately. Since the Department ID column can uniquely represent a department, and the hierarchical relationship between departments is also represented by the value of the Department ID column, the design table is as follows:
1) The template has three cell organizations, and each cell expansion represents a level;
2) Drag the cells into the Department ID column in the dataset, and then set different filter conditions.
The final table effect is as follows:
1) Filtering the first layer of data
Cell A1 represents the first-level department, such as Headquarters. However, all department IDs are expanded by default, and the first-level departments need to be filtered out through filter conditions.
It can be seen from the data that the Superior ID of the first level is empty, so double-click the A1 cell, select Filter, and add the condition "Superior ID" equal to'NULL', as shown in the following figure:
2) Filtering the second layer data
Cell A2 represents the second-level department, such asHuman Resources Department, Marketing Department and so on.
Select cell A2, select Cell Attributes>Extend, set the left parent cell of A2 to A1, and the upper parent cell as the default, and expand vertically so that the department ID of this level can be automatically expanded following the ID of the parent department, as shown in the figure below Show:
Double-click cell A2, click Filter, and add the condition "Superior ID" equal to 'A1'.
Cancel the parent cell filter: Because the parent cell is set, the data columns in A1 and A2 come from the same dataset. At this time, the child cell will use the parent cell as the filter condition by default, that is, the Department ID = Superior ID will be filtered out Data, resulting in no data that meets the conditions. So you need to uncheck Use parent cell as filter
condition when filtering the A2 cell.
The settings are as follows:
The principle of the third layer is the same as that of the second layer, and the specific settings are as follows:
Set parent cell: set the left parent cell of A3 to A2, and the upper parent cell to the default.
Double-click cell A2, click Filter, and add the condition: "Superior ID" is equal to "A2" and "Superior ID" is not equal to "NULL".
Note: When the tree node is expanded, if there are multiple root nodes and no child nodes under the root node, there will be one layer left blank. It is necessary to add a condition like parentID not equal to NULL in the filter condition.
Cancel the parent cell filter
Note: If there are more than three organizational layers in the data, you can add them in the same way.
4) Cell display value settingDepartment ID is used in the template. To display it as the corresponding department name, select cells A1, A2, and A3 respectively, select Cell Attributes> Display> Data Dictionary, set the type to Data Query, dataset Is ds1, the actual value and the displayed value are DepartmentID and Department_name respectively, as shown in the figure below:
2) If some of a layer has sub-layers and some do not have sub-layers, some blank rows will appear due to expansion. You can set the conditional attribute to hide the blank rows. For details, please refer to: Hidden rows and columns.
3. Effect preview
1) PC terminal
Save the report, clickPaging Preview, the effect is as shown in the figure below:
2) Mobile
App and HTML5 have the same effect, as shown in the following figure:
4. Template download
The completed template can be found in %FR_HOME%\webroot\WEB-INF\reportlets\doc-EN\ReportApplication\BasicApplication\Organization tree sample.cpt
Click to download the template: Organization tree sample.cpt
III. Sample Index
| Help document | Application scenarios | Effect preview |
|---|---|---|
| Organization tree by tree dataset | For tree data sets, only simple settings can automatically recursively generate levels, which facilitates the realization of organizational tree reports. |
|
| Irregular organization tree | Irregular organizational structure: some hierarchical structures have sub-layers under them, and some hierarchical structures have no sub-layers under them, that is, the hierarchical structure is uncertain. | |
| Organization tree linkage | The tree diagram is displayed on the left, and the detailed information of a node in the tree is displayed on the right. When you click the organization tree on the left, the table on the right will automatically change accordingly. | |
| Dynamic folding tree | When displaying tree-level data, you can expand or collapse each level of data by clicking a button. | |
| JS Collapse or expand all tree nodes in one click | Achieve one-click to expand or collapse the collapsed tree. | |
| JS Custom number of layers of tree expansion | When the level of the organization tree is uncertain, a certain level of tree nodes can be expanded according to requirements during initialization | |
Previous：Setting Scheduled Tasks for Word Reports
