The TreeBuilder Plugin is available to create a "tree table", with all necessary information to build a tree-like presentation in reports.
Syntax:
PLUGIN TreeBuilder vResult +
|TABLE <TableName> +
|ID <IDColumn> +
|PARENT <ParentColumn> +
|ORDER <OrderColumn> +
|TREE_TABLE <TreeTableName>
Options:
Option |
Value |
Description |
TABLE |
value |
Specifies the source table |
ID |
value |
Specifies the ID field in the source table |
PARENT |
value |
Specifies the parent field in the source table |
ORDER |
value |
Specifies the field that controls sorting (optional). This is usually the field to be the node's text. |
TREE_TABLE |
value |
Specifies the newly created output table |
Where:
vResult is the text variable to return the status, such as "OK" or the exact -ERROR- message
Notes:
The created "Tree Table" output table has the following structure:
CREATE TABLE <TreeTable> +
(TREE_TABLE_ID INTEGER, +
TREE_TABLE_PARENT_ID INTEGER, +
TREE_TABLE_ROW_ID INTEGER, +
TREE_LEVEL INTEGER, +
TREE_HAS_CHILDREN INTEGER)
•TREE_TABLE_ID - Incrementing integer starting with 1
•TREE_TABLE_PARENT_ID - Value of the source table's parent field
•TREE_TABLE_ROW_ID - Value of source table's ID field
•TREE_LEVEL - The node's level. Outermost node is zero (0), then next level is one (1), ...
•TREE_HAS_CHILDREN - Indicator if node has children. One (1) if node has children, zero (0) if no child nodes
RRBYW20 Sample:
PLUGIN TreeBuilder vResult +
|TABLE Departments +
|ID DepartmentID +
|PARENT OwnerDept +
|ORDER DepartmentID +
|TREE_TABLE DeptTree
A view is created based on the tree output table and the source table. The view is then used as the report's data source.
CREATE VIEW `DepartmentTree` +
(TREE_TABLE_ID,TREE_TABLE_PARENT_ID,TREE_TABLE_ROW_ID,+
TREE_LEVEL,TREE_HAS_CHILDREN,+
TREE_VEW_CAPTION,DepartmentID,Description,OwnerDept) AS +
SELECT T1.TREE_TABLE_ID,T1.TREE_TABLE_PARENT_ID, +
T1.TREE_TABLE_ROW_ID,T1.TREE_LEVEL,T1.TREE_HAS_CHILDREN,+
((SFIL(' ', T1.TREE_LEVEL*5)) + T2.Description), +
T2.DepartmentID,T2.Description,T2.OwnerDept +
FROM DeptTree T1 LEFT OUTER JOIN Departments T2 +
ON T1.TREE_TABLE_ROW_ID = T2.DepartmentID
With the following variables defined in the report expression builder;
1 : INTEGER : D : vTreeLevel = TREE_LEVEL
2 : INTEGER : D : vHasChildren = TREE_HAS_CHILDREN
the report Detail band's "On Before Generate" EEP can use the TREE_LEVEL and TREE_HAS_CHILDREN fields to manipulate the label and image locations, to emulate a tree view presentation.
-- Use TREE_LEVEL and TREE_HAS_PARENT to indent for the tree view
--Node label
SET VAR vMargin DOUBLE = 1.5
SET VAR vLevelIndent DOUBLE = 0.2
SET VAR vLeft = .vMargin + (.vLevelIndent * .vTreeLevel)
PROPERTY lblCaption LEFT .vLeft
--Node image. A few mms to the left of the label
SET VAR vLeft = (.vLeft - 0.3)
PROPERTY imgParent LEFT .vleft
PROPERTY imgChild LEFT .vleft
IF vHasChildren = 0 THEN
PROPERTY imgParent VISIBLE FALSE
PROPERTY imgChild VISIBLE TRUE
ELSE
PROPERTY imgParent VISIBLE TRUE
PROPERTY imgChild VISIBLE FALSE
ENDIF
RETURN
Using this approach, a tree view presentation with unlimited node levels can be created. In addition, the tree structure in a report can be created with very complex appearance (e.g. a sub report inside each node). This was made possible by the TREE_LEVEL and TREE_HAS_CHILDREN fields.
