ChartDirector offers a variety of options for chart formatting. ChartDirector is implemented using PHP class directives, and for native commands, myDBR offers direct access to the myDBR's ChartDirector's chart-object ($c), to the layer-object ($layer), and to trend layers array ($trend_layers). Commands are passed as PHP-code snippets using 'dbr.chart.options', 'chartdirector' command.

select 'dbr.chart.options', 'chartdirector', '$c->chartdirectorcommand';

In the example, we'll change the y-axis to logarithmic and set minimum and maximum values, display only every second x-axis label, set the border and background to gray and finally display a drop shadow for the chart.

  select 'dbr.chart', 'Line', 'Chart';

  select 'dbr.chart.options','rotateNames', 90;
  select 'dbr.chart.options','showvalues', 0;
  select 'dbr.chart.options','chartdirector', '$c->yAxis->setLogScale(300,700);$c->xAxis->setLabelStep(2);';
  select 'dbr.chart.options','chartdirector', '$c->setBorder(0xEEEEEE);$c->setBackground(0xeeeeee);$c->setDropShadow();';

  select Name, sum(Items)
  from mydb.TestTable
  where year(InsertDate)=2008
  group by Name;
  

For a full description of ChartDirector's class library, see the ChartDirector documentation.

If you have multiple charts and wish to keep the same colors between different charts you can define colors based on labels (or series). If you use the same color for the same labels in many places, it is easier to create a helper procedure so your definitions can be found in one place.

You can also leave out the optional color definition. If one is not set, the color is defined from the normal color set.

  select 'dbr.chart.options', 'label_color', 'Q1', '0x006F00';
  select 'dbr.chart.options', 'label_color', 'Q2', '0xFF66CC';
  select 'dbr.chart.options', 'label_color', 'Q3', '0x9900FF';
  select 'dbr.chart.options', 'label_color', 'Q4', '0x99FFCC';

  select 'dbr.chart', 'mscolumn';

  select Name, Quarter, sum(Items)
  from mydb.TestTable
  group by Name, Quarter;

  select 'dbr.chart.options', 'label_color', 'Q1', '0x006F00';
  select 'dbr.chart.options', 'label_color', 'Q2', '0xFF66CC';
  select 'dbr.chart.options', 'label_color', 'Q3', '0x9900FF';
  select 'dbr.chart.options', 'label_color', 'Q4', '0x99FFCC';

  select 'dbr.chart', 'Column';

  select Quarter, sum(Items)
  from mydb.TestTable
  group by Quarter;
  

In the example, we'll generate a simple pie chart. As for any other XY-chart we need to provide X and Y values for the chart.

select 'dbr.chart', 'Pie', 'Sample Pie chart';

select category, total_sales
from mydb.sales_by_film_category;

In the multi-series charts, we have multiple Y-values. In the following example, we'll select data for each quarter for selected years and make a multi-series column chart out of the data.

select 'dbr.chart', 'mscolumn';
select 'dbr.chart.options', 'showValues', 1;

select production_year, production_quarter, value 
from mydata;

Multi-series charts can easily be transposed so that the categories and the series will swap places. We'll use the same query as in the previous example and we'll transpose the chart prior to showing it. This is done with a chat option 'chart_transpose'.

select 'dbr.chart', 'mscolumn';
select 'dbr.chart.options', 'showValues', 1;
select 'dbr.chart.options', 'chart_transpose';

select production_year, production_quarter, value 
from mydata;

Hierarchical and diagram charts will produce a network chart. In a hierarchical chart, there is a parent-child connection, while in diagram-charts there is just a connection between the nodes. Both charts are generated the same way, the only difference being the chart type.

select 'dbr.chart', 'Hierarchy';

select 'Corporate', 'Sales'
union
select 'Corporate', 'Production'
union
select 'Corporate', 'Management'
union
select 'Sales', 'Northern Sales'
union
select 'Sales', 'Southern Sales'
union
select 'Production', 'Top part production'
union
select 'Production', 'Bottom part production';

The example is a query against the MySQL's sakila-demo database's system tables in order to draw a quick ER-diagram. The chart is a Hierarchy-chart and with the diagram-option the chart is set to Left-to-Right (see Graphviz documentation for more info):

