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 myDBR's ChartDirector chart object ($c), the layer object ($layer), and the trend layers array ($trend_layers). Commands are passed as PHP code snippets using the '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 this example, we'll generate a simple pie chart. Like 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 multi-series charts, we have multiple Y-values. In the following example, we'll select data for each quarter for selected years and create a multi-series column chart from 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 swap places. We'll use the same query as in the previous example, and we'll transpose the chart before displaying it. This is done using the chart 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 both produce network charts. In a hierarchical chart, there is a parent-child connection, whereas in diagram charts, there is a connection between the nodes. Both charts are generated in the same way, with 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 queries MySQL's sakila-demo database system tables to create 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 minimum, maximum, 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 a box-and-whisker chart that draws independent boxes for each series or label with minimum and maximum values.

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 fine-tune 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;

A Gantt chart has many options that you can use to configure it.

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 for 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 involves drawing the chart based on single values/nodes.

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

Option 2 involves nodes containing both an ID value and a visible value to the user. This can be used when creating charts for export purposes, where retaining the IDs is necessary.

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 all be formatted with the same style, or specific formatting can be applied to 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 using the 'dbr.chart.gv.cluster' command. In addition to the cluster itself, the nodes attached to the cluster also need 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 accomplished 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. Refer to the Graphviz documentation for more information 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 this example, the file will be preprocessed with sfdp and gvmap. The $GV variable 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 either the 'dbr.chart' command's x and y parameters or the native Graphviz scaling (see the 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;