Commands

dbr.org.chart - Display organization chart
dbr.org.chart.nodeclass - Add CSS class to HTML format organization node
dbr.org.chart.nodesize - Set node sizes
dbr.org.chart.nodewidth - Set node width for individual node
dbr.org.chart.nodehspace - Horizontal space in between the boxes. Default is 30px
dbr.org.chart.nodevspace - Vertical space in between the boxes. Default is 30px
dbr.org.chart.nodehshift - The number of pixels siblings are shifted horizontally (global for all boxes). Default is 15px
dbr.org.chart.nodeheight - Set node height for individual node
dbr.org.chart.autobalance - Define 'a'-node positioning
dbr.org.chart.animate - Disable chart animation
dbr.org.chart.lineclass - Set CSS-class for the line ending to the node
dbr.org.chart.toggle - Set ability to toggle sub-charts on/off with the option of saving the status to database
dbr.org.chart.nodetoggle - Defined node's initial toggle state 1=toggled, 0=open
dbr.org.chart.tooltip - Add a tooltip to the node
dbr.org.chart.move - Allow reorganization by moving subtrees around
dbr.org.chart.drop - Allow dropping nodes on top of each other
dbr.org.target - Add a target to the node

Syntax

select 'dbr.org.chart' {,options}
select 'dbr.org.chart.nodeclass', ColumnReference
select 'dbr.org.chart.nodesize', width, height
select 'dbr.org.chart.nodewidth', ColumnReference
select 'dbr.org.chart.nodehspace', space
select 'dbr.org.chart.nodevspace', space
select 'dbr.org.chart.nodehshift', space
select 'dbr.org.chart.nodeheight', ColumnReference
select 'dbr.org.chart.autobalance', '[r[,[l,level2u]]]'
select 'dbr.org.chart.animate', 0
select 'dbr.org.chart.lineclass', ColumnReferenceLineClass
select 'dbr.org.chart.toggle', 0 | 1 | procedure_name
select 'dbr.org.chart.nodetoggle', ColumnReferenceToggle
select 'dbr.org.chart.tooltip', ColumnReference
select 'dbr.org.chart.move', move_report[, can_move_report, [callback]]
select 'dbr.org.chart.drop', drop_report[, can_drop_report, [callback]]
select 'dbr.org.target', node_id, weight, 0, color, text
Data query for HTML chart: select id, parent_id, position, label

Where position is 'r', 'l', 'u' or 'a', 'sl' and 'sr' (right, left, under, auto, spouse left and spouse right).

Where options is a JSON string. The options include:

Target parameters are: node_id, weight, 0, color, text

select 'dbr.org.chart';
select 'dbr.org.chart.nodesize', 120,30;

select id, parent_id, position, name
from mydb.organizationdata;

The topmost node has a parent_id value of null and the position is a string 'r', 'l', 'u' or 'a' (right, left, under, auto).

A node can be positioned left, right, or under its parent node. You can define the explicit position (by using left, right, or under) or let myDBR automatically balance the chart by assigning node automatic ('a') position.

When automatic balancing is chosen, myDBR will put nodes without siblings to left and right (divided equally) and the nodes with siblings to under. The left and right balancing can be adjusted with the 'autobalance' option (value of 'l' will put all nodes without siblings to left and the value of 'r' to the right).

With default automatic balancing, left and right siblings are balanced. The chart becomes wider and shorter. The "Business Support"-node is placed left as it does not have siblings whereas other nodes in the same level are placed under. This will be suitable for organization charts where there are few siblings for a node.

To create perfect organization charts, one can dynamically adjust positions by using linked reports. We'll create a table called 'organization_node_positions' which will hold the node ID and the position information. A linked report is attached to the nodes to handle the positioning of individual nodes.