select 'dbr.chart', 'Hierarchy';
select 'dbr.chart.gv.style', 'graph', 'rankdir=LR';
select 'dbr.chart.gv.style', 'node', 'shape=box,fillcolor=#CCCCFF,style=filled';


select distinct referenced_table_name, table_name
from information_schema.key_column_usage
where table_schema='sakila' and referenced_table_name is not null
order by referenced_table_name;

The boxwhisker chart consists of the bottom and top values. Optionally you can add min, max, and middle values

select 'dbr.chart', 'Hierarchy';
select 'dbr.chart.gv.style', 'graph', 'rankdir=LR';
select 'dbr.chart.gv.style', 'node', 'shape=box,fillcolor=#CCCCFF,style=filled';

select label, box_min, box_max, minim, maxim, middle
from mydb.data;

A floating box chart is a variation of boxwhisker chart which draws independent boxes for series/label with min-max

select 'dbr.chart', 'floatingbox', 'floatingbox';

select series, label, box_min, box_max
from mydb.data;

A sample bubble chart with options

declare vTechA varchar(30);
declare vTechB varchar(30);
declare vTechC varchar(30);

select 'dbr.chart', 'bubble3d', '3D Bubble chart', 612, 345;
select 'dbr.chart.options', 'bubble.scale', 'y';

select 'dbr.chart.color', '0x00FF00', '0xFF0000','0x0000FF';

set vTechA = 'Techology A';
set vTechB = 'Techology B';
set vTechC = 'Techology C';

select 150 as 'x', 12 as 'y', 20 as 'r', vTechA as 'name', 'x1' as 'rn'
union
select 30, 60, 50, vTechA, 'x2'
union
select 300, 25, 50, vTechA, 'x3'
union
select 240, 125, 165, vTechA, 'x4'
union
select 50, 35, 30, vTechB, 'x5'
union
select 100, 50, 55, vTechB, 'x6'
union
select 130, 175, 95, vTechC, 'x7';

With the 'shading' option you can use CharDirector's chart shading feature

sselect 'dbr.chart', 'Pie3D', 'Shading Pie chart';
select 'dbr.chart.options', 'shading', 'ConcaveShading';
select 'dbr.chart.options', 'pie_label_format', '0';

select category, total_sales
from mydb.sales_by_film_category;

A donut-chart (or you can use alias 'doughnut'), with options

select 'dbr.chart', 'donut', '', 550, 350;

/* Set the donut thickness */
select 'dbr.chart.options', 'donut.size', 20;
/* Text in the middle */
select 'dbr.chart.options', 'donut.text', 'Income';
/* Font size of the text in the middle */
select 'dbr.chart.options', 'donut.font.size', 24;
/* Label font sizes */
select 'dbr.chart.options', 'label_font_size', 12;

select 'dbr.chart.color', '0x194996', '0x5F77BC', '0x9AA7DA', '0xE6E8F7', '0xF7F7FF';
select 'dbr.colstyle', 'Percentage', '{value} %\n{label}';

select Sector, Percentage
from mydb.income;

A basic chatter chart includes x, y, and z data.

select 'dbr.chart', 'scatter3d';

select x, y, z
from scatter_3d;

A scatter3d chart has multiple options so you can finetune the chart to suit your needs.

/* A scatter chart with multiple options */
select 'dbr.chart', 'msscatter3d', '', 760, 440;

select 'dbr.chart.options', 'axis', 'Date', 'Importance', 'Effect';
select 'dbr.chart.options', 'marker', 'Unsuccessful', 'Cross2Shape()', 15;
select 'dbr.chart.options', 'scatter3d.y_axis_labels', "Low", "Medium", "High";

select 'dbr.chart.color', '0x00ff00', '0x0000cc';

select 'dbr.chart.options', 'scatter3d.plot_area', 385, 210, 400, 280, 240;
select 'dbr.chart.options', 'scatter3d.view_angle', 15, 25;

select 'dbr.chart.options', 'axis_font' ,'arialbd.ttf';
select 'dbr.chart.options', 'axis_font_size' , 10;

select 'dbr.chart.options', 'label_font_size', 8;

select 'dbr.chart.options', 'scatter3d.drop_line', 'Successful', null, 'dot';
select 'dbr.chart.options', 'scatter3d.drop_line', 'Unsuccessful', '0x0000cc', 'altdash';

