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 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 the 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 the organization target setting. See the 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.nodewidth - Set node width for individual node
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.toggle - Set ability to toggle subcharts on/off with option of saving the status to db
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 nodes around
dbr.org.chart.drop - Allow dropping nodes on top of eachother
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.nodeheight', ColumnReference
select 'dbr.org.chart.autobalance', '[r[,[l,level2u]]]'
select 'dbr.org.chart.animate', 0
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', procedure_name
select 'dbr.org.chart.drop', procedure_name
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
ColumnReferenceToggle
Reference to a column telling if the subchart is toggled (0) or displayed in full (1)

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 (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 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.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').change_node_position(",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 uopdate the internal JS struture for the node position and redraw the chart. If you just want to redraw the chart, you can use the recalculate_positions-method.

Rearranging the chart by moving nodes to new position

One can rearrange the chart by dragging the nodes into new positions by defining the move report procedure. The report takes two parameters: new parent id and the node id that user moved.

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;

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

Allowing dropping nodes on top of eachother

One can also allow dropping nodes on top of eachother 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

Defining and saving the node toggle status

The toggle-functionality is on by default. One can save the toggled state in 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