select 'dbr.css', '
.organization_chart .n.ceo {background: #327F09; color: white;}
.organization_chart .n.support {background: #ececec;}
.organization_chart .n.bu1 {background: #e1e2ff;}
.organization_chart .n.bu2 {background: #BAE24B;}
.organization_chart .n.bu3 {background: #ABE5E1;}
.organization_chart .n.bu4 {background: #4B94E2;color: white;}
';


select 'dbr.resultclass', 'myorg';
select 'dbr.org.chart';
select 'dbr.org.chart.nodesize', 140,50;
select 'dbr.org.chart.nodeclass', 'nodeclass';

select 'dbr.report', 'sp_DBR_set_node_position', 'action_div[]', 'in_id=id', 'in_pos="l"', '"Position left"';
select 'dbr.report', 'sp_DBR_set_node_position', 'action_div[]', 'in_id=id', 'in_pos="r"', '"Position right"';
select 'dbr.report', 'sp_DBR_set_node_position', 'action_div[]', 'in_id=id', 'in_pos="u"', '"Position under"';

select o.id, o.parent_id, ifnull(p.position, 'u'), o.name, bu as 'nodeclass'
from mydb.organizationdata
	left join organization_node_positions p on p.id=n.id;

Where 'action_div[]' is dymanically ([]) created embed_object for the output of sp_DBR_set_node_position. The positions are set with one procedure with different parameters (l,r,u) and link name.

Where the linked report set's the node position and uses organization chart's change_node_position-method to update the chart:

create procedure sp_DBR_set_node_position( 
in_id int, 
in_pos char(1) 
)
begin

delete from organization_node_positions where id=in_id;

insert into organization_node_positions (id, pos) 
values (in_id, in_pos);

select 'dbr.javascript',concat("orgchart_from_class('myorg').changeNodePosition(",in_id,",'",in_pos,"',1);");

end

The orgchart_from_class('myorg') will get the chart JS object which has change_node_position function which will update the internal JS structure for the node position and redraw the chart. If you just want to redraw the chart, you can use the recalculate_positions-method.

One can rearrange the chart by dragging the subtrees (move) or nodes (drop) into new positions by defining the move/drop report procedure. The report takes two parameters: the new parent id and the node id that the user moved. One is able to define a report that defines if such a move between the node dragged and the node dropped onto is allowed. Finally, a callback JS-function can be defined to determine what happens in the UI when a move/drop is done.

In a simple move operation, the move_report updates the node's new parent and when no callback is defined, myDBR will automatically redraw the chart. The subtree/node is by default drawn in the same location ((u)nder, (l)eft, (r)ight) than the subtree's/node's original position. One can define the new position by returning a value from the move-procedure ('u', 'l', 'r' or 'a' for auto).

select 'dbr.org.chart.move', 'sp_DBR_organization_move_node;

create procedure sp_DBR_organization_move_node (
in_parent_id int,
in_node_id int
)
begin

update organization
set parent_id = in_parent_id
where id=in_node_id;

-- return 'r' // Would position the subtree to right. Optional

end

When the can_move_report/can_drop_report is defined, myDBR checks from the database if the user is allowed to move/drop the subtree/node on top of the specific node.

select 'dbr.org.chart.move', 'sp_DBR_organization_move_node', 'sp_DBR_organization_can_move_node';

The can_move_report/can_drop_report report gets two parameters: parent_id and node_id and returns 1 when a move can be made and 0 when the move is prohibited.

create procedure sp_DBR_organization_can_move_node (
in_parent_id int,
in_node_id int
)
begin

if (a condition determining if a move/drop can be made...) then
  select 1;
else
  select 0;
end if;

end

When a subtree move is done, myDBR will move the subtree into the new location via JavaScript and redraw the chart. In case of a node drop, myDBR will move the individual node to new the location, move the children of the moved node to be children of the previous parent (basically just moving a node from the middle to the new location).

One can extending/override the default behavior by using a callback function which is called after the database operation in can_move_report/can_drop_report is done.

  
select 'dbr.javascript', 'function moveNode(c, scr, dst, con) {
c.moveSubtree(scr, dst, con);
}';

select 'dbr.org.chart.move', 'sp_DBR_organization_move_node', 'sp_DBR_organization_can_move_node', 'moveSubtree';

The default callback for the move has a form of:

function moveSubtree(c, scr, dst, con) {
c.moveSubtree(scr, dst, con);
}

The default callback for the drop has a form of:

function dropNode(c, scr, dst, con) {
c.moveNode(scr, dst, con);
}

If you want to extend the functionality, define a JS function like described above, and add your own code to the function. If you do not want the subtree/node to be moved, skip the c.moveSubtree/c.moveNode part, and define your own action. The c-parameter exposes the API's available for the internal JS structure management.

select 'dbr.title', 'Target browser';

select 'dbr.report', 'sp_DBR_organization_set_node_position', 'action_div[]', 'in_id=id', 'in_pos="l"', '"Position left"';
select 'dbr.report', 'sp_DBR_organization_set_node_position', 'action_div[]', 'in_id=id', 'in_pos="r"', '"Position right"';
select 'dbr.report', 'sp_DBR_organization_set_node_position', 'action_div[]', 'in_id=id', 'in_pos="u"', '"Position under"';

select 'dbr.resultclass', 'orgdemo';

select 'dbr.org.chart';
select 'dbr.org.chart.nodesize', 120,40;
select 'dbr.org.chart.animate', 1;
select 'dbr.org.chart.move', 'sp_DBR_organization_move_node;

select 'dbr.org.target', organization_node_id , weight, 0, color, txt
from mydb.organizationdata;

select  
    id, 
    parent_id, 
    fn_org_node_position( id, type_id ),
    concat('<b>', name, '</b>', char(10), owner, char(10) ) /* Last linefeed makes room for the targets */
from organization
order by id;

Where 'sp_DBR_organization_set_node_position' allows node positioning. The orgchart_from_class-function takes a class defined in dbr.resultclass and get's the organization chart object which has the change_node_position-method.

create procedure sp_DBR_organization_set_node_position( 
in_id int, 
in_pos char(1) 
)
begin

delete from organization_node_positions where id=in_id;

insert into organization_node_positions (id, pos) 
values (in_id, in_pos);

select 'dbr.javascript',concat("orgchart_from_class('orgdemo').change_node_position(",in_id,",'",in_pos,"',1);");

end

And the 'fn_org_node_position'-function determines the node position. It looks for the defined position from the organization_node_positions-table, if not found, it uses the default.

CREATE FUNCTION `fn_org_node_position`( in_id int, in_type int ) RETURNS char(1)
begin

declare vPos char(1);

select pos into vPos
from organization_node_positions
where id=in_id;

if (vPos is not null) then
  return vPos;
end if;

return 'u'; 

end

This will create an organization chart where node positions can be changed by clicking into the node. Targets are shown in color and hovering over the target shows the details.

One can also allow dropping nodes on top of each other by passing the procedure name to dbr.org.chart.drop. myDBR will provide the drop functionality, one needs to define what the drop means in your installation. If one makes changes to the organization chart, one can refresh the whole chart or modify it via JavaScript.

select 'dbr.org.chart';
select 'dbr.org.chart.drop', 'sp_DBR_organization_drop';

select  
    id, 
    parent_id, 
    fn_org_node_position( id, type_id ),
    name
from organization
order by id;

create procedure sp_DBR_organization_drop(
in_parent_id varchar(10),
in_node_id varchar(10)
)
begin

// Define what to do with the drop

end

The toggle-functionality is on by default. One can save the toggled state in the database by defining the toggle-report as a parameter to dbr.org.chart.toggle. The report will take two parameters: the node id and the toggle state (1=toggled, 0 not toggled). One can also disable toggle altogether by passing 0 as a parameter to dbr.org.chart.toggle.

select 'dbr.org.chart';
select 'dbr.org.chart.nodetoggle', 'toggled'; /* Define the column stating the toggle-state for the node */
select 'dbr.org.chart.toggle', 'sp_DBR_organization_toggle'; /* Save the toggle status to db */

select  
    id, 
    parent_id, 
    fn_org_node_position( id, type_id ),
    name,
    toggled
from organization
order by id;

create procedure sp_DBR_org_movable_toggle(
in_id int,
in_toggled int
)
begin

update organization
set toggled=in_toggled
where id=in_id;

end