select label, x, y, z
from msscatter_3d;

In the most basic Gantt chart, we have labels with start and end dates

select 'dbr.chart', 'gantt', 'Gantt';

select label, cast(start as date), cast(end as date)
from (
  select 'Market Research' as 'label', '2017-03-16' as 'start', '2017-03-30' as 'end', 'Market' as 'stage'
  union
  select 'Market Research', '2017-06-02', '2017-06-20', 'Market'
  union
  select 'Define Specifications', '2017-03-30', '2017-04-13', 'Specification'
  union
  select 'Overall Archiecture', '2017-04-13', '2017-04-27', 'Specification'
  union
  select 'Project Planning', '2017-04-20', '2017-05-04', 'Detail Specification'
  union
  select 'Detail Design', '2017-04-27', '2017-05-11', 'Final Specification'
  union
  select 'Software Development', '2017-05-04', '2017-06-03', 'Development'
  union
  select 'Test Plan', '2017-05-25', '2017-06-03', 'Development'
  union
  select 'Testing and QA', '2017-06-01', '2017-06-22', 'Development'
  union
  select 'User Documentation', '2017-06-03', '2017-06-22', 'Development'
 ) as q;

If your Gantt-chart data covers only a day, the x-axis shows hours. You can set the scale with the gantt.start and gantt.end options-

select 'dbr.chart', 'gantt', 'Gantt hours', 600, 150;
select 'dbr.chart.options', 'gantt.scale', 1;
select 'dbr.chart.options','gantt.start', '2000-01-01 06:00:00';
select 'dbr.chart.options','gantt.end', '2000-01-01 21:00:00';

select label, cast(concat('2000-01-01 ',start) as datetime), cast(concat('2000-01-01 ',end) as datetime)
from (
  select 'Breakfast' as 'label', '07:00' as 'start', '07:20' as 'end'
  union
  select 'Lunch', '11:00', '10:30'
  union
  select 'Dinner', '17:45', '18:30'
 ) as q;

Gantt-chart has many options that you can use to configure the Gantt-chart.

select 'dbr.chart', 'gantt', 'Gantt with options', 800, 'auto';

select 'dbr.chart.options', 'category_order', 'Extra', 'Detail Specification';
select 'dbr.chart.options', 'category_order', 'Development';
select 'dbr.chart.options','gantt.baseline_text', 'Baseline text'; 
select 'dbr.chart.options','gantt.baseline_pattern.border', '0x000000'; /* Show no border for baseline bars */
select 'dbr.chart.options','gantt.baseline_pattern.color', '0x1B32C4'; /* Show baseline bars in dark blue */
select 'dbr.chart.options', 'gantt.start_of_day_format', '-{value|dd}'; /* Show dates as "Monthname dd" */
select 'dbr.chart.options','gantt.baseline', 'Market Research', '2017-03-12', '2017-03-20' /* Add baseline bars */
union
select 'dbr.chart.options','gantt.baseline', 'Overall Archiecture', '2017-04-08', '2017-04-26';
select 'dbr.chart.options', 'gantt.dateline', '2017-05-09'; /* Add vertical bar */
select 'dbr.chart.options', 'gantt.scale', 30; /* x-axis per month */
select 'dbr.chart.options','height_per_item', 30;

select label, cast(start as date), cast(end as date), stage
from (
  select 'Market Research' as 'label', '2017-03-16' as 'start', '2017-03-30' as 'end', 'Market' as 'stage'
  union
  select 'Market Research', '2017-06-02', '2017-06-20', 'Market'
  union
  select 'Define Specifications', '2017-03-30', '2017-04-13', 'Specification'
  union
  select 'Overall Archiecture', '2017-04-13', '2017-04-27', 'Specification'
  union
  select 'Project Planning', '2017-04-20', '2017-05-04', 'Detail Specification'
  union
  select 'Detail Design', '2017-04-27', '2017-05-11', 'Final Specification'
  union
  select 'Software Development', '2017-05-04', '2017-06-03', 'Development'
  union
  select 'Test Plan', '2017-05-25', '2017-06-03', 'Development'
  union
  select 'Testing and QA', '2017-06-01', '2017-06-22', 'Development'
  union
  select 'User Documentation', '2017-06-03', '2017-06-22', 'Development'
 ) as q;

