Organization chart extension

The Organization chart extension allows the creation of optimized organization charts. It uses HTML5 Canvas for the charts. Organization nodes can be positioned left, right and under their ancestor node allowing the creation of compact organization charts.

In most cases, databases contain the information about the organization hierarchy (parent-child), but not the positioning of the node (left, right and under). What can be done, is that a linked report is attached to the node so that the positioning can be selected when the chart is shown. See example below.

The extension will automatically calculate the size of the chart and the nodes. You can define various parameters with the options.

Organization nodes can optionally contain targets (KPI metrics). This allows an organization chart to be used for showing organization target setting. See example below.

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.autobalance - Define 'a'-node positioning
dbr.org.chart.animate - Toggle chart animation
dbr.org.target - Add a target to the node

Syntax

select 'dbr.org.chart' {,options}
select 'dbr.org.chart.nodeclass', 'classname'
select 'dbr.org.chart.nodesize', width, height
select 'dbr.org.chart.autobalance', '[r[,[l,level2u]]]'
select 'dbr.org.chart.animate', 1
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' (right, left, under, auto).

Where options is a JSON string. The options include:

boxWidth
Node width. If not defined it will be automatically calculated.
boxHeight
Node height. If not defined it will be automatically calculated.
hSpace
Horizontal space in between the boxes. Default is 30px
vSpace
Vertical space in between the boxes. Default is 30px
hShift
The number of pixels vertical siblings are shifted (global for all boxes). Default is 15px
autobalance
Balance left and right nodes automatically. Default is 'auto'. Values can be 'l', 'r' and level2u=0 where level 2 nodes are not u-positioned (default)

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

node_id
Node where the target is attached to
weight
target width in node: weight / (sum of all target's weights in node)
target
reserved, use 0
color
Color to be used to show the target in the chart
text
Tooltip to be shown when user hovers over the target

Usage


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).

Defining the node positions

A node can be positioned left, right or under from it's 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 (didived 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 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.

Allow node positioning dynamically within the report

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.org.chart';
select 'dbr.org.chart.nodesize', 120,40;
select 'dbr.org.chart.animate', 1;

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
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("org_chart1.change_node_position(",in_id,",'",in_pos,"',1);");

end

Full organization chart with positioning and targets

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.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 organization3
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.