You can format various parts of meter charts

You can choose between step and gradient colors.

Using step colors and custom color scales

select 'dbr.chart','meter';
/* Use step colors */
select 'dbr.chart.options', 'meter.color.type', 'step';

/* First step represents where the coloring starts, subsequent steps define next step and used color */
select 'dbr.chart.options', 'meter.color.step', step, color
from (
  select 10 as 'step', null as 'color'
  union
  select 40, '0xFFFF00'
  union
  select 70, '0x00FF00'
  union
  select 100, '0xFF0000'
) as q;

select 60;

Using gradient colors and custom color scales

select 'dbr.chart','meter';
/* Use of gradient colors is done by default, you can also define the color type to gradient*/
select 'dbr.chart.options', 'meter.color.type', 'gradient';

/* Use custom scale min, max, step */
select 'dbr.chart.options', 'scale', 20, 180, 20;

/* Choose white theme */
select 'dbr.chart.options', 'meter.theme', 'white';

/* Set gradient steps */
select 'dbr.chart.options', 'meter.color.gradient', step, color
from (
select 20 as 'step', '0xd3d3d3' as 'color'
union
select 40, '0xd3d3d3'
union
select 100, '0xa9a9a9'
union
select 180, '0x000000'
) as q;


select 60;

Meter themes (defined in defaults.php: default, white, black) allow defining various aspects of a chart

Using the black theme and setting text

select 'dbr.chart','rectangularmeter';
select 'dbr.chart.options', 'meter.text', 'CPU';
select 'dbr.chart.options', 'meter.theme', 'black';
select 'dbr.chart.options', 'scale', 20, 200, 20;

select 'dbr.chart','meter';

select 40;

You can set the linearmeter to be vertical

select 'dbr.chart','linearmeter';
select 'dbr.chart.options', 'linearmeter.orientation', 'vertical';

select 76;

Nodes represent the connected elements in Graphviz. If no node definitions are given, the chart with nodes is drawn with default Graphviz formatting.

In Graphviz charts there are two options for the data query:

Option 1 where the chart is drawn based on single values/node.

select node1, node2 [, edge_style]
from mydb.Data;

Option 2 where node contains both ID value and the visible value to the user. This can be used, when making charts to being exported and you want to keep the IDs.

select node1_id, node2_id, node1_name, node2_name [, edge_style]
from mydb.Data;

Here is a simple example of a hierarchical chart.

select 'dbr.chart', 'Hierarchy';

select 'Corporate', 'Sales'
union
select 'Corporate', 'Production'
union
select 'Corporate', 'Management'
union
select 'Sales', 'Northern Sales'
union
select 'Sales', 'Southern Sales'
union
select 'Production', 'Top part production'
union
select 'Production', 'Bottom part production'
union
select 'Extra', null;

Formatting needed for the Graphviz graph can be passed through the dbr.chart.gv.style command.

select 'dbr.chart.gv.style', 'graph', style_definition;

select 'dbr.chart', 'Hierarchy';
select 'dbr.chart.gv.style', 'graph', 'rankdir=LR,splines=false';

select 'Corporate', 'Sales'
union
select 'Corporate', 'Production'
union
select 'Corporate', 'Management'
union
select 'Sales', 'Northern Sales'
union
select 'Sales', 'Southern Sales'
union
select 'Production', 'Top part production'
union
select 'Production', 'Bottom part production';

The nodes can be formatted all with the same formatting or use specific formatting for individual nodes. In the next example, we'll change the default formatting and format selected nodes individually.

select 'dbr.chart.gv.node', nodeID, name, style_definition[, clusterID]

The style_definition is a comma-separated list of Graphviz-attributes.

select 'dbr.chart', 'Hierarchy';

/* This will change the default node formatting */

select 'dbr.chart.gv.style', 'node', 'shape=box,fillcolor=#CCCCFF,style=filled';
/* This will change formatting from individual nodes */

select 'dbr.chart.gv.node', 'Sales', 'Sales', 'shape=box3d,fillcolor=red,style=filled';
select 'dbr.chart.gv.node', 'Production', 'Production', 'shape=box3d,fillcolor=red,color=white,style=filled';

select 'Corporate', 'Sales'
union
select 'Corporate', 'Production'
union
select 'Corporate', 'Management'
union
select 'Sales', 'Northern Sales'
union
select 'Sales', 'Southern Sales'
union
select 'Production', 'Top part production'
union
select 'Production', 'Bottom part production';

Adding clusters to the chart can be done by using 'dbr.chart.gv.cluster' command. In addition to the cluster itself, the nodes attached to the cluster also needs to be defined.

select 'dbr.chart.gv.cluster', clusterID, name, style_definition

select 'dbr.chart', 'Hierarchy';
select 'dbr.chart.gv.style', 'graph', 'rankdir=TB,splines=true,ranksep=0.8'; 
select 'dbr.chart.gv.style', 'node', 'shape=box,fillcolor=#CCCCFF,style=filled,fontsize=10';

select 'dbr.chart.gv.node', 'Sales', 'Sales', 'shape=box,fillcolor=red,fontcolor=white,style="rounded,filled",fontsize=10', 1;
select 'dbr.chart.gv.node', 'Production', 'Production', 'shape=box,fillcolor=red,fontcolor=white,style="rounded,filled",fontsize=10', 1;

select 'dbr.chart.gv.cluster', 1, 'Organization', 'style=filled,fillcolor=lightgrey,labelloc=b,fontsize=12';

select 'Corporate', 'Sales'
union
select 'Corporate', 'Production'
union
select 'Corporate', 'Management'
union
select 'Sales', 'Northern Sales'
union
select 'Sales', 'Southern Sales'
union
select 'Production', 'Top part production'
union
select 'Production', 'Bottom part production';

Passing formatting information to edges can be done simply by adding the style information to the node-query.

select 'dbr.chart', 'Hierarchy';

select 'Corporate', 'Sales', 'color=red,arrowhead=empty'
union
select 'Corporate', 'Production', ''
union
select 'Corporate', 'Management', ''
union
select 'Sales', 'Northern Sales', ''
union
select 'Sales', 'Southern Sales', ''
union
select 'Production', 'Top part production', ''
union
select 'Production', 'Bottom part production', '';

myDBR allows you to select the layout engine used by using the command dbr.chart.gv.engine. See the Graphviz documentation for more info on layout engines and how to choose the most suitable one for your chart.

select 'dbr.chart.gv.engine', engine

Possible engines:

Example of engine usage:

declare vShape varchar(100)

/* Make it a network chart */

select 'dbr.chart', 'network';

/* Use the neato engine */

select 'dbr.chart.gv.engine', 'neato';

/* Prepare the shape, we'll get the color from the data */

select 'shape=octagon,style=filled,fillcolor=' into vShape;

/* Set individual node's color, no label */

select 'dbr.chart.gv.node', id, null, concat(vShape, color)
from mydb.nodes;

/* Get the chart data */

select parent, child
from mydb.node_connections;

Example: Same chart drawn with different layout engines.

The Graphviz file (gv-file), can be preprocessed. In the example, the file will be preprocessed with sfdp and gvmap. The $GV refers to the original gv-file. The preprocessed file will be piped to the renderer.

select 'dbr.chart', 'network', '', 1765, 1300;
select 'dbr.chart.gv.engine', 'neato';
select 'dbr.chart.gv.preprocess', 'sfdp -Goverlap=prism $GV |gvmap  -e';
select 'dbr.chart.gv.cmdoptions', '-Ecolor="#55555522" -n2';

select c.from_id, c.to_id, f.name, t.name
from gvmap_connections c
  join gvmap_nodes f on f.id=c.from_id
  join gvmap_nodes t on t.id=c.to_id
;

Report linking (clicking a node launches another report) is also available for Graphviz charts. The report's parameters must always be bound to the first column.

select 'dbr.chart', 'network';
select 'dbr.report', 'sp_DBR_NodeZoom','vNodeID=1';

select parent, child
from mydb.node_connections;

If you want to scale the size of a Graphviz-chart, you can use both 'dbr.chart' -commands x,y parameters or native Graphviz scaling (see size-attribute). If both are applied, myDBR uses the size-attribute.

select 'dbr.chart', 'network';
select 'dbr.chart.gv.style', 'graph', 'size="4,3"';
select 'dbr.report', 'sp_DBR_NodeZoom','vNodeID=1';

select parent, child
from mydb.node_